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’.