prostore

A declarative ORM for Crystal targeting SQLite and PostgreSQL. The model is the single source of truth for schema state — you do not write migration files. Schema evolution is computed by diffing the desired state against the live database, with a protobuf-inspired discipline that makes renames safe and forbids in-place type changes.

The architectural decisions are recorded in doc/adr/. Read those for why; this README covers how.

---

Contents

1. Installation
2. Quick start
3. Defining a model
   • field
   • table_name
   • index
   • foreign_key
   • query
   • reserved tags
4. Type system
5. CRUD
6. Querying
   • Query builder
   • Predicates (Q module)
   • Named queries
7. Default values, backfills, and lazy fields
8. Schema evolution
9. Migration system
10. Drift detection
11. Connection
12. Operator CLI
13. Testing
14. Architecture

---

Installation

# shard.yml
dependencies:
  prostore:
    github: threez/prostore

shards install

require "prostore"

---

Quick start

require "prostore"

class User < Prostore::Model
  field 1, :id,    Int64,  primary: true, auto_increment: true
  field 2, :email, String
  field 3, :name,  String?

  index 1, [:email], unique: true
end

# Boot: migrate then connect (shares the same connection for in-memory SQLite)
Prostore.setup("sqlite3://app.db")

# Insert
u = User.allocate
u.email = "alice@example.com"
u.name  = "Alice"
u.save           # → INSERT; u.id is now set
u.persisted?     # → true

# Find
alice = User.find(u.id)        # raises if missing
alice = User.find?(u.id)       # returns nil if missing

# Update
alice.name = "Alice Smith"
alice.save       # → UPDATE

# Delete
alice.destroy

# Query
User.all.order_by(:email).to_a
User.where(email: "alice@example.com").first
User.where(Prostore::Q.gt(:id, 10)).count

---

Defining a model

class Post < Prostore::Model
  # ... fields, indexes, etc.
end

Every subclass is automatically registered in Prostore.models at compile time.

field

field <tag>, :<name>, <Type>, **opts

• tag (Int literal, required) — stable numeric identity; survives renames; never reuse a retired tag
• name (Symbol literal, required) — column name
• Type (Crystal type, required) — see Type system; T? declares nullable
• primary: (Bool) — marks the primary key column; at most one per model
• auto_increment: (Bool) — auto-assign PK on INSERT; requires primary: true and Int32/Int64; uses GENERATED BY DEFAULT AS IDENTITY on PostgreSQL, INTEGER PRIMARY KEY AUTOINCREMENT on SQLite
• default: — value for new rows; accepts scalar literal, symbol, SQL.expr(...), or lambda; see Default values
• backfill: — value for existing rows when adding a non-null column; accepts the same forms as default:
• lazy: (lambda) — compute-on-read field; requires T?; mutually exclusive with default:/backfill:

class User < Prostore::Model
  field 1, :id,         Int64,      primary: true, auto_increment: true
  field 2, :email,      String
  field 3, :created_at, Time,       default: :CURRENT_TIMESTAMP
  field 4, :status,     String,     default: "active", backfill: "active"
  field 5, :active,     Bool,       default: true,     backfill: true
  field 6, :score,      Int32?,     lazy: ->(_u : User) { compute_score(_u) }
  field 7, :nickname,   String?
  field 8, :slug,       String,     default: ->(_u : User) { _u.email.split("@").first }
end

Scalar literal auto-wrap: default: "active" becomes SQL 'active'; default: false becomes SQL false; default: 0 becomes SQL 0.

Symbol shorthand for SQL keywords/functions: :CURRENT_TIMESTAMP emits CURRENT_TIMESTAMP verbatim — no quotes. Use this for any SQL keyword or 0-argument function. Use SQL.expr("...") when you need a full expression with operators or arguments (gen_random_uuid(), column references, etc.).

Lambda shape: Lambda defaults and backfills receive the model instance as a single argument: ->(_m : MyModel) { ... }. 0-arg lambdas ->{ ... } are also accepted — prostore detects the arity at compile time.

table_name

Override the auto-derived table name (snake_case of the class name):

class EmailMessage < Prostore::Model
  table_name "messages"
  # ...
end

Without an override, Txmail::Account → txmail_account. Module separators :: become _. The prostore_ prefix is reserved.

index

index <tag>, [:<col>, ...], **opts

• tag (required) — stable numeric identity
• columns (required) — array of symbol column names; order matters for composite index left-prefix rules
• unique: — true to add UNIQUE; default false
• where: — SQL.expr("...") partial-index predicate (PostgreSQL / SQLite)
• name: — override the auto-generated name (<table>_<col1>_..._idx)

index 1, [:email],              unique: true
index 2, [:tenant_id, :status]
index 3, [:deleted_at],         where: SQL.expr("deleted_at IS NOT NULL")
index 4, [:slug],               name: "posts_slug_unique_idx", unique: true

On PostgreSQL, indexes are created with CREATE INDEX CONCURRENTLY IF NOT EXISTS to avoid locking the table.

foreign_key

foreign_key <tag>, [:<col>, ...], references: TargetModel, **opts

• tag (required) — stable numeric identity
• columns (required) — local columns participating in the constraint
• references: (required) — target Prostore::Model subclass
• references_fields: — target columns (defaults to the target's primary key)
• on_delete: — :no_action (default), :restrict, :cascade, :set_null, :set_default
• on_update: — same options as on_delete:
• name: — override the auto-generated constraint name (<table>_<col>_fkey)

class Comment < Prostore::Model
  field 1, :id,      Int64, primary: true, auto_increment: true
  field 2, :post_id, Int64
  field 3, :body,    String

  index 1, [:post_id]
  foreign_key 1, [:post_id], references: Post, on_delete: :cascade
end

SQLite enforces PRAGMA foreign_keys = ON on every connection automatically.

query

Declare a named, statically-analysed query. The block receives a query builder via with builder yield, so where, order_by, limit, etc. dispatch directly without a receiver.

query :<name>, ->(<args>) { <builder chain> }

class User < Prostore::Model
  query :by_email,      ->(e : String)  { where(email: e) }
  query :active,        ->              { where(status: "active") }
  query :top_in_tenant, ->(t : Int64)   { where(tenant_id: t).order_by(:score, desc: true).limit(10) }
  query :recent,        ->(t : Time)    { where(Q.lt(:created_at, t)).order_by(:created_at, desc: true) }
end

User.by_email("alice@example.com").first
User.active.count
User.top_in_tenant(42_i64).to_a
User.recent(1.hour.ago).to_a

Comparison predicates (Q.lt, Q.gt, Q.lte, Q.gte, Q.ne, Q.in, Q.like) are fully supported inside named query bodies. The macro walker extracts the field name from the predicate for index-coverage analysis.

At migrate time, every named query is validated: each filtered or sorted field must be covered by a declared index (ADR-0006 strict mode, left-prefix rule). A SchemaError is raised if coverage is missing — add an index declaration before or alongside the query.

reserved tags

Permanently retire a tag when you remove a field, index, or foreign key. Omitting a tag without reserving it is a runtime error.

reserved 3              # field tag 3 is permanently retired
reserved_index 2        # index tag 2 is permanently retired
reserved_foreign_key 1  # FK tag 1 is permanently retired

Retired tags may never be reused on the same model.

---

Type system

Prostore maps Crystal types to a portable type tag, which is then translated to backend-specific DDL. Nullable types are declared by appending ?.

• Int32 → tag int32, SQLite INTEGER, PostgreSQL INTEGER
• Int64 → tag int64, SQLite INTEGER, PostgreSQL BIGINT
• Float32 → tag float32, SQLite REAL, PostgreSQL REAL
• Float64 → tag float64, SQLite REAL, PostgreSQL DOUBLE PRECISION
• String → tag string, SQLite TEXT, PostgreSQL TEXT
• Bool → tag bool, SQLite INTEGER, PostgreSQL BOOLEAN
• Time → tag time, SQLite TEXT, PostgreSQL TIMESTAMP WITH TIME ZONE
• Bytes / Slice(UInt8) → tag bytes, SQLite BLOB, PostgreSQL BYTEA
• UUID → tag uuid, SQLite TEXT (36-char), PostgreSQL UUID
• BigDecimal → tag decimal, SQLite TEXT, PostgreSQL NUMERIC
• JSON::Any → tag json, SQLite TEXT, PostgreSQL JSONB
• Array(T) (T = any above) → tag array_<T>, SQLite TEXT (JSON-encoded), PostgreSQL JSONB
• Any Enum subclass (default) → tag enum_string, SQLite TEXT (member name), PostgreSQL TEXT
• Any Enum subclass, as: :int → tag enum_int, SQLite INTEGER (member value), PostgreSQL BIGINT

Time on PostgreSQL is TIMESTAMP WITH TIME ZONE (timestamptz). Crystal's Time is always UTC.

Array types use JSONB on PostgreSQL and JSON-encoded TEXT on SQLite. Native PostgreSQL arrays (e.g., INTEGER[]) are not used — JSONB provides a uniform IO path across both backends.

Reads return Array(T) directly and writes accept Array(T) — encoding to JSON happens at the model boundary, so callers never touch to_json / from_json.

Array literals are not accepted as default: values (the parser only takes scalar literals, symbols, SQL.expr(...), or a lambda). For an "always an array, possibly empty" field, use a 0-arg lambda:

field 9, :domain_ids, Array(String), default: ->{ [] of String }

Alternatively, declare the field nullable (Array(String)?) — the default is then auto-inferred as NULL, at the cost of nil-checking every read site.

Enums (ADR-0016)

Any Crystal Enum subclass — including @[Flags] enums — is a valid field type. Storage is name-backed (TEXT) by default; as: :int selects integer storage. The declared member set becomes part of the schema and is enforced by a named CHECK constraint on the column.

enum Status
  Active
  Pending
  Archived
end

enum Tier
  Bronze
  Silver = 5
  Gold   = 10
end

@[Flags]
enum Perms
  Read
  Write
  Execute
end

class User < Prostore::Model
  field 1, :id,     Int64,  primary: true, auto_increment: true
  field 2, :status, Status                 # TEXT, CHECK IN ('Active', 'Pending', 'Archived')
  field 3, :tier,   Tier,   as: :int       # INTEGER, CHECK IN (0, 5, 10)
  field 4, :perms,  Perms                  # @[Flags] → INTEGER, CHECK 0..7 (1|2|4)
end

Reads return the Crystal Enum value directly; writes accept it directly. No manual to_s / from_value at call sites.

Evolution: members can be added freely — migration emits a CHECK-swap step that widens the accepted set, existing rows untouched. Members cannot be removed, renamed, or have their integer value changed in place (existing rows may carry the old value; ADR-0003 + ADR-0016). The validator surfaces these as SchemaError before any DDL runs and points at the reserve-and-readd workflow.

@[Flags] enums are implicitly int-backed (any combination via | must round-trip through the integer wire form). Passing as: :string for a flags enum is a compile error.

Wire format — naming: (ADR-0017)

By default, enum_string columns store the source-level member name ("BounceHard"). Pass naming: to translate to a different convention — useful when the column is exposed verbatim to external surfaces (JSON APIs, Prometheus labels, HTML <option value> markup) that already use snake_case or lower_case:

enum Reason
  Active
  BounceHard
  ComplaintAbuse
end

class Suppression < Prostore::Model
  field 1, :id,     Int64,  primary: true, auto_increment: true
  field 2, :reason, Reason, naming: :snake_case
  # → stored as "active" / "bounce_hard" / "complaint_abuse"
  # → CHECK (reason IN ('active', 'bounce_hard', 'complaint_abuse'))
end

Supported algorithms:

• :as_declared (default) — BounceHard
• :snake_case — bounce_hard
• :kebab_case — bounce-hard
• :lower_case — bouncehard

The Crystal source-level name remains the canonical identifier in your code (Reason::BounceHard). The naming: option only changes how the value is stored and how it appears in the CHECK constraint. Reads and writes via the ORM are transparent — you continue to assign and read Crystal enum values directly.

naming: only applies to enum_string columns — int-backed and @[Flags] enums store integers, so there's no name to translate. The macro rejects naming: on those at compile time.

Changing naming: on an existing column is a data migration. The stored bytes encode the old wire form; switching the algorithm without rewriting existing rows would either fail the CHECK constraint or silently corrupt reads. The validator detects this and raises a SchemaError with the recommended workflow (UPDATE the column to the new wire values via raw SQL, then change the declaration). See ADR-0017 for the full discipline.

---

CRUD

All CRUD methods use Prostore.default_connection. Set it at boot via Prostore.connect(url) or Prostore.setup(url).

Creating records

u = User.allocate
u.email = "bob@example.com"
u.save        # INSERT; lambda defaults are evaluated here
u.id          # assigned by the DB (auto_increment) or set before save
u.persisted?  # true after a successful save

save decides INSERT vs UPDATE based on persisted?, not the PK value. A record is not persisted until save succeeds. This means user-assigned (non-auto-increment) primary keys work correctly:

class Tag < Prostore::Model
  field 1, :id,    String, primary: true
  field 2, :label, String
end

t = Tag.allocate
t.id = "tag:featured"
t.label = "Featured"
t.save           # INSERT — persisted? was false
t.save           # UPDATE — persisted? is now true

Updating records

user = User.find(1_i64)
user.email = "new@example.com"
user.save        # UPDATE

Deleting records

user.destroy     # DELETE WHERE id = <pk>

Finding records

User.find(42_i64)     # returns User, raises Prostore::Error if not found
User.find?(42_i64)    # returns User?, nil if not found

All records

User.all            # Builder(User) — not yet executed
User.all.to_a       # executes SELECT *

---

Querying

Query builder

All query methods return a new Builder(T) — each call is immutable and chainable. Execution happens when a terminal method is called.

Filtering

# Named-arg equality (shorthand for common cases)
User.where(status: "active")
User.where(tenant_id: 1_i64, status: "active")  # implicit AND

# Range → BETWEEN / < / <=
User.where(score: 10..100)    # 10 <= score <= 100
User.where(score: 10...100)   # 10 <= score < 100

# Array → IN (...)
User.where(status: ["active", "pending"])

# Nil → IS NULL
User.where(deleted_at: nil)

# Arbitrary predicate
User.where(Prostore::Q.gt(:score, 50))

# Multiple where calls are ANDed together
User.where(tenant_id: 1_i64).where(Prostore::Q.gt(:score, 10))

Pure-equality predicates collapse into a single where — kwargs are themselves ANDed, so prefer the one-call form:

# Verbose
User.where(id: id).where(token: token).where(status: "active")

# Equivalent, preferred
User.where(id: id, token: token, status: "active")

Chain only when one of the predicates can't live in kwargs (e.g., Q.gt, Q.or, Q.not).

Ordering

User.order_by(:email)               # ASC
User.order_by(:score, desc: true)   # DESC
User.order_by(:tenant_id).order_by(:score, desc: true)  # multiple

Paging

User.limit(10)
User.offset(20).limit(10)

Column projection

users = User.all.select(:id, :email).to_a
# Other ivars are nil; accessing a non-projected non-nullable field raises

Joins

FK-resolved join (automatically determines join columns from declared FKs):

# User has posts via Post.foreign_key 1, [:user_id], references: User
User.all.joins(Post).where(Prostore::Q.gt(:total, 100)).to_a

If multiple FKs exist between two models, pass fk_tag: to disambiguate:

User.all.joins(Post, fk_tag: 2)

Explicit join with raw column lists:

User.all.joins("orders", ["id"], ["user_id"])

Terminal methods

• to_a (Array(T)) — execute and return all rows
• each { |row| } (Nil) — stream rows without building an array
• first (T?) — first row or nil
• first! (T) — first row, raises if none
• count (Int64) — SELECT COUNT(*)
• empty? (Bool) — count.zero?
• exists? (Bool) — !empty?

Predicates (Q module)

Prostore::Q (aliased as Q at top level) provides predicate constructors for use with where(predicate) or inside named query blocks.

# Equality / comparison
Q.eq(:status, "active")        # status = 'active'
Q.ne(:status, "deleted")       # status != 'deleted'
Q.lt(:score, 10)               # score < 10
Q.lte(:score, 10)              # score <= 10
Q.gt(:score, 100)              # score > 100
Q.gte(:score, 100)             # score >= 100

# Membership
Q.in(:status, ["active", "pending"])

# Null checks
Q.null?(:deleted_at)           # deleted_at IS NULL
Q.not_null?(:deleted_at)       # deleted_at IS NOT NULL

# Pattern match
Q.like(:email, "%@example.com")

# Boolean combinators
Q.all(Q.gt(:score, 10), Q.lt(:score, 100))   # AND
Q.any(Q.eq(:status, "active"), Q.eq(:status, "pending"))  # OR
Q.not(Q.eq(:status, "deleted"))              # NOT

Predicates compose arbitrarily:

User.where(
  Q.all(
    Q.eq(:tenant_id, 1_i64),
    Q.any(Q.eq(:status, "active"), Q.eq(:status, "trial")),
    Q.not(Q.is_null(:email))
  )
)

Named queries

class User < Prostore::Model
  index 1, [:email],      unique: true
  index 2, [:tenant_id]
  index 3, [:status]
  index 4, [:created_at]

  query :by_email,    ->(e : String)  { where(email: e) }
  query :in_tenant,   ->(t : Int64)   { where(tenant_id: t) }
  query :active,      ->              { where(status: "active") }
  query :created_before, ->(t : Time) { where(Q.lt(:created_at, t)).order_by(:created_at, desc: true) }
end

User.by_email("alice@example.com").first
User.in_tenant(7_i64).order_by(:email).to_a
User.active.count
User.created_before(1.week.ago).to_a

Named queries are validated against index coverage at migrate time. Every where field (including comparison predicate fields like Q.lt(:col, val)) and order_by field must be covered by a declared index under the left-prefix rule.

---

Default values, backfills, and lazy fields

default: accepts four forms. They differ in when the value is computed and who computes it — pick the form that matches your need:

• Scalar literal (true, 42, "active") — DDL DEFAULT emitted; ivar seeded on .new + .save; applies on raw SQL INSERT
• Symbol (:CURRENT_TIMESTAMP, :gen_random_uuid) — DDL DEFAULT emitted verbatim; DB-side only [†]; applies on raw SQL INSERT
• SQL.expr("…") — DDL DEFAULT emitted verbatim; DB-side only [†]; applies on raw SQL INSERT
• Crystal lambda ->(_m) { … } — no DDL DEFAULT; lambda runs before INSERT; ORM-only (not applied on raw SQL INSERT)

[†] The macro always lists every non-PK column in the ORM's INSERT, so the DDL DEFAULT is shadowed by the explicit NULL bound from the unset ivar. If you need a DB-computed default to also apply on .new + .save, wrap it in a lambda (default: ->{ Time.utc }).

Scalar literal defaults

Bool, Int, and String literals are auto-wrapped to their SQL equivalents and captured as Crystal values so .new + .save seeds the ivar before INSERT (otherwise the DDL DEFAULT would be shadowed by the explicit NULL):

field 3, :active, Bool,   default: false,    backfill: false
field 4, :count,  Int32,  default: 0,        backfill: 0
field 5, :role,   String, default: "member", backfill: "member"

u = User.allocate
u.email = "a@b.com"
u.save           # → INSERT (..., role) VALUES (..., 'member')
User.find(u.id).role   # => "member"

Symbol and SQL.expr defaults (DB-side only)

Symbol literals are emitted verbatim as SQL keywords or 0-argument functions — no quotes added:

field 6, :created_at, Time,  default: :CURRENT_TIMESTAMP
field 7, :uuid_col,   String, default: :gen_random_uuid   # PostgreSQL

Use SQL.expr("...") when the default must be a full SQL expression with operators, arguments, or column references:

field 8, :created_at, Time,   default: SQL.expr("CURRENT_TIMESTAMP")
field 9, :status,     String, default: SQL.expr("'active'")

The expression is emitted verbatim as a column-level DEFAULT (...) clause. Existing rows are unaffected when you add such a column — the default only applies to new inserts. These forms do not seed the ivar on .new + .save — the macro emits every column in the INSERT, so the DB sees an explicit NULL and never falls back to the DEFAULT clause. Wrap in a lambda if you need the value applied through the ORM:

field 6, :created_at, Time, default: ->{ Time.utc }

SQL expression backfills

When adding a non-null column to a table that already has rows, declare both default: (for new rows) and backfill: (for existing rows):

field 8, :role, String,
  default:  "member",
  backfill: "member"

If default: and backfill: are identical SQL expressions, the migration is a single ADD COLUMN … NOT NULL DEFAULT (…) step (SQLite and PostgreSQL both apply the default to existing rows in this case).

If they differ, the migration uses three steps: add nullable → server-side UPDATE → apply NOT NULL:

field 6, :verified, String,
  default:  SQL.expr("'pending'"),
  backfill: SQL.expr("CASE WHEN email LIKE '%@verified.example' THEN 'verified' ELSE 'pending' END")

Crystal-lambda defaults

A lambda runs in Crystal before the INSERT, not at the DB level:

field 7, :slug, String,
  default: ->(_u : User) { _u.email.split("@").first }

0-arg lambdas are also supported:

field 8, :token, String,
  default: ->{ Random::Secure.hex(32) }

Crystal-lambda backfills

The lambda receives each existing row as a model instance:

field 9, :score, Int32,
  default:  SQL.expr("0"),
  backfill: ->(row : User) { compute_score(row) }

The runner materializes rows in chunks, calls the lambda for each, and writes the result back. The WHERE col IS NULL filter makes the loop idempotent.

Lazy fields

A lazy field is stored as NULL in the database. The lambda runs on the first read of the field and persists the value:

field 10, :badge, String?,
  lazy: ->(_u : User) { derive_badge(_u) }

Lazy fields:

• Must be nullable (T?).
• Cannot be used in where or order_by (compile-time error via ADR-0006).
• If a named query references a lazy field non-projectionally, the analyzer overrides it to eager and emits a diagnostic at migrate time.
• Are mutually exclusive with default: and backfill:.

---

Schema evolution

All schema changes are expressed by editing the model. prostore computes the diff at boot and plans the necessary DDL steps.

Add a nullable column

field 5, :nickname, String?

One step: ALTER TABLE users ADD COLUMN nickname TEXT.

Add a non-null column (same default and backfill)

field 6, :role, String,
  default:  "member",
  backfill: "member"

One step: ALTER TABLE users ADD COLUMN role TEXT NOT NULL DEFAULT ('member'). Existing rows get the default.

Add a non-null column (different backfill)

field 7, :verified, String,
  default:  "pending",
  backfill: SQL.expr("CASE WHEN email LIKE '%@verified.example' THEN 'verified' ELSE 'pending' END")

Three steps: add nullable → server-side UPDATE with backfill expression → rebuild to apply NOT NULL with default.

SQLite + foreign keys: if the target table is referenced by a FK from another table, the three-step path fails on SQLite because foreign_keys=ON prevents the table rebuild. prostore raises a clear MigrationError at plan time listing the referencing tables and both fix strategies (declare the field nullable, or use identical default:/backfill: to take the single-step path).

Add a non-null column with Crystal-lambda backfill

field 8, :score, Int32,
  default:  SQL.expr("0"),
  backfill: ->(row : User) { compute_score(row) }

Three steps: add nullable → chunked Crystal-side backfill loop → apply NOT NULL.

Rename a field

Change the symbol in the field declaration; the tag stays the same:

field 2, :handle, String   # was :email; tag 2 unchanged

One step: ALTER TABLE users RENAME COLUMN email TO handle. Data is preserved.

Remove a field

Reserve the tag. The column is dropped:

reserved 3   # column tenant_id (tag 3) will be dropped

One step: ALTER TABLE users DROP COLUMN tenant_id. Tag 3 may never be reused.

Replace a field (old → new pattern)

When you need a new type or default for an existing column, add a new tag and keep both during a transition window:

reserved 9                  # :legacy_email retired
field 10, :email_v2, String,
  default:  ->(u : User) { u.email },
  backfill: SQL.expr("legacy_email")

Rename an index

Change the name: option (or rename the columns — but columns must be reserved and re-added to change composition):

index 1, [:email], unique: true, name: "users_email_unique"  # was "users_email_idx"

One step: ALTER INDEX … RENAME TO … (PostgreSQL) or rebuild (SQLite).

Remove an index

reserved_index 2    # index tag 2 will be dropped

Remove a foreign key

reserved_foreign_key 1    # FK tag 1 will be dropped

---

Migration system

Running migrations

# URL-based: opens a temporary connection, migrates, closes it.
# For file-based databases, use this at startup before Prostore.connect.
Prostore.migrate("postgres://user:pass@host/dbname")

# Connection-based: migrate on a pre-opened connection.
conn = Prostore::Connection.open(url)
Prostore.migrate(conn)

# Combined: open, migrate, set as default connection, return it.
# Use for sqlite3::memory: — all three operations share one connection.
conn = Prostore.setup("sqlite3::memory:?max_pool_size=1")

Boot sequence

When migrate or setup runs:

1. Ensure bookkeeping tables (prostore_migration, prostore_migration_step, prostore_schema) exist.
2. Compute the target schema fingerprint over all registered models.
3. Check for an in-progress migration:
   • If yes: verify target fingerprint matches; resume from the first incomplete step.
   • If no: validate model changes, diff, plan, persist the plan, start.
4. Claim a time-bounded lease (claimed_until; 5-minute default). Another process holding a live lease causes an immediate error.
5. Execute each step in ordinal order: mark_running → execute → mark_complete → heartbeat.
6. Mark migration complete and release the lease.

Resuming crashed migrations

Steps are persisted before any DDL runs (plan-at-start). If the process crashes mid-migration, the next boot finds the in-progress migration and continues from the first non-complete step. Each step is independently transactional and idempotent.

Concurrent safety

The lease mechanism (a time-bounded row lock in prostore_migration) ensures only one runner executes at a time. A background heartbeat fiber renews the lease every 30 seconds. If the runner crashes without releasing the lease, the next runner can steal it after the TTL expires.

A version-skew check prevents a new deployment from attaching to a migration started by an old schema version: if the target fingerprints differ, the new runner refuses to start.

Schema fingerprint

The fingerprint is a SHA-256 hash over field tags, types, nullability, primary key, auto_increment, default SQL, backfill SQL, and lazy flags — but not over field names. A rename does not change the fingerprint. The fingerprint identifies the target state, not the labeling.

---

Drift detection

If the database is modified outside of prostore (a DBA renames a column, drops an index, etc.), the next migrate call detects the drift:

• Managed column renamed externally — auto-fix: rename back
• Managed column dropped externally — Error: data was lost; restore or reserve the tag
• Managed column type/nullability changed externally — Error: in-place changes are forbidden (ADR-0003)
• Managed index dropped — auto-fix: recreate
• Managed index renamed externally — auto-fix: rename back
• Managed index UNIQUE flag changed — Error: definition change requires a new tag
• Unmanaged column, index, or table — tolerated

---

Connection

Opening a connection

conn = Prostore::Connection.open("sqlite3://app.db")
conn = Prostore::Connection.open("postgres://user:pass@host/dbname")

Default connection

Prostore.connect("sqlite3://app.db")        # set default connection
Prostore.default_connection                 # retrieve it
Prostore.default_connection = conn          # set directly (useful in tests)

CRUD and query methods use the default connection automatically. The migration runner also accepts an explicit Connection.

Connection methods

Connection exposes the underlying pool via .db and also delegates the most common methods directly:

conn.exec("INSERT INTO …", arg1, arg2)
conn.scalar("SELECT COUNT(*) FROM users")
conn.query_one("SELECT * FROM users WHERE id = ?", 1_i64) { |rs| … }
conn.query_one?("SELECT …", id, as: String)  # nil-safe
conn.query_each("SELECT * FROM …") { |rs| … }
conn.transaction { |tx| … }
conn.with_connection { |db_conn| … }   # pins all DDL to one connection
conn.db                                # DB::Database (full crystal-db API)
conn.adapter                           # Prostore::Adapter::Base
conn.close

SQLite URL parameters

Several SQLite pragmas can be passed as URL query parameters and are applied to every connection via PRAGMA:

• journal_mode — PRAGMA journal_mode = <value>
• synchronous — PRAGMA synchronous = <value>
• cache_size — PRAGMA cache_size = <value>
• temp_store — PRAGMA temp_store = <value>

# Enable WAL mode
Prostore.setup("sqlite3://app.db?journal_mode=wal&synchronous=normal")

Note: sqlite3::memory: does not support WAL mode; the pragma is silently ignored by SQLite in that case.

SQLite in production (concurrent access)

SQLite's default journal mode (delete) serializes all access — readers and the writer block each other, and connections fail immediately with SQLITE_BUSY when another connection holds a lock (busy_timeout defaults to 0). For any production workload with concurrent readers alongside a writer, enable WAL mode and set a non-zero busy_timeout:

Prostore.setup("sqlite3://app.db?journal_mode=wal&synchronous=normal&busy_timeout=5000")

• journal_mode=wal — readers never block the writer; the writer never blocks readers
• synchronous=normal — safe with WAL (checkpoints are still fsync'd); faster than full
• busy_timeout=5000 — writer retries for up to 5 s on lock contention instead of failing immediately

Increase max_pool_size (via URL) to allow Crystal fibers to hold multiple concurrent reader connections:

Prostore.setup("sqlite3://app.db?journal_mode=wal&synchronous=normal&busy_timeout=5000&max_pool_size=10")

There is no separate read-replica configuration — all connections share the same pool and adapter. WAL is the mechanism that makes concurrent reads safe.

In-memory SQLite

sqlite3::memory: creates a fresh database per DB.open call. To use an in-memory database for tests, use Prostore.setup so migration and app queries share the same connection:

url = "sqlite3::memory:?max_pool_size=1&initial_pool_size=1&max_idle_pool_size=1"
conn = Prostore.setup(url, [User, Post] of Prostore::Model.class)
# conn is now both migrated and set as default_connection

---

Backup

Prostore::Backup.run writes a point-in-time backup of the database to a destination path. The destination may contain strftime tokens (%Y %m %d %H %M %S), which are expanded to the current UTC time — so a single cron line produces naturally-rotated, timestamped files:

path = Prostore::Backup.run(conn, "/var/backups/app_%Y%m%d_%H%M%S.db")
# => "/var/backups/app_20260514_103045.db"

Or via the operator CLI:

prostore backup /var/backups/app_%Y%m%d_%H%M%S.db
# Backup written to /var/backups/app_20260514_103045.db

SQLite

Uses VACUUM INTO — an online, non-blocking backup that works under WAL mode and produces a defragmented copy. Requires SQLite 3.27.0 or later (released 2019).

PostgreSQL

Shells out to pg_dump (plain-text format). pg_dump must be available in PATH. Connection parameters (host, port, user, database) are derived from DATABASE_URL; the password is passed via PGPASSWORD.

Cron examples

# SQLite — hourly backup (one file per hour, 24 kept by filename convention)
0 * * * * DATABASE_URL=sqlite3:///var/app/app.db \
  /path/to/prostore backup /var/backups/app_%Y%m%d_%H.db

# SQLite — every 6 hours
0 */6 * * * DATABASE_URL=sqlite3:///var/app/app.db \
  /path/to/prostore backup /var/backups/app_%Y%m%d_%H.db

# PostgreSQL — daily dump at 03:00
0 3 * * * DATABASE_URL=postgres://user:pass@localhost/mydb \
  /path/to/prostore backup /var/backups/mydb_%Y%m%d.sql

Use OS-level tools (find -mtime, logrotate) to prune old backups.

---

Operator CLI

The prostore binary exposes operator commands. Set DATABASE_URL in the environment:

export DATABASE_URL=sqlite3://app.db

# Show migration history and drift summary
prostore migrate status

# Dry-run drift check (no DDL applied)
prostore drift check

# Abort a stuck in-progress migration (does NOT revert completed steps)
prostore migrate abort 42

The same operations are available programmatically:

conn = Prostore::Connection.open(ENV["DATABASE_URL"])
Prostore::Migration::CLI.status(conn)
Prostore::Migration::CLI.drift_check(conn)
Prostore::Migration::CLI.abort(conn, migration_id: 42_i64)

---

Testing

SQLite in tests

Use a temp file or in-memory database with Prostore.setup:

# Temp file (supports WAL, isolated per process)
TEST_DB = "/tmp/myapp_test_#{Process.pid}.db"
conn = Prostore.setup("sqlite3://#{TEST_DB}", [User, Post] of Prostore::Model.class)

# In-memory (fastest, but requires setup to share the connection)
conn = Prostore.setup("sqlite3::memory:?max_pool_size=1", [User] of Prostore::Model.class)

Resetting state between tests

Prostore.delete_all (aliased as Prostore.test_reset) deletes all rows from the given model set in reverse FK dependency order so child rows are always removed before their parent rows, even with foreign_keys=ON:

ALL_MODELS = [User, Post, Comment] of Prostore::Model.class

after_each do
  Prostore.test_reset(ALL_MODELS, conn)
end

Both methods accept an explicit Connection. If omitted, the process-wide default connection is used.

Setting the default connection in specs

before_each do
  conn = Prostore.setup(TEST_URL, MODELS)
end

after_each do
  Prostore.default_connection = nil
  conn.close
end

Testing against PostgreSQL

Set POSTGRES_URL in the environment:

POSTGRES_URL=postgres://user:pass@localhost/test_db crystal spec

---

Architecture

DSL (macros + Schema types)
  ↓ compile time
Schema::Definition (Class.prostore_schema)
  ↓ runtime planning
Diff::Engine + Diff::Validator + Drift::Detector + Query::Analyzer
  ↓ produces
Operation list → Steps::Planner → Step list
  ↓ persisted to prostore_migration_step
Steps::Executor (per backend) — each step in its own transaction
  ↓
Live database; prostore_schema bookkeeping updated atomically

Key invariants:

• Tags are the stable identity of fields, indexes, and foreign keys — not names.
• In-place type changes and nullability changes are forbidden (ADR-0003).
• The plan is persisted before any DDL runs; crashes resume from the last completed step (ADR-0009).
• Every named query must have index coverage; the runner enforces this at boot (ADR-0006).
• prostore_ is a reserved prefix for table names and field names.

See doc/adr/ for the full design rationale.

---

Development

shards install
crystal spec spec/unit/                        # unit tests
crystal spec spec/integration/sqlite/          # SQLite integration tests
POSTGRES_URL=postgres://... crystal spec       # full matrix

---

License

MIT