db-migrate / node-db-migrate Goto Github PK

there is no "bigint" in lib/data_type.js, is there a way i can manually add this?

db-migrate works with mysql version 0.9.6, however it doesn't appear to work with version 2.0.0-alpha3.

The mysql site (https://github.com/felixge/node-mysql) writes:
npm install [email protected]
Despite the alpha tag, this is the recommended version for new applications. For information about the previous 0.9.x releases, visit the v0.9 branch.

According to the change notes, "The first thing you will run into is that the old Client class is gone and has been replaced with a less ambitious Connection class. So instead of `mysql.createClient() you now have:

var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'me',
password : 'secret',
});

connection.query('SELECT 1', function(err, rows) {
if (err) throw err;

console.log('Query result: ', rows);
});

connection.end();

db-migrate up --config config.json -e dev

message: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(255) NOT NULL, run_on INTEGER NOT NULL)' at line 1',
sql: 'CREATE TABLE IF NOT EXISTS migrations (id INTEGER PRIMARY KEY auto_increment NOT NULL, name VARCHAR(255) (255) NOT NULL, run_on INTEGER NOT NULL)',

Hey, this module looks awesome! My current project uses mysql 0.9.5--an older-than-usual version--and I wasn't sure what versions you support, since mysql isn't explicitly mentioned as a dependency in package.json (probably with good reason, since you don't want folks using your product w/ sqlite or pg to needlessly bring it in). However, the minimum version requirement also isn't documented in the README, so I ended up cloning the repo and running the test suite against mysql 0.9.5 just to make sure. It does work, which is great, though I'm wondering if you intend to continue supporting older versions like this... so any documentation regarding this in the README would be really helpful.

Thanks again for making this great tool.

db-migrate appears to successfully execute migrations, but when it's done, it sits there without returning to the shell. I have to manually press Control+C to break out.

Trace:

$ db-migrate up
[INFO] Processed migration 20120828003940-add-users
[INFO] Processed migration 20120830212635-add-approver-id-to-users
[INFO] Processed migration 20120831174003-add-roles
...
[INFO] Processed migration 20130208181305-create-timecard-completions
[INFO] Processed migration 20130211185210-add-managers-table
[INFO] Processed migration 20130213194120-remove-invalid-facebook-list-posts
[INFO] Done
(blinking cursor forever)

System:

$ db-migrate --version
0.3.2

$ specs node bash git os
Specs:

specs 0.2
https://github.com/mcandre/specs#readme

npm --version
1.2.10

node --version
v0.8.19

bash --version
GNU bash, version 3.1.0(1)-release (i686-pc-msys)
Copyright (C) 2005 Free Software Foundation, Inc.

git --version
git version 1.8.0.msysgit.0

systeminfo | findstr /B /C:"OS Name" /C:"OS Version"
OS Name:                   Microsoft Windows XP Professional
OS Version:                5.1.2600 Service Pack 3 Build 2600

This API will need a callback on completion. It would also be nice to have a method to wipe the database out or a safe drop table.

var dbOptions = {
      driver: 'sqlite3',
      filename: ':memory:',
      trace: true
    };
    dbMigrate.connect(dbOptions, function(err, migrator) {
      if(err) { console.error(err); return; }
      migrator.up(
        function(db) {
          db.dropTable('person', { ifExists: true });
          db.createTable('person', {
            columns: {
              name: dbMigrate.type.STRING
            },
            engine: 'innodb'
          });
          db.runSql('DELETE FROM person');
        },
        function() {
          console.log("done");
        });
    });

SQL is wrong. Fix is coming soon.

Hi I got this issue with this Postgresql migrations
running on mac with postgre 9.1.3

db-migrate up --config config/database.json --verbose
[INFO] Using config: { driver: 'pg',
user: 'dbuser',
password: '',
host: 'localhost',
database: 'dbdev' }
[INFO] pg.runSql { '0': 'CREATE TABLE  migrations (id   SERIAL PRIMARY KEY NOT NULL, name VARCHAR (255) NOT NULL, run_on TIMESTAMP  NOT NULL)',
 '1': [Function] }
[ERROR] { [error: relation "migrations" already exists]
length: 97,
name: 'error',
severity: 'ERROR',
code: '42P07',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
file: 'heap.c',
line: '1011',
routine: 'heap_create_with_catalog' }

After a while, the project accumulates a large number of migrations.

When working in Rails, I typically delete and squash migration files to reduce clutter.

node-db-migrate seems to require the migration file immediately, instead of lazily, which causes an error after I remove a file.

Running my first migration, once I turned off --dry-run (#82), my migrations successfully ran (even with hstore and uuid column-types, thanks for that), but the migration failed to write to the migrations table:

$ node_modules/.bin/db-migrate up -v --config config/database.json -e dev
[INFO] Using dev settings: { driver: 'pg', database: '**' }
[INFO] require: ./pg
[INFO] connecting
[INFO] connected
[SQL] select version() as version
[INFO] creating table: migrations
[SQL] CREATE TABLE IF NOT EXISTS migrations (id SERIAL PRIMARY KEY NOT NULL, name VARCHAR (255) NOT NULL, run_on TIMESTAMP NOT NULL)
[INFO] migration table created
[INFO] loading migrations from dir **/migrations
[INFO] loading migrations from database
[INFO] preparing to run up migration: 20130320005029-createItems

[SQL] INSERT INTO migrations (name, run_on) VALUES ($1, $2) [ [ '20130320005029-createItems',
Tue Mar 19 2013 18:26:53 GMT-0700 (PDT) ] ]
[ERROR] error: syntax error at or near "-"
at Connection.parseE (_/node_modules/pg/lib/connection.js:508:11)
at Connection.parseMessage (_/node_modules/pg/lib/connection.js:368:17)
at Socket.Connection.attachListeners (***/node_modules/pg/lib/connection.js:84:20)
at Socket.EventEmitter.emit (events.js:96:17)
at TCP.onread (net.js:397:14)

I might dig in and try fixing this one myself, but figured I'd post in case there was a known fix or this was user error.

This would enable db specific column info. For example, on a table create:

db.createTable( 'name', {
id: { type: 'uuid',
passthrough: true,
primaryKey: true
}
}

If an unrecognized type is found and passthrough is not explicitly set to true, you could throw an error. If passthrough is true, just write out the value to a string and try to execute the sql...

This would enable me to maintain my table info in a consistent, declarative manner instead of having to sometimes drop down to executing full blown native sql queries.

Is it possible to test the value being used for 'env' from within a migration? I would like to add additional records for 'test' and 'dev' environments. I'm looking for something like:

dbm = require("db-migrate");

export.up = function(db, callback) {
     // create/modify tables
     ...

    // Add initial data
    ... Add initial data for all environments. ...
    if (dbm.env != 'prod') {
        ... add initial data for testing and development ...
    }

I suppose I could just parse the command line myself but it seems like this should be an API function. I am new to node.js so I hope I am not missing something obvious.

The MySQL driver currently has an invalid CHANGE COLUMN syntax. It should include a new column name (this can be the old name).

ALTER TABLE `table` CHANGE COLUMN `a` `a` INT

Unless I'm completely screwing it up.

Here's my database.json:

/* database.json */
{
    "dev": {
        "driver": "mysql",
        "user": "username",
        "password": "password",
        "database": "database"
    }
}

And how I'm executing it:

./node_modules/db-migrate/bin/db-migrate --config database.json -e dev up --verbose
[INFO] Using config: { driver: 'mysql',
  user: 'username',
  password: 'password',
  database: 'database' }
[ERROR] { [Error: connect ECONNREFUSED]
  code: 'ECONNREFUSED',
  errno: 'ECONNREFUSED',
  syscall: 'connect' }

But here is evidence I can connect with those connection parameters:

$ mysql -uusername -ppassword database
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 273
Server version: 5.1.41-3ubuntu12.10-log (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
Empty set (0.00 sec)

Of course I changed the parameters to protect the innocent.

Can't possibly make that working, probably there's need to force migration to use ssl, but how to do it?

Code pushed to heroku then

heroku run ./db-migrate up

does not finds any migrations (db-migrate is symbolic link to node_modules/db-migrate/bin/db-migrate)

please refer to #49 as there's some info about fallback to database.js (which is not working as I checked), and there's no info what to return there?

Manually changing

var pg = require('pg')

to

var pg = require('pg').native

triggers segmentation fault error:

WARNING: ev_io is deprecated, use uv_poll_t
Segmentation fault

When I try using --dry-run during my first migration, I get this.

$ node_modules/.bin/db-migrate up -v --config config/database.json -e dev --dry-run
[INFO] dry run
[INFO] Using dev settings: { driver: 'pg', database: '***' }
[INFO] require: ./pg
[INFO] connecting
[INFO] connected
[SQL] select version() as version
select version() as version
[ERROR] TypeError: Cannot read property 'rows' of undefined

I got the above error when running 'db-migrate' up. I have installed mysql using 'npm install mysql'. My database.json is located here https://github.com/trivektor/Scrummy/blob/master/database.json. Please advise. Thanks.

It's often helpful to be able to --version a command line program to report its version number, e.g. for retrieving system specs in bug reports.

In the mean time, this is available as npm list db-migrate, but only from a project directory for which db-migrate is a dependency.

$ cd COOLPROJECT/
$ npm list db-migrate
[email protected] c:\COOLPROJECT
└── [email protected]

TEXT is limited to 64kb. LONGTEXT is 4GB. Add support for LONGTEXT.

I think it would be better to keep the rows so you have a complete audit log of what migrations have been applied to the database.

Running a line like this produces a SQL error because the apostrophe in "isn't" isn't escaped in the string sent down to the DB, thus producting a syntax error.

  db.insert("reasons", ["id", "title", "description"], [1, "Resource Unavailable", "The requested URL isn't available due to server down or HTTP 4xx errors"], group());

I'm using db-migrate in a project where it works great locally, but then I get a bunch of these on Travis-CI:

[ERROR] 20121016214552-add-group-notes { [Error: Incorrect datetime value: '2012-10-25T16:25:16.357Z' for column 'run_on' at row 1]
2068 number: 1292,
2069 sqlStateMarker: '#',
2070 sqlState: '22007',
2071 message: 'Incorrect datetime value: \'2012-10-25T16:25:16.357Z\' for column \'run_on\' at row 1',
2072 sql: 'INSERT INTO migrations (name, run_on) VALUES (\'20121016214552-add-group-notes\', \'2012-10-25T16:25:16.357Z\')',
2073 setMaxListeners: [Function],
2074 emit: [Function],
2075 addListener: [Function],
2076 on: [Function],
2077 once: [Function],
2078 removeListener: [Function],
2079 removeAllListeners: [Function],
2080 listeners: [Function],
2081 addVow: [Function: addVow] }

I'm not sure if this is a bug or there's something I need to be doing config-wise.

The following command:

./node_modules/db-migrate/bin/db-migrate create sample --migrations-dir components/database/migrations

will correctly create the components/database/migrations directory if it doesn't yet exist. But, it will then try to open

migrations/0000digits_here0000-sample.js

instead of

components/database/migrations/0000digits_here0000-sample.js

I looked at the code and figured out that the directory option isn't even getting passed to the index.createMigration() call. It also isn't getting stored away anywhere else. Instead the function formatPath() in migration.js has a path hard-coded in:

function formatPath(name) {
  return process.cwd() + '/migrations/' + name + '.js';
}

I didn't want to try to fix the issue myself since I imagine you might have a preferred way to do so.

Example. If I have a table named "people" and my migration tries to drop the table and then recreate it I would expect that to work. Instead I get a people table already exists error.

to fix this maybe all sql statements should be queued up and executed in order. use https://github.com/caolan/async#queue with a concurrency of 1?

With the refactoring done in 38cfdf8, createMigrationDir only calls the callback if the migration directory doesn't exist.

Would be good for db-migrate to keep track of a full database schema so you can just load up the schema on new installs instead of running every migration.

db-migrate load

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

Currently (assuming #59 and #61 are addressed) one cannot add AUTO_INCREMENT to a column.

{
    type: type.INTEGER,
    primaryKey: true,
    unsigned: true,
    autoIncrement: true
}

... currently results in:

ALTER TABLE `users` CHANGE COLUMN `id` `id` INT PRIMARY KEY AUTO_INCREMENT
-- [Err] 1068 - Multiple primary key defined

It should be (perhaps by omitting primaryKey: true):

ALTER TABLE `table` CHANGE COLUMN `id` `id` INT AUTO_INCREMENT

I expected integer[] to work just like integer does, but it doesn't.

For now I can just use runSql, so no big deal, but maybe you want to support it. Feel free to close this w/ no explanation if you don't want to!

sample code:

var pcb = require('parallel_callback');
var dbm = require('db-migrate');
var type = dbm.dataType;

exports.up = function (db, done) {
  db.createTable('recipes', {
    name: 'string'
    // direction_ids: 'integer[]' // <-- doesn't work
  }, function(a) {
    // but this does
    db.runSql("ALTER TABLE recipes ADD COLUMN direction_ids integer[];", done);
  });
};

I can't see an option in usage for specifying a database password.

Does db-migrate require the databases to have no password?

E.g., NIC+ uses db-migrate, but dev environments require the developer to manually configure MySQL first, creating empty databases.

Could db-migrate do this for us?

At the moment I don't have a test case to make this fail, but it seems like it would throw an exception if a blob type was used on postgres. It's not a priority for me, but I could submit a pull request if needed.

https://github.com/nearinfinity/node-db-migrate/blob/master/lib/driver/pg.js#L36

the code could look like this:

db.dropTable('person', { ifExists: true });
db.createTable('person', {
  columns: {
    name: dbMigrate.type.STRING
  },
  engine: 'innodb'
});

$node --version
v0.8.12

$ npm list
[email protected] /home/greg/work/node-postgres
├─┬ [email protected]
│ ├── [email protected]
│ ├── [email protected]
│ ├── [email protected]
│ ├─┬ [email protected]
│ │ └── [email protected]
│ └── [email protected]
└─┬ [email protected]
└── [email protected]

$ ./db-migrate up -v
[INFO] Using dev settings: { driver: 'pg',
user: 'dev',
password: 'dev',
host: 'localhost',
database: 'links' }
[INFO] require: ./pg
[INFO] connecting
[INFO] connected
[SQL] select version() as version
[INFO] creating table: migrations
[SQL] CREATE TABLE IF NOT EXISTS migrations (id SERIAL PRIMARY KEY NOT NULL, name VARCHAR (255) NOT NULL, run_on TIMESTAMP NOT NULL)
[INFO] migration table created
[INFO] loading migrations from dir /home/greg/work/node-postgres/migrations
[INFO] loading migrations from database
[INFO] preparing to run up migration: 20130122213408-add-links-table
[SQL] BEGIN;
[INFO] creating table: links
[SQL] CREATE TABLE links (link VARCHAR (255) UNIQUE, status VARCHAR DEFAULT 'new')

It creates migrations table (empty) and then freezes on this moment.

My migration:

var dbm = require('db-migrate');
var type = dbm.dataType;

exports.up = function(db, callback) {
db.createTable('links', {
    link: {type: 'string', length: 255, unique: true},
    status: {type: 'string', defaultValue: 'new'}
}, callback);
};

exports.down = function(db, callback) {
db.dropTable('links', callback);
};

I have no errors and can't find anything in docs.

When I try to MysqlDriver#changeColumn(), no options are passed to MysqlDriver#createColumnDef() and #createColumnConstraint(). These options are used when changing a primary key. The migration stops here:

if (spec.primaryKey && options.emitPrimaryKey) {
  constraint.push('PRIMARY KEY');

What are these options and what is emitPrimaryKey?

Migration

var dbm = require('db-migrate');
var type = dbm.dataType;

exports.up = function(db, callback) {
    db.changeColumn(
        "users",
        "id",
        {
            type: type.INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        callback
    );
};

exports.down = function(db, callback) {
    db.changeColumn(
        "users",
        "id",
        {
            type: type.INTEGER,
            primaryKey: true
        },
        callback
    );
};

$ ./node_modules/db-migrate/bin/db-migrate --config database.json -e dev up; echo $?
[ERROR] { [Error: connect ECONNREFUSED]
  code: 'ECONNREFUSED',
  errno: 'ECONNREFUSED',
  syscall: 'connect' }
0

I would have expected it to exit with a non-zero to indicate a failure, allowing scripts to bail out appropriately.

This would be really helpful when db-migrate can't be run directly and would help in debugging.

Is there any support, or plans for supporting functions like 'now()' in defaultValue? Maybe something like defaultFunction, where the value would not be quoted in the resulting SQL?

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

Trying to test this with a postgresql database which is running. I can connect via the pg module on my own but I can't load the pg module via db-migrate.

Here's my config:

{
"dev": {
"driver": "pg",
"user": "jakobdamjensen",
"password": "mysecret=)",
"host": "localhost",
"database": "ingenium_golf"
}
}

The pg module is installed (v.0.8.4) and is working in my node app.

I've tried using your pets example but not luck =(

• MSSQL

Especially foreign keys.

Maybe something like this:

colName: { type: 'string',
constraints: {
notNull: true
foreignKey: {name: 'fk_employee_id', references: 'employee(id)' }
}
}

I am trying to add a MySQL primary key column with addColumn which is throwing the error:

Fatal error: Cannot read property 'emitPrimaryKey' of undefined

The problem comes from /lib/driver/mysql.js#L68. The code is assuming that primary keys (and auto increments) will only be added in #createTable which is incorrect. I fixed this by changing:

Old

// /lib/driver/mysql.js#L68
if (spec.primaryKey && options.emitPrimaryKey) {
  constraint.push('PRIMARY KEY');
  if (spec.autoIncrement) {
    constraint.push('AUTO_INCREMENT');
  }
}

New

if (spec.primaryKey && (!options || options.emitPrimaryKey)) {
  constraint.push('PRIMARY KEY');
  if (spec.autoIncrement) {
    constraint.push('AUTO_INCREMENT');
  }
}

This seems a little weird, but hell if it doesn't work. Another option would be to set options.emitPrimaryKey to true in #addColumn, #changeColumn and such. What is going on here?

Perhaps this is similar to #59 and #60, but I think this is a separate issue.

Hello,

When running the following migration:

var dbm = require('db-migrate');
var type = dbm.dataType;

exports.up = function(db, callback) {
  db.createTable('pets', {
    id: { type: 'int', primaryKey: true },
    name: 'string'
  }, callback);
};

exports.down = function(db, callback) {
  db.dropTable('pets', callback);
};

I got the following error message:

[ERROR] { [Error: SQLITE_ERROR: near "(": syntax error] errno: 1, code: 'SQLITE_ERROR', __augmented: true }

I use node-sqlite3 version 2.1.5

Any idea of the problem ?

thanks,
Luc

I have a whole bunch of migrations I've built up over a period of time, and I just tried to run them on an empty database (to create a brand new database for my app). I received errors that indicate the migrations are being run in the wrong order (using --verbose I see it running a migration that it shouldn't be running based on the timestamps). I'm at a loss to explain how this could happen from looking at the code, since I see the array of migrations is sorted twice (once when read from the filesystem and again in filterUp()). Is it possible that the filtering code is messing up the order? Is it possible that sort() isn't sorting quite the way that is intended? Please let me know if I can offer any further info.

Here is what I really want to do:

• update my database.json file with my heroku postgres db connection info
• run db-migrate against a remote postgres heroku instance

Heroku only allows remote connections to come over SSL. My understanding is that this requires('pg').native. So I'd love for some way to specify whether I want 'pg' or 'pg' native. Then I can try to run my remote migrations (and I'll let you know if it works or if I run into another issue...).

Thanks!

Do you have plan to add CUBRID Database (http://www.cubrid.org) support in node-db-migrate? What would it take to support CUBRID?

CUBRID provides high level of SQL compatibility with MySQL, so I think adding support would be straightforward.

The documentation says to call removeIndex as

removeIndex(indexName, callback)

However, doing so against a MySQL server leads to

[ERROR] { [Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(err) {
var args = Array.prototype.slice.call(arguments,' at line 1] code: 'ER_PARSE_ERROR', index: 0 }

A cursory search of the code base shows that in lib/driver/base.js it is indeed removeIndex(indexName, callback)
However in lib/driver/mysql.js it is removeIndex(tableName, indexName, callback)

This makes the API inconsistent. I suspect both base.js and the documentation need to be updated to be compatible with the MySQL driver library, although I haven't looked into it in detail.

Could we add Redis support? I'm not sure if that would even make sense, since Redis is a key-value not a schema-based.

This is not so much an issue as a request.
I'm including db-migrate as a module require() and writing my own equivalent of bin/db-migrate. This is because I need to manage and migrate a dynamically generated list of databases (e.g. one per customer).
The API is documents for use as a command line application, but not for use as a module, and I just want to make a request that an effort be made to stabilize this module API.
Thanks.