ticket

Database

The schema is a list of migrations. From it Ticket generates your records and a typed query API, so SQL strings stay out of controllers.

The migrations zone

An app writes its migrations in one module, Schema. The zone is replayed in order on every build, so there is no schema file to keep in sync.

module Schema

uses
  Std.{Id, Json, List, Option, Time}
  Ticket.{Db, Migration, Model, Query}

migrations
  20261001_120000 create_table users
    name   String
    email  String  unique

  20261001_130000 create_table posts
    title      String
    published  Bool              default false
    author_id  Option<Id<User>>  references users
    timestamps

  20261002_090000 rename_column posts
    title -> heading

That generates User and NewUser, Post and NewPost, a migrations constant for the runner, and the model API below. NewPost is Post without id and without timestamps columns, which SQLite fills.

Change Lines under it
create_table <table> [as Record] columns: name Type [default v] [references table [on_delete action]] [unique]
add_column <table> columns, which need a default or an Option type
remove_column <table> / drop_table <table> column names / nothing
rename_column <table> old -> new
add_index <table> / remove_index <table> column… [unique], one index per line

The column types are Int, Float, String, Bool, Time, Id<Record> (with references) and Option<…>. Each migration's down is worked out from the schema, so remove_column and drop_table can be rolled back. Mistakes are reported at the line that makes them.

Time columns and timestamps (created_at and updated_at, filled by SQLite) need Std.Time in uses. timestamps goes in create_table only.

Running migrations

The database comes from [database] in ticket.toml. SQLite is built in, through node:sqlite. For Postgres, set adapter = "postgres" with a connection URL as the database, and add the pg package to your app's package.json.

[database]
adapter  = "postgres"
database = "postgres://app:secret@localhost:5432/app"

Models, queries and migrations are the same on both. Migrations pick the column types for the adapter (timestamptz and identity columns on Postgres), and each request's transaction gets its own pooled connection, so concurrent requests don't see each other's uncommitted rows. A statement that fails inside a Postgres transaction aborts it, so a savepoint is what lets you recover and carry on.

Command What
ticket db:migrate [--version V] Applies the pending migrations, up to V if given
ticket db:rollback [--step N] Undoes the last N migrations (one by default)
ticket db:status Lists each migration and whether it has run
ticket db:reset Rolls everything back, migrates again, then seeds
ticket db:seed Runs the app's seed()

The app's main exports migrate and seed, as it exports router:

migrate(command: String, step: Int, version: String) -> Int / {Db, Clock} {
  Migrate.run(Schema.migrations, command, step, version)
}

Set [database] migrate = "auto" in ticket.toml to apply pending migrations when the app boots.

Generators

ticket g migration <Name> [field…] appends an entry to src/schema.px, named from the migration (Create…, Add…To…, Remove…From…). ticket g model <Name> [field…] does the same and also writes src/models/<name>.px.

A field is name:Type[:modifier…]. Type? is Option<Type>, author:references is author_id Option<Id<Author>> references authors, and the modifiers are unique and default=V.

$ ticket g model Post title:String body:String? published:Bool:default=false author:references

Model queries

Each table also gets a host-less effect, Posts, bound in Node to SQLite, and a typed constant per column, post_title: Column<Post, String>, named <record>_<column>.

index(conn: Conn) -> Response / {Posts, Throws<DbFailure>} {
  let posts = Posts.filter(
    [Query.eq(Schema.post_published, true)],
    [Query.order_desc(Schema.post_id)],
  )

  Response.render(Layout.app("Posts", PostViews.index(posts)))
}

create(conn: Conn) -> Response / {Posts, Throws<DbFailure>} {
  let post = Posts.insert({ title: "Hello", published: false, author_id: None })

  Response.redirect_to(conn, Paths.post_path(post.id))
}
Operation Returns
find(id) Option<Post>
all(), filter(filters, clauses) List<Post>
count(filters) Int
insert(NewPost), update(Post) The stored Post. update sets every column, and updated_at
delete(id) Bool: whether a row was there

Filters are Query.eq, not_eq, lt, gt, like and is_null, and a list of them is ANDed. Clauses are order_asc, order_desc, limit and offset. Every value is bound to a ?, never spliced into the SQL.

The column constants are phantom-typed, so these are compile errors rather than runtime ones:

  • Query.eq(Schema.post_title, 5): the value isn't a String
  • Posts.filter([Query.eq(Schema.user_name, "Ada")], []): a User column on Posts
  • Query.is_null(Schema.post_title): the column isn't an Option
  • Schema.post_titel: no such column
  • Posts.insert({ title: "x" }): missing fields

Filters, pagination and aggregates

Operation Returns
count(filters) Int
paginate(filters, clauses, page, per_page) Page<Post> = { rows, page, per_page, total, pages }. A page below 1 is 1, and a page past the end has no rows
sum(column, filters), max, min Option of the column's type: None over no rows

Filters are Query.eq, not_eq, lt, gt, gte, lte, in_list, like and is_null, and are ANDed. Query.any([f1, f2]) is OR and Query.all([…]) is AND, and they nest. in_list(col, []) and any([]) match nothing, and all([]) matches everything. Clauses are order_asc, order_desc, limit and offset.

Errors and tests

Operations throw DbFailure(DbError) when the database refuses. error.kind is unique, foreign_key, not_null and so on, or not_found when update finds no row. Catch it with try … catch, or let it reach the router's 500.

try {
  Posts.insert(row)
} catch {
  DbFailure(error) -> ...
}

A test can swap in its own storage by writing binds Posts in Node { … } in its module, for example over a Std.Table. Its bind replaces the generated one.

Associations

A references column named <name>_id gives both ends of the relation, as functions in Schema over the two tables' effects. A test that rebinds Users or Posts keeps them working.

// posts: author_id  Option<Id<User>>  references users
// comments: post_id  Id<Post>  references posts
Schema.post_author(post)            // belongs_to: Option<User> (User if the column is not an Option)
Schema.user_posts_as_author(user)   // has_many: List<Post>, by id
Schema.user_posts_as_author_filter(
  user,
  [Query.eq(Schema.post_title, "Hi")],
  [Query.order_desc(Schema.post_id)],
)
Schema.post_comments(post)          // a column named <record>_id drops the _as_…

The belongs_to is <record>_<name>. The has_many is <target record>_<referencing table>, with _as_<name> unless the column is <target record>_id, so two references to one table, or a parent_id on comments, never clash. A non-null belongs_to throws DbFailure (not_found) if the row is gone. A name that is already taken is reported at the column.

on_delete cascade, set_null or restrict after references becomes ON DELETE …. SQLite can't change it later, so it is set where the column is created. Without it, deleting a referenced row is a foreign_key failure. set_null needs an Option column.

Forms and validations

Params.record(conn, "post") decodes the post[…] form fields into the insert type through a Form impl that the migrations zone generates. The insert type is the permit list: other keys are dropped, and a missing or ill-typed field is a message on that field. A model module's validations zone adds Rails' validates:

module Post

uses
  Std.Result
  Ticket.{Errors, Validations}
  Schema

validations
  title   presence  length 3..120
  status  inclusion "draft" "live"
  slug    presence  format "^[a-z0-9-]+$"  uniqueness
create(conn: Conn) -> Response / {Posts, Throws<DbFailure>} {
  let decoded: Result<Errors, NewPost> = Params.record(conn, "post")

  match Result.and_then(decoded, Post.validate) {
    Ok(row) -> Response.redirect_to(conn, Paths.post_path(Posts.insert(row).id)),
    Err(errors) -> Response.render_status(422, PostViews.new(errors)),
  }
}

The zone generates validate(row: NewPost) and validate_update(row: Post), which differ only in that uniqueness skips the row itself. Both return every message at once as Errors: read a field's with Errors.on(errors, "title"), or all of them with Errors.full_messages(errors) ("Title can't be blank").

Rule Fails when
presence a string is blank, a list is empty, an Option is None
length 3..120, 3.., ..120 the character count is out of range
inclusion "a" "b" the value isn't listed
format "regex" a String doesn't match. It is unanchored as in JS, so write ^…$. Needs Std.Regex in the compiler's std
uniqueness another row holds the value. It queries Posts, so it needs Std.List, Ticket.Db and Ticket.Query in uses, and validate then has the effects {Posts, Throws<DbFailure>}

Fields read as Int, Float, String, Bool (true, on, 1; an absent checkbox is false), Id<…> and Time. A blank Option field is None. A misspelled field, or a rule the field's type can't take, is a type error on the rule's line.

Note: Posts.where is spelled Posts.filter for now, because where is a reserved word in Polar.