Installing SQLite package

SQLite is a software used to interact with an embedded SQL database. The database is stored in a single file, and the code of this software is distributed via a TypeScript npm package

You can install SQLite with npm and then integrate it in a Typescript project with:

  • npm install sqlite3
  • npm install --save-dev @types/sqlite3

Running your first SQL query with Typescript

To run a SQL query with SQLite, you have to import the Database class from the package. Then, instanciate it with the file path where the database will be stored as argument. If the file does not exists, it will be created.

Then, you can launch any SQL query. Since we have no data in the database at that time, we will launch a SELECT query without a FROM clause.

The SQL query 'SELECT RANDOM() % 100 as result will return a random integer. We will use the open database instance to run this query, and then display its result with a callback arrow function:

import { Database } from 'sqlite3';

// Open a SQLite database, stored in the file db.sqlite
const db = new Database('db.sqlite');

// Fetch a random integer between -99 and +99
db.get(
  'SELECT RANDOM() % 100 as result',
  (_, res) => console.log(res)
);

Creating a database schema

Let’s create a first table in our database. We name this table articles. Each article will have an unique generated id, a title and a description.

CREATE TABLE IF NOT EXISTS articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(200) NOT NULL,
    description TEXT NOT NULL
)

Here are the types of each columns:

  • id is an unique generated integer and the primary key of the articles table
  • The title is a text of variable length ranging from 0 to 200. An article with a NULL title cannot be inserted in the database, as well as an article with a title longer than 200 charaters
  • The description if a variable length text without length limit

We store this table schema under sql/articles.sql. We will use SQLite to create this table (if it does not currently exists)

import fs from 'fs';

// Read and execute the SQL query in ./sql/articles.sql
db.exec(fs.readFileSync(__dirname + '/sql/articles.sql').toString());

Inserting articles in the database

Our article table have been created but is empty. We will use an INSERT query to add articles to the table

INSERT OR REPLACE INTO articles VALUES
    (1, 'First article', 'Neque porro quisquam est qui'),
    (2, 'Second article', 'ipsum quia dolor sit amet'),
    (3, 'Last article', 'dolorem consectetur, adipisci velit')

We used the OR REPLACE keywords to handle the conflict case when articles with id 1, 2 or 3 are already present in the table. In this case, they will be replaced by the articles we want to insert

As we previously did, we read the query from a file and then run the SQL query with TypeScript:

// Insert the three example articles
db.exec(fs.readFileSync(__dirname + '/sql/sample-articles.sql').toString());

Reading data

Now that we have a non empty table in our database, we are able to query its data

As an example, we can fetch the title of the two articles with the longest description:

db.all(
  'SELECT title FROM articles ORDER BY LENGTH(description) DESC LIMIT 2',
  (_, res) => console.log(res)
);

The all method of the Database instance of SQLite package is used in order to fetch all query result. The callback function will be called with two arguments:

  1. Eventually an error
  2. An array of results

Here is the result printed to the console:

[
  {
    "title": "Last article"
  },
  {
    "title": "First article"
  }
]

Updating rows

We can update articles in the database. The title of article of id 3 is Last article. We can UPDATE it to Third article and fetch the new title with these statements:

db.exec(`UPDATE articles SET title='Troisième article' WHERE id=3`);

db.get(
  'SELECT title FROM articles WHERE id=3',
  (_, res) => console.log(res)
);

Note that you should launch the second query as a callback of the first db.exec statement because the second query could be ran before the first one complete. Using async/await is also an option

Use parameters in a SQL query

We hardcoded the id=3 in the previous query. But how could we update a specific id without having to build a different query string ?

We could be tempted to use string interpolation like WHERE id=${id} but this could lead to SQL injection, a security breach.

To pass a parameter to a query, we will instead use a prepared query. This consists in writing the query with ? where parameters will be replaced and then passing the query and its parameters to the SQLite driver. The driver will handle a secure replacement of the parameters by the argument ones

const statement = db.prepare(
  `UPDATE articles SET title=Third article' WHERE id=?`
)
statement.run([3]);

Removing data from a table

If we don’t want the third article to be present in the articles table, we can remove it with a DELETE statement. Don’t forget to add a WHERE clause, otherwise the table will be wiped

const delete_statement = db.prepare(
  `DELETE FROM articles WHERE id=?`
)
delete_statement.run([3]);

Conclusion

SQLite is a light SQL database stored in a single file. A use case is to get started with SQL database without the burden of configuring and installing a database

SQLite supports most of common SQL standards (but don’t expect to be able to compute a linear regression). In this article, we saw table creation, and insertion, selection, update and deletion of rows in a table