Quick Guide: Bun + SQLite Setup

Need a fast, dependency-free database for your Bun project? Setting up Bun SQLite takes just minutes and gives you a powerful local database without external dependencies or complex configurations. Here’s everything you need to get started with bun:sqlite and integrate it into your projects.

Getting Started with Bun SQLite

The built-in bun:sqlite module provides a synchronous, high-performance SQLite driver that’s perfect for local development, prototypes, and small production applications. No npm packages required—it’s already part of Bun.

Basic Database Connection

import { Database } from "bun:sqlite"

// Create or open a database file
const db = new Database("app.db")

// Or use an in-memory database for testing
const memDb = new Database(":memory:")

That’s it—you now have a working Bun SQLite database connection. The database file is created automatically if it doesn’t exist.

Running Your First Queries with bun:sqlite

Creating Tables and Basic Operations

// Create a table
db.run(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  )
`)

// Insert data using parameters (prevents SQL injection)
const insertUser = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)")
insertUser.run("Alice", "alice@example.com")

// Query data
const getUser = db.prepare("SELECT * FROM users WHERE name = ?")
const user = getUser.get("Alice")
console.log(user) // { id: 1, name: "Alice", email: "alice@example.com" }

// Get all results
const allUsers = db.prepare("SELECT * FROM users").all()

The Bun SQLite API uses prepared statements by default, making your queries both safe and fast. The .get() method returns a single row, while .all() returns an array of all matching rows.

Working with Transactions

For operations that need to succeed or fail together, use transactions:

const insertMany = db.transaction((users) => {
  const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)")
  for (const user of users) {
    stmt.run(user.name, user.email)
  }
})

// This either inserts all users or none
insertMany([
  { name: "Bob", email: "bob@example.com" },
  { name: "Charlie", email: "charlie@example.com" }
])

Advanced Bun SQLite Features

Using Named Parameters

Named parameters make complex queries more readable:

const query = db.prepare(`
  SELECT * FROM users 
  WHERE name = $name AND email = $email
`)

const result = query.get({
  $name: "Alice",
  $email: "alice@example.com"
})

Handling Different Return Types

Bun SQLite offers multiple methods for retrieving data:

const stmt = db.prepare("SELECT * FROM users")

// Get first row as object
const firstUser = stmt.get()

// Get all rows as objects
const allUsers = stmt.all()

// Get values as arrays (useful for data processing)
const values = stmt.values()
// Returns: [[1, "Alice", "alice@example.com"], [2, "Bob", "bob@example.com"]]

// Execute without returning results (for INSERT/UPDATE/DELETE)
stmt.run()

Integrating with Drizzle ORM

For larger projects, combining bun:sqlite with Drizzle ORM provides type safety and better developer experience:

// Install Drizzle
// bun add drizzle-orm drizzle-kit

import { drizzle } from 'drizzle-orm/bun-sqlite'
import { Database } from 'bun:sqlite'

const sqlite = new Database('app.db')
const db = drizzle(sqlite)

// Now use Drizzle's type-safe query builder
const users = await db.select().from(usersTable)

Best Practices and Performance Tips

Always Close Connections

While Bun SQLite automatically closes databases during garbage collection, explicitly closing them is good practice:

// When done with the database
db.close()

Use Prepared Statements

Prepared statements in Bun SQLite are cached and reused, offering better performance for repeated queries:

// Good: Prepare once, use many times
const stmt = db.prepare("SELECT * FROM users WHERE id = ?")
for (const id of userIds) {
  const user = stmt.get(id)
  // Process user
}

// Less efficient: Creating new statement each time
for (const id of userIds) {
  const user = db.prepare("SELECT * FROM users WHERE id = ?").get(id)
}

Choose the Right Storage Mode

• Use file-based databases (Database("app.db")) for persistent data
• Use in-memory databases (Database(":memory:")) for tests or temporary data
• Use read-only mode (Database("data.db", { readonly: true })) for static datasets

Conclusion

Bun SQLite provides everything you need for local database operations without external dependencies. Whether you’re building a CLI tool, API prototype, or small production service, the built-in bun:sqlite module offers the perfect balance of simplicity and performance. Start with the synchronous API for quick scripts, then add Drizzle ORM when you need type safety and migrations. With just a few lines of code, you have a fully functional SQL database ready to go.

FAQs

Gain Debugging Superpowers

Unleash the power of session replay to reproduce bugs, track slowdowns and uncover frustrations in your app. Get complete visibility into your frontend with OpenReplay — the most advanced open-source session replay tool for developers. Check our GitHub repo and join the thousands of developers in our community.