knex: a Library to Access Databases

knex is a npm module that works with:

  • MySQL
  • PostgreSQL
  • SQLite3
  • WebSQL
  • Oracle

You can easily retrieve database data using functions, similar to C#’s Linq. First you need to configure, example config for Postgres:

  name: 'global',
  client: 'pg',
  debug: false,
  connection: {
    multipleStatements: true,
    host: url.parse(process.env.DB_PORT).hostname,
    user: process.env.DB_USER,
    port: parseInt(url.parse(process.env.DB_PORT).port, 10),
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
  migrations: {
    directory: `${__dirname}/../db/migrations`,


    POSTGRES_DB: test

And to use it you have to create a connection passing the config:

const db = knex(config);

After that you can start building queries:

const query = db('table_name').where('id', 5);

Just like C# it has a deferred execution. To actually make the query run you have to call it with await:

await query; // performs database query
// or query.then() if you are using old stuff

You can also make queries with a transaction:

try {
  await db.transaction(async (trx) => {
    await db('user').insert({ name: 'Saitama' });
    await db('user').insert({ name: 'Genos' });

    await trx.commit(); // call this to submit all queries
    // await trx.rollback(); // this will throw and not run the queries
catch (err) {
  console.log('Rollback happened');

Like in C#, you can use Migrations, and also Seeds. You have to specify a directory where your migrations files are, and put the files in order they have to execute.

A migration file might look like this:

function up(knex) {
  return knex

    .createTable('user', table => {


function down(knex) {
  return knex

module.exports = {

To run all migrations:

await db.migrate.latest();

Basically it has to export two functions, one called up that will for example create things and one called down that will revert the things you created on up. Both functions receive a parameter knex which is the object


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s