SQLite Made Easy: A Quick and Simple Introduction for Beginners Using Node.js

I love sqlite. It’s a very light database that uses the SQL language and lives in 1 local file. Super easy to manage. Wanna have a backup? Just copy your .db file and there you have it.

It’s light. A fast and efficient database.

That’s why in this tutorial I’ll show you how I set it up for my web development projects. Follow along. It won’t take more than 5 minutes.

As always, setup your project with node.js and install sqlite like so:

yarn add sqlite3 sqlite

You’ll need both, sqlite and sqlite3.

Now create a start function that initialises the database and creates a table like so:

const path = require('path')
const sqlite3 = require('sqlite3')
const { open } = require('sqlite')
let db = null

const start = async () => {
  try {
    db = await open({
      filename: path.join(__dirname, 'database.db'),
      driver: sqlite3.Database,
    })
    await db.exec('CREATE TABLE IF NOT EXISTS users (name, colour)')
  } catch (e) {
    return console.log(‘Error’, e)
  }
}

start()

You can call the database however you want. As long as it ends in .db.

As you can see I created a function called start() and it’s an async function that initialises the open() function with the filename of the database.

Once the database is initialised, we simply create a table if it doesn’t exist with the usual SQL structure. The elements inside the brackets are the table columns, in this case they are “name” and “colour”.

Then you can simply update the database like so:

await db.run('INSERT INTO users VALUES (?, ?)', [
  'John', 'red',
])

By using db.run you can insert values as the second parameter array and use the question mark ?as the placeholder for those values.

In this case I simply insert a new item in the users table with John as the name column and red as the colour column.

To get values you simply use the db.all() function like so:

const results = await db.all(‘SELECT * FROM users’)
console.log(‘results’, results)

That will give you an array with all the results. You can use get instead of all but I prefer to use always all for simplicity.

That’s it! You can now use sqlite on your node.js applications. You can always search for more information on SQL commands at w3schools. I personally like that website for it’s clarity.

Use this guide as your reference every time you want to setup a new web development project and subscribe to me here to see new articles.

Subscribe to me here: https://merunasgrincalaitis.medium.com/subscribe