meyer is a database migration tool with inversion of control (via pluggable DBMS).
Meyer supports sequentially named plain SQL files which apply migrations one after another. An UP and DOWN section is specified in each file which should provide all the necessary instructions on how to both apply and remove a migration. While your project is in development, Meyer will freely apply statements in the DOWN if prior migrations are modified.
Installation
Install meyer into your project using your package manager:
yarn add meyer
Additionally, you will need to add a DBMS. We have created both a generic Knex DBMS and an MSSQL DBMS.
Install one of these options:
yarn add meyer-dbms-knex
or
yarn add meyer-dbms-mssql
Usage
Here is an example of how you could use meyer to apply migrations to your database. Note that this example assumes you're using postgres and meyer-dbms-knex. It also relies on having pg-connection-string installed. You can install it with the following command: yarn add pg-connection-string. Finally, it assumes that you are storing the connection string to your database in an environment variable named DATABASE_URL. You can use dotenv if you want to load it from a local file or you can set this variable in your system.
Create a file named migrate.js inside of a folder named database with the following content:
This code specifies the migrationsPath directory as ../migrations which means we also need another folder inside the root of our project named migrations.
Create this migrations directory and add your first migration!
Migration format:
Migrations happen in chronological order, so suggest the following naming pattern:
001-myfirstmigration.sql
002-mysecondmigration.sql
etc.
Each migration must contain an up and down section, here is an example migration which shows how to use them:
-- up:begincreate extension citext;CREATE TABLE sessions ( id SERIAL, user_id INTEGER NOT NULL, expires TIMESTAMPTZ NOT NULL, session_token text NOT NULL, access_token text NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id));CREATE TABLE users ( id SERIAL, name text, email citext, email_verified
Note that the down should happen in reverse order of the up in case there are dependencies on other items.
If you created the above script as mentioned, running yarn migrate here would put run this migration.
Alternatively, you could invoke the migrate function on application start by adapating that code into a module and calling the run function from wherever makes sense in your code.