Databases
sql.blueprint
import sql.blueprint
Zuri
sqllifts part of this module out to its own top level; each name below is shown with the path that reaches it. Anything still spelledsql.blueprint.*needsimport 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')
})
ZuriColumn 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'
ZuriDeletes the rows that refer to a deleted row.
RESTRICT
sql.blueprint.RESTRICT = 'restrict'
ZuriRefuses to delete a row while others refer to it.
SET_NULL
sql.blueprint.SET_NULL = 'set null'
ZuriSets the referring column to null.
SET_DEFAULT
sql.blueprint.SET_DEFAULT = 'set default'
ZuriSets the referring column to its default.
NO_ACTION
sql.blueprint.NO_ACTION = 'no action'
ZuriDoes 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 = [...]
ZuriEvery action on_delete() and on_update() accept.
MAX_NAME
sql.blueprint.MAX_NAME = 63
ZuriThe longest name every engine keeps whole.
Functions
index_name()
sql.blueprint.index_name(table: string, columns: list, suffix: string) -> string
ZuriThe 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
ZuriParameters
table(string)columns(list[string])suffix(string)
Returns string
Classes
ForeignKey
class sql.ForeignKey
ZuriA 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(), soechoandprint()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)
ZuriParameters
columns(list[string])table(string)references(list[string])
ForeignKey.on_delete()
sql.ForeignKey.on_delete(action: string) -> ForeignKey
ZuriSays what deleting a referred row does to the rows referring to it.
Parameters
action(string) — One ofACTIONS:'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
ZuriSays what changing a referred key does to the rows referring to it.
Parameters
action(string) — One ofACTIONS.
Returns ForeignKey — This key, for chaining.
Raises QueryError for any other action.
ForeignKey.named()
sql.ForeignKey.named(name: string) -> ForeignKey
ZuriNames 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
ZuriThe constraint's name on table.
Parameters
table(string) — The referring table.
Returns string
ForeignKey.to_string()
sql.ForeignKey.to_string()
ZuriColumn
class sql.Column
ZuriOne 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(), soechoandprint()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)
ZuriParameters
name(string)kind(string)
Column.nullable()
sql.Column.nullable() -> Column
ZuriLets the column hold null. Without this, every column is required.
Returns Column
Column.default_value()
sql.Column.default_value(value) -> Column
ZuriGives 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({})
ZuriParameters
value(any)
Returns Column
Column.use_current()
sql.Column.use_current() -> Column
ZuriDefaults 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
ZuriGives the column a unique index of its own, named by index_name().
Returns Column
Column.index()
sql.Column.index() -> Column
ZuriGives the column an index of its own, named by index_name().
Returns Column
Column.primary()
sql.Column.primary() -> Column
ZuriMakes the column the table's primary key. A key of several columns is
Blueprint.primary().
Returns Column
Column.exact()
sql.Column.exact() -> Column
ZuriCompares 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
ZuriMakes 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')
ZuriParameters
table(string)column(string|nil) — The referred column,idwhen nil.
Returns Column
Column.on_delete()
sql.Column.on_delete(action: string) -> Column
ZuriWhat deleting the referred row does, on a column that references()
another table.
Parameters
action(string) — One ofACTIONS.
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
ZuriWhat changing the referred key does, on a column that references()
another table.
Parameters
action(string) — One ofACTIONS.
Returns Column
Raises QueryError if the column refers to nothing, or for an
unknown action.
Column.change()
sql.Column.change() -> Column
ZuriTurns 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()
})
ZuriReturns Column
Column.to_string()
sql.Column.to_string()
ZuriBlueprint
class sql.Blueprint
ZuriThe 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(), soechoandprint()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)
ZuriParameters
table(string)creating(bool)
Blueprint.columns()
sql.Blueprint.columns() -> list[Column]
ZuriThe columns described, added and changed, in order.
Returns list[Column]
Blueprint.id()
sql.Blueprint.id(name) -> Column
ZuriAn auto-numbered primary key, a big integer the engine assigns on insert and never assigns twice.
Parameters
name(string|nil) —idwhen nil.
Returns Column
Blueprint.integer()
sql.Blueprint.integer(name: string) -> Column
ZuriA 32 bit integer.
Parameters
name(string)
Returns Column
Blueprint.big_integer()
sql.Blueprint.big_integer(name: string) -> Column
ZuriA 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
ZuriA 16 bit integer.
Parameters
name(string)
Returns Column
Blueprint.float()
sql.Blueprint.float(name: string) -> Column
ZuriA double precision float.
Parameters
name(string)
Returns Column
Blueprint.decimal()
sql.Blueprint.decimal(name: string, precision: number, scale) -> Column
ZuriAn 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 toprecision. Nil means 0.
Returns Column
Raises QueryError if either is out of range.
Blueprint.string()
sql.Blueprint.string(name: string, length) -> Column
ZuriText 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
ZuriText of any length.
Parameters
name(string)
Returns Column
Blueprint.boolean()
sql.Blueprint.boolean(name: string) -> Column
ZuriA 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
ZuriA calendar date.
Parameters
name(string)
Returns Column
Blueprint.datetime()
sql.Blueprint.datetime(name: string) -> Column
ZuriA moment, to the microsecond. Reads back as a date.Date.
Parameters
name(string)
Returns Column
Blueprint.time()
sql.Blueprint.time(name: string) -> Column
ZuriA 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
ZuriBytes of any length.
Parameters
name(string)
Returns Column
Blueprint.json()
sql.Blueprint.json(name: string) -> Column
ZuriA 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
ZuriA UUID, held as its 36 character text.
Parameters
name(string)
Returns Column
Blueprint.references()
sql.Blueprint.references(name: string, table: string, column) -> Column
ZuriA column referring to another table's id(): a big integer carrying a
foreign key.
table.references('author_id', 'authors').on_delete('cascade')
ZuriParameters
name(string)table(string) — The table referred to.column(string|nil) — The column referred to,idwhen nil.
Returns Column
Blueprint.timestamps()
sql.Blueprint.timestamps() -> list[Column]
Zuricreated_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)
ZuriA primary key spanning columns.
Parameters
columns(string|list[string])
Blueprint.unique()
sql.Blueprint.unique(columns, name)
ZuriA unique index on columns.
Parameters
columns(string|list[string])name(string|nil) —index_name()'s when nil.
Blueprint.index()
sql.Blueprint.index(columns, name)
ZuriAn 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
ZuriA foreign key from columns to references of table.
table.foreign(['order_id', 'line'], 'order_lines', ['order_id', 'line'])
.on_delete('cascade')
ZuriParameters
columns(string|list[string])table(string)references(string|list[string]|nil) —idwhen nil.
Returns ForeignKey
Raises QueryError if the two lists differ in length.
Blueprint.rename_column()
sql.Blueprint.rename_column(from: string, to: string)
ZuriRenames a column, keeping its data, indexes and keys.
Parameters
from(string)to(string)
Blueprint.drop_column()
sql.Blueprint.drop_column(names)
ZuriDrops 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)
ZuriDrops 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 thatindex_name()gives them.
Blueprint.drop_unique()
sql.Blueprint.drop_unique(name)
ZuriDrops a unique index, by its name or by its columns.
Parameters
name(string|list[string])
Blueprint.drop_foreign()
sql.Blueprint.drop_foreign(name)
ZuriDrops a foreign key, by its name or by its columns.
Parameters
name(string|list[string])
Blueprint.drop_primary()
sql.Blueprint.drop_primary()
ZuriDrops the primary key. The columns stay.
Blueprint.to_string()
sql.Blueprint.to_string()
Zuri2026, Richard Ore and Zuri contributors