Node.js with SQLite

A few weeks ago, I created an article about handling POST requests with Node.js. I wanted to follow that article with a brief explanation of how to implement data persistence. Unfortunately, I ended up a little busier than I had expected and had to postpone.

Before we get started, I'll be referencing the project built here.

First, be sure you have SQLite, and a reference to the executable in your PATH.

We'll add a db folder to the top level of our project's working directory as well. Our file structure should look as follows.

controllers
    app_controller.js
    items_controller.js
db
views
    items
        index.html
        new.html
    index.html
app.js

Now, we'll create our SQLite database and create an items table, with a value column.

Assuming that you're in the top level of the working directory, enter the following commands.

cd db
sqlite3 development.db
create table items(value string);
insert into items values("item 1");
.exit
cd ..

Next, we'll install the Node.js package, sqlite3. Enter the following command, and wait for the package to install.

npm install sqlite3

Open items_controller.js and implement the following changes at the top of the file.

...
const sqlite3 = require("sqlite3");

const items = [];
const db = new sqlite3.Database("./db/development.db");
db.all("SELECT * FROM items", (error, rows) => {
    if (error) throw error;
    for (const row of rows) {
        items.push(row.value);
    }
});
...

Here, you can see that we're establishing a connection to our database, and running the query SELECT * FROM items to get the collection of rows from our table. We're then utilizing a callback to determine what we want to do with the data retrieved. In this case, we're iterating over the collection and storing the value of the value column of each item in our items array.

If you run the application and navigate to localhost:3000/items, you'll see the value of the item we inserted into the database earlier appear in our unordered list.

Now, we want to be able to store items submitted via POST request from our items form as well. To do this, we'll utilize the sqlite3 package within the callback of our request's end event.

Locate our call to request.on within the items controller, and edit the callback so that it looks as follows.

...
request.on("end", () => {
    const parsedBody = qs.parse(body);
    items.push(parsedBody.value);
    const insert = db.prepare("INSERT INTO items VALUES(?)");
    insert.run(parsedBody.value);
    insert.finalize();
});
...

You'll notice that, much like the query we ran earlier, when we were creating our database, we have INSERT INTO items VALUES(?). This is our template query with which we want to pass the value of the value key within our parsed body. We achieve this by passing the value to insert.run, and call insert.finalize to finalize the statement.

If you run the application and navigate to localhost:3000/items/new, you should, as before, be able to enter and submit a value before being redirected to the items index route and seeing the item appear in the list. You will also, however, be able to exit the application, and, upon running the application again, find your persisted list at localhost:3000/items.

I know that I wanted to cover rendering JSON for API setups, but I'm probably going to save that for next week's post. Next week, I plan on expanding this project to utilize the other two CRUD actions, update, and delete.

Comments

[Tobias Nickel]

when serving data to an API, you don't need to close the db every request. Just open the file and use it all the time.
In development even a sqlite3 GUI tool can view the data, while the server is running. sqlite3 is made in a way, that multiple pricesses can access the file at the same time.

[Brandon Weaver]

Tobias, thanks for pointing this out to me. I've only recently decided to pick up Node.js, and I'm bound to make oversights, so I really appreciate any corrections. I'll be sure to make adjustments to the post once I'm comfortable with the approach you've suggested.

[Tobias Nickel]

you liked that one? I also want to recommend using a Promises version of sqlite3. then you can use async/await and avoid the callback-hell.

if you like, can follow me back, then we can have private chat. I like that you share your experience. 👍