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 Node.js 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 ‘SQL query’, a ‘SQL query’ object is created.
• For the others APIs, a ‘SQL query’ object is created. The ‘SQL query’ is partially evaluated (only the CRUD operations and table names are evaluated).

Icon	Description
	Node.js 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 ‘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 ‘Node.js SQL query’ objects are created. The SQL Analyzer 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 ‘Node.js SQL query’. The SQL Analyzer 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’.