Go Back

The Best Nodejs SQLite Tutorial – Part 3: Refactoring SQLite for Project Use

Posted by Charlie Ontiveros on 2018-09-17

Welcome to Part 3 of a multi-part series on working with SQLite within Nodejs. In this part, I will go over a few structural things you can do to improve the code from the previous two parts. This includes creating modules to store functions related to SQLite and refactoring within the modules.

If you skipped over the second part, be sure to go my GitHub and download the NodejsSQLite Pt. 2 folder as the rest of this tutorial will build on top of that. You can download the files here:

https://github.com/CharlieTheProgrammer/NodejsSQLite-Pt2

Create a Module for Database Functions

  1. Create a new file under the NodejsSQLite folder called database.js.
  2. Move over lines 1 to 43 from server.js to database.js. We'll set aside server.js until database.js object creation is complete.

Create a New Object to Export

In this step, we'll need to create a new object to house the SQLite initialization steps and functions.

  1. Create a new object called DataBaseAPI. In this case, we're referring to API as a module interface rather than an HTTP-based interface.

    function DatabaseAPI() {}
  2. Move the code that was used to initialize the database inside the DatabaseAPI function.

    function DatabaseAPI() {
        const DB = new sqlite3.Database(DB_PATH, function(err){
                if (err) {
                    console.log(err)
                    return
                }
                console.log('Connected to ' + DB_PATH + ' database.')
    
                DB.exec('PRAGMA foreign_keys = ON;', function(error)  {
                    if (error){
                        console.error("Pragma statement didn't work.")
                    } else {
                        console.log("Foreign Key Enforcement is on.")
                    }
                });
            });
    
        var dbSchema = `CREATE TABLE IF NOT EXISTS Users (
            id integer NOT NULL PRIMARY KEY,
            login text NOT NULL UNIQUE,
            password text NOT NULL,
            email text NOT NULL UNIQUE,
            first_name text,
            last_name text
        );
    
        CREATE TABLE IF NOT EXISTS Blogs (
            id integer NOT NULL PRIMARY KEY,
            user_id integer NOT NULL UNIQUE,
            blog text ,
            title text NOT NULL,
            publish_date date,
                FOREIGN KEY (user_id) REFERENCES Users(id)
        );`
    
        DB.exec(dbSchema, function(err){
            if (err) {
                console.log(err)
            }
        });
    }

    When the DatabaseAPI object is instantiated, it will automatically execute this code. Just as an aside, this code is 'private' to the function, meaning it cannot be accessed by code outside the DatabaseAPI function. While this provides protection against unwanted modification, it also means that dynamic values should be provided as parameters instead of hardcoding the values into the code.

  3. Copy the DB_PATH parameter to the DatabaseAPI function statement and as a parameter to sqlite3.Database.

    function DatabaseAPI(DB_PATH) {
        const DB = new sqlite3.Database(DB_PATH, function(err){ ...

Quick Test Check

  1. Import the new database file into server.js.

    const { DatabaseAPI } = require('./database')
    const DB_PATH = './sqlite.db'
    
    var DB = new DatabaseAPI(DB_PATH)

    The code above imports the DatabaseAPI as a named object and creates an instance of it.

  2. Run server.js and confirm that no errors occur. You should also see the following statements appear in the console.

    Connected to ./sqlite.db database.
    Foreign Key Enforcement is on.

Modularize Database Schema

Since the database schema is very likely to change and grow as you are developing your application, it is a good idea to store it in a separate file. This will also make that part of the code easier to reuse by removing hardcoded values.

  1. Create a file called dbSchema.js.

  2. Move the database schema from database.js into the new file.

    var dbSchema = `CREATE TABLE IF NOT EXISTS Users (
        id integer NOT NULL PRIMARY KEY,
        login text NOT NULL UNIQUE,
        password text NOT NULL,
        email text NOT NULL UNIQUE,
        first_name text,
        last_name text
    );
    
    CREATE TABLE IF NOT EXISTS Blogs (
        id integer NOT NULL PRIMARY KEY,
        user_id integer NOT NULL UNIQUE,
        blog text ,
        title text NOT NULL,
        publish_date date,
            FOREIGN KEY (user_id) REFERENCES Users(id)
    );`

    Now there's less scrolling to do in database.js and most dynamic values have been turned into parameters!

  3. Export the database schema by changing "var dbSchema" to "module.exports.dbSchema".

    var dbSchema = `CREATE TABLE IF NOT EXISTS Users ( ...
    
    to
    
    module.exports.dbSchema = `CREATE TABLE IF NOT EXISTS Users ( ...
  4. Go to database.js and add a parameter to pass in the schema.

    function DatabaseAPI(DB_PATH) { ...
    
    to
    
    function DatabaseAPI(DB_PATH, dbSchema) { ...

    Note that we are not importing the schema here. That is because server.js is going to be used as the main configuration file for the application. As a general rule of thumb, it's better to centralize configuration in as few files as possible. This makes it much easier to swap out configurations for testing or other purposes.

  5. Go to server.js and add an import statement for the schema.

    const { DatabaseAPI } = require('./database')
    const dbMeta = require('./dbSchema')    // Add this in
    
    const DB_PATH = './sqlite.db'

    Unlike the DatabaseAPI, the dbSchema object is being passed as a default export. That means we can call the object whatever we want. With a named export, like with DatabaseAPI, the same variable name from the file the object is exported from must be used.

  6. Pass in the schema as a second parameter to the DatabaseAPI.

    var DB = new DatabaseAPI(DB_PATH)
    
    to
    
    var DB = new DatabaseAPI(DB_PATH, dbMeta.dbSchema)

Quick Test Check

  1. Run server.js and confirm that no errors occur. You should also see the following statements appear in the console.

    Connected to ./sqlite.db database.
    Foreign Key Enforcement is on.

Thus far, we've created parameters for most of the dynamic values in database.js. This makes it very easy to swap configurations and has made the code reusable. As a matter of fact, what we have now for database.js can be used a template to create other databases. You just have to change the schema.

At this point, you may be asking why I didn't take out the pragma statement?

Although technically, the pragma statement can change, it's not likely to change. In my own experience, I have not had any need to make pragma changes besides enabling foreign keys. If you find yourself needing to change pragmas frequently or the query starts to grow, it can be moved into it's own file as shown in this section.

Add Database Functions to the DatabaseAPI Module

In this section, we are going to add methods to the DatabaseAPI so we can run queries. We are going to do this by returning an object with functions. Each function will refer to the instantiated variable, DB, through a closure. To keep things really simple, a closure just means that all the functions have access to the DB object even though the DB object is declared outside of them.

  1. Return an empty object. This will soon be populated with functions.

    const sqlite3 = require('sqlite3').verbose()
    
    function DatabaseAPI(DB_PATH, dbSchema) {
        const DB = new sqlite3.Database(DB_PATH, function(err){
                if (err) {
                    console.log(err)
                    return
                }
                console.log('Connected to ' + DB_PATH + ' database.')
    
                DB.exec('PRAGMA foreign_keys = ON;', function(error)  {
                    if (error){
                        console.error("Pragma statement didn't work.")
                    } else {
                        console.log("Foreign Key Enforcement is on.")
                    }
                });
            });
    
        DB.exec(dbSchema, function(err){
            if (err) {
                console.log(err)
            }
        });
    
        return {}    // Add this in
    }

Add registerUser function

  1. Move over the registerUser function from server.js to database.js and place it within the return { }.

    return {
        function registerUser(login, password, email) {
            var sql= "INSERT INTO Users (login, password, email) "
            sql += "VALUES (? ,?, ?) "
    
            DB.run(sql, [login, password, email], function(error) {
                if (error) {
                    console.log(error)
                } else {
                    console.log("Last ID: " + this.lastID)
                    console.log("# of Row Changes: " + this.changes)
                }
            });
        }
    }
    1. Update the function signature.

      function registerUser(login, password, email) { ...

    to

    registerUser: function(login, password, email) { ...

Quick Test Check

  1. Go to server.js and add "DB." in front of the registerUser function.

    // RUN QUERY  ===========================================
    DB.registerUser("newuser9", "pass", "test@test9999.com")
    // registerUser("newuser2", "pass", "test@test4827.com")
    // registerUser("newuser3", "pass", "test@test5830.com")

    We're adding "DB." since the function is now within the DB object.

  2. Run server.js and confirm that no errors occur. You should also see the following statements appear in the console.

    Connected to ./sqlite.db database.
    Foreign Key Enforcement is on.
    Last ID: 1    // Can be greater than 1 depending on how many users are already in db.
    # of Row Changes: 1
  3. Comment out the registerUser function to avoid errors.

Add findUserByLogin Function

  1. Same as before, move over the findUserByLogin function from server.js to database.js and add that underneath the registerUser function.

    return {
        registerUser: function(login, password, email) {
            var sql= "INSERT INTO Users (login, password, email) "
            sql += "VALUES (? ,?, ?) "
    
            DB.run(sql, [login, password, email], function(error) {
                if (error) {
                    console.log(error)
                } else {
                    console.log("Last ID: " + this.lastID)
                    console.log("# of Row Changes: " + this.changes)
                }
            });
        },
        function findUserByLogin(user_login) {
            var sql = 'SELECT email '
            sql += 'FROM Users '
            sql += 'WHERE login = ? '
        DB.get(sql, user_login, function(error, row) {
            if (error) {
                console.log(error)
                return
            }

            printUserEmail(row.email)
        });
    }
}
```
Don't forget the comma after the registerUser object.
  1. Update the function signature.

    function findUserByLogin(user_login) { ...
    
    to
    
    findUserByLogin: function(user_login) { ...

    There's actually an issue with this function. The printUserEmail function is no longer usable because that's in the server.js file. The findUserByLogin function will need to be refactored to accept a callback as a parameter.

  2. Add a new parameter for the callback function.

    findUserByLogin: function(user_login, _callback) { ...

    I personally like to use "_callback", but I've also seen "callback" and "cb" variations.

  3. Change the "printUserEmail" to "_callback" and also remove the ".email" from "row.email".

    DB.get(sql, user_login, function(error, row) {
        if (error) {
            console.log(error)
            return
        }
    
        _callback(row)    // Updated function name
    });

    Now, we can pass in any function as a callback as long as the callback accepts row as a parameter. In general, it's a good idea to leave the filtering of the data to the callback function. One exception is sensitive data. In that case, you want to make sure that's not returned in the database results unless it's needed.

  4. Go back to server.js and update the "printUserEmail" function there.

    function printUserEmail(userInfo) {
        console.log("User's email is: " + userInfo.email)
    }

Quick Test Check

  1. Go to server.js, uncomment and update the following line.

    // findUserByLogin('newuser')
    
    to
    
    DB.findUserByLogin('newuser9', printUserEmail)

    We're passing printUserEmail as a callback. If there are no errors, the printUserEmail will be called and the user's email printed out.

Add Remaining Functions

For brevity, I won't go over adding the remaining functions in detail since the steps are exactly the same as above. Try to do this on your own first, then check the completed code at the bottom of this section. To recap, here are the instructions from the previous section.

  1. Move over function from server.js to database.js.
  2. Update the function signature.
  3. Add a parameter for a callback if needed.

Finished 'return' Object

    return {
        registerUser: function(login, password, email) {
            var sql= "INSERT INTO Users (login, password, email) "
            sql += "VALUES (? ,?, ?) "

            DB.run(sql, [login, password, email], function(error) {
                if (error) {
                    console.log(error)
                } else {
                    console.log("Last ID: " + this.lastID)
                    console.log("# of Row Changes: " + this.changes)
                }
            });
        },
        findUserByLogin: function(user_login, _callback) {
            var sql = 'SELECT email '
            sql += 'FROM Users '
            sql += 'WHERE login = ? '


            DB.get(sql, user_login, function(error, row) {
                if (error) {
                    console.log(error)
                    return
                }

                _callback(row.email)
            });
        },
        getUserEmailsAll: function(_callback) {
            var sql = 'SELECT email '
            sql += 'FROM Users '

            DB.all(sql, [], function(error, rows) {
                if (error) {
                    console.log(error)
                    return
                }

                _callback(rows)
            });
        },
        getUserEmailsEach: function(_callback) {
            var sql = 'SELECT email '
            sql += 'FROM Users '

            DB.each(sql, [], function(error, row){
                if (error) {
                    console.log(error)
                    return
                }

                _callback(rows)
            });
        }
    }

The second getUserEmails uses the "each" method instead of the "all". Therefore, the callback should take this into account.

Conclusion

In this tutorial, we went over a couple of different techniques for organizing SQLite related code. This included breaking out the database code and schema into separate files, and refactoring the database code to create an object with an API. If you would like to see the finished code for this part of the tutorial, please go to my Github link below.

https://github.com/CharlieTheProgrammer/NodejsSQLite-Pt3

The previous two parts of this tutorial covered setting up Nodejs with SQLite and SQLite query methods. Links to the articles are below if you wish to visit.

The Best Nodejs SQLite Tutorial – Part 1: Setting Up Nodejs with SQLite

The Best Nodejs SQLite Tutorial – Part 2: Running SQLite Queries from Nodejs