NodeJS SQlite3 Tutorial

Introduction

  SQlite is a very powerful database that you can use in your NodeJS applications. It can be easily installed and setup for you to use in your applications. SQlite3 is an NPM (node package manager) package that allows you to easily work with SQlite databases. While the tutorial was pretty much made to be used within discord.js, it can also be used in different NodeJS applications that do not deal with discord.js. If you want to watch my video tutorial, check it out here: SQLite Databases Tutorial In Discord.js

Installation

  To install sqlite3 with npm, simply open a terminal and use “npm install sqlite3”. If you are having trouble you may want to try “npm install [email protected]” instead.

  Along with sqlite3, you may want to download and install DB Browser. DB Browser lets you look at and change data in your database file. Since you cannot open this type of file up with a text editor.

Databases, Tables, Columns, and Rows

  First, let's talk about tables. In a single SQlite database file there can be any number of tables inside of it. Each table is like a separate sheet in excel for example. You might have a table that contains data on users and another table that holds data on dogs. The tables are completely separate and will have different columns in them.

  With that said, let's talk about columns. Columns are names you give to your data in the table and go from left to right. For instance, your user table might have a column called “id” and another column called “email”. Obviously in the “email” column there would be the user's email. It would look something like this (using DB Browser to view it) (insert DB screenshot). This is all personal preference, but you could have many columns for your table, like if we were describing a car for example. Let's say we want to give the car a make, model, year, and color. We could create four columns: “make”, “model”, “year”, and “color”. Or we could just have one column called something like “specs”. Then this one column could contain all the data in a JSON object into a string. Once we have created a JSON object in JS, we can easily convert it to a string. Once we get it back from the database, it as just as easy to convert the string back into an object. Here is an example:

        let car = { //create JSON object
                make: "MetalRain",
                model: "Rain 2000",
                year: 2021,
                color: "red"
            }
            
            let carstr = JSON.stringify(car); //turn the object into a string
            
            let carObject = JSON.parse(carstr); //turn the string into and object
        
        

  Before we get to writing the SQLite code, we will quickly talk about rows. Columns are left to right, things you create for each table, while rows are individual bits of information that go up and down. Each row would be a separate user for example, like in this image: (DB screenshot). When you add new data to a database, you are inserting a row. Keep in mind to specify which user you will need to get that user by a certain column. It is best to give each user an ID, in Discord you can just use the user ID discord gives each user. You can also get one row from a table or multiple. If you are getting multiple rows you can also sort the rows by any column.

Setting Up Our Database

  Now that we finally got that out of the way, let's set up our database. The best way to do this, that I have found, is using DB Browser. Simply open up DB Browser and click “New Database”. For this tutorial I will call mine “database.db”. When you click save it will pop a screen to “Edit Table Definition”, simply hit cancel as we will have our program create the tables for us. Be sure to save or move this table to the right spot! With that said let's get to some code!

Creating a Table

  Creating a table is a simple task, all you need to decide on is a table name and then the column names. Don't forget a single database file can have multiple tables in it. Don't forget to import sqlite3 as well.

        const sqlite3 = require('sqlite3').verbose(); //import the sqlite3 module and the use the .verbose configuration

            let db = new sqlite3.Database('./databasename.db', sqlite3.OPEN_READWRITE); //here we are getting the database so that we can manipulate it
            //we do "OPEN_READWRITE" so that we can read and write to the databse

            //next we will create a table if it doesn't exist already
            db.run('CREATE TABLE IF NOT EXISTS users (userid TEXT NOT NULL, username TEXT NOT NULL, car TEXT NOT NULL, number INTEGER NOT NULL)', function (err) {
                if (err) { //if there is an error when we run this, log it
                    console.log(err);
                }
                db.close(); //don't forget to close it when you're done!
            });
        
        

  "db.run(...)" will run whatever parameters you provide it, here we created a table named "users" that has the columns "userid", "username", "car", and "number". Besides the column "number", the rest of the columns we said were strings. Now if you were to put a number in a string column or vice versa, it would still work fine. We wrote "NOT NULL" in all caps because we don't ever want this to be null, as in nothing exists there. I especially do this so that if I screw up and try to set someone's data to nothing, it'll error and leave the original data there. Be sure to make sure this runs before you try to access the table, for discord bots the ready listener is a good spot for it. Now that we got our table created, it's time to add some data.

Inserting Data

        //once again we will get the database itself so we can use it
            let db = new sqlite3.Database('./databasename.db', sqlite3.OPEN_READWRITE);

            //To get ready let's create a JSON object for the user's car and turn it into a string like we showed before
            let car = {
                make: "MetalRain",
                model: "Rain 2000",
                year: 2021,
                color: "red"
            }

            let userdata = db.prepare('INSERT INTO users VALUES(?,?,?,?)'); //We need to use a prepare statement to get ready to insert the data
            //the number of question marks should be the same as the number of columns, in our case that'll be 4
            //we are inserting into the table "users", the values we will fill in the next line

            let carstr = JSON.stringify(car); //here we tunred it into a JSON string

            //here I filled in the userid and username as if we were using discord, but you could do it however you would like
            userdata.run(message.author.id, message.author.tag, carstr, 1);
            //these parameters are what the database will enter, starting from the left column and going to the right

            userdata.finalize(); //this needs to be run before you close the database, this only applies for inserting
            db.close(); //finally close the database and a new row should be inserted!
        
        

Retrieving Data

Now that we inserted some data, how can we retrieve it? We can simply get the database again and query from it. For now we will just get one row, but you can multiple rows, which we will discuss that later.

        let db = new sqlite3.Database('./databasename.db', sqlite3.OPEN_READWRITE);
            let getdata = `SELECT * FROM users WHERE userid = ?`; //first we write out what we are getting
            //selecting * gets everything, but somthing like "SELECT username FROM" would only get the username. It is not good practice to use *, but for something small it should be fine
            //Here selected everything from the table called "users" and are getting the row where "userid" is equal to something, which we will define what in the next line

            let db = new sqlite3.Database('./databasename.db', sqlite3.OPEN_READWRITE); //get the database again
                db.get(getdata, [message.author.id], (err, row) => { //we use a "db.get" statement to get one row and put the value of "userid" in the square brackets
                //the parameter in the square brackets lets us get one row and then that row is returned as a parameter and is a JSON object

                if (err) { //here we just double check for errors
                    console.log(err); //log the error to the console
                    db.close(); //close the database
                }
                if (row === undefined) { //if there is no row. in this case, where the column "userid" is equal to the message author's ID or whatever parameter you passed
                    //that previous line could also be written "if (!row) {" since this is the same thing as saying it is undefined

                    db.close(); //close the database before returning something
                    return null; //if this were a function, we could return null since the user doesn't exist
                } else { //otherwise if there is a user that has this userid
                    //if we wanted to get any one of the user's columns we can easily do it like so:
                    console.log(row.username); //logs the user's username

                  let car = JSON.parse(row.car); //turn's the user's car string into a JSON object
                  console.log(car.color);

                  db.close(); //close the database before returning something
                  return car; //we could return the car if this were a function, or we could return the whole row
                }
            });
        
        

Updating Data

  Hopefully you got a good handle on sqlite3 so far. To use it at it's most basic level, we just need to go through updating rows. Don't worry, this is pretty simple.

        let db = new sqlite3.Database('./databasename.db', sqlite3.OPEN_READWRITE); //get the database again

            //we can update data in a table without even checking the value of it if we want
            db.run('UPDATE users SET username = ?, number = ? WHERE userid = ?', ["new user name", 7, "123456"], function (err) {
                if (err) { //check for errors again
                    console.log(err);
                }
                db.close();
            });
            //let's go through this step by step
            //first we say we are updating the table "users"
            //next we say let's update username and number, but we will say what to set them as later. Be sure to have a comma between columns if you have mutiple
            //then we say where the column "userid" equals something we also will define later
            //then we add the parameters in the square brackets
            //this goes from left to right, so we define the "username" to "new user name"
            //then we say set the "number" to 7 and lastly do this update to the row where "userid" is 123456
        
        

Putting It All Together

Congrats on getting this far! Now to help you out, I am going to combine these things we learned and walk you through it.

        let db = new sqlite3.Database('./databasename.db', sqlite3.OPEN_READWRITE); //start out the same
            let userid = message.author.id; //get the user's ID

            let getdata = `SELECT * FROM users WHERE userid = ?`; //get all the columns from the user
            db.get(getdata, [userid], (err, row) => { //get the row
              if (err) {
                console.log(err);
                db.close();
              }
              if (row === undefined) { //here there is no data on this user, so let's add some right away

                let newcar = { //let's create a defualt car
                  make: "none",
                  model: "none",
                  year: 2021,
                  color: "none"
                }

                let userdata = db.prepare('INSERT INTO users VALUES(?,?,?,?,?)');
                let carstr = JSON.stringify(car);
                userdata.run(message.author.id, message.author.tag, car, 1);
                userdata.finalize(); //user has been added!
                db.close();
              } else { //there is data on the user, so let's update it
                //once again we could get each column for the user

                let rng = Math.round(Math.random() * 5); //generate a random number from 0-5
                let number = row.number + rng; //add the random number to the user's "number" column

                //update their number
                db.run('UPDATE users SET number = ? WHERE userid = ?', [number, 7, userid], function (err) {
                  if (err) { //check for errors again
                  console.log(err);
                }
                db.close();
                });
              }
            });