Deno SQLite3

To use Deno SQLite3, import the Database class from the JSR registry. You can initialize a new database by providing a filename. The library allows you to prepare statements and retrieve values using the .value<T>() method on the prepared statement.

import { Database } from "jsr:@db/sqlite@0.13";

const db = new Database("test.db");

const [version] = db.prepare("select sqlite_version()").value<[string]>()!;
console.log(version);

db.close();

The library uses Uint8Array for BLOB types to ensure V8 Fast API optimization and consistency. To work with other typed arrays (like Float32Array), you should convert them using the underlying buffer to avoid unnecessary copies.

const f32 = new Float32Array(1);
const u8 = new Uint8Array(f32.buffer); // no copy, can pass this

const u8FromSqlite = new Uint8Array(4);
const f32FromSqlite = new Float32Array(u8FromSqlite.buffer); // safely convert back when retrieved from sqlite, no copy

Because this library relies on the Deno FFI (Foreign Function Interface) API, you must grant specific permissions when running your script. You need --allow-env and --allow-ffi to support FFI and the automatic downloading/caching of the prebuilt native library. Additionally, network and file system permissions are required for the initial download and caching process.

For convenience, you can use the --allow-all or -A flag to grant all permissions.

deno run -A <file>

To open a database connection, instantiate the Database class with a file path or a special string for in-memory databases. If the file does not exist, it will be created by default unless create: false is specified in the options.

// Open using default options
const db = new Database("test.db");

// Open using URL path (relative to current file/module, not CWD)
const db = new Database(new URL("./test.db", import.meta.url));

// Open in memory
const db = new Database(":memory:");

// Open in read-only mode
const db = new Database("test.db", { readonly: true });

// Open existing database, error if it doesn't exist
const db = new Database("test.db", { create: false });

To verify the performance of this library, you can run the benchmarks located in the bench directory. The easiest way to execute them is via the provided Deno task.

deno task bench-deno

Prepared statements consume resources in the SQLite engine. To prevent memory leaks, you should explicitly release them.

• Manual: Call stmt.finalize() to free the statement object.
• Automatic (Disposable): The Statement class implements [Symbol.dispose], so you can use it within a using block (in supported TypeScript/Deno environments).
• Garbage Collection: The driver uses a FinalizationRegistry to attempt to finalize statements when they are garbage collected, but explicit finalization is preferred.

// Using explicit finalization
const stmt = db.prepare("SELECT 1");
stmt.finalize();

// Using Symbol.dispose (if supported)
{ 
  using stmt = db.prepare("SELECT 1");
  stmt.run();
}

Once a statement is prepared, use stmt.run(...params) to execute it. This returns the number of changes made by the statement.

const changes = stmt.run(...params);

Use db.exec(sql, ...params) to execute SQL.

• If no parameters are provided, it executes all statements in the string and returns the number of changes made by the last statement. This is ideal for DDL (CREATE, DROP, etc.) or PRAGMA statements.
• If parameters are provided, it uses the prepared statement API and can only execute one statement at a time.

Alternatively, use the .sql tagged template for safe, parameterized queries that return all rows via .all().

// Executing multiple statements (DDL/Pragma)
const changes = db.exec(
  "CREATE TABLE foo (bar TEXT); INSERT INTO foo VALUES ('baz');",
);

// Using tagged template for safe parameterized queries
const minimum = 20;
const results = db.sql`
  SELECT id, name, age FROM students WHERE age > ${minimum}`;