User accounts

In this tutorial we are going to learn how to connect to and query a (SQL) database, and how to store the extracted information in a session. We will use these technologies to build a simple app for managing user accounts.

We will use the module express-session for handling sessions, and the database abstraction layer knex for querying the database.

The references for this tutorial are

Preparing your workspace

Create a new Node app on Glitch. Install the following packages through package.json:

Note: The sqlite3 module won’t work if you develop locally on Windows. Fortunately, Knex is compatible with all flavors of SQL (MySQL, Postgres, MariaDB, …), so you can have your application talk with any of these servers with very few code changes. Follow the instructions in the docs.

Building the schema

For a starter, we need a database, and some tables in it. Glitch supports SQLite natively, so that’s what we’re going to use. However, we cannot create the tables at the moment we start the application, as those instructions would be executed at every restart.

Instead, we are going to write a separate script to initialize the database with the tables we need.

  1. Create a script named db_init.js, containing the following code:

    var knex = require('knex')({
        client: 'sqlite3',
        connection: {
            filename: "./db.sqlite3"
        debug: true,

This code creates a SQLite file named db.sqlite3, and initiates a connection. We can query the connection by operating on the knex object. For example, to create a SQL statement, we can run

var chain = knex.raw('SELECT * FROM toto');

This code does not execute any action for the moment. In order to send the query to the database, we need to use the then() method of the chain:

  1. Using a CREATE TABLE statement in db_init.js, create a table named users, with the following columns:

    • login: type varchar(255), primary key,
    • pass: type varchar(255) NOT NULL.

    Of course, nothing is going to happen if you don’t run the script. We will use Glitch’s Linux console to do the sysadmin magic. From the main menu (top left, click on the name of your project), select “Advanced Options”, then “Open Console”.

    A Linux terminal opens in a new window. Run your script by typing

    node db_init.js

    Some debug messages will appear. If things have gone well, you can check that your database was created and populated by issuing the command

    sqlite3 db.sqlite3 .schema

Damn! I need to update the schema

The schema we chose for our users table was too minimalist: we need to add more columns. SQL offers a ALTER TABLE command to modify a table, and we could in principle run it from a Node script, or directly from the SQLite command line. However, this does not play very well with the idea of having a script to run just once to initialize the database. Instead, we are going to modify db_init.js so that it erases the database and recreates the table. This way, we can easily update the script and rerun it whenever we need a fresh state.

But first we need to understand what .then() does. Before, we wrote something along these lines:

var chain = knex.raw(`CREATE TABLE users (
  pass VARCHAR(255) NOT NULL

Actually, the .then() method takes one argument, that is a function (a callback) to execute when the query is done. For example, we may have written

var chain = knex.raw(`CREATE TABLE users (
    login VARCHAR(255) PRIMARY KEY,
    pass VARCHAR(255) NOT NULL
chain.then(function () {
    console.log('Created table users');

and that would have printed “Created table users” only after the table was created in the database. But it gets better: .then() also has a return value, which is a modified version of chain (in JS-speak, chain is called a Promise). We can keep calling .then() on the chain to execute SQL statements in sequence.

var chain = knex.raw(`CREATE TABLE users (
    login VARCHAR(255) PRIMARY KEY,
    pass VARCHAR(255) NOT NULL
chain = chain.then(function () {
    console.log('Created table users');
    // The return is needed here: 
    // it passes the promise up the chain
    return knex.raw('CREATE TABLE test (a INTEGER)');
chain.then(function() {
    console.log('Create table test');

Actually, we don’t need to give a name to the chain variable: we can instead chain the method calls to .then(). If we combine with the ES6 syntax for functions we get something quite concise:

knex.raw(`CREATE TABLE users (
    login VARCHAR(255) PRIMARY KEY,
    pass VARCHAR(255) NOT NULL
)`).then(() => {
    console.log('Created table users');
    return knex.raw('CREATE TABLE test (a INTEGER)');
}).then(() => {
    console.log('Create table test');
  1. Modify db_init.js to:

    • first drop the table users (use DROP TABLE IF EXISTS to avoid errors),
    • then (re)-create the table users with the following columns:
      • login: type varchar(255), primary key,
      • pass: type varchar(255) NOT NULL,
      • name: type varchar(255),
      • color1: type varchar(10),
      • color2: type varchar(10).

    Rerun db_init.js via the console and verify that the table users is recreated correctly.

  2. Until now we have only used callbacks with no arguments inside .then(). However, promises pass values from one callback to the next through .then(). For example, knex('users').columnInfo() returns a promise containing a description of the columns of the users table. It can be used in a promise chain like thus:

    knex('users').columnInfo().then(function(cols) {

    Add a call to .columnInfo() to db_init.js to show the structure of the users table after it has been created.

  3. Add some default users to the table by using a INSERT INTO statement.

  4. Print the contents of the table on the console by using a SELECT statement (Note: the rows returned by SELECT are passed as a list to the callback in .then()).

The Web app

We are now ready to write our web application. We keep it very simple: we want to be able to create a new user, log in, and show the list of users.

  1. Initialize the server with the usual Express skeleton (with body-parser and nunjucks configured). Add a Knex configuration section as done in db_init.js. Add (emtpy) routes for the URLs /, /signin, /logout and /userlist.

  2. Write the handler for /userlist. It must select all rows from the users table, and show them in a HTML table.

  3. Replace the text of the color1 column with a rectangle colored in the same color. If color1 is NULL use a default color. The result may look something like this:

    Login Name Color
    kasparov Garry Kasparov
    karpov Anatoly Karpov

    Hint: for the colored rectangle you can use a <span> with fixed height and width and the display: inline-block property.

Creating users

The /signin route will let us create a new user. For this we use a HTML form, and a POST request. In the handler for the POST request we will need to send a INSERT INTO query with user supplied parameters. We recall that the knex.raw() method takes an optional second argument, which is a list of replacement values for prepared statements, like thus:

knex.raw('INSERT INTO users VALUES (?, ?, ?, ?, ?)',
         [ 'karpov', 'checkmate', 'Anatoly Karpov', 'green', 'black' ]);
  1. Create a Nunjucks template for a web form for creating a new user, and serve it at the /signin URL. It must contain a login field, a password field, a name field, a favorite color and a secondary color (try type=color for the color fields!). The form must use the POST method (method attribute) and it must send the data back to /signin (it is enough to leave the action attributed empty).

  2. Create a POST handler for /signin where you take the values sent in the form and insert then in the users table, only if the login and the password field are non-empty (an empty field in a form is read as an empty string '' in req.body). After the insertion has succeeded, redirect to / (using res.redirect()).

    If one of the fields is empty, do not insert in the database, and send instead the same form template, with a message explaining what error happened (use the template engine to show the message). Using the template engine, you can also pre-fill the fields with the values that had been sent.

    Test with the “Network” tool of the browser’s developer tools.

Errors may happen when you do SQL queries, and we cannot let them crash our application. For example, a user may try to create an account for a login that already exists (violating the PRIMARY KEY constraint). To handle errors in a Promise chain, we use the .catch() method in the same way we used .then():

knex.raw('INSERT INTO ...')
    .then(() => { ... })
    .catch((error) => { console.log(error) })
  1. Add a error handler to your promise chain. If the error is a violation of the primary key constraint (inspect the error in the console, and find out the error code for this violation), send back the form with an explanatory message. For any other error, send back the form with a generic message.

Login and sessions

We come to the last part: handling logins. We will show a login form at the / URL, and redirect to /userlist upon successful login.

Since we are moving between different handlers, we need a way to keep the information that the user is logged in across many requests. We cannot, and do not want to, transfer the username and password with every request, hence we will use a session mechanism.

As seen in the sessions lesson, we must start by configuring the Express app with

var session = require('express-session');

    secret: '12345',
    resave: false,
    saveUninitialized: false,

Then the session will be available in every handler at req.session.

  1. Write a handler for / that shows a form similar to this one:

    login: password:
  2. Like you did for /signin, write a second handler for POST requests to /. The handler checks whether the pair login-password corresponds to a user in the database, then:

    • if login and password are wrong, it shows again the form with an error message;

    • if they are correct, it saves the login, the name and the colors in the session, and redirects to /userlist.

  3. Modify /userlist so that it redirects to / when the user is not logged in.

  4. Write the /logout route to log the user out. For that, it is enough to put a special value in the session (for example, set the login to null); when a handler detects this special value, it treats the request as an unauthenticated one.

  5. Add a link to /logout in the /userlist view.

Advanced stuff (optional)

Here are some optional questions to better master Knex and sessions.

  1. In /signin, ask for the password twice, and only accept the new user if the two password fields match.

  2. We have only used knex.raw() in this tutorial, however knex has a very convenient query builder to construct SQL statements with a more object-oriented syntax. Read the docs and replace your raw queries with built ones. You can also try Schema building in db_init.js.

  3. Promises are nice (nicer than callbacks, at least), but the syntax is still quite heavy. The new async/await framework allows us to write promised code as if it were synchronous. Read this tutorial and replace your promises with async/await statements.

  4. It is very bad practice to store cleartext passwords in the database: what you if suffer a data breach and your database is divulged?

    Instead, it is preferable to store hashed passwords: an irreversible transformation of the cleartext password, so that the cleartext is only seen in transit, but never stored. The hash computation must happen on the server, and must be salted with a secret, otherwise the technique is no safer than storing cleartext passwords.

    If this is done properly, not even the administrator will be able to recover the cleartext passwords. A misunderstanding of this technique was at the origin of the famous leak of 150M Adobe passwords in 2013 :

    It’s best not to roll your own crypto, as it is easy to make mistakes. Use instead trusted ready-made solutions, such as the bcrypt module.

Fork me on GitHub