The sqlite npm package is a wrapper for SQLite, a C library that provides a lightweight, disk-based database. It doesn't require a separate server process and allows access to the database using a nonstandard variant of the SQL query language. The sqlite npm package allows you to interact with SQLite databases in a Node.js environment.
Create a Database
This code demonstrates how to create an in-memory SQLite database, create a table, insert data, and query the data.
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');
db.serialize(() => {
db.run('CREATE TABLE lorem (info TEXT)');
const stmt = db.prepare('INSERT INTO lorem VALUES (?)');
for (let i = 0; i < 10; i++) {
stmt.run('Ipsum ' + i);
}
stmt.finalize();
db.each('SELECT rowid AS id, info FROM lorem', (err, row) => {
console.log(row.id + ': ' + row.info);
});
});
db.close();Open an Existing Database
This code demonstrates how to open an existing SQLite database file, query data from a table, and print the results.
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('example.db');
db.serialize(() => {
db.each('SELECT rowid AS id, info FROM lorem', (err, row) => {
console.log(row.id + ': ' + row.info);
});
});
db.close();Parameterized Queries
This code demonstrates how to use parameterized queries to prevent SQL injection attacks. It shows how to insert and query data using placeholders.
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');
db.serialize(() => {
db.run('CREATE TABLE lorem (info TEXT)');
const stmt = db.prepare('INSERT INTO lorem VALUES (?)');
stmt.run('Ipsum 1');
stmt.finalize();
db.get('SELECT info FROM lorem WHERE info = ?', ['Ipsum 1'], (err, row) => {
console.log(row.info);
});
});
db.close();better-sqlite3 is a wrapper for SQLite3 that is faster and simpler to use than the sqlite package. It provides a more synchronous API, which can be easier to work with in many cases. Unlike sqlite, better-sqlite3 does not require a callback for each query, making the code more readable and easier to maintain.
Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication, and more. While it supports SQLite, it is more complex and feature-rich compared to the sqlite package, making it suitable for larger applications with more complex database interactions.
Knex.js is a SQL query builder for PostgreSQL, MySQL, MariaDB, SQLite3, and Oracle. It is designed to be flexible and powerful, allowing you to build complex queries with ease. Knex.js can be used as a query builder in conjunction with an ORM or as a standalone query builder. It provides a more abstracted way to interact with databases compared to the sqlite package.
A wrapper library that adds ES6 promises and SQL-based migrations API to sqlite3 (docs).
🔥 Want to strengthen your core JavaScript skills and master ES6?
I would personally recommend this awesome ES6 course by Wes Bos.
3/22/2020: Version 4 of sqlite is now in beta.
Installation instructions and changelog here.
$ npm install sqlite --save
NOTE: For Node.js v5 and below use var db = require('sqlite/legacy');.
This module has the same API as the original sqlite3 library (docs),
except that all its API methods return ES6 Promises and do not accept callback arguments.
Below is an example of how to use it with Node.js, Express and Babel:
import express from 'express';
import Promise from 'bluebird';
import sqlite from 'sqlite';
const app = express();
const port = process.env.PORT || 3000;
const dbPromise = sqlite.open('./database.sqlite', { Promise });
app.get('/post/:id', async (req, res, next) => {
try {
const db = await dbPromise;
const [post, categories] = await Promise.all([
db.get('SELECT * FROM Post WHERE id = ?', req.params.id),
db.all('SELECT * FROM Category')
]);
res.render('post', { post, categories });
} catch (err) {
next(err);
}
});
app.listen(port);
This module is compatible with sql-template-strings.
import SQL from 'sql-template-strings';
import sqlite from 'sqlite';
const db = await sqlite.open('./database.sqlite');
const book = 'harry potter';
const author = 'J. K. Rowling';
const data = await db.all(SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`);
If you want to enable the database object cache
sqlite.open('./database.sqlite', { cached: true })
This module comes with a lightweight migrations API that works with SQL-based migration files as the following example demonstrates:
migrations/001-initial-schema.sql-- Up
CREATE TABLE Category (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE Post (id INTEGER PRIMARY KEY, categoryId INTEGER, title TEXT,
CONSTRAINT Post_fk_categoryId FOREIGN KEY (categoryId)
REFERENCES Category (id) ON UPDATE CASCADE ON DELETE CASCADE);
INSERT INTO Category (id, name) VALUES (1, 'Business');
INSERT INTO Category (id, name) VALUES (2, 'Technology');
-- Down
DROP TABLE Category
DROP TABLE Post;
migrations/002-missing-index.sql-- Up
CREATE INDEX Post_ix_categoryId ON Post (categoryId);
-- Down
DROP INDEX Post_ix_categoryId;
app.js (Node.js/Express)import express from 'express';
import Promise from 'bluebird';
import sqlite from 'sqlite';
const app = express();
const port = process.env.PORT || 3000;
const dbPromise = Promise.resolve()
.then(() => sqlite.open('./database.sqlite', { Promise }))
.then(db => db.migrate({ force: 'last' }));
app.use(/* app routes */);
app.listen(port);
NOTE: For the development environment, while working on the database schema, you may want to set
force: 'last' (default false) that will force the migration API to rollback and re-apply the
latest migration over again each time when Node.js app launches.
The open method resolves to the db instance which can be used in order to reference multiple open databases.
import sqlite from 'sqlite';
Promise.all([
sqlite.open('./main.sqlite', { Promise }),
sqlite.open('./users.sqlite', { Promise })
]).then(function([mainDb, usersDb]){
...
});
import sqlite from 'sqlite';
async function main() {
const [mainDb, usersDb] = await Promise.all([
sqlite.open('./main.sqlite', { Promise }),
sqlite.open('./users.sqlite', { Promise })
]);
...
}
main();
const sqlite = require('sqlite');
const SQL = require('sql-template-strings');
async function main() {
const db = await sqlite.open("./my-db.sqlite");
const firstName = 'Tamika';
const lastName = 'Washington';
// First value is auto-incrementing primary key
const statement = await db.run(SQL`insert into People values (NULL, ${firstName}, ${lastName})`);
// Get access to the primary key of the inserted row:
console.log(statement.lastID);
await sqlite.close(db);
}
main();
The MIT License © 2015-present Kriasoft. All rights reserved.
Made with ♥ by Konstantin Tarkus (@koistya), Theo Gravity and contributors
FAQs
SQLite client for Node.js applications with SQL-based migrations API written in Typescript
We found that sqlite demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 3 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Research
A malicious npm package disguised as a WhatsApp client is exploiting authentication flows with a remote kill switch to exfiltrate data and destroy files.
Security News
PyPI now supports digital attestations, enhancing security and trust by allowing package maintainers to verify the authenticity of Python packages.
Security News
GitHub removed 27 malicious pull requests attempting to inject harmful code across multiple open source repositories, in another round of low-effort attacks.