aaronabramov / node-sql-migrations

raw SQL migrations for node

node-sql-migrations

raw SQL migrations for node

Example

In your project

// migrate.js
var path = require('path');

require('sql-migrations').run({
    // configuration here. See the Configuration section
});

CLI

run node ./migrate.js with arguments

---

node ./migrate create migration_name

will create two migration files (up and down)

./migrations/1415860098827_up_migration_name.sql
./migrations/1415860098827_down_migration_name.sql

---

node ./migrate migrate

will run all pending migrations

---

node ./migrate.js rollback

will rollback the last migration if there is one

Programmatic API

Migrate

In your project

require('sql-migrations').migrate({
    // configuration here. See the Configuration section
});

This returns a promise which resolves/rejects whenever the migration is complete.

Rollback

In your project

require('sql-migrations').rollback({
    // configuration here. See the Configuration section
});

This returns a promise which resolves/rejects whenever the rollback is complete.

Configuration

Configuration should be specified as below:

var configuration = {
    migrationsDir: path.resolve(__dirname, 'migrations'), // This is the directory that should contain your SQL migrations.
    host: 'localhost', // Database host
    port: 5432, // Database port
    db: 'sql_migrations', // Database name
    user: 'dabramov', // Database username
    password: 'password', // Database password
    adapter: 'pg', // Database adapter: pg, mysql
    // Parameters are optional. If you provide them then any occurrences of the parameter (i.e. FOO) in the SQL scripts will be replaced by the value (i.e. bar).
    parameters: {
        "FOO": "bar"
    },
    minMigrationTime: new Date('2018-01-01').getTime() // Optional. Skip migrations before this before this time.
};

You can also swap out the default logger (the console object) for another one that supports the log and error methods. You should do this before running any other commands:

require('sql-migrations').setLogger({
    log: function() {},
    error: function() {}
});

Migration files

Write raw sql in your migrations. You can also include placeholders which will be substituted. example

-- ./migrations/1415860098827_up_migration_name.sql
create table "test_table" (id bigint, name varchar(255));

-- ./migrations/1415860098827_down_migration_name.sql
drop table "test_table";

node-sql-migrations's Issues

Programmatical migrate() won't work

When you use require('sql-migrations').migrate(configuration) instead of run() it does not attach adapter. So it breaks with an error:
..../node_modules/sql-migrations/commands/run-migrations-command.js:4
return adapter.appliedMigrations()
^
TypeError: Cannot read property 'appliedMigrations' of undefined

migrate() command requires adapter instance as a 2nd argument
as well as rollback()

Add custom logger

It would be nice to be able to provide a custom logger instead of logging to the console all the time

conflict with pg-promise

    pgp().pg.types.setTypeParser(20, parseInt);

this code will result in wrong detection of already applied migrations.

The reason, i guess, is that id column has BigInt type, and gets affected by this

Redundant Entry in __migrations__ table

Hey,
The same issue is also with me.
I am working with dynamic databases.
I've created run time databases as per the requirements.
and every time i work with this i need to create a new database even to test .

With the new database migration works well as Godlike.
but if we want to execute the migration for the old databases it shows me everything OK in the console.

and There is one more thing which I have observed each time when i am executing the migration, the migrations table is getting redundant data for the migrations executed every time.

I'd like to suggest the team that we can create a batch like migration system.
that every time it getting executed so there can be a file which can be possibly fast and feasible way to get all the migrations executes as per the requirements.

You guys are doing a great job. Please let me know if I can help.

Supporting up/down migrations in a single file

Would you open to a PR that adds support for up/down migrations in a single file, similar to the library careen? A combined migration file would have the following form:

CREATE TABLE people (first_name TEXT NOT NULL, last_name TEXT NOT NULL);
---
DROP TABLE people;

Thanks for reading!

migration file can only execute 1 sql statement

Migration will have an error if migration file contains multiple sql statements...

appliedMigrations function not returning migrations in correct order

The statement select * from __migrations__; from pg.js does not return the migrations in the correct order as no order is specified in the postgres command meaning that the results are returned in an unspecified order (docs).

This means that rollbacks are not executed in the order that they were applied. To resolve this the command should be changed to SELECT * FROM __migrations__ ORDER BY id ASC;

Add "parameter" support

It would be nice to be able to specify parameters

For example when provided with the parameters { "FOO": "bar" } it would look for occurrences of ${FOO} in the SQL scripts and replace it with bar.

Migrations are not working after the first time

Migrations are not working after the first time

migrations id property is BIGINT

this returns migration.match(/^(\d+)/)[0]; a string

so this appliedMigrationIds.indexOf(id) is equals -1

please check my PR #17

New Maintainer

I'm not working with Postgres these days. If anyone would like to maintain this project get in touch.

Suggestion: Common queries can be generated for migrations

Hi,

Is there any way, so that we can create these migrations things like classes and having function up() and down() in a single file.

This will be more user friendly that if we need not to write whole queries as in the migrations files.

I feel so shy to suggest you guys this thing as I am a developer only.
But this can be a good and time saving process for the developers.

Please ping me for any discussion.

Thanks and cheers guys.

Support for PostgreSQL Uri Connection

Support for postgresql uri connection:
postgresql://user:secret@localhost:435/somedb

Reference link: docs

pg dependency update needed

I tried to run a migration on Node v6.5.0 and was getting ERROR: { error: password authentication failed for user .... The password was actually correct so it was very confusing.

It costed me substantial time to dig the code and google to find a similar issue loopbackio/loopback-connector-postgresql#138

After updating pg to 4.5.5 the migrations finally worked. I'm making a pull request.