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());
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
Get started with SQLite database in a TypeScript project