Go Back

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

Posted by Charlie Ontiveros on 2018-07-04

Welcome to Part 2 of a multi-part series on working with SQLite within Nodejs. In this part, I will go over the basic functions the SQLite library has to run queries plus examples.

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

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

SQLite Query Methods

Below are the SQLite methods used to run queries.

  • run: Runs the SQL query, but is only meant to insert, update, or delete. It does not return back database values. However, it does return back two pieces of information, lastID and changes.
  • get: Runs the SQL query and only returns the one row's worth of data.
  • all: Runs the SQL query and return all rows.
  • each: Runs SQL query and applies a callback for each row of data that is returned.

With the exception of each, which has 4 parameters, the above methods all accept 3 parameters:

  • sql: The sql query string.
  • params: This is either a single variable or an array of variables. Although this is technically an optional value, the next section explains why you should always include these when using dynamic values.
  • callback(error, row data): A function SQLite will call when a query is completed. The first parameter will be an error and the second parameter will include row data where applicable.

About SQL Injections

A SQL injection is an attack a malicious actor can use to produce harmful effects. For brevity, I won't go into detail about what this is here, but I will explain what you can do to stop SQL injection attacks. As a matter of fact, SQLite makes it very easy to avoid this type of vulnerability.

Hardcoded Query

Below is a simple example of a query that inserts what the user enters as a login directly into the query.

findUserByLogin(user_login, _callback) {
    let sql = 'SELECT user_id, user_login '
    sql += 'FROM Users '
    sql += 'WHERE user_login = ' + user_login

    DB.get(sql, [], _callback);
};

This is very dangerous because an attacker could enter in a second SQL query as part of the user login. Since the entire query is simply a string, SQLite has no way of knowing that the second query is unintended.

Another thing to note is that an empty array will need to be passed as the second parameter when utilizing a callback. In this way SQLite nudges users to use this feature.

Parametized Query

Below is a simple example of a query that uses parameters/placeholders. The parameter is denoted by a ? mark within the SQL string. The amount of parameters in the database call must match the amount of placeholders. Order is also important.

findUserByLogin(user_login, _callback) {
    let sql = 'SELECT user_id, user_login '
    sql += 'FROM Users '
    sql += 'WHERE user_login = ? '

    DB.get(sql, user_login, _callback);
};

SQLite will automatically sanitize the value in user_login before using it to execute the query. This will prevent all SQL injection attacks. All examples in this tutorial will utilize this method for parametizing sql queries.

Run Query

Since our User table is empty, lets run a query to add a user.

  1. Create a function to store the SQL string and DB execution.

    function registerUser(login, password, email) {}

    If you recall, a user can also have a first and last name, but those are optional fields. For simplicity, those fields will be omitted in these examples.

  2. Define a variable to hold the SQL query string. Make sure you have a space right before the last quote or an error will occur.

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

    As mentioned in the SQL Injection section, the '?' are placeholders for the values in the login, password, and email columns.

  3. Add the run method call to execute the query when the registerUser function is called.

    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)
            }
        });
    };

    The run method only calls the callback if an error occurs. Fortunately, the run method exposes two properties within the function that can be used to positively confirm the query was successful; lastID and changes.

    The lastID property returns back the primary key value of the last row that was inserted in the database. The changes property returns back the number of rows that were modified. If either of these are greater than 0, the query was successful.

Quick Test Check

  1. Add the following line after the function and run server.js.

    registerUser("newuser", "pass", "test@test4637.com")

    You should see a number after Last ID and # of Row Changes in the console.

  2. Run server.js again. This time, you should see an error from SQLite.

    { Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: Users.email errno: 19, code: 'SQLITE_CONSTRAINT' }

    This error occurs because the login and email need to be unique in the database.

  3. Modify the login and email values in the registerUser function and run server.js again to create a few more users.
  4. Comment out the registerUser function to prevent errors from occurring in the rest of the tutorial.

Get Query

In this example, we are going to create a get query to retrieve data from the user that was created in the previous section. When using the run method to insert or delete data, it's usually not necessary to pass the results up to the function that initiated the run method.

However, when getting data out of the database, you'll need to pass in the action you want to perform on the data as a callback.

  1. Create a function that will use the data being returned from the database.

    function printUserEmail(email) {
        console.log("User's email is: " + email)
    }
  2. Create another function to store the SQL string and DB execution.

    function findUserByLogin(login) {}
  3. Define a variable and store the SQL string with placeholders. For this example, we will be getting the user's email only.

    function findUserByLogin(login) {
        var sql = 'SELECT email '
        sql += 'FROM Users '
        sql += 'WHERE login = ? '
    }
  4. Add the get method call to execute the query + error handling.

    function findUserByLogin(login) {
        var sql = 'SELECT email '
        sql += 'FROM Users '
        sql += 'WHERE login = ? '
    
        DB.get(sql, login, function(error, row) {
            if (error) {
                console.log(error)
                return
            }
    
            printUserEmail(row.email)
        });
    }

    If an error occurs, it will be logged and then the function will end. If the query is successful, a JSON object will be returned from SQLite. The email value will then be passed to the printUserEmail function. The key values are the column names in the database.

Quick Test Check

  1. Add the following line after the function and then run server.js.

    findUserByLogin('newuser')

    You should see "User's email is: test@test4637.com" appear in the console. You can comment out this line or leave it as is for the remainder of this tutorial.

All Query

For this example, we are going to take the previous get query and modify it to return all user emails.

  1. Create a function that will utilize all user emails.

    function listUserEmails(userEmails) {
        userEmails.forEach(email => {
            console.log(email.email)
        });
    }

    userEmails is going to be an array of objects. Therefore, we will use the forEach method to iterate over the array.

  2. Create another function to store the SQL string and DB execution.

    function getUserEmails() {}
  3. Define a variable to store the SQL string with placeholders.

    function getUserEmails() {
        var sql = 'SELECT email '
        sql += 'FROM Users '
    }
  4. Add the all method to execute the query + error handling.

    function getUserEmails() {
        var sql = 'SELECT email '
        sql += 'FROM Users '
    
        DB.all(sql, [], function(error, rows) {
            if (error) {
                console.log(error)
                return
            }
    
            listUserEmails(rows)
        });
    }

    Just as before, if an error occurs, it will be printed out and function call will exit. Else, the array of objects will be passed as a parameter to the listUserEmails function. Furthermore, note that the use of [ ] as a placeholder is acceptable here since we are not using dynamic values in the query.

Quick Test Check

  1. Add the following line after the function and run server.js.

    getUserEmails()

    You should see a list of user emails printed out to console. You can comment out this line or leave it as is for the remainder of this tutorial.

Each Query

In this section, we'll also print out user's emails, but will be using the each method. This method runs the callback function on each row of data that is returned. This is just to show that there are different ways of doing the same thing.

  1. Change DB.all to DB.each and rows to row.

    function getUserEmails() {
        var sql = 'SELECT email '
        sql += 'FROM Users '
    
        DB.each(sql, [], function(error, row) {  // Updated
            if (error) {
                console.log(error)
                return
            }
    
            listUserEmails(rows)
        });
    }
  2. Replace listUserEmails(rows) with console.log(row.email).

    function getUserEmails() {
        var sql = 'SELECT email '
        sql += 'FROM Users '
    
        DB.each(sql, [], function(error, row) {    // Updated
            if (error) {
                console.log(error)
                return
            }
    
            console.log(row.email)        // Updated
        });
    }

Quick Test Check

  1. Run server.js. You should see the same results as before.

While the each method may result in less code, I have not found any use for it in my personal projects. Indeed, there is a drawback to this method. For instance, there is no way to stop execution halfway through the results.

Furthermore, in order to maintain separations of concerns, I recommend keeping the code that interacts directly with SQLite (or any database) as simple as possible. I'll go over this in more detail in the next part of this series.

Conclusion

In this part of the tutorial, we went over the different methods of running queries with the SQLite NodeJS library. You can download the complete code here:

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

Part 3 will cover techniques you can use to make SQLite related code more usable in a small to medium sized application.