Adding Database Interactions to Express Apps
Tags: webdev, programming, javascript, node
Published: 2021-01-24 22:04:23

**Check out my books on Amazon at https://www.amazon.com/John-Au-Yeung/e/B08FT5NT62**

**Subscribe to my email list now at http://jauyeung.net/subscribe/**

Most back end apps need to interact with a database to do something useful. With Express apps, we can add database interactivity easily.

In this article, we’ll look at how to get and save data into an SQLite database with an Express app.

Getting Started
===============

We get started by creating a project folder, then go in it and run:

```
npm init -y
```

to create a `package.json` file.

The `-y` flag just answers all the question from `npm init` with the default options.

Next, we install Express, `body-parser` to parse request bodies, and `sqlite3` for interacting with our SQLite database by running:

```
npm i express sqlite3 body-parser
```

Building Our App
================

Initialization Code
-------------------

The app we build will get and save data to the `persons` database.

To start, we create an `app.js` for the app. Then we create the app by first importing the packages we need to use and setting the port that our app will listen to connections to.

To do this, we add:

```
const express = require('express');  
const sqlite3 = require('sqlite3').verbose();  
const bodyParser = require('body-parser');  
const app = express();  
const port = 3000;
```

We include the packages we installed earlier, including Express, `body-parser` , and `sqlite3` .

Then we create an instance of our Express app and set the `port` to 3000 to listen to requests on port 3000.

Next, we create our database initialization code. We do this by writing:

```
const db = new sqlite3.Database('db.sqlite');
```

The code above tells us that our app will use `db.sqlite` to get and save data. If it doesn’t exist, it’ll be created on the fly so we don’t have to create it beforehand. Also, we don’t need any credentials to use the database file.

Then we create the `person` table if it doesn’t already exist by writing:

```
db.serialize(() => {  
    db.run('CREATE TABLE IF NOT EXISTS persons (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');  
});
```

We have an `id` auto-incrementing integer column, and `name` and `age` columns for saving some personal data.

We include `IF NOT EXISTS` in our `CREATE TABLE` query so that the database won’t be attempted to be created every time the app restarts. Since if it already exists, the query will fail if the table already exists.

Then we include our `body-parser` middleware so that JSON request bodies will be parsed into the `req.body` object as follows:

```
app.use(bodyParser.json());
```

Adding the Routes
-----------------

Now we add the routes for getting and saving data.

First, we add a route to handle GET requests for getting data from the `persons` table, we add the following:

```
app.get('/', (req, res) => {  
    db.serialize(() => {  
        db.all('SELECT * FROM persons', [], (err, rows) => {  
            res.json(rows);  
        });  
    })  
})
```

We use `app.get` to handle the GET request to the `/` route.

`db.serialize` makes sure that each query inside the callback runs in sequence.

Then we select all the rows from the `persons` table and send the array returned as the response.

Next, we add our POST route to handle the POST request for saving a new entry to the database:

```
app.post('/', (req, res) => {  
    const { name, age } = req.body;  
    db.serialize(() => {  
        const stmt = db.prepare('INSERT INTO persons (name, age) VALUES (?, ?)');  
        stmt.run(name, age);  
        stmt.finalize();  
        res.json(req.body);  
    })  
})
```

We start by using the `app.post` to indicate that we’re handling a POST request. The `/` means that we’ll handle requests to the `/` path.

Next, we get the parsed JSON request body returned by `body-parser` by writing:

```
const { name, age } = req.body;
```

We decomposed the fields into variables with the destructing assignment operator.

Next, we create a prepared statement to insert data to the `persons` table with `db.prepare`. This lets us set data for the placeholders marked by the `?` and also sanitizes the data to avoid SQL injection attacks.

Then we run:

```
stmt.run(name, age);  
stmt.finalize();
```

to run the statement and return the response with `res.json(req.body);` .

Next, we add the route to let us update an entry:

```
app.put('/:id', (req, res) => {  
    const { name, age } = req.body;  
    const { id } = req.params;  
    db.serialize(() => {  
        const stmt = db.prepare('UPDATE persons SET name = ?, age = ? WHERE id = ?');  
        stmt.run(name, age, id);  
        stmt.finalize();  
        res.json(req.body);  
    })  
})
```

It’s similar to the POST request route above, except that we’re handling PUT requests.

The difference is that we have an `:id` in the URL parameter, which we get by writing:

```
const { id } = req.params;
```

So when we make a request to `/1` , `id` will be set to 1.

Then we ran our update statement with `name` , `age` , and `id` and return the request body as the response.

Finally, we have our DELETE route to let us delete items from the `persons` table as follows:

```
app.delete('/:id', (req, res) => {  
    const { id } = req.params;  
    db.serialize(() => {  
        const stmt = db.prepare('DELETE FROM persons WHERE id = ?');  
        stmt.run(id);  
        stmt.finalize();  
        res.json(req.body);  
    })  
})
```

It’s similar to the other routes except that we have a delete request and we run a delete statement with the ID.

Then we return the request body as the response.

Finally, we add the following line:

```
const server = app.listen(port);
```

So that when we run `node app.js` , our app will run.

Conclusion
==========

We run our app by running `node app.js` .

In the end, we have the following code if we put everything together:

```
const express = require('express');  
const sqlite3 = require('sqlite3').verbose();  
const bodyParser = require('body-parser');  
const app = express();  
const port = 3000;  
const db = new sqlite3.Database('db.sqlite');
db.serialize(() => {  
    db.run('CREATE TABLE IF NOT EXISTS persons (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');  
});

app.use(bodyParser.json());  
app.get('/', (req, res) => {  
    db.serialize(() => {  
        db.all('SELECT * FROM persons', [], (err, rows) => {  
            res.json(rows);  
        });  
    })  
})

app.post('/', (req, res) => {  
    const { name, age } = req.body;  
    db.serialize(() => {  
        const stmt = db.prepare('INSERT INTO persons (name, age) VALUES (?, ?)');  
        stmt.run(name, age);  
        stmt.finalize();  
        res.json(req.body);  
    })  
})

app.put('/:id', (req, res) => {  
    const { name, age } = req.body;  
    const { id } = req.params;  
    db.serialize(() => {  
        const stmt = db.prepare('UPDATE persons SET name = ?, age = ? WHERE id = ?');  
        stmt.run(name, age, id);  
        stmt.finalize();  
        res.json(req.body);  
    })  
})

app.delete('/:id', (req, res) => {  
    const { id } = req.params;  
    db.serialize(() => {  
        const stmt = db.prepare('DELETE FROM persons WHERE id = ?');  
        stmt.run(id);  
        stmt.finalize();  
        res.json(req.body);  
    })  
})

const server = app.listen(port);
```

Once we include a database library, we can create an Express app that interacts with a database to make our apps more useful.