The Zuri Standard Library Zuri

Databases

sql.blueprint

import sql.blueprint
Zuri

sql lifts part of this module out to its own top level; each name below is shown with the path that reaches it. Anything still spelled sql.blueprint.* needs import sql.blueprint.

Describes the structure of a table once, for every database engine.

A Blueprint is what db.schema.create_table() and db.schema.alter_table() pass to their body. It describes columns, keys and indexes in Zuri, and the active adapter turns that description into the statements its engine understands. The same blueprint creates the same table on SQLite, PostgreSQL, MySQL and MariaDB, so one migration works with every database a program runs against.

db.schema.create_table('posts', @(table: Blueprint) {
  table.id()
  table.references('author_id', 'authors').on_delete('cascade')
  table.string('title', 200)
  table.text('body').nullable()
  table.boolean('published').default_value(false)
  table.timestamps()
  table.index('published')
})
Zuri

Column types

Each type holds the same kind of value on every engine, stored in the way that suits that engine best.

Method SQLite PostgreSQL MySQL and MariaDB
id() integer primary key autoincrement bigint generated by default as identity bigint auto_increment
integer() integer integer int
big_integer() bigint bigint bigint
small_integer() smallint smallint smallint
float() real double precision double
decimal(p, s) decimal(p, s) numeric(p, s) decimal(p, s)
string(n) varchar(n) varchar(n) varchar(n)
text() text text longtext
boolean() boolean boolean tinyint(1)
date() date date date
datetime() datetime timestamp with time zone datetime(6)
time() time time time(6)
binary() blob bytea longblob
json() json jsonb json
uuid() char(36) uuid char(36)

An id() is never reused, even after the row with the highest one is deleted. On SQLite, a decimal() holds at most 15 significant digits, which is the most a double can store exactly, so a larger precision raises a NotSupportedError there, instead of storing digits that might silently change.

Columns are required by default

Every column is NOT NULL unless you call nullable() on it. A missing value is then an error reported by the database, not a null that nobody meant to store.

Names

Indexes, unique indexes and foreign keys are named after their table and columns, for example posts_author_id_index, posts_slug_unique and posts_author_id_foreign. Because the name can be worked out from the columns, you can drop one by giving its columns just as easily as by giving its name. A name longer than 63 characters, the longest that PostgreSQL keeps, is shortened and given a hash of the full name, so that it stays unique.

Constants

CASCADE

sql.blueprint.CASCADE = 'cascade'
Zuri

Deletes the rows that refer to a deleted row.

RESTRICT

sql.blueprint.RESTRICT = 'restrict'
Zuri

Refuses to delete a row while others refer to it.

SET_NULL

sql.blueprint.SET_NULL = 'set null'
Zuri

Sets the referring column to null.

SET_DEFAULT

sql.blueprint.SET_DEFAULT = 'set default'
Zuri

Sets the referring column to its default.

NO_ACTION

sql.blueprint.NO_ACTION = 'no action'
Zuri

Does nothing at the moment of the change, and refuses it at the end of the statement if a reference is left dangling. What a foreign key does when it names no action.

ACTIONS

sql.blueprint.ACTIONS = [...]
Zuri

Every action on_delete() and on_update() accept.

MAX_NAME

sql.blueprint.MAX_NAME = 63
Zuri

The longest name every engine keeps whole.

Functions

index_name()

sql.blueprint.index_name(table: string, columns: list, suffix: string) -> string
Zuri

The name an index or key on columns of table takes.

suffix is index, unique, foreign or primary. The table's own name is used without any schema in front of it, and a name past MAX_NAME characters is cut short and finished with eight characters of a hash of the whole, so two long names that share a beginning still differ.

index_name('posts', ['author_id', 'created_at'], 'index')
# posts_author_id_created_at_index
Zuri

Parameters

  • table (string)
  • columns (list[string])
  • suffix (string)

Returns string

Classes

ForeignKey

class sql.ForeignKey
Zuri

A foreign key: the columns of one table that refer to rows of another.

Made by Blueprint.foreign(), or by Column.references() for a key of one column. The actions say what happens to a referring row when the row it refers to is deleted or has its key changed. With none given, the engine refuses the change while references remain.

  • printable — has a @to_string(), so echo and print() show something useful

Fields

Field Type Description
columns list[string] The referring columns.
table string The table referred to.
references list[string] The columns referred to, in the same order as columns.
on_delete_action What deleting a referred row does, one of ACTIONS, or nil for the engine's default, NO_ACTION.
on_update_action What changing a referred key does, one of ACTIONS, or nil.
name The constraint's name, or nil to take the one index_name() gives.

Constructor

sql.ForeignKey(columns, table, references)
Zuri

Parameters

  • columns (list[string])
  • table (string)
  • references (list[string])

ForeignKey.on_delete()

sql.ForeignKey.on_delete(action: string) -> ForeignKey
Zuri

Says what deleting a referred row does to the rows referring to it.

Parameters

  • action (string) — One of ACTIONS: 'cascade', 'restrict', 'set null', 'set default' or 'no action'.

Returns ForeignKey — This key, for chaining.

Raises QueryError for any other action.

ForeignKey.on_update()

sql.ForeignKey.on_update(action: string) -> ForeignKey
Zuri

Says what changing a referred key does to the rows referring to it.

Parameters

  • action (string) — One of ACTIONS.

Returns ForeignKey — This key, for chaining.

Raises QueryError for any other action.

ForeignKey.named()

sql.ForeignKey.named(name: string) -> ForeignKey
Zuri

Names the constraint, in place of the name index_name() gives.

Parameters

  • name (string)

Returns ForeignKey — This key, for chaining.

ForeignKey.name_on()

sql.ForeignKey.name_on(table: string) -> string
Zuri

The constraint's name on table.

Parameters

  • table (string) — The referring table.

Returns string

ForeignKey.to_string()

sql.ForeignKey.to_string()
Zuri

Column

class sql.Column
Zuri

One column, as a blueprint describes it.

Every column method on Blueprint returns one of these, and every method here returns the column again, so a column is described in one chain:

table.string('email', 254).exact().unique()
Zuri
  • printable — has a @to_string(), so echo and print() show something useful

Fields

Field Type Description
name string The column's name.
kind string Which of the blueprint's types this is, as the method that made it is named: 'integer', 'string',...
length The length of a string().
precision The total digits of a decimal().
scale The digits after the point of a decimal().
is_nullable Whether the column may hold null.
has_default Whether a default was given, which a default of nil needs saying apart from no default at all.
initial The value a row starts with when an insert leaves the column out: a value, or a sql.Raw fragment.
is_current Whether the column defaults to the time a row is inserted.
is_unique Whether the column has a unique index of its own.
is_indexed Whether the column has an index of its own.
is_primary Whether the column is the table's primary key.
is_exact Whether the column is compared byte for byte.
is_generated Whether the engine numbers the column itself, as id() does.
foreign The foreign key the column carries, or nil.
is_change Whether this redefines a column that the table already has, instead of adding one.

Constructor

sql.Column(name, kind)
Zuri

Parameters

  • name (string)
  • kind (string)

Column.nullable()

sql.Column.nullable() -> Column
Zuri

Lets the column hold null. Without this, every column is required.

Returns Column

Column.default_value()

sql.Column.default_value(value) -> Column
Zuri

Gives the column a default, which is used when an insert leaves the column out.

The value is written into the table definition as a literal of the column's type: a string, a number, a bool, nil, a sql.Decimal, or a list or dictionary for a json() column. A sql.Raw is written exactly as given, for a default that's an expression instead of a value. It's SQL, and the engine reads it as SQL.

table.integer('views').default_value(0)
table.json('settings').default_value({})
Zuri

Parameters

  • value (any)

Returns Column

Column.use_current()

sql.Column.use_current() -> Column
Zuri

Defaults a datetime() column to the moment a row is inserted.

Returns Column

Raises QueryError on a column that is not a datetime().

Column.unique()

sql.Column.unique() -> Column
Zuri

Gives the column a unique index of its own, named by index_name().

Returns Column

Column.index()

sql.Column.index() -> Column
Zuri

Gives the column an index of its own, named by index_name().

Returns Column

Column.primary()

sql.Column.primary() -> Column
Zuri

Makes the column the table's primary key. A key of several columns is Blueprint.primary().

Returns Column

Column.exact()

sql.Column.exact() -> Column
Zuri

Compares the column byte for byte, so case and accents always count: 'Ada' and 'ada' are two values, and so are 'é' and 'e'.

SQLite compares this way already. PostgreSQL is given the "C" collation and MySQL and MariaDB utf8mb4_bin, whose default collations treat case, and on MySQL accents, as the same. Use it for a column that holds a key, where two values differing only in case must not collide or match each other.

Returns Column

Raises QueryError on a column that does not hold text.

Column.references()

sql.Column.references(table: string, column) -> Column
Zuri

Makes the column refer to a row of another table.

The column's values have to be the referred column's values, or null on a nullable column. on_delete() and on_update() on this column then say what happens when the referred row changes.

table.uuid('owner_id').references('users', 'id')
Zuri

Parameters

  • table (string)
  • column (string|nil) — The referred column, id when nil.

Returns Column

Column.on_delete()

sql.Column.on_delete(action: string) -> Column
Zuri

What deleting the referred row does, on a column that references() another table.

Parameters

  • action (string) — One of ACTIONS.

Returns Column

Raises QueryError if the column refers to nothing, or for an unknown action.

Column.on_update()

sql.Column.on_update(action: string) -> Column
Zuri

What changing the referred key does, on a column that references() another table.

Parameters

  • action (string) — One of ACTIONS.

Returns Column

Raises QueryError if the column refers to nothing, or for an unknown action.

Column.change()

sql.Column.change() -> Column
Zuri

Turns this into a change to a column the table already has, inside alter_table().

The column takes the type, nullability, default and comparison declared here, whatever it had before, so declare it in full: leaving out nullable() makes it required. Its indexes stay as they are, and unique() or index() here adds one.

db.schema.alter_table('posts', @(table: Blueprint) {
  table.string('title', 300).change()
})
Zuri

Returns Column

Column.to_string()

sql.Column.to_string()
Zuri

Blueprint

class sql.Blueprint
Zuri

The description of one table, or of the changes to one.

db.schema.create_table() and db.schema.alter_table() make one and pass it to their body, then hand it to the adapter, which carries out what it describes in the order it was described. A program does not build one itself.

  • printable — has a @to_string(), so echo and print() show something useful

Fields

Field Type Description
table string The table's name.
creating bool Whether this creates the table, as opposed to altering it.
operations list[dict] Everything described, in order.

Constructor

sql.Blueprint(table, creating)
Zuri

Parameters

  • table (string)
  • creating (bool)

Blueprint.columns()

sql.Blueprint.columns() -> list[Column]
Zuri

The columns described, added and changed, in order.

Returns list[Column]

Blueprint.id()

sql.Blueprint.id(name) -> Column
Zuri

An auto-numbered primary key, a big integer the engine assigns on insert and never assigns twice.

Parameters

  • name (string|nil) — id when nil.

Returns Column

Blueprint.integer()

sql.Blueprint.integer(name: string) -> Column
Zuri

A 32 bit integer.

Parameters

  • name (string)

Returns Column

Blueprint.big_integer()

sql.Blueprint.big_integer(name: string) -> Column
Zuri

A 64 bit integer. Values past 2^53 read back as a bigint.

Parameters

  • name (string)

Returns Column

Blueprint.small_integer()

sql.Blueprint.small_integer(name: string) -> Column
Zuri

A 16 bit integer.

Parameters

  • name (string)

Returns Column

Blueprint.float()

sql.Blueprint.float(name: string) -> Column
Zuri

A double precision float.

Parameters

  • name (string)

Returns Column

Blueprint.decimal()

sql.Blueprint.decimal(name: string, precision: number, scale) -> Column
Zuri

An exact decimal with precision digits, scale of them after the decimal point. It's read back as a sql.Decimal.

Parameters

  • name (string)
  • precision (number) — From 1 to 65, and at most 15 on SQLite.
  • scale (number|nil) — From 0 to precision. Nil means 0.

Returns Column

Raises QueryError if either is out of range.

Blueprint.string()

sql.Blueprint.string(name: string, length) -> Column
Zuri

Text of at most length characters.

Parameters

  • name (string)
  • length (number|nil) — From 1 to 16383, 255 when nil.

Returns Column

Raises QueryError if length is out of range.

Blueprint.text()

sql.Blueprint.text(name: string) -> Column
Zuri

Text of any length.

Parameters

  • name (string)

Returns Column

Blueprint.boolean()

sql.Blueprint.boolean(name: string) -> Column
Zuri

A boolean. MySQL and MariaDB store it as 0 and 1, and read it back that way unless a model casts it.

Parameters

  • name (string)

Returns Column

Blueprint.date()

sql.Blueprint.date(name: string) -> Column
Zuri

A calendar date.

Parameters

  • name (string)

Returns Column

Blueprint.datetime()

sql.Blueprint.datetime(name: string) -> Column
Zuri

A moment, to the microsecond. Reads back as a date.Date.

Parameters

  • name (string)

Returns Column

Blueprint.time()

sql.Blueprint.time(name: string) -> Column
Zuri

A time of day, or on MySQL and MariaDB a signed span, as a sql.Time.

Parameters

  • name (string)

Returns Column

Blueprint.binary()

sql.Blueprint.binary(name: string) -> Column
Zuri

Bytes of any length.

Parameters

  • name (string)

Returns Column

Blueprint.json()

sql.Blueprint.json(name: string) -> Column
Zuri

A JSON document. A list or dictionary written to it reads back as one, except on MariaDB, whose JSON is text.

Parameters

  • name (string)

Returns Column

Blueprint.uuid()

sql.Blueprint.uuid(name: string) -> Column
Zuri

A UUID, held as its 36 character text.

Parameters

  • name (string)

Returns Column

Blueprint.references()

sql.Blueprint.references(name: string, table: string, column) -> Column
Zuri

A column referring to another table's id(): a big integer carrying a foreign key.

table.references('author_id', 'authors').on_delete('cascade')
Zuri

Parameters

  • name (string)
  • table (string) — The table referred to.
  • column (string|nil) — The column referred to, id when nil.

Returns Column

Blueprint.timestamps()

sql.Blueprint.timestamps() -> list[Column]
Zuri

created_at and updated_at, both datetime() columns defaulting to the moment a row is inserted. A model with timestamps() keeps them up to date.

Returns list[Column] — The two columns.

Blueprint.primary()

sql.Blueprint.primary(columns)
Zuri

A primary key spanning columns.

Parameters

  • columns (string|list[string])

Blueprint.unique()

sql.Blueprint.unique(columns, name)
Zuri

A unique index on columns.

Parameters

  • columns (string|list[string])
  • name (string|nil) — index_name()'s when nil.

Blueprint.index()

sql.Blueprint.index(columns, name)
Zuri

An index on columns.

Parameters

  • columns (string|list[string])
  • name (string|nil) — index_name()'s when nil.

Blueprint.foreign()

sql.Blueprint.foreign(columns, table: string, references) -> ForeignKey
Zuri

A foreign key from columns to references of table.

table.foreign(['order_id', 'line'], 'order_lines', ['order_id', 'line'])
  .on_delete('cascade')
Zuri

Parameters

  • columns (string|list[string])
  • table (string)
  • references (string|list[string]|nil) — id when nil.

Returns ForeignKey

Raises QueryError if the two lists differ in length.

Blueprint.rename_column()

sql.Blueprint.rename_column(from: string, to: string)
Zuri

Renames a column, keeping its data, indexes and keys.

Parameters

  • from (string)
  • to (string)

Blueprint.drop_column()

sql.Blueprint.drop_column(names)
Zuri

Drops a column, or each of a list of them.

The indexes, unique indexes and keys the column is part of go with it, on every engine.

Parameters

  • names (string|list[string])

Blueprint.drop_index()

sql.Blueprint.drop_index(name)
Zuri

Drops an index, either by its name or by the columns it was created on.

Parameters

  • name (string|list[string]) — The name, or a list of the columns, which gives the index the name that index_name() gives them.

Blueprint.drop_unique()

sql.Blueprint.drop_unique(name)
Zuri

Drops a unique index, by its name or by its columns.

Parameters

  • name (string|list[string])

Blueprint.drop_foreign()

sql.Blueprint.drop_foreign(name)
Zuri

Drops a foreign key, by its name or by its columns.

Parameters

  • name (string|list[string])

Blueprint.drop_primary()

sql.Blueprint.drop_primary()
Zuri

Drops the primary key. The columns stay.

Blueprint.to_string()

sql.Blueprint.to_string()
Zuri

2026, Richard Ore and Zuri contributors