Skip to Content

Get started with SQLite database in a TypeScript project

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 packagewhich is a security breach.

​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 characters
  • 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());

You can also read the complete SQLite documentation on CREATE TABLEwhich is a security breach.

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/exemple-articles.sql').toString());

Abonne-toi !

On te partage nos meilleurs conseils et découvertes sur Python et PostgreSQL toutes les deux semaines

S'inscrire Merci

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: an eventual error and an array of results​

[
  {
    ​"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 UPDATEit 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)
);

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 toSQL injectionwhich is 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

​ 

Request the project sources


Séparez les adresses email par une virgule.
Submit

Merci pour votre feedback

Notre équipe vous renverra un message dès que possible.
En attendant, nous vous invitons à visiter notre site web.

Sign in to leave a comment
Créer et tester ses rôles Ansible avec Docker et Molecule