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.