Today I am going to teach you the basics of using SQLite in Node JS! For those of you who do not know, SQLite is a small, fast, and self-contained database that can live on your server in the form of a file. This is one of the easiest forms of SQL to use, as you can be up and running in a matter of minutes, with a relational database system that is full of features. Did I mention it’s also free? This makes it a phenomenal option for small low-traffic projects. I would highly recommend using this if you are making a portfolio site, or for prototyping an idea that you would like to eventually make into a large-scale high-traffic application.
Adding the Sqlite3 package
The first thing we must do is set up our project with the package, open your terminal in the, desired folder and simply type:
npm i sqlite3 --saveConnecting to the database
To connect to the database, we must first create it. Since this is SQLite, our database will be a file. Add a new file to your project called “test.db”. Now we can make our main JavaScript file. Let’s call this app.js. At the top of app.js, enter the following code (if you name your database something else, be sure to change the name here)
const db = new sqlite3.Database("./test.db", sqlite3.OPEN_READWRITE, (err) => {if (err) return console.error(err.message);});Creating your first table
After successfully connecting to the database, create a variable called SQL. We will be modifying the value of this to change how we alter the database.
let sql;We can now assign the variable an SQL string. We are going to create a simple table called users. This table will contain several columns. These are the id (primary key for the table), first_name, last_name, username, password, and email.
sql = `CREATE TABLE users (id INTEGER PRIMARY KEY, first_name, last_name, username, password, email)`;To execute the query, we can pass the SQL to the database’s run method.
db.run(sql); Inserting data
We can insert data into our database by creating a different sql string. Here our string will include all of the column names, except for id, as we set that to automatically increment. You can statically type the values in the same way you would regular sql. However, for this example, we are going to do something more advanced. We are going to only use the question mark symbol as the value, and we will later assign it a value when executing the sql.
sql = `INSERT INTO users (first_name, last_name, username, password, email) VALUES(?,?,?,?,?)`;
This time, when we run the sql, we will pass in a second variable. This variable is an array that contains the values that the question marks will be replaced with. This is very handy if we are ever looping through data and want to dynamically store multiple users. We will also be passing a callback function, which will simply log an error message if our execution of the SQL is unsuccessful.
sql = `INSERT INTO users (first_name, last_name, username, password, email) VALUES(?,?,?,?,?)`;
db.run(
sql,
["mike", "michaelson", "mike_user", "test", "mike@gmail.com"],
(err) => {
if (err) return console.error(err.message);
}
);
Updating data
This data can be updated at any point. For this example, we will change the user’s first name to “Jake”. In order to do this we will target only the user with an id of 1.
sql = `UPDATE users SET first_name = ? WHERE id = ?`;
db.run(sql, ["Jake", 1], (err) => {
if (err) return console.error(err.message);
});Querying / retrieving data
For our example, we will use a simple select all statement for the SQL.
sql = `SELECT * FROM users`; Instead of using the database’s run method, we will be using the all method. This allows us to loop through the table, grabbing each row. In our example, we will output each row to the console.
db.all(sql, [], (err, rows) => {
if (err) return console.error(err.message);
rows.forEach((row) => {
console.log(row);
});Deleting data
Deleting data can be done by running a simple SQL delete statement. Here, we will directly target the desired row via the id field.
sql = `DELETE FROM USERS WHERE id = ?`;
db.run(sql, [1], (err) => {
if (err) return console.error(err.message);
});In conclusion, integrating SQLite with Node.js offers developers a lightweight and efficient solution for database management within their applications. The combination of SQLite and Node.js presents a compelling option for building fast and reliable applications. When utilizing the power of Node.js’s asynchronous nature and SQLite’s embedded capabilities, developers can create robust and scalable applications with ease.
