Databases

Draft

Declare a SQLite schema and use its generated, typed API.

On this page · 10 sections

A Ship database declaration is both a schema and an application API. The dialect is explicit; this page uses SQLite for a small local todo database. Postgres has its own broader surface rather than pretending both dialects are interchangeable.

Continue with PostgreSQL schemas and the checked-in, snapshot-addressed ship db migration workflow. Parallel changes to different tables, changes to one table, and migration conflicts walk through PR integration case by case. Transactions shows how writes go through an explicit transaction.

A schema

Fields describe stored values. Metadata describes facts the compiler, SQL generator, migrations, and generated operations all share.

database AppDb(Sqlite) {
  table Todos {
    id: Int #{
      primary = True;
      rowid = True;
      autoincrement = True;
    };

    title: String;
    note: Option(String);
    completed: Bool #{ default = False; };
  }
}

Generated table operations

The declaration creates Row, Insert, and Update types under AppDb.Todos. It also creates operations from the schema facts. insert accepts no id because SQLite generates it, and callers may omit completed because the field has a default. A table with a primary key also gets find, which returns the one row with that key or None. Every operation takes the transaction it runs in as its first argument.

let add_todoadd_todo: fn(tx: Database.Transaction(AppDb), title: String) -> AppDb.Todos.Row, exn! tx [> :db([ :rejected(String) | :unique(String) ]) ] = (txtx: Database.Transaction(AppDb), titletitle: String) =>
  AppDb.Todos.insertinsert: fn(tx: Database.Transaction(AppDb), input: AppDb.Todos.Insert) -> AppDb.Todos.Row, exn! tx [> :db([ :rejected(String) | :unique(String) ]) ](txtx: Database.Transaction(AppDb), { title = titletitle: String; note = None; });

let complete_todocomplete_todo: fn(tx: Database.Transaction(AppDb), id: Int) -> AppDb.Todos.Row, exn [> :db.not_found(Int) ], exn! tx [> :db([ :rejected(String) | :unique(String) ]) ] = (txtx: Database.Transaction(AppDb), idid: Int) =>
  AppDb.Todos.updateupdate: fn(tx: Database.Transaction(AppDb), id: Int, patch: AppDb.Todos.Update) -> AppDb.Todos.Row, exn [> :db.not_found(Int) ], exn! tx [> :db([ :rejected(String) | :unique(String) ]) ](txtx: Database.Transaction(AppDb), idid: Int, { completed = TrueTrue: Bool; });

Generated Table API

test✓

  ✓add_todoadd_todo: fn(tx: Database.Transaction(AppDb), title: String) -> AppDb.Todos.Row, exn! tx [> :db([ :rejected(String) | :unique(String) ]) ]: fn(tx: Database.Transaction(AppDb), title: String) -> AppDb.Todos.Row,
    exn! tx [> :db([ :rejected(String) | :unique(String) ]) ];
  ✓complete_todocomplete_todo: fn(tx: Database.Transaction(AppDb), id: Int) -> AppDb.Todos.Row, exn [> :db.not_found(Int) ], exn! tx [> :db([ :rejected(String) | :unique(String) ]) ]: fn(tx: Database.Transaction(AppDb), id: Int) -> AppDb.Todos.Row,
    exn [> :db.not_found(Int) ],
    exn! tx [> :db([ :rejected(String) | :unique(String) ]) ];
  ✓AppDb.Todos.allall: fn(tx: Database.Transaction(AppDb)) -> Vector(AppDb.Todos.Row), exn! tx [> :db.rejected(String) ]: fn(tx: Database.Transaction(AppDb)) -> Vector(AppDb.Todos.Row),
    exn! tx [> :db.rejected(String) ];
  ✓AppDb.Todos.findfind: fn(tx: Database.Transaction(AppDb), id: Int) -> Option(AppDb.Todos.Row), exn! tx [> :db.rejected(String) ]: fn(tx: Database.Transaction(AppDb), id: Int) -> Option(AppDb.Todos.Row),
    exn! tx [> :db.rejected(String) ];
  ✓AppDb.transactiontransaction: fn!() -> Database.Transaction(AppDb), eff AppDb: fn!() -> Database.Transaction(AppDb), eff AppDb;
  ✓AppDb.test_databasetest_database: pure fn!() -> Service.Provider(AppDb): pure fn!() -> Service.Provider(AppDb);

eff AppDb says opening a transaction needs this particular database service. It does not collapse every SQLite database into one global capability. An operation needs only its transaction: holding tx is the capability to use it.

Queries and updates

A SQLite declaration also creates test_database. The provider opens a new in-memory database, applies this declaration's generated schema before each test, and closes the connection afterward. Tests use the exact typed API the application uses; no test-only query layer or handwritten setup SQL exists. A with region commits when its body finishes, and must states that the test expects no failure.

suite · SQLite Database

  providing AppDb = Test.Service.per_testper_test: pure fn!('service)(factory: pure fn!(case: Test.Case) -> Service.Provider(AppDb)) -> pure fn!(case: Test.Case) -> Service.Provider(AppDb)((_test_test: Test.Case) => AppDb.test_databasetest_database: pure fn!() -> Service.Provider(AppDb)());

An omitted completed field takes the schema's False default. SQLite supplies the autoincrementing id.