Bun + SQLite Setup

🍞 Introduction to Bun + SQLite

Combining Bun — the ultra-fast JavaScript runtime — with SQLite — a lightweight and file-based SQL database — gives you a powerful backend stack that's fast, portable, and efficient. Whether you're building a local API, CLI tool, or lightweight server, this combo is ideal for quick data persistence without spinning up an external DB server.

⚙️ Why Bun + SQLite?

• 🚀 Blazing speed: Bun executes JavaScript faster than Node and supports native bindings.
• 📁 No server required: SQLite is just a file. No need to install or run a separate service.
• 📦 Zero-config storage: Drop in a DB file and start reading/writing immediately.
• 🧠 Perfect for small projects, prototyping, or embedded apps.

📦 Installing Required Packages

Let’s start by initializing your Bun project and adding SQLite support:

bun init
bun add better-sqlite3

We’re using better-sqlite3, a blazing fast, synchronous SQLite client that works beautifully with Bun.

📂 Setting Up the Database File

Let’s create a file called db.ts to handle our SQLite connection.

// db.ts
import Database from 'better-sqlite3';

const db = new Database('app.db'); // this creates 'app.db' if it doesn't exist

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

export default db;

That’s it — your database and user table are ready to go!

📝 Inserting and Querying Data

Now let's create a simple file to insert a user and fetch all users:

// user-service.ts
import db from './db.ts';

const insert = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');
insert.run('Aditya', 'aditya@example.com');

const getAll = db.prepare('SELECT * FROM users');
const users = getAll.all();

console.log("👥 All Users:", users);

This example demonstrates synchronous inserts and selects, perfect for command-line tools, small APIs, or scripting tasks.

🔄 Updating and Deleting Records

// Update user
const update = db.prepare('UPDATE users SET name = ? WHERE id = ?');
update.run('Adi', 1);

// Delete user
const del = db.prepare('DELETE FROM users WHERE id = ?');
del.run(1);

Each of these operations runs instantly and safely thanks to SQLite's atomic design.

📁 Directory Structure Overview

my-bun-sqlite-app/
├── db.ts
├── user-service.ts
├── app.db           # SQLite DB file (auto-created)
├── bun.lockb
└── package.json

💡 Pro Tips for SQLite with Bun

• 🧪 Use transactions: Wrap batch operations in db.transaction() for performance and atomicity.
• 🔐 Handle constraints: Use IF NOT EXISTS and ON CONFLICT clauses wisely.
• 🚦 Validation: Validate input manually or with a schema library (e.g., zod) before inserting.

📡 Creating a Simple Bun Server with SQLite

Let’s expose our SQLite queries via a simple HTTP server using Bun’s built-in APIs:

// index.ts
import db from './db.ts';

const handler = () => {
  const stmt = db.prepare('SELECT * FROM users');
  const users = stmt.all();
  return new Response(JSON.stringify(users), {
    headers: { 'Content-Type': 'application/json' }
  });
};

Bun.serve({
  port: 3000,
  fetch: handler,
});

console.log("🚀 Server running at http://localhost:3000");

🧵 Is This Production Ready?

SQLite is a great choice for local dev, small-scale apps, or internal tools. For larger-scale apps with concurrent writes, you might want to explore external DBs. But for MVPs and quick bootstrapped backends, Bun + SQLite is a dream combo.

🧾 Final Thoughts

Setting up Bun + SQLite is simple, fast, and powerful. Whether you’re building microservices, automation scripts, or prototypes, this pairing gives you database-backed logic in minutes without any extra infrastructure.

So next time you want a backend without the bloat, just Bun it and let SQLite handle the rest. ⚡📁