better-sqlite3-typescript for TypeScript: complete guide with examples

In the world of TypeScript development, there is often a need for local data storage without deploying a full-fledged database. SQLite is an ideal solution for such tasks: it is an embedded relational database that works without a separate server. However, the standard better-sqlite3 library is written in JavaScript and does not provide built-in typing. This is where better-sqlite3-typescript comes to the rescue — a wrapper that adds strict TypeScript typing on top of the synchronous better-sqlite3 API.

This library allows you to write type-safe code for working with SQLite, getting autocompletion in your IDE, type checking at compile time, and reducing the number of runtime errors. You get all the benefits of synchronous database access (simplicity, predictability, high performance) with the power of the TypeScript type system.

In this article, we will thoroughly cover installation, main methods, provide real examples, and give performance tips. The material will be useful for both beginners and experienced developers who want to effectively use SQLite in their TypeScript projects.

Installation

To install the library, run the following command in your project's terminal:

npm install better-sqlite3-typescript better-sqlite3

You will also need to install the types for TypeScript:

npm install -D @types/better-sqlite3

After installation, you can import the library into your project:

import Database from 'better-sqlite3-typescript';

Quick Start

A minimal working example of creating a database, creating a table, and inserting data:

import Database from 'better-sqlite3-typescript';\n\n// Create or open a database\nconst db = new Database('my_database.db');\n\n// Create a table\ndb.exec(`\n  CREATE TABLE IF NOT EXISTS users (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    name TEXT NOT NULL,\n    email TEXT UNIQUE NOT NULL,\n    age INTEGER\n  )\n`);\n\n// Insert data\nconst insert = db.prepare('INSERT INTO users (name, email, age) VALUES (?, ?, ?)');\ninsert.run('Alice', 'alice@example.com', 30);\ninsert.run('Bob', 'bob@example.com', 25);\n\n// Read data\nconst select = db.prepare('SELECT * FROM users');\nconst users = select.all();\nconsole.log(users);\n\n// Close the connection\ndb.close();

This code will create a my_database.db file in the current directory, create a users table, add two users, and print them to the console.

Main Methods and Functions

Let's look at the key methods of the library. All examples assume you already have a database instance db.

1. new Database(filename, options?)

Signature: new Database(filename: string, options?: DatabaseOptions): Database

Description: Creates or opens an SQLite database. If the file does not exist, it will be created. Options allow you to configure the opening mode (readonly, memory, etc.).

Example:

// Open an in-memory database (not saved to disk)\nconst memDb = new Database(':memory:');\n\n// Open a database in read-only mode\nconst readOnlyDb = new Database('data.db', { readonly: true });

2. db.prepare(sql)

Signature: prepare(sql: string): Statement

Description: Compiles an SQL query into a prepared statement. Returns a Statement object that can be executed multiple times with different parameters.