Table of Contents General Setup Migration Create Execute Rollback Seed Create Execute Query Builder raw SQL WITH Debug Correlation

Scope:

• SQL Domain Specific Language (aka, fluent SQL, aka query builder) in js for:
  • DDL
  • DML
• database (schema) versioning
• dataset handling (aka "seeding")

Note than SQL is already a DSL and a functional language. Now, consider the use-case of any query-builder is to plug it with an ORM (Bookshelf, Knex-ORM..) that will generate queries. Otherwise, for more convenience and safety than using directly node-postgres, use slonik. More on this here.

Links:

• Cheatsheet
• run raw query, one-liner, from REPL: node -e "( async() => { await require('./knex/knex').raw('CALL removeUser(2)') })();"

From here:

• install your db driver dependency: npm install DATABASE_DRIVER_PACKAGE
• install dependency: npm install knex
• create directories

mkdir knex
mkdir knex/migrations
mkdir knex/seeds

• create configuration file (aka initialization object): npx knex init
• update it vi knexfile.js it to use directories and db connections

module.exports = {

  development: {
    client: 'postgresql',
    connection: {
      database: <DATABASE_NAME>
      port:     <DATABASE_PORT>
      user:     <DATABASE_USER_NAME>
      password: <DATABASE_USER_PASSSORD>
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations',
      directory: './knex/migrations',
    },
    seeds: {
      directory: './knex/seeds',
    },
  }

};

• note: you shouldn't store such credentials in source code, except for training purpose, see 12-factor app
• make bootstrap touch knex/knex.js

const environment = process.env.ENVIRONMENT || 'development'
const config = require('../knexfile.js')[environment];
module.exports = require('knex')(config);

• test connexion:
  • create simple query

touch index.js

const knex = require('./knex/knex.js');

knex.raw('SELECT current_database() AS "databaseName"' ).then((status) => {
    console.log('Successfully connected to ' + status.rows[0].databaseName);
});

• • execute: npm start

To fine-tune migration, consider adding knex-migrate

Execute npx knex migrate:make <MIGRATION_NAME>

Then update corresponding file in knex/migrations

const tableName = 'recipe';
exports.up = function(knex) {
    return knex.schema.createTable(tableName, table => {
        table.string('name')
    });
};

exports.down = function(knex) {
    return knex.schema.dropTable(tableName);
};

You can run the migration as many times as you like, it'll run it once (it checks knex_migrations beforehand) npx knex migrate:latest

If any error occurs, causing migration not to be applied, after fix, you may have to mark this migration as not having been executed, eg. DELETE FROM knex_migrations

npx knex migrate:rollback

Execute npx knex seed:make <SEED_NAME>

Then update corresponding file in knex/seed

exports.seed = function(knex, Promise) {
  return knex('recipe').del()
  .then(function () {
    // Inserts seed entries
    return knex('recipe').insert([
      {
        name: 'chicken-sofrito'
      },
      {
        name: 'saag-feta'
      }
    ]);
  });
};

You can run the seed as many times as you like, it'll replace the data npx knex seed:run

API

knex.raw(QUERY)

knex.with('with_alias', knex.raw('select * from "books" where "author" = ?', 'Test')).select('*').from('with_alias')

List :

• using knexfile.js: add debug = true

development: {
  client: (..),
  connection: (..),
  debug: true
}

• listening on events : knex.on('query', console.log)
• using an environment variable: DEBUG:
  • get everything: DEBUG=knex:* npm start , you'll get

  knex:client acquired connection from pool: __knexUid1 +0ms
  knex:query select "name", "serving" from "recipe" undefined +0ms
  knex:bindings [] undefined +0ms
  knex:client releasing connection to pool: __knexUid1 +5ms

• • get query in plain SQL only : query DEBUG=knex:query npm start
  • get bindings (parameter values) only: bindings DEBUG=knex:bindings npm start
  • get transaction only: tx DEBUG=knex:tx npm start
• on a single query, invoking .debug(true) method: await knex('user').insert([user]).debug(true);

You can log query execution time manually listening on events

module