Operating the Database

In Node.js, accessing different databases requires installing specific database drivers. Since we’re using SQLite, we need to install the SQLite driver. We’ll choose the sqlite3 driver, which has SQLite built-in.

You can create a db object with the following code:

javascript

// Open test.db in the specified mode:
const db = new sqlite3.Database('test.db', sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE | sqlite3.OPEN_FULLMUTEX);

The OPEN_CREATE parameter allows the database to be created automatically if it doesn’t exist, which is convenient for development.

sqlite3 uses a callback pattern for executing queries and updates, as shown here:

javascript

// Query:
db.all('SELECT * FROM users WHERE id=?', [1], function (err, rows) {});

// Update:
db.run('UPDATE users SET name=? WHERE id=?', ['Bob', 1], function (err) {});

Since sqlite3 doesn’t provide a Promise interface, we can encapsulate a Promise for asynchronous querying and updating:

javascript

// db.mjs:
import sqlite3 from 'sqlite3';

export function createDatabase(file) {
    const db = new sqlite3.Database(file, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE | sqlite3.OPEN_FULLMUTEX);
    const wrapper = { db: db };

    wrapper.update = async function (strs, ...params) {
        return new Promise((resolve, reject) => {
            let sql = strs.join('?');
            db.run(sql, ...params, function (err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.changes);
                }
            });
        });
    };

    wrapper.insert = async function (strs, ...params) {
        return new Promise((resolve, reject) => {
            let sql = strs.join('?');
            db.run(sql, ...params, function (err) {
                if (err) {
                    reject(err);
                } else {
                    resolve(this.lastID);
                }
            });
        });
    };

    wrapper.select = async function (strs, ...params) {
        return new Promise((resolve, reject) => {
            let sql = strs.join('?');
            db.all(sql, ...params, function (err, rows) {
                if (err) {
                    reject(err);
                } else {
                    resolve(rows);
                }
            });
        });
    };

    wrapper.fetch = async function (strs, ...params) {
        // Implementation goes here...
    };

    return wrapper;
}

Next, create a new project named sql to replace hard-coded login logic with actual database interaction:

sql/
├── app.mjs
├── db.mjs
└── ...

Install the sqlite3 dependency with npm install sqlite3 and update the dependencies in your package.json.

In app.mjs, initialize the db object and bind it to app.context:

javascript

import { createDatabase } from './db.mjs';

async function initDb() {
    const email = 'admin@example.com';
    const name = 'Bob';
    const password = '123456';
    const db = createDatabase('test.db');

    await db.update`CREATE TABLE IF NOT EXISTS users(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL, password TEXT NOT NULL)`;
    let user = await db.fetch`SELECT * FROM users WHERE email=${email}`;
    
    if (user === null) {
        await db.insert`INSERT INTO users (email, name, password) VALUES (${email}, ${name}, ${password})`;
    }
    return db;
}

// Bind db to app.context:
app.context.db = await initDb();

This code facilitates development by automatically creating the table and user.

With database support, modify the signin.mjs to query the database for user credentials:

javascript

// signin:
async function signin(ctx, next) {
    let email = ctx.request.body.email || '';
    let password = ctx.request.body.password || '';
    
    let user = await ctx.db.fetch`SELECT * FROM users WHERE email=${email}`;
    
    if (user !== null && user.password === password) {
        console.log('signin ok!');
        ctx.render('signin-ok.html', { title: 'Sign In OK', name: user.name });
    } else {
        console.log('signin failed!');
        ctx.render('signin-failed.html', { title: 'Sign In Failed' });
    }
}

This query utilizes a tagged template function to ensure parameterized SQL, preventing SQL injection:

javascript

let user = await ctx.db.fetch`SELECT * FROM users WHERE email=${email}`;

Finally, execute node app.mjs to see the functionality in action, along with SQL statements and bound parameters printed in the backend.

Operating the Database ​

Operating the Database