Michael "lampe" Lazarski

Posted on Aug 17, 2019

💾 Getting Sqlite running with await/async and expressjs/polkajs

#database #javascript #webdev #beginners

❗❗❗❗ This blog post is inspired by our work over at our live twitch stream. 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👈.

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

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.

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.

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.

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!

// 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:

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.

// 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

// 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:

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

// 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!

// 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.

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.

 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:

// 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.

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

It would help me if you could do the following for me!
Go to Twitch and leave a follow for me! If just a few people would do that, then this would mean the world to me! ❤❤❤😊