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

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

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.

Bare strings are rejected: default: "CURRENT_TIMESTAMP" is a compile error. Use SQL.expr("CURRENT_TIMESTAMP") or a lambda.

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

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

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) }
end

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

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 ?.

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.

---

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))

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

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]

  query :by_email,   ->(e : String)  { where(email: e) }
  query :in_tenant,  ->(t : Int64)   { where(tenant_id: t) }
  query :active,     ->              { where(status: "active") }
end

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

Named queries are validated against index coverage at migrate time. Every where field and order_by field must be covered by a declared index under the left-prefix rule.

---

Default values, backfills, and lazy fields

SQL expression defaults

field 3, :created_at, Time,   default: SQL.expr("CURRENT_TIMESTAMP")
field 4, :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.

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 5, :role, String,
  default:  SQL.expr("'member'"),
  backfill: SQL.expr("'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:  SQL.expr("'member'"),
  backfill: SQL.expr("'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:  SQL.expr("'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.

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:

---

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:

# 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.

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

---

Operator CLI

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

export DATABASE_URL=sqlite3://app.db

# Show migration history and drift summary
bin/prostore migrate status

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

# Abort a stuck in-progress migration (does NOT revert completed steps)
bin/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

# In-memory SQLite: just open a fresh connection per test
# File-based SQLite or PostgreSQL: DROP and recreate tables
conn.db.exec("DELETE FROM users")

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