Persistent storage SQL queries with Knex

Persistent storage

Every web app needs to store data permanently on the server.

  • In the file system: SQLite, …
  • In a SQL DB: MySQL, MariaDB, PostgreSQL, …
  • In a NoSQL DB: MongoDB, Couchbase, CouchDB, Redis, …


All web frameworks offer modules to smooth interactions with databases:

  • DBAL (Database Abstraction Layer): unique API to access several DB systems (e.g., MySQL, SQLite, …).
  • ORM (Object Relational Mapping): translations between objects in the host language and entities in the DB system.

Storage in Node.js


Several modules to interact with SQL DBs available in npm:

Plus several abstraction modules built on top.


MySQL in Node.js

Install mysql

npm install mysql


var mysql = require('mysql');
var db    = mysql.createConnection({
  host     : 'localhost',
  user     : 'foo',
  password : '12345',
  database : 'mydb',

Use (asynchronous call semantics only)

db.query('SELECT * FROM users',  function(error, rows) {
    // Handle data in the callback asynchronously

Read more:

SQL abstraction through Knex

Knex is a Node DBAL for SQL:

  • Compatible with: Postgres, MSSQL, MySQL, MariaDB, SQLite3, and Oracle.
  • Query builder: construct SQL queries through method calls.
  • Schema builder: construct SQL tables through method calls.
  • Asynchronous APIs: Promises (preferred, compatible with async/await), callbacks and streams.
  • More features: transactions, migrations, connection pooling.

Install with

npm install knex

Also install the low-level module for your database(s), e.g:

npm install sqlite3 mysql

Advantages over low-level modules:

  • Write once, use with many DBs (dev, testing, production, …),
  • Safer queries with the query builder.

Using Knex

Configure (e.g., with mysql)

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host     : 'localhost',
    user     : 'foo',
    password : '12345',
    database : 'mydb',


Inside an async function (preferred):

try {
  var rows = await knex.raw('SELECT * FROM users');
} catch (error) {

Using raw promises (for advanced parallelism):

knex.raw('SELECT * from users')
  .then(function (rows) {
  .catch(function (error) {

Using an old-style callback (not chainable):

knex.raw('SELECT * from users')
  .asCallback(function (error, rows) {

Query builder

SELECT * FROM `users`
await knex('users').select('*');
SELECT * FROM `users`
await knex('users');
SELECT * FROM `users`
await knex('users')
  .select('name', 'surname')
  .where('town', 'Paris')
  .andWhere('age', '<', 18);
SELECT 'name', 'surname'
FROM users
WHERE `town` = 'Paris'
AND `age` < 18

To test the query builder, use toString() (not asynchronous)

console.log(knex('user').where('name', 'foo').toString())
// Outputs: select * from `user` where `name` = 'foo'

Create tables

Using raw SQL

await knex.raw(`CREATE TABLE users (
  password VARCHAR(30) NOT NULL

Using the schema builder

await knex.schema.createTable('users', function (table) {
  table.string('password', 30).notNullable();

WARNING! Asynchronous calls ahead

This does nothing (x is only a promise)

var x = knex('users').select('*');

This prints out of order (control jumps to 'Done' before the callback is executed)

knex('users').then(function (rows) {
  for (var r of rows) {
User: foo
User: bar

Always prefer await (unless you need complex parallelism)

var rows = await knex('users');
for (var r of rows) {
User: foo
User: bar

A full example'/login', async function(req, res) {
  var login = req.body.login;                  // Get data from request body
  var pass = req.body.pass;
  try {
    var users = await knex('users').where({    // Query database:
      'login'   : login,                       // SELECT * FROM users
      'password': pass,                        // WHERE login = ? AND password = ?

    if (users.length == 1) {                   // Check if credentials matched
      res.send(`Hello, ${users[0].name}`);
    } else {
      res.status(401).send('Unknown user');
  } catch (err) {                              // Handle errors
    console.error('Database error:', err);     // (log and send generic 500 code)

SQL Escaping

We have with SQL the same problem we found when generating HTML code

app.get('/login', async function(req, res) {
  await knex.raw(`SELECT * FROM users WHERE login = ${req.query.user}`);

What if we sent this request?'ti
SELECT * FROM users WHERE login = 'le_ch'ti'
  • Special SQL characters `, ', ", ; must be escaped.
  • Escaping syntax depends on the database engine (MySQL, Postgres, …)

Do not escape manually, rely on your DBAL:

  • Use the query builder,
  • Use prepared queries.

Escaping in the query builder

await knex('users').where('login', req.query.user);

Escaping raw queries

await knex.raw('SELECT * FROM users WHERE login = ?', [req.query.user]);


await knex.raw('SELECT * FROM users WHERE login = :user', {
  'user': req.query.user,



Passing req.query.user = "le_ch'ti" gives in all cases

SELECT * FROM users WHERE login='le_ch''ti'


Fork me on GitHub