Node.js with SQLite
Tags: node, sql, beginners
Published: 2020-09-19 21:45:44

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](https://dev.to/brandonmweaver/handling-post-requests-in-node-js-1mgo).

First, be sure you have [SQLite](https://www.sqlite.org/download.html), 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.

```javascript
...
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.

```javascript
...
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.