Database

Database: migrations

Migrations are your database's version control: each one changes the schema, in code, so every copy of your app, your colleagues' and your servers', builds the same tables.

Introduction#

A migration is a class with an up() method, which changes the database, and a down() method, which undoes it. They describe tables with the Schema facade, which writes the SQL for SQLite, PostgreSQL and MySQL. Your migrations are in database/migrations, and a new app has five: its users, sessions and password reset tokens, its cache, its jobs, its personal access tokens and its notifications.

Generating migrations#

make:migration writes a migration in database/migrations. The name of the file starts with the date and time, so the migrations run in the order you write them:

node oriel make:migration create_flights_table

A name such as create_flights_table writes a migration that creates the flights table, and one such as add_votes_to_users_table a migration that changes users. --create=flights and --table=users name the table yourself, and --path writes the file in another folder.

Migration structure#

A migration's file default-exports the class. up() adds tables, columns or indexes; down() reverses what up() did:

import { Migration, Schema } from '@orieljs/framework'export default class extends Migration {  /** Run the migrations. */  async up() {    await Schema.create('flights', (table) => {      table.id()      table.string('name')      table.string('airline')      table.timestamps()    })  }  /** Reverse the migrations. */  async down() {    await Schema.dropIfExists('flights')  }}

Setting the migration connection#

A migration runs on the default connection, unless its connection names another:

export default class extends Migration {  override connection = 'pgsql'  async up() {    // ...  }  async down() {    // ...  }}

Migrations in a transaction#

On PostgreSQL and SQLite, each migration runs in a transaction, so one that fails halfway leaves nothing behind. MySQL can't roll back schema changes, so there a failed migration may leave part of its work. Set withinTransaction = false on a migration that mustn't run in a transaction.

Running migrations#

migrate runs the migrations that haven't run yet:

node oriel migrate

migrate:status shows which have run. migrate records each one in the migrations table, which it creates the first time, in a numbered batch: the migrations of one migrate are a batch, or one batch each with --step.

When it finishes, outside production and in an app with a types folder, migrate writes types/models.d.ts again, so your models' types follow your tables (see the model:types command).

Forcing migrations to run in production#

In production, migrate and the other commands that change your schema stop, unless you pass --force:

node oriel migrate --force

Rolling back migrations#

migrate:rollback undoes the last batch, running each migration's down(). --step undoes that many migrations instead, and --batch a batch by its number:

node oriel migrate:rollbacknode oriel migrate:rollback --step=5node oriel migrate:rollback --batch=3

migrate:reset undoes every migration.

Roll back and migrate using a single command#

migrate:refresh undoes every migration, then runs them all again; --step undoes and runs again only that many. --seed runs your seeders after it:

node oriel migrate:refreshnode oriel migrate:refresh --seed

Drop all tables and migrate#

migrate:fresh drops every table, without running any down(), then runs every migration:

node oriel migrate:freshnode oriel migrate:fresh --seed

Each command takes --database to run on another connection, and --path to run the migrations of another folder.

Tables#

Creating tables#

Schema.create() takes the table's name, and a function that defines its columns:

await Schema.create('users', (table) => {  table.id()  table.string('name')  table.string('email')  table.timestamps()})

Checking for table / column existence#

if (await Schema.hasTable('users')) {  // The "users" table exists...}if (await Schema.hasColumn('users', 'email')) {  // The "users" table exists and has an "email" column...}

hasColumns() checks a list of columns, and hasIndex() an index.

Database connection#

Schema.connection() works on another connection:

await Schema.connection('sqlite').create('users', (table) => {  table.id()})

Updating tables#

Schema.table() changes a table that exists:

await Schema.table('users', (table) => {  table.integer('votes')})

Renaming / dropping tables#

await Schema.rename('posts', 'articles')await Schema.drop('users')await Schema.dropIfExists('users')

Columns#

Creating columns#

Inside Schema.table(), each column method adds a column:

await Schema.table('users', (table) => {  table.integer('votes')})

Available column types#

SQLite stores every integer type as integer, and JSON as text.

Column modifiers#

Modifiers follow the column:

await Schema.table('users', (table) => {  table.string('email').nullable()})

Default expressions#

A default given with DB.raw() is written as SQL, so it can call a function:

await Schema.create('flights', (table) => {  table.id()  table.json('movies').default(DB.raw('(JSON_ARRAY())'))  table.timestamp('created_at').default(DB.raw('CURRENT_TIMESTAMP'))})

Modifying columns#

change() gives an existing column a new definition. The column becomes exactly what you write, so repeat every modifier you keep:

await Schema.table('users', (table) => {  table.string('name', 50).nullable().change()})

Here name becomes a nullable varchar(50); without nullable(), it would become NOT NULL, which fails while a row holds NULL.

In one Schema.table(), changes run after the columns you add and before everything else, such as renames, indexes and drops: to change a column you rename, rename it in a Schema.table() of its own first.

Renaming columns#

await Schema.table('users', (table) => {  table.renameColumn('from', 'to')})

Dropping columns#

dropColumn() takes a column, or a list of them:

await Schema.table('users', (table) => {  table.dropColumn(['votes', 'avatar', 'location'])})

dropTimestamps(), dropSoftDeletes(), dropRememberToken() and dropMorphs('taggable') drop the columns their creating method added.

Indexes#

Creating indexes#

unique() after a column makes it unique, and index() indexes it:

await Schema.table('users', (table) => {  table.string('email').unique()})

On the table, they take one column or a list, for a compound index, and a name:

await Schema.table('users', (table) => {  table.index(['account_id', 'created_at'])  table.unique('email', 'unique_email')  table.primary(['id', 'parent_id'])})

An index without a name is named after the table, the columns and its kind: users_email_unique.

Dropping indexes#

dropUnique() and dropIndex() take the index's name, or its columns, from which the name is made; dropPrimary() takes the name, {table}_pkey unless you give one:

await Schema.table('users', (table) => {  table.dropUnique('users_email_unique')  table.dropIndex(['account_id', 'created_at'])})

Foreign key constraints#

foreign() adds a foreign key: the column, the column it refers to, and the table:

await Schema.table('posts', (table) => {  table.unsignedBigInteger('user_id')  table.foreign('user_id').references('id').on('users')})

foreignId() with constrained() does both in one line: constrained() guesses the table from the column's name, users for user_id, or takes it:

await Schema.table('posts', (table) => {  table.foreignId('user_id').constrained()  table.foreignId('author_id').constrained('users')})

The actions on delete and update follow:

await Schema.table('posts', (table) => {  table.foreignId('user_id').constrained().cascadeOnDelete()})

dropForeign() drops a foreign key, given its columns: table.dropForeign(['user_id']). dropConstrainedForeignId('user_id') drops the key and the column.

Toggling foreign key constraints#

await Schema.enableForeignKeyConstraints()await Schema.disableForeignKeyConstraints()await Schema.withoutForeignKeyConstraints(async () => {  // Constraints disabled within this closure...})

SQLite and schema changes#

SQLite can't change a column, or add or drop a primary or foreign key, on a table that exists. Oriel does these as Laravel does: it builds the table again with the new definition, copies the rows over, and drops the old one. You write the migration as for any database, with Schema.table().

Each migration on SQLite runs in a transaction with foreign keys turned off, as SQLite asks for schema changes, and checks every foreign key before it commits: a migration that leaves a row pointing at a missing parent fails, and nothing of it is kept. With foreign keys off, ON DELETE and ON UPDATE actions don't run inside the migration, so change the child rows yourself first, or set withinTransaction = false.