db-migrate/db-migrate-base

Base driver for db-migrate

db-migrate-base

Base driver for db-migrate

db-migrate-base's Issues

Creating migrations table fails in version 1.6.0

I'm seeing the following error since upgrading to version 1.6.0, at the point of db-migrate creating the migrations table:

[ERROR] TypeError: Cannot read property 'raw' of undefined
    at Object._prepareSpec (/usr/lib/node_modules/db-migrate-pg/node_modules/db-migrate-base/index.js:46:26)
    at Object.createTable (/usr/lib/node_modules/db-migrate-pg/node_modules/db-migrate-base/index.js:258:12)
    at /usr/lib/node_modules/db-migrate/lib/driver/shadow.js:34:50
    at tryCatcher (/usr/lib/node_modules/db-migrate/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/usr/lib/node_modules/db-migrate/node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (/usr/lib/node_modules/db-migrate/node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromiseCtx (/usr/lib/node_modules/db-migrate/node_modules/bluebird/js/release/promise.js:606:10)
    at _drainQueueStep (/usr/lib/node_modules/db-migrate/node_modules/bluebird/js/release/async.js:142:12)
    at _drainQueue (/usr/lib/node_modules/db-migrate/node_modules/bluebird/js/release/async.js:131:9)
    at Async._drainQueues (/usr/lib/node_modules/db-migrate/node_modules/bluebird/js/release/async.js:147:5)
    at Immediate.Async.drainQueues (/usr/lib/node_modules/db-migrate/node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:649:20)
    at tryOnImmediate (timers.js:622:5)
    at processImmediate [as _immediateCallback] (timers.js:594:5)

Edit
This is being installed as dependencies of the following:
[email protected] [email protected]

Unknown types are uppercased without recourse

In our migrations using the db.createTable API, if we specify an unsupported type, it becomes uppercased (see here). This is not desirable; in some cases the type is a list of enum values (using the MySQL db-migrate driver). While it "works", it means that the values returned for the column are uppercased, but we'd prefer them to be in the case given (lowercase), since MySQL will return the enum value in whichever case it was when the table was defined.

If you want the unsupported type to be uppercased, please parse for quoted strings and preserve the content of each quoted string.

Need to use escapeDDL for "key"

Mysql treats "key" as a reserved keyword.

[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 'key = 'dbmigrate_schema'' at line 1

functions _getKV and _deleteKV are the obvious offenders.

How to add a custom command?

I'd like to create a custom command, and maybe make a plugin out of it for those who might need it.

I don't see any documentation on how to do it, and current plugins don't add commands.

Current plugin looks like this

module.exports = {

  loadPlugin: function() {

    module.exports = Object.assign(module.exports, {

      'init:api:addfunction:hook': function() {

        console.log('custom command')

        return {};
      }
    });

    delete module.exports.loadPlugin;
  },
  name: 'fresh',
  hooks: [
    'init:api:addfunction:hook'
  ]
};

It's loaded successfully as a plugin but I can't find out how to add it to the bin file & add it as an actual command

---

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

When no primary key is defined on a table during `.createTable` things blow up

Found this today while running migrations from 2 years ago on a build server.

https://github.com/db-migrate/db-migrate-base/blob/master/index.js#L207

If you define a table that doesn't have a column as a primary key, this will fail. Having a table with no primary key is not always standard but it is a thing.

I will submit a PR that fixes this shortly.

Migration Table add "sql" VARCHAR col (traceability)

So currently the migrations table has id, name, and run_on as columns. I am proposing to either add a sql column to actually save off what has run for traceability.

Granted this is pumped to stdout and could just be utilized in your CI architecture.

Even if this is not accepted would it be possible to make createMigrationsTable's options more flexible so that custom columns could be added more easily.

Seems hardcoded here

Escape doesn't escape anything at all when using double quotes

db-migrate-base/index.js

Line 682 in 05762f3

return str.replace(/"/g, '"');

return str.replace(/"/g, '"');

The replacement string was probably meant to be '\\"'.

The code should be

return str.replace(/\\/g, '\\\\').replace(/"/g, '\\"');

---

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

addIndex doesn't work with JSONB indexes

In PostgreSQL, there's a JSONB data type that you can use, allowing you to use PostgreSQL kind of like a document database. In order to make this efficient, you can tell PostgreSQL to create indexes on keys and nested keys in JSONB documents. This can be done with:

CREATE INDEX "idx_TABLE_on_KEYS" ON "TABLE" ((data::jsonb->'KEY1'->>'KEY2'))

Using db.addIndex, I'd have thought I could do the following:

await db.addIndex(
  'TABLE',
  'idx_TABLE_on_KEYS',
  `(data->'KEY1'->>'KEY2')`
);

To produce the same index as the above SQL. However, it turns out that db.addIndex automatically (and perhaps sensibly) attempts to escape the values you're passing, which means you get the following query being executed:

CREATE INDEX "idx_TABLE_on_KEYS" ON "TABLE" ("(data->'KEY1'->>'KEY2')")

The additional quotes in this case actually break the creation of the index.

> [ERROR] AssertionError [ERR_ASSERTION]: ifError got unwanted exception: column "(data->'KEY1'->>'KEY2')" does not exist

I'm not sure what the right fix is, but perhaps it'd be an idea to allow the user to say "actually, I know what I'm doing, please don't escape this" whilst giving the default of escaping column names.

Thoughts?

---

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

Not able to run db-migrate up

I am trying to run the db-migrate up and getting this error.

[ERROR] Error: not implemented at Object.runSql (/Users/ahmedwasfy/Documents/sears/PIT/ca.sears.intelligence.price/server/node_modules/db-migrate-base/index.js:496:11) at /Users/ahmedwasfy/Documents/sears/PIT/ca.sears.intelligence.price/server/db/migration/mongodb/scripts/20170612183925-test.js:32:15 at tryCatcher (/Users/ahmedwasfy/Documents/sears/PIT/ca.sears.intelligence.price/server/node_modules/bluebird/js/release/util.js:16:23) at Promise._settlePromiseFromHandler (/Users/ahmedwasfy/Documents/sears/PIT/ca.sears.intelligence.price/server/node_modules/bluebird/js/release/promise.js:512:31) at Promise._settlePromise (/Users/ahmedwasfy/Documents/sears/PIT/ca.sears.intelligence.price/server/node_modules/bluebird/js/release/promise.js:569:18) at Promise._settlePromise0 (/Users/ahmedwasfy/Documents/sears/PIT/ca.sears.intelligence.price/server/node_modules/bluebird/js/release/promise.js:614:10) at Promise._settlePromises (/Users/ahmedwasfy/Documents/sears/PIT/ca.sears.intelligence.price/server/node_modules/bluebird/js/release/promise.js:693:18) at Async._drainQueue (/Users/ahmedwasfy/Documents/sears/PIT/ca.sears.intelligence.price/server/node_modules/bluebird/js/release/async.js:133:16) at Async._drainQueues (/Users/ahmedwasfy/Documents/sears/PIT/ca.sears.intelligence.price/server/node_modules/bluebird/js/release/async.js:143:10) at Immediate.Async.drainQueues (/Users/ahmedwasfy/Documents/sears/PIT/ca.sears.intelligence.price/server/node_modules/bluebird/js/release/async.js:17:14) at runCallback (timers.js:672:20) at tryOnImmediate (timers.js:645:5) at processImmediate [as _immediateCallback] (timers.js:617:5)

here is my config

{ "dev": { "driver": "mongodb", "database": "dev", "host": "localhost", "sql-file": true, "multipleStatements": true }, "test": { "driver": "mongodb", "database": "test", "host": "192.168.33.10", "sql-file": true, "multipleStatements": true } }
Is that a bug or am I missing a config ?

Add showDatabase method

Refers to db-migrate/node-db-migrate#339

showDatabase should response with not implemented on this driver for the base driver.

--- Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/29956615-add-showdatabase-method?utm_campaign=plugin&utm_content=tracker%2F11792942&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F11792942&utm_medium=issues&utm_source=github).

unescaped reference to 'key' field in migration_state table.

There is an unescaped reference to the key field in the migration_state table on line 648 of index.js in the _updateKVC function definition. This is a syntax error in MySQL and MariaDB because key is a reserved keyword. I have confirmed that wrapping it in backticks solves the problem. This can be applied by calling the escapeDDL function on it similar to value in the same statement. I can submit a pull request if this helps.

Support for Aurora Data API backend

Hi there,

I just wanted to ask what you guys think about supporting the Data API for Aurora Serverless?

Basically, this allows you to run queries synchronously via HTTP, using AWS IAM and AWS Secrets Manager for authentication and authorization. Using the Data API lets you avoid thinking about VPCs, subnets, and SSH tunnels, and is especially powerful when accessing the database from a Lambda.

As far as I know, there would have to be a new driver created to support this, which used the AWS SDK to run the queries. Do you have any thoughts whether this is possible?

Seems like some ORMs in the Node ecosystem have connectors already, including sequelize and TypeORM.

Feature Request: Partial Indexes

Currently, I can do db.addIndex to add a new index to a table.

However, there is no support for partial indexes.

Partial index example in raw sql for postgresql:

 'create unique index account_id_name_index on foo(account_id, name) where bar_id IS NULL'

Currently the addIndex method signature looks like this:

   addIndex: function(tableName, indexName, columns, unique, callback) {
    if (typeof(unique) === 'function') {
      callback = unique;
      unique = false;
    }

    if (!Array.isArray(columns)) {
      columns = [columns];
    }
    var sql = util.format('CREATE %s INDEX "%s" ON "%s" (%s)', (unique ? 'UNIQUE' : ''),
      indexName, tableName, this.quoteDDLArr(columns).join(', '));

    return this.runSql(sql).nodeify(callback);
  },

We could either modify the existing method signature by accepting another parameter that specifies the where clause, or a new method addPartialIndex.

Do you think that this is something you'd like added in? If yes, I wouldn't mind working on adding this feature in as I am currently using db-migrate for work and currently we resort to using raw sql to create the partial indexes, but it would be a nice to have if the db-migrate SQL API allowed for this.

---

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

db.insert with a callback does not work

It appears that db.insert does not work with a callback. The code involved is here: https://github.com/db-migrate/db-migrate-base/blob/master/index.js#L453

We never reassign the callback variable to arguments[3], so the callback is never called and the migration hangs.

It looks like at one point we did reassign the callback, but took that out in this commit: 8160337#diff-168726dbe96b3ce427e7fedce31bb0bc

A workaround is to convert to using the promise API.

Instead of

exports.up = function (db, callback) {
  db.insert('tablename', [ col1 ] , [ val1 ], callback);
};

do

exports.up = function (db) {
 return db.insert('tablename', [ col1 ], [ val1 ]);
};

---

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

Add method to retrieve the current environment and config from the driver

Refers to db-migrate/node-db-migrate#416

--- Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/38088261-add-method-to-retrieve-the-current-environment-and-config-from-the-driver?utm_campaign=plugin&utm_content=tracker%2F11792942&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F11792942&utm_medium=issues&utm_source=github).

createTable can't create table in specific postgres schema

Suggestion to improve .insert method signature

Right now .insert method has the following signature:

insert(tableName, columnNameArray, valueArray, callback)

Unlike with SQL, it feels awkward to separate column names and values in JS.
I would like to suggest changing this method signature or adding a new one with signature

insert(tableName, rowObject, callback)

where rowObject is simply an object where keys represent column names and values are values for the according column, for example:

await insert('test_table', {
  id: 1,
  name: 'test',
  'strange column name': true
})

Unexpected behaviour of insert method when no callback is passed

The following code

exports.up = async function up(db) {
  await db.insert('test_table', ['data'], ['test']);
}

will fail with an error

/home/nikarh/workspace/agilestacks/automation-hub/api/node_modules/bluebird/js/release/async.js:61
        fn = function () { throw arg; };
                           ^

TypeError: callback is not a function
at Object.insert [as _super] (/home/nikarh/db-migrate-test/node_modules/db-migrate-base/index.js:366:14)
at Object.insert (/home/nikarh/db-migrate-test/node_modules/db-migrate-pg/index.js:440:26)
at Object.prototype.(anonymous function) (/home/nikarh/db-migrate-test/node_modules/db-migrate-base/class.js:36:24)
at Object.Interface.(anonymous function) [as insert] (/home/nikarh/db-migrate-test/node_modules/db-migrate-shared/util.js:135:41)
at Class.up (/home/nikarh/db-migrate-test/migrations/20170830131445-stack-table-data.js:4:14)
at Class.<anonymous> (/home/nikarh/db-migrate-test/node_modules/db-migrate/lib/skeleton.js:81:41)
at Promise._execute (/home/nikarh/db-migrate-test/node_modules/bluebird/js/release/debuggability.js:300:9)
at Promise._resolveFromExecutor (/home/nikarh/db-migrate-test/node_modules/bluebird/js/release/promise.js:483:18)
at new Promise (/home/nikarh/db-migrate-test/node_modules/bluebird/js/release/promise.js:79:10)
at Class._up (/home/nikarh/db-migrate-test/node_modules/db-migrate/lib/skeleton.js:65:12)
at Class.up (/home/nikarh/db-migrate-test/node_modules/db-migrate/lib/skeleton.js:149:17)
at Migrator.up (/home/nikarh/db-migrate-test/node_modules/db-migrate/lib/migrator.js:112:14)
at /home/nikarh/db-migrate-test/node_modules/db-migrate/lib/migrator.js:178:25
at tryCatcher (/home/nikarh/db-migrate-test/node_modules/bluebird/js/release/util.js:16:23)
at Promise._settlePromiseFromHandler (/home/nikarh/db-migrate-test/node_modules/bluebird/js/release/promise.js:512:31)
at Promise._settlePromise (/home/nikarh/db-migrate-test/node_modules/bluebird/js/release/promise.js:569:18)

I can do a PR, I suggest checking the type of a last argument to differentiate between promise-based and callback based API.

---

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