Knex.js support for Node.js

Introduction

Knex.js is a “batteries included” SQL query builder for PostgreSQL, MSSQL, MySQL, SQLite3, Oracle - see https://knexjs.org/  for more information.

Objects

This extension creates the NodeJS SQL Query object when one of the supported Knex APIs is used:

• For the API raw, the first argument is evaluated and if it corresponds to a NodeJS SQL Query, a NodeJS SQL Query object is created.
• For the others APIs, a NodeJS SQL Query object is created. The NodeJS SQL Query is partially evaluated (only the CRUD operations and table names are evaluated).

Icon	Description
	NodeJS SQL Query

Supported persistence SQL databases

Supported links

For the API raw, possibly all four link types can be created depending on query value.

The APIs join (join, leftJoin, leftOuterJoin, rightJoin, joinRaw, rightOuterJoin, outerJoin, fullOuterJoin, crossJoin) can be used to specify joins between tables. For each API join used with one of the supported APIs listed above, a NodeJS SQL Query object is created.

Example

Take the following codes of javascript file:

const knex = require('knex')({
    client: 'sqlite3',
    connection: {
    }
});

function createBook(book) {
    knex.raw('INSERT INTO books_1 VALUES ?');
    knex('books_2').insert(book);
    knex.table('books_3').insert(book);
}

function selectBook() {
    knex.select('title', 'author', 'year').from('books_2');
}

function updateBook(id, newDetails) {
    knex.table('books_3').where('id', id).update(newDetails);
}

In this example, five NodeJS SQL Query objects are created. The SQL Analyzer or Missing tables and procedures for Node.js links these queries with the corresponding Missing Tables.

Take another example with API join:

const knex = require('knex')({
    client: 'sqlite3',
    connection: {
    }
});

const join_tables = knex('users as u')
    .join('contacts AS c', 'u.id', '=', 'c.id')
    .select('c.id', 'u.username', 'c.contents')

In this example, the API join is used to join tables users and contacts. This extension creates two NodeJS SQL Query objects. The SQL Analyzer or Missing tables and procedures for Node.js then links these queries with the corresponding tables. In the present case, this extension creates two useSelect links from each query to its corresponding missing tables contacts or users.