17.1 · The Big Modules
Changing the Schema
db.schema can change a database as well as describe it. You describe a
table once, in Zuri, using a Blueprint, and the adapter turns that
description into the right statements for its engine. The same description
creates the same table on SQLite, PostgreSQL, MySQL and MariaDB.
- Creating a Table
- Column Types
- Columns Are Required by Default
- Keys and Indexes
- Changing a Table
- Renaming and Dropping Tables
- How Changes Are Applied
- How SQLite Rebuilds a Table
- SQL for a Specific Engine
Creating a Table
create_table() passes a Blueprint to its body, and creates the table
when the body returns. If you annotate the body's table parameter with its
type, as below, your editor can complete the blueprint's methods:
import sql
import sql { Blueprint }
var db = sql.open(':memory:')
db.schema.create_table('authors', (table: Blueprint) {
table.id()
table.string('name', 80).unique()
table.timestamps()
})
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')
})
echo db.schema.column_names('posts')
echo db.schema.indexes('posts').map((index) => index.name)
echo db.schema.foreign_keys('posts')[0].references_table
db.close()
Zuri[id, author_id, title, body, published, created_at, updated_at]
[posts_published_index]
authors
OutputEvery column method returns the column, and so does every modifier, so you
can describe a column in a single chain. timestamps() adds created_at
and updated_at columns, each defaulting to the time the row was inserted.
If you pass { if_not_exists: true } as a third argument, a table that
already exists is left exactly as it is, indexes included. A library that
creates its own table every time it starts would pass this.
Column Types
Each type holds the same kind of value on every engine, stored in whatever way 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(name) |
integer |
integer |
int |
big_integer(name) |
bigint |
bigint |
bigint |
small_integer(name) |
smallint |
smallint |
smallint |
float(name) |
real |
double precision |
double |
decimal(name, p, s) |
decimal(p, s) |
numeric(p, s) |
decimal(p, s) |
string(name, n) |
varchar(n) |
varchar(n) |
varchar(n) |
text(name) |
text |
text |
longtext |
boolean(name) |
boolean |
boolean |
tinyint(1) |
date(name) |
date |
date |
date |
datetime(name) |
datetime |
timestamp with time zone |
datetime(6) |
time(name) |
time |
time |
time(6) |
binary(name) |
blob |
bytea |
longblob |
json(name) |
json |
jsonb |
json |
uuid(name) |
char(36) |
uuid |
char(36) |
string() has a length of 255 characters if you don't specify one. An
id() value is never reused, even after the row with the highest ID is
deleted. On SQLite, a decimal() can hold at most 15 significant digits,
which is the most a double can store exactly, and a larger precision raises
NotSupportedError there. references(name, table) creates a big integer
column that refers to another table's id(), along with its foreign key.
The modifiers are:
table.string('slug', 120).nullable() # may hold null
table.integer('views').default_value(0) # a default
table.json('settings').default_value({}) # a default of any JSON type
table.text('code').default_value(sql.raw("lower('X')")) # an expression
table.datetime('seen_at').use_current() # the moment of the insert
table.string('email', 254).unique() # a unique index of its own
table.string('city', 80).index() # an index of its own
table.string('code', 10).primary() # the primary key
table.string('username', 40).exact() # compared byte for byte
table.uuid('owner_id').references('users', 'id').on_delete('set null')
Zuriexact() makes a text column compare values byte by byte on every engine,
so 'Ada' and 'ada' are different values, and so are 'é' and 'e'.
SQLite already compares this way. On PostgreSQL, the column gets the "C"
collation, and on MySQL and MariaDB, utf8mb4_bin, because their default
collations treat uppercase and lowercase letters as equal, and on MySQL,
accented and unaccented letters too. Use it for a column that holds keys,
where two values that only differ in case must not clash.
Columns Are Required by Default
Every column is NOT NULL unless you call nullable(). That way, if a
program forgets to provide a value, the database reports an error instead of
storing a null that nobody intended:
import sql
import sql { Blueprint }
var db = sql.open(':memory:')
db.schema.create_table('notes', (table: Blueprint) {
table.id()
table.string('title', 80)
table.text('body').nullable()
})
db.insert('notes', { title: 'kept', body: nil })
catch {
db.insert('notes', { body: 'no title' })
} as error {
echo typeof(error)
}
db.close()
ZuriNotNullViolation
OutputKeys and Indexes
A key or index on a single column is a modifier on that column. A key or index on several columns is declared on the table:
table.primary(['order_id', 'line'])
table.unique(['author_id', 'slug'])
table.index(['published', 'created_at'])
table
.foreign(['order_id', 'line'], 'order_lines', ['order_id', 'line'])
.on_delete('cascade')
ZuriIndexes, unique indexes and foreign keys are named after their table and
columns, such as posts_author_id_index, posts_slug_unique and
posts_author_id_foreign. You can pass a second argument to choose a
different name. Because the name is predictable, you can drop an index by
its columns just as easily as by its name. A name longer than 63 characters,
which is the longest PostgreSQL allows, is shortened and ends with a hash of
the full name, so it stays unique.
Foreign keys are always written as table constraints. MySQL parses a
references clause on a column and then ignores it, so a key declared that
way would exist on the other engines but not on MySQL. Declared on the
table, it exists on every engine.
The actions a foreign key can take when the row it refers to is deleted, or
has its key changed, are 'cascade', 'restrict', 'set null',
'set default' and 'no action'. The last one is what a key does if you
don't specify an action.
Changing a Table
alter_table() describes changes on a blueprint and applies them in the
order you describe them:
import sql
import sql { Blueprint }
var db = sql.open(':memory:')
db.schema.create_table('posts', (table: Blueprint) {
table.id()
table.string('title', 100)
table.text('body').nullable()
table.integer('views').default_value(0)
})
db.insert('posts', { title: 'On Engines', body: 'Notes.' })
db.schema.alter_table('posts', (table: Blueprint) {
table.string('slug', 120).nullable().unique()
table.string('title', 300).change()
table.rename_column('body', 'content')
table.drop_column('views')
})
echo db.schema.column_names('posts')
echo db.fetch_one('select * from posts', nil)
db.close()
Zuri[id, title, content, slug]
{id: 1, title: On Engines, content: Notes., slug: nil}
OutputA column described in alter_table() is added to the table. change()
turns the description into a change to an existing column instead. The
column takes the type, nullability, default and comparison you describe,
whatever it had before, so you need to describe the column in full,
including nullable() where it applies. The column's indexes are kept.
Here's what else an alteration can do:
table.rename_column('body', 'content')
table.drop_column(['views', 'notes'])
table.drop_index(['published', 'created_at'])
table.drop_unique('posts_slug_unique')
table.drop_foreign(['author_id'])
table.drop_primary()
ZuriDropping a column also drops every index, unique index and key that it's
part of, on every engine. A required column without a default can only be
added to an empty table, because there'd be nothing to fill the existing
rows with. On a table that has rows, the engine reports a
NotNullViolation.
Renaming and Dropping Tables
db.schema.rename_table('posts', 'articles')
db.schema.drop_table('articles')
db.schema.drop_table('articles', { if_exists: true })
ZuriA renamed table keeps its indexes under their existing names, and foreign keys that refer to it are updated to follow it. If another table refers to a table through a foreign key, dropping it is rejected on every engine, instead of deleting the rows that refer to it as well:
import sql
import sql { Blueprint }
var db = sql.open(':memory:')
db.schema.create_table('authors', (table: Blueprint) {
table.id()
})
db.schema.create_table('posts', (table: Blueprint) {
table.id()
table.references('author_id', 'authors')
})
catch {
db.schema.drop_table('authors')
} as error {
echo typeof(error)
}
db.schema.drop_table('posts')
db.schema.drop_table('authors')
echo db.schema.tables()
db.close()
ZuriQueryError
[]
OutputHow Changes Are Applied
A change made outside a transaction is applied as a single unit. On SQLite and PostgreSQL, that means it runs in a transaction of its own, so if the third step of an alteration fails, the first two are undone as well. MySQL and MariaDB commit every statement that changes a table's definition, whatever transaction is open, so on those engines each statement is permanent once it has run.
A change made inside a transaction becomes part of it, and on engines that support it, it's rolled back along with the transaction:
import sql
import sql { Blueprint }
var db = sql.open(':memory:')
db.schema.create_table('posts', (table: Blueprint) {
table.id()
})
catch {
db.transaction((tx) {
tx.schema.alter_table('posts', (table: Blueprint) {
table.string('title', 80).nullable()
})
raise Error('changed my mind')
})
} as _error {}
echo db.schema.column_names('posts')
db.close()
Zuri[id]
OutputHow SQLite Rebuilds a Table
SQLite's ALTER TABLE can add and rename columns, but not much else. For
anything more, such as changing a column, adding or dropping a key, or
dropping a column that an index or key uses, the adapter follows the
procedure in SQLite's own documentation. It creates a new table with the
changed definition, copies the rows across, drops the old table and renames
the new one to take its place. It then recreates the indexes and triggers
and carries over the sequence behind the id() column. The new definition
is based on the table's own create table statement, with only the changed
parts rewritten, so everything else it declared, including checks and
collations, is kept.
Dropping the old table deletes its rows first, and when foreign keys are
enforced, that delete triggers the on delete action of every table that
refers to it. With enforcement on, a rebuild would therefore delete every
row that refers to the table. So a rebuild only runs with enforcement turned
off, which SQLite only allows outside a transaction. A change made outside a
transaction turns enforcement off for its own duration, and checks every
foreign key before it commits, and sql.migrate does the same for every
migration. A rebuild requested inside a transaction while foreign keys are
enforced raises a NotSupportedError instead of putting the rows at risk:
import sql
import sql { Blueprint }
var db = sql.open(':memory:')
db.schema.create_table('authors', (table: Blueprint) {
table.id()
table.string('name', 80)
})
db.schema.create_table('books', (table: Blueprint) {
table.id()
table.references('author_id', 'authors').on_delete('cascade')
})
var ada = db.insert('authors', { name: 'Ada' })
db.insert('books', { author_id: ada })
# Outside a transaction: built again, and the book is still there.
db.schema.alter_table('authors', (table: Blueprint) {
table.string('name', 200).change()
})
echo db.count('books')
catch {
db.transaction((tx) {
tx.schema.alter_table('authors', (table: Blueprint) {
table.string('name', 300).change()
})
})
} as error {
echo typeof(error)
}
db.close()
Zuri1
NotSupportedError
OutputSQL for a Specific Engine
A blueprint covers what every engine can do. For a feature that only one engine has, such as a PostgreSQL extension or a partial index, write the statement in SQL and run it where it applies:
if db.driver_name() == 'postgres' {
db.exec('create index posts_live on posts (created_at) where published', nil)
}
ZuriStatements run this way are part of the same change as the blueprint's statements, including its transaction.