Bun SQLite
Bun has a built-in, high-performance SQLite driver via
bun:sqlite
.
Quick Start
import
{
Database
}
from
"bun:sqlite"
;
// Create/open database
const
db
=
new
Database
(
"mydb.sqlite"
)
;
// Create table
db
.
run
(
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE
)
)
;
// Insert data
db
.
run
(
"INSERT INTO users (name, email) VALUES (?, ?)"
,
[
"Alice"
,
"alice@example.com"
]
)
;
// Query data
const
users
=
db
.
query
(
"SELECT * FROM users"
)
.
all
(
)
;
console
.
log
(
users
)
;
// Close
db
.
close
(
)
;
Opening Databases
import
{
Database
}
from
"bun:sqlite"
;
// File-based database
const
db
=
new
Database
(
"data.sqlite"
)
;
// In-memory database
const
memDb
=
new
Database
(
":memory:"
)
;
// Read-only mode
const
readDb
=
new
Database
(
"data.sqlite"
,
{
readonly
:
true
}
)
;
// Create if not exists (default)
const
createDb
=
new
Database
(
"new.sqlite"
,
{
create
:
true
}
)
;
// Strict mode (recommended)
const
strictDb
=
new
Database
(
"strict.sqlite"
,
{
strict
:
true
}
)
;
Running Queries
Direct Execution
// Run (for INSERT, UPDATE, DELETE, DDL)
db
.
run
(
"CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)"
)
;
db
.
run
(
"INSERT INTO items (name) VALUES (?)"
,
[
"Item 1"
]
)
;
db
.
run
(
"DELETE FROM items WHERE id = ?"
,
[
1
]
)
;
// Get changes info
const
result
=
db
.
run
(
"DELETE FROM items WHERE id > ?"
,
[
10
]
)
;
console
.
log
(
result
.
changes
)
;
// Rows affected
console
.
log
(
result
.
lastInsertRowid
)
;
// Last inserted ID
Prepared Statements (Recommended)
// Create prepared statement
const
stmt
=
db
.
prepare
(
"SELECT * FROM users WHERE id = ?"
)
;
// Get single row
const
user
=
stmt
.
get
(
1
)
;
// Get all rows
const
allUsers
=
db
.
prepare
(
"SELECT * FROM users"
)
.
all
(
)
;
// Get values as array
const
values
=
db
.
prepare
(
"SELECT name, email FROM users"
)
.
values
(
)
;
// [[name1, email1], [name2, email2], ...]
// Iterate with for...of
const
iter
=
db
.
prepare
(
"SELECT * FROM users"
)
;
for
(
const
user
of
iter
.
iterate
(
)
)
{
console
.
log
(
user
)
;
}
Parameters
Positional Parameters
const
stmt
=
db
.
prepare
(
"INSERT INTO users (name, email) VALUES (?, ?)"
)
;
stmt
.
run
(
"Bob"
,
"bob@example.com"
)
;
// Or as array
stmt
.
run
(
[
"Charlie"
,
"charlie@example.com"
]
)
;
Named Parameters
const
stmt
=
db
.
prepare
(
"INSERT INTO users (name, email) VALUES ($name, $email)"
)
;
stmt
.
run
(
{
$name
:
"Dave"
,
$email
:
"dave@example.com"
}
)
;
// Also works with : and @
const
stmt2
=
db
.
prepare
(
"SELECT * FROM users WHERE name = :name"
)
;
stmt2
.
get
(
{
name
:
"Dave"
}
)
;
// Note: no colon in object key
Query Methods
const
stmt
=
db
.
prepare
(
"SELECT * FROM users WHERE active = ?"
)
;
// .get() - First row or null
const
first
=
stmt
.
get
(
true
)
;
// .all() - All rows as array
const
all
=
stmt
.
all
(
true
)
;
// .values() - Rows as arrays (not objects)
const
values
=
stmt
.
values
(
true
)
;
// [[1, "Alice", true], [2, "Bob", true]]
// .iterate() - Iterator for memory efficiency
for
(
const
row
of
stmt
.
iterate
(
true
)
)
{
processRow
(
row
)
;
}
// .run() - Execute without returning data
db
.
prepare
(
"DELETE FROM cache WHERE expires < ?"
)
.
run
(
Date
.
now
(
)
)
;
Transactions
// Simple transaction
const
insertMany
=
db
.
transaction
(
(
users
:
{
name
:
string
;
email
:
string
}
[
]
)
=>
{
const
insert
=
db
.
prepare
(
"INSERT INTO users (name, email) VALUES ($name, $email)"
)
;
for
(
const
user
of
users
)
{
insert
.
run
(
user
)
;
}
return
users
.
length
;
}
)
;
const
count
=
insertMany
(
[
{
name
:
"User1"
,
email
:
"user1@example.com"
}
,
{
name
:
"User2"
,
email
:
"user2@example.com"
}
,
]
)
;
// Transaction modes
const
tx
=
db
.
transaction
(
(
)
=>
{
db
.
run
(
'INSERT INTO users (name, email) VALUES (?, ?)'
,
[
'Alice'
,
'alice@example.com'
]
)
;
db
.
run
(
'UPDATE accounts SET balance = balance - 100 WHERE user_id = ?'
,
[
1
]
)
;
}
)
;
tx
.
deferred
(
)
;
// Default: defer lock until first write
tx
.
immediate
(
)
;
// Lock immediately on transaction start
tx
.
exclusive
(
)
;
// Exclusive lock, blocks all other connections
Batch Operations
// WAL mode for better concurrent performance
db
.
run
(
"PRAGMA journal_mode = WAL"
)
;
// Bulk insert with transaction
const
insertBulk
=
db
.
transaction
(
(
items
:
string
[
]
)
=>
{
const
stmt
=
db
.
prepare
(
"INSERT INTO items (name) VALUES (?)"
)
;
for
(
const
item
of
items
)
{
stmt
.
run
(
item
)
;
}
}
)
;
insertBulk
(
[
"A"
,
"B"
,
"C"
,
"D"
,
"E"
]
)
;
Column Types
// SQLite types map to JavaScript
/
SQLite JavaScript
------ ----------
INTEGER number | bigint
REAL number
TEXT string
BLOB Uint8Array
NULL null
/
// Handle BigInt for large integers
const
bigStmt
=
db
.
prepare
(
"SELECT COUNT(*) as count FROM users"
)
;
const
result
=
bigStmt
.
get
(
)
;
// result.count may be bigint if > Number.MAX_SAFE_INTEGER
// Store/retrieve Uint8Array
db
.
run
(
"INSERT INTO files (data) VALUES (?)"
,
[
new
Uint8Array
(
[
1
,
2
,
3
]
)
]
)
;
const
file
=
db
.
prepare
(
"SELECT data FROM files WHERE id = ?"
)
.
get
(
1
)
;
// file.data is Uint8Array
Column Definitions
// Get column info
const
stmt
=
db
.
prepare
(
"SELECT * FROM users"
)
;
const
columns
=
stmt
.
columnNames
;
// ["id", "name", "email"]
// Type annotations (Bun extension)
const
typedStmt
=
db
.
prepare
<
{
id
:
number
;
name
:
string
}
,
[
number
]
( "SELECT id, name FROM users WHERE id = ?" ) ; const user = typedStmt . get ( 1 ) ; // user is typed as { id: number; name: string } | null Error Handling import { Database , SQLiteError } from "bun:sqlite" ; try { db . run ( "INSERT INTO users (email) VALUES (?)" , [ "duplicate@example.com" ] ) ; } catch ( error ) { if ( error instanceof SQLiteError ) { console . error ( "SQLite error:" , error . code , error . message ) ; // error.code: "SQLITE_CONSTRAINT_UNIQUE" } throw error ; } Database Management // Close database db . close ( ) ; // Check if open console . log ( db . inTransaction ) ; // Is in transaction // Serialize to buffer const buffer = db . serialize ( ) ; await Bun . write ( "backup.sqlite" , buffer ) ; // Load from buffer const data = await Bun . file ( "backup.sqlite" ) . arrayBuffer ( ) ; const restored = Database . deserialize ( data ) ; // Filename console . log ( db . filename ) ; // Path or ":memory:" Common Patterns Repository Pattern import { Database } from "bun:sqlite" ; interface User { id : number ; name : string ; email : string ; } class UserRepository { private db : Database ; private stmts : { findById : ReturnType < Database [ "prepare" ]
; findAll : ReturnType < Database [ "prepare" ]
; create : ReturnType < Database [ "prepare" ]
; update : ReturnType < Database [ "prepare" ]
; delete : ReturnType < Database [ "prepare" ]
; } ; constructor ( db : Database ) { this . db = db ; this . stmts = { findById : db . prepare ( "SELECT * FROM users WHERE id = ?" ) , findAll : db . prepare ( "SELECT * FROM users" ) , create : db . prepare ( "INSERT INTO users (name, email) VALUES ($name, $email)" ) , update : db . prepare ( "UPDATE users SET name = $name, email = $email WHERE id = $id" ) , delete : db . prepare ( "DELETE FROM users WHERE id = ?" ) , } ; } findById ( id : number ) : User | null { return this . stmts . findById . get ( id ) as User | null ; } findAll ( ) : User [ ] { return this . stmts . findAll . all ( ) as User [ ] ; } create ( user : Omit < User , "id"
) : number { const result = this . stmts . create . run ( user ) ; return Number ( result . lastInsertRowid ) ; } } Common Errors Error Cause Fix SQLITE_CONSTRAINT Constraint violation Check UNIQUE/FK constraints SQLITE_BUSY Database locked Use WAL mode, add retry logic no such table Table doesn't exist Run CREATE TABLE first database is locked Concurrent access Enable WAL mode Performance Tips -- Enable WAL mode (better concurrency) PRAGMA journal_mode = WAL ; -- Faster writes (less durable) PRAGMA synchronous = NORMAL ; -- Increase cache size PRAGMA cache_size = 10000 ; -- Enable foreign keys PRAGMA foreign_keys = ON ; When to Load References Load references/pragmas.md when: Performance tuning Journal modes Memory configuration Load references/fts.md when: Full-text search FTS5 configuration