Migrations

Connection

The script will take the sql.js.

Directory

The migration files needs to be in the directory migrations.

Filename

The name of the migration files needs to start with a number and can have also a name, e.g. 201908221621-create-table-users(.sql/.js). The numbers of all migration files needs to be in an ascending order to get executed in the right order.

There is a migrate_make (recommended to name it migrate:make in your user scripts) command available to create new migration files which get prefixed with the current unix timestamp.

Formats

There are two different formats for the migration files supported.

• .sql with plain SQL queries, e.g.:

CREATE TABLE "users" {
  "id" SERIAL NOT NULL PRIMARY KEY,
  "name" VARCHAR (255) NOT NULL,
  "email" VARCHAR (255) UNIQUE NOT NULL,
  "password" CHAR (60),
  "validated" BOOLEAN DEFAULT 0
};

• .js exporting an async function getting the client, e.g.:

module.exports = async sql => sql.query(
  sql`CREATE TABLE "users" {
    "id" SERIAL NOT NULL PRIMARY KEY,
    "name" VARCHAR (255) NOT NULL,
    "email" VARCHAR (255) UNIQUE NOT NULL,
    "password" CHAR (60),
    "validated" BOOLEAN DEFAULT 0
  }`
)

Column and Updated At Helpers

Every table should have an id column defined as SERIAL NOT NULL PRIMARY KEY and also created_at and updated_at defined as TIMESTAMPTZ NOT NULL DEFAULT NOW(). The updated_at should also be automatically updated with the current timestamp if the row is changed, which can be tricky with PostgreSQL.

For these requirements there are helpers which can be used in .js based migrations, e.g.:

module.exports = async (sql, { columns, updatedAt }) => {
  await sql.query(
    sql`CREATE TABLE "users" {
      ${columns.id},
      ${columns.created_at},
      ${columns.updated_at},
      "name" VARCHAR (255) NOT NULL,
      "email" VARCHAR (255) UNIQUE NOT NULL,
      "password" CHAR (60),
      "validated" BOOLEAN DEFAULT 0
    }`
  )
  await sql.query(updatedAt('users'))
}

CLI Commands

{
  "scripts": {
    "migrate": "migrate",
    "migrate:make": "migrate_name"
  }
}

For local usage, if dotenv-safe is available, it will be used.

If the npm package debug is installed, it will be used and all outputs are labeled with migrate. Otherwise console.log is used.

Attention

The migration script will create and maintain the table migrations to check which migration files are already executed.

Also a trigger function named trigger_set_timestamp will be created and for every table which will use the Updated At Helper (including the migrations table itself) there will be a trigger created named set_timestamp_${table}.

So be careful there are no name conflicts.