The Zuri Programming Language Zuri

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

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
Output

Every 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')
Zuri

exact() 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()
Zuri
NotNullViolation
Output

Keys 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')
Zuri

Indexes, 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}
Output

A 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()
Zuri

Dropping 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 })
Zuri

A 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()
Zuri
QueryError
[]
Output

How 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]
Output

How 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()
Zuri
1
NotSupportedError
Output

SQL 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)
}
Zuri

Statements run this way are part of the same change as the blueprint's statements, including its transaction.