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 aString -
Posts.filter([Query.eq(Schema.user_name, "Ada")], []): aUsercolumn onPosts -
Query.is_null(Schema.post_title): the column isn't anOption Schema.post_titel: no such columnPosts.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.