💾 Getting Sqlite running with await/async and expressjs/polkajs
Tags: database, javascript, webdev, beginners
Published: 2019-08-17 13:28:18

---
title: 💾 Getting Sqlite running with await/async and expressjs/polkajs
published: true
description: Async/Await is awesome but sometimes it is hard to get running here is how you can do it with sqlite and expressjs!
cover_image: https://thepracticaldev.s3.amazonaws.com/i/2cnqszpumqmsz9f40ofn.png
tags: #database #javascript #webdev #beginners
---


❗❗❗❗ **This blog post is inspired by our work over at our [live twitch stream](https://twitch.tv/lampewebdev). If you want to follow how we over there work on making a blog in svelte3 or maybe you have some questions about web development? Every question is welcome, and there are no stupid questions! I will try to answer them as best as I can! To get to the twitch page click 👉[here](https://twitch.tv/lampewebdev)👈.**

### Introduction

SQLite is by many big companies like Google, Mozilla, or Facebook. Android and iOS Applications use it as there local databases. It is used in embedded devices like BMW's iDrive. It was even found in malware like Flame. So as you can see, SQLite can be used in a lot of situation and products!

### Requirements

* Installed NodeJS and NPM in their latest version or at least with Async/Await support.
* Basic SQL Knowledge. Basic! We will do nothing fancy here
* Basic understanding of Async/Await. I recommend reading my article about [Async/Await](https://dev.to/lampewebdev/i-promise-this-is-a-practical-guide-to-async--await-39ek)
* All commands will be Unix commands if you want to run them on windows you need to change them to the Windows versions. I would highly recommend [WSL](https://en.wikipedia.org/wiki/Windows_Subsystem_for_Linux).

### The Setup

The first thing we need to do is initialize a new NPM project. Let's create a new git repo and initialize NPM.

```bash
mkdir sqlite-expressjs-async-await-blogpost
cd sqlite-expressjs-async-await-blogpost
git init
npm init -y
```

This will create a folder named `SQLite-expressjs-async-await-blogpost`. Then we change the directory to the just created one and initialize git and create a new `package.json` with the `npm init` command. The `-y` stands for yes accept everything.

Now that we have our project initialized, let's install the needed NPM packages.

```bash
npm i --save polka sqlite-async
```

`polka` is an extremely minimal, highly performant Express.js alternative. If you know expressjs then you fell at home. We are using the `sqlite-async` NPM package here instead of `sqlite` or `sqlite3` because like you can see the package from the name it supporting async/await out of the box, and we don't need to do some voodoo to make it run.

### Running polka

We need to create an entry point for our app.

```bash
touch server.js
```
This will create a server.js file in the folder, and now we finally can use a code editor.
Let's first try to start polka!

```js
// server.js
const polka = require('polka');

polka()
    .get('/', (req, res) => {
        res.end('<h1>hello world</h1>');
    })
    .listen(3000, err => {
        if (err) throw err;
        console.log(`> Running on localhost:3000`);
    });
```
We first need to require `polka` and then we are creating a route called `/`. This is the root route, and browser usually don't show this in the address bar when you are for example on `localhost:3000` the correct address is: `http://localhost:3000/` but who wants to type that right? What we described is all done by the `get()` function. The `listen()` function tells polka to listen to the port `3000` and the second argument is the callback that is run when the server started. You can change 3000 to whatever you want! You can even be a hacker and run it on `1337`. Now you can call your self `Elite` ;)

But how to start that server? This is easy!
In a Terminal that is in the project folder you can type:
```bash
node server.js
```

This command will start polka, and you can go to `localhost:3000` and you should see a big fat **`hello world`**!

### Creating an empty database

Now that we know that we can run our server, we can set up SQLite.

Keep in mind that we are not using any fancy auto-reload of our server. You need to shut it down after every save and rerun it. You do this by pressing `CTRL+C` in the terminal where the server is running and then just rerunning it with `node server.js`.
```js
// server.js
const Database = require('sqlite-async')
```
We need to import sqlite-async first, and now we need to rewrite our `server.js` a little bit to make it work with async/await

```js
// server.js
const main = async () => {
    try {
        db = await Database.open(":memory:");
    } catch (error) {
        throw Error('can not access sqlite database');
    }
    polka()
        .get('/', (req, res) => {
            res.end('<h1>hello world</h1>');
        })
        .listen(3000, err => {
            if (err) throw err;
            console.log(`> Running on localhost:3000`);
        });
}

main();
```

Let's go step by step.

We did not change the `polka()` code at all.
We wrapped everything in a fat arrow function with an async statement, and at the end of the file, we are calling this function. We need to do this to make await work.

Let us talk about this line:
```js
db = await Database.open(":memory:");
```
This line is the biggest new thing! We are 'opening' a new Database. The function actually checks if there is already a database if yes it just connects to that database and if there is now database it creates a new one and then connects to it. `:memory:` means that we are creating our database in the RAM of the computer and not somewhere on the Filesystem as you should do it if you want your data to survive a server crash or reload! We are using `:memory:` here because it is easier to clean up because you don't have to clean up at all ;).
So when this is successful, we have a connection to our database!
The `try/catch` is there because nodejs will crash if there is an unhandled error! Please always use `try/catch` when working with promises!

### Creating an empty table!

Now that we have a database, we also need a table. We will create the following table named user with the columns:
- FirstName type TEXT
- LastName type TEXT
```js
// server.js
// insert that code after the `database.open` try/catch
    try {
        await db.run(`
        CREATE TABLE user (
                    firstName TEXT,
                    lastName TEXT
        )
        `);    
    } catch (error) {
        throw Error('Could not create table')
    }
```

The code will create a table called `user` and this table will have 2 columns `firstName` and `lastName` of the type TEXT.

### Inserting some data

Let's now insert some data into the table!

```js
// server.js
// Insert this after the Create table try/catch
    try {
        const insertString = `
            INSERT INTO blogPosts 
            (firstName, lastName)
            VALUES (?,?)
        `;
        await db.run(insertString,
            "Michael",
            "Lazarski"
        );
    } catch (error) {
        throw Error('Could not insert new user');
    }
```

 Okay, This query has two parts. The `const insertString` and the actual run command with the data that we want to insert.

```sql
INSERT INTO users(firstName, lastName)
```

This tells SQLite that we want to insert into the database and the first field is the firstName and the second field ist lastName.

```sql
 VALUES (?, ?)
```
This line is exciting. `VALUES` here means that we need to specify the values we want to insert into the table. Think of this like a parameter list that you would pass into a function. This also has a connection to the `users(firtName, lastName)` line. The order matters here! The first question mark, in this case, is the first name, and the second question mark is the last name. But why `?`. Take a look again the the `db.run()` function. The first parameter is our query. The second and third are again in the same order as the question marks. This has two jumps. In the insert line we are telling what we want to insert in the `VALUES` line we are telling SQLite that we want to insert the second and third parameter of the `db.run()` function. It is good practice to do it like this because the `sqlite-async` npm package will also prepare the string and escape characters for you that you can't insert that easy like `'` or other special characters.

### Getting data end showing it on our page

We now need to query our data and then send it back to the client.
The following code can do this:
```js
// server.js
// change the .get('/') function
polka()
    .get('/', async (req, res) => {
        const {firstName, lastName} = 
            await db.get("SELECT firstName, lastName FROM user");
        res.end(`<h1>hello ${firstName} ${lastName} </h1>`);
    })
```

The first thing we did is make the second parameter that is a fat arrow function async so that we can use await. We can use a simple Select here because we only have one row in our table. We are selecting firstName and lastName from the user table again and because we are getting back just on an object with the `db.get()` function, we can destructor it. The last step is to use a template literal to create our small HTML example.

### Optional: Searching for a specific user

Imagine you now have a lot of users, and you want to find the first `Michael` in your database. For this, you need to change the `SELECT` a little bit.

```js
await db.get(`SELECT firstName, lastName 
                FROM user 
                WHERE firstName LIKE ?`,
                "%Michael%");
```
The only new thing here is the `WHERE` and the `LIKE`. What we are doing here is we are searching for the first entry where our firstName matches `Michael`. The % before and after means that what `Michael` can be anywhere in that name. `MMichael` or `Michaels` for example, would also match.

### Finale code
If you want to check out the finale code you can find it in the following [github repo](
https://github.com/lampewebdev/sqlite-expressjs-async-await-blogpost)

It would help me if you could do the following for me!
Go to [Twitch](https://dev.to/twitch_live_streams/lampewebdev) and leave a follow for me! If just a few people would do that, then this would mean the world to me! ❤❤❤😊

**👋Say Hallo!** [Instagram](https://www.instagram.com/lampewebdev/) | [Twitter](https://twitter.com/lampewebdev) | [LinkedIn](https://www.linkedin.com/in/michael-lazarski-25725a87) | [Medium](https://medium.com/@lampewebdevelopment) | [Twitch](https://dev.to/twitch_live_streams/lampewebdev) | [YouTube](https://www.youtube.com/channel/UCYCe4Cnracnq91J0CgoyKAQ)