Knex.js postgresql database connection

Question

I'm having a bit of an issue & this is most likely a user error. I just trying to connect my Node/Express application to a database using Knex.js. I'm using Postgres App.

migrations/20180618143210_item.js

exports.up = function (knex, Promise) {
    knex.schema.createTable('items', (table) => {
        table.increments('id').primary();
        table.string('name');
    })
};

exports.down = function (knex, Promise) {
    knex.schema.dropTable('items')
};

knexfile.js

module.exports = {

  development: {
    client: 'postgresql',
    connection: {
      host : 'localhost',
      database: 'my_db',
      user: 'User',
      password: ''
    },
    migrations: {
      directory: __dirname + '/db/migrations'
    },
    seeds: {
      directory: __dirname + '/db/seeds/development'
    }
  },

  staging: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },

  production: {
    client: 'postgresql',
    connection: {
      database: 'my_db',
      user:     'username',
      password: 'password'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  }

};

When I run knex migrate:latest it creates the database and I can see the following \dt database tables when connected to my_db database:

                  List of relations
 Schema |         Name         | Type  |    Owner
--------+----------------------+-------+-------------
 public | knex_migrations      | table | User
 public | knex_migrations_lock | table | User

When I select * from knex_migrations; I'm returned:

 id |          name          | batch |       migration_time
----+------------------------+-------+----------------------------
  2 | 20180618143210_item.js |     1 | 2018-06-18 14:40:08.994-06

However, if I try to run something like select * from items I'm getting a ERROR: relation "items" does not exist error. Am I missing something here? The same error is occuring when I try to seed the data knex seed:run

items.js

exports.seed = function(knex, Promise) {
  // Deletes ALL existing entries
  return knex('items').del()
    .then(function () {
      // Inserts seed entries
      return knex('items').insert([
        {id: 1, name: 'rowValue1'},
        {id: 2, name: 'rowValue2'},
        {id: 3, name: 'rowValue3'}
      ]);
    });
};

Error: error: relation "items" does not exist.

Nappstir · Accepted Answer · 2018-06-18 22:08:45Z

1

I figured out why this was occurring. I wasn't returning anything out of the knex up & down functions. So instead of:

exports.up = function (knex, Promise) {
    knex.schema.createTable('items', (table) => {
        table.increments('id').primary();
        table.string('name');
    })
};

exports.down = function (knex, Promise) {
    knex.schema.dropTable('items')
};

I needed:

exports.up = function (knex, Promise) {
    **return** knex.schema.createTable('items', (table) => {
        table.increments('id').primary();
        table.string('name');
    })
};

exports.down = function (knex, Promise) {
    **return** knex.schema.dropTable('items')
};

answered Jun 18, 2018 at 22:08

Nappstir

9952 gold badges20 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mikael Lepistö Over a year ago

declaring up and down functions as async might be a good idea to always implicitly return promise from them

Collectives™ on Stack Overflow

Knex.js postgresql database connection

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related