Getting started¶
This page takes an existing database under hazel's management: the schema is written to a file, the file is changed, and the change is applied.
Install¶
brew install winebarrel/hazel/hazel
See Installation for the other ways.
Write the schema to a file¶
hazel dump prints the schema of the database:
$ hazel dump app.db > schema.sql
$ cat schema.sql
-- Database: /path/to/app.db
-- Dump (2 tables, 0 views, 1 index, 0 triggers)
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users (id),
body TEXT
);
CREATE INDEX posts_user_id ON posts (user_id);
From now on, schema.sql is the schema of the database. Given back to plan, it has nothing to do:
$ hazel plan app.db schema.sql
-- Database: /path/to/app.db
-- Plan (2 tables, 0 views, 1 index, 0 triggers)
-- No changes
For one file per table and view, use hazel dump --split=schema app.db and give schema/*.sql to the commands.
Change the schema¶
Edit the file to the schema the database should have. Here users gets an email column with a unique index, and posts a created_at column:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT
);
CREATE UNIQUE INDEX users_email ON users (email);
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users (id),
body TEXT,
created_at TEXT
);
CREATE INDEX posts_user_id ON posts (user_id);
hazel plan prints the DDL that takes the database there, without running it:
$ hazel plan app.db schema.sql
-- Database: /path/to/app.db
-- Plan (2 tables, 0 views, 2 indexes, 0 triggers)
ALTER TABLE users ADD COLUMN email TEXT;
ALTER TABLE posts ADD COLUMN created_at TEXT;
CREATE UNIQUE INDEX users_email ON users (email);
The new column is written at the end of the table, since that is the only place ADD COLUMN can put it; the order of the columns is not compared. A change that ALTER TABLE cannot make, such as a new column type, needs a rebuild of the table; plan then fails with exit status 3 until --allow-rebuild is given. See Schema objects.
Apply it¶
hazel apply runs the plan in one transaction:
$ hazel apply app.db schema.sql
-- Database: /path/to/app.db
-- Plan (2 tables, 0 views, 2 indexes, 0 triggers)
ALTER TABLE users ADD COLUMN email TEXT;
-- ok (0ms)
ALTER TABLE posts ADD COLUMN created_at TEXT;
-- ok (0ms)
CREATE UNIQUE INDEX users_email ON users (email);
-- ok (0ms)
-- Applied 3 statements
If a statement fails, nothing is changed. A new database is set up the same way: apply creates the file when it does not exist.
Keep the file and the database in step¶
plan --check exits with status 2 when the database is not what the files say, which suits a CI job or a deploy step:
$ hazel plan --check app.db schema.sql > /dev/null; echo $?
0
hazel fmt schema.sql lays out a file the way dump writes it, and hazel fmt --check reports the files that are not laid out so.
Next steps¶
- Rename a table or a column without losing its data: Renaming.
- Keep a table out of hazel's hands, or run SQL after the DDL: Directives.
- Put options in a file: Configuration.