Dead simple database migration

example create table migration (more examples at end)

• filename `001-user-table.sql

-- Up
create table users (
    id SERIAL PRIMARY key,
    user_id TEXT,
    password TEXT,
    active BOOLEAN,
    created timestamp,
    updated timestamp
);
-- Down
drop table users;

database table creation

• the following is the migration tracking table added to the database

create table dead_simple_migrations (
    id integer primary key,
    name text,
    up text,
    dn text,
    run_at timestamp default now()
)

Settings

• order of precedence
  • defaults
  • environment vars
  • programatic setting

Code config - call to migrate if using like a library

• dialect is currently either pg for postgres or sqlite for sqlite

const migrate = require('simple-db-migrate');

// These are the default settings if none are used
let config = {
  dialect: 'pg',
  database: 'test',
  user: 'postgres',
  password: 'postgres',
  host: 'localhost',
  port: 5432,
};

// call migrate(full_path_to_migrations, config options);
await migrate('./migrations', config);

Environment vars

• dialect: process.env.MIGRATE_DIALECT
• database: process.env.MIGRATE_DATABASE
• user: process.env.MIGRATE_USER
• password: process.env.MIGRATE_PASSWORD
• host: process.env.MIGRATE_HOST
• port: process.env.MIGRATE_PORT

command line options to simple-db-migrate

• migrate and rollback options

  • --verbose, -v toggle on
  • --dialect, -d one of [pg, sqlite]
  • --database, -db string
  • --user, -u string
  • --password, -p string
  • --host, -h string
  • --port number

• rollback only options

  • --rollback, -r toggle on runs rollback script from database
  • --force, -f toggle on Will force read from files not database
  • --step, -s number step to rollback inclusive

• misc

  • --help Show help text

• --dialect pg and --dialect=pg are equivalent same for all options

migrate <options>

my other npm package(s)

https://www.npmjs.com/package/fluent-sql

• SQL generator
• lets you do something like the following

const query = new SqlQuery()
  .select(users.id, users.username, users.password)
  .from(users)
  .where(users.username.eq('jdoe'));
  // ...
const userDetail = new SqlQuery()
  .select(users.star(), addresses.star())
  .from(users)
  .join(addresses.on(addresses.id).using(users.addressId));

more examples

• add some user data (don't do this, since the password is plain text)

-- Up
insert into users (
    user_id,
    password,
    active,
    created,
    updated,
Values
('id1', 'password11', 1, '1/1/2019', '1/1/2019'),
('id2', 'password12', 0, '1/1/2019', '1/1/2019'),
('id3', 'password13', 0, '1/1/2019', '1/1/2019'),
('id4', 'password14', 1, '1/1/2019', '1/1/2019'),
('id5', 'password15', 0, '1/1/2019', '1/1/2019'),
('id6', 'password16', 1, '1/1/2019', '1/1/2019');
-- Down
delete from users where id = 'id1';
delete from users where id = 'id2';
delete from users where id = 'id3';
delete from users where id = 'id4';
delete from users where id = 'id5';
delete from users where id = 'id6';

• alter table NOTE: this will not work for SQLite since it does not support drop column

-- Up
alter table some_table add column engine text;
alter table some_table add column result text;

-- Down
alter table some_table drop column engine;
alter table some_table drop column result;

if you are in the repo, not the npm install

docker-compose up

Will start a local postgres server in docker on port 5432.