Skip to content
Emigrate

Baseline

A common scenario is to have an existing database that you want to start managing with Emigrate. This is called baselining.

Baselining an existing database schema

Let’s assume you have a PostgreSQL database with the following schema:

CREATE TABLE public.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);


CREATE TABLE public.posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES public.users(id),
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
PostgreSQL Storage Plugin See how to configure the PostgreSQL storage plugin here...
Storage Plugins Learn more about storage plugins here...

Create a baseline migration

You can baseline this database by first creating a baseline migration (here we name it “baseline”):

Terminal window
npx emigrate new --plugin postgres baseline

Which will generate an empty migration file in your migration directory:

migrations/20240118123456789_baseline.sql
-- Migration: baseline

You can then add the SQL statements for your database schema to this migration file:

migrations/20240118123456789_baseline.sql
-- Migration: baseline
CREATE TABLE public.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);


CREATE TABLE public.posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES public.users(id),
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Log the baseline migration

For new environments this baseline migration will automatically be run when you run emigrate up. For any existing environments you will need to run emigrate up with the --no-execution flag to prevent the migration from being executed and only log the migration:

Terminal window
npx emigrate up --no-execution

In case you have already added more migration files to your migration directory you can limit the “up” command to just log the baseline migration by specifying the --to option:

Terminal window
npx emigrate up --no-execution --to 20240118123456789_baseline.sql

Verify the baseline migration status

You can verify the status of the baseline migration by running the emigrate list command:

Terminal window
npx emigrate list

Which should output something like this:

emigrate list
Emigrate list v0.14.1 /your/project/path


  ✔ migrations/20240118123456789_baseline.sql (done)


  1 done (1 total)

Happy migrating!

You can now start adding new migrations to your migration directory and run emigrate up to apply them to your database. Which should be part of your CD pipeline to ensure that your database schema is always up to date in each environment.