Back to Blog

SQLite for Local Development: Fast, Simple, and Effective

October 16, 2025•By Timothy Murphy
SQLite for Local Development: Fast, Simple, and Effective

SQLite is the unsung hero of local development. While it might not be your production database of choice, it's a powerful tool that can dramatically speed up your development workflow. Let's explore why SQLite deserves a place in every developer's toolkit and how to use it effectively.

Why SQLite for Local Development?

SQLite offers unique advantages that make it perfect for development environments:

  • Zero configuration: No server setup required
  • Single file database: Easy to backup, share, and version
  • Fast: Lightweight and quick for development workloads
  • Cross-platform: Works on Windows, Mac, and Linux
  • ACID compliant: Real database, not a toy
  • Testing friendly: Create and destroy databases instantly

Getting Started with SQLite

Installation

SQLite comes pre-installed on macOS and most Linux distributions. For Windows, download from sqlite.org.

# Check if SQLite is installed
sqlite3 --version

# Create a new database
sqlite3 myapp.db

Basic Operations

-- Create a table
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (name, email) VALUES 
  ('Alice', '[email protected]'),
  ('Bob', '[email protected]');

-- Query data
SELECT * FROM users;

-- Exit SQLite shell
.quit

SQLite vs Production Databases

When SQLite is Perfect

✅ Local development ✅ Testing and CI/CD ✅ Prototyping and demos ✅ Small to medium applications ✅ Embedded applications ✅ Mobile apps

When to Use Alternatives

❌ High concurrency writes ❌ Multiple servers ❌ Very large datasets (>1TB) ❌ Network access required ❌ Complex permissions

Setting Up SQLite in Different Frameworks

Node.js with better-sqlite3

const Database = require('better-sqlite3');
const db = new Database('myapp.db');

// Create table
db.exec(`
  CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// Insert data
const insert = db.prepare('INSERT INTO posts (title, content) VALUES (?, ?)');
insert.run('My First Post', 'Hello World!');

// Query data
const posts = db.prepare('SELECT * FROM posts').all();
console.log(posts);

Python with sqlite3

import sqlite3

# Connect to database
conn = sqlite3.connect('myapp.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL,
    stock INTEGER DEFAULT 0
  )
''')

# Insert data
cursor.execute(
  'INSERT INTO products (name, price, stock) VALUES (?, ?, ?)',
  ('Laptop', 999.99, 10)
)

# Query data
cursor.execute('SELECT * FROM products')
products = cursor.fetchall()

# Commit and close
conn.commit()
conn.close()

Ruby on Rails

# config/database.yml
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

Laravel (PHP)

// .env
DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite

// Or use relative path in config/database.php
'sqlite' => [
    'driver' => 'sqlite',
    'database' => database_path('database.sqlite'),
    'prefix' => '',
    'foreign_key_constraints' => true,
],

Advanced SQLite Features

Foreign Keys

Enable foreign key constraints for referential integrity.

-- Enable foreign keys (off by default)
PRAGMA foreign_keys = ON;

-- Create tables with relationships
CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER,
  FOREIGN KEY (author_id) REFERENCES authors(id)
    ON DELETE CASCADE
);

Indexes for Performance

-- Create index on frequently queried column
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_posts_author_date 
  ON posts(author_id, created_at DESC);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Check indexes
.indexes users

Full-Text Search

-- Create FTS5 table
CREATE VIRTUAL TABLE articles_fts USING fts5(title, content);

-- Insert data
INSERT INTO articles_fts (title, content) VALUES
  ('SQLite Guide', 'Complete guide to SQLite database'),
  ('Database Tips', 'Performance optimization techniques');

-- Search
SELECT * FROM articles_fts 
WHERE articles_fts MATCH 'database performance';

JSON Support

SQLite has excellent JSON support since version 3.38.

-- Store JSON data
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  name TEXT,
  metadata JSON
);

INSERT INTO events (name, metadata) VALUES
  ('user_login', json('{"ip": "192.168.1.1", "device": "mobile"}'));

-- Query JSON
SELECT 
  name,
  json_extract(metadata, '$.device') as device
FROM events
WHERE json_extract(metadata, '$.device') = 'mobile';

Testing with SQLite

In-Memory Database for Tests

// Jest test example
const Database = require('better-sqlite3');

describe('User Model', () => {
  let db;
  
  beforeEach(() => {
    // Create in-memory database
    db = new Database(':memory:');
    
    // Set up schema
    db.exec(`
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE
      )
    `);
  });
  
  test('should create user', () => {
    const insert = db.prepare(
      'INSERT INTO users (name, email) VALUES (?, ?)'
    );
    
    const result = insert.run('Alice', '[email protected]');
    expect(result.changes).toBe(1);
  });
  
  afterEach(() => {
    db.close();
  });
});

Parallel Test Isolation

import sqlite3
import pytest

@pytest.fixture
def db():
    # Each test gets its own database
    conn = sqlite3.connect(':memory:')
    conn.execute('''
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            username TEXT UNIQUE
        )
    ''')
    yield conn
    conn.close()

def test_user_creation(db):
    db.execute('INSERT INTO users (username) VALUES (?)', ('testuser',))
    db.commit()
    
    result = db.execute('SELECT * FROM users').fetchone()
    assert result[1] == 'testuser'

SQLite CLI Tips

Useful Dot Commands

# Enter SQLite shell
sqlite3 myapp.db

# Show all tables
.tables

# Show schema for a table
.schema users

# Change output format
.mode column
.headers on

# Export to CSV
.mode csv
.output users.csv
SELECT * FROM users;
.output stdout

# Import from CSV
.mode csv
.import data.csv users

# Show query execution plan
.eqp on
SELECT * FROM users WHERE email = '[email protected]';

# Backup database
.backup backup.db

# Restore from backup
.restore backup.db

Performance Analysis

-- Analyze query performance
EXPLAIN QUERY PLAN
SELECT * FROM posts WHERE author_id = 1;

-- Gather statistics for query optimizer
ANALYZE;

-- Check database integrity
PRAGMA integrity_check;

-- View database stats
SELECT * FROM sqlite_master;

Migrations with SQLite

Handling Schema Changes

SQLite doesn't support all ALTER TABLE operations, so use this pattern:

-- Start transaction
BEGIN TRANSACTION;

-- Create new table with updated schema
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  phone TEXT,  -- New column
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Copy data from old table
INSERT INTO users_new (id, name, email, created_at)
SELECT id, name, email, created_at FROM users;

-- Drop old table
DROP TABLE users;

-- Rename new table
ALTER TABLE users_new RENAME TO users;

-- Commit transaction
COMMIT;

Development Workflow Best Practices

Keep Database in Version Control

# Add to .gitignore
*.sqlite
*.sqlite-journal
*.sqlite-wal

# But version control the schema
git add db/schema.sql

Seed Data for Development

-- seed.sql
INSERT INTO users (name, email, role) VALUES
  ('Admin User', '[email protected]', 'admin'),
  ('Test User', '[email protected]', 'user');

INSERT INTO products (name, price, stock) VALUES
  ('Product 1', 19.99, 100),
  ('Product 2', 29.99, 50);

# Load seed data
sqlite3 dev.db < seed.sql

Quick Database Reset

#!/bin/bash
# reset-db.sh

# Remove old database
rm -f dev.db

# Create new database with schema
sqlite3 dev.db < schema.sql

# Load seed data
sqlite3 dev.db < seed.sql

echo "Database reset complete!"

Performance Optimization

Connection Settings

-- Recommended pragmas for development
PRAGMA journal_mode = WAL;  -- Write-Ahead Logging
PRAGMA synchronous = NORMAL;  -- Balance safety and speed
PRAGMA cache_size = -64000;  -- 64MB cache
PRAGMA temp_store = MEMORY;  -- Keep temp tables in memory
PRAGMA mmap_size = 30000000000;  -- Memory-mapped I/O

Batch Operations

// Good: Use transactions for bulk inserts
const insertMany = db.transaction((users) => {
  const insert = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');
  for (const user of users) {
    insert.run(user.name, user.email);
  }
});

// This is much faster than individual inserts
insertMany([
  { name: 'Alice', email: '[email protected]' },
  { name: 'Bob', email: '[email protected]' },
  // ... thousands more
]);

Common SQLite Gotchas

1. Type Affinity

SQLite uses dynamic typing, which can be surprising:

-- SQLite accepts this
CREATE TABLE test (id INTEGER);
INSERT INTO test VALUES ('hello');  -- Works!

-- Be explicit with constraints
CREATE TABLE test (
  id INTEGER NOT NULL CHECK(typeof(id) = 'integer')
);

2. Concurrent Writes

SQLite locks the entire database for writes:

// This will cause "database is locked" errors
// Bad: Multiple simultaneous writes
Promise.all([
  db.prepare('INSERT INTO users ...').run(),
  db.prepare('INSERT INTO users ...').run(),
  db.prepare('INSERT INTO users ...').run()
]);

// Good: Use transactions or sequential writes

3. Case Sensitivity

-- Column names are case-insensitive
SELECT Name FROM users;  -- Works
SELECT name FROM users;  -- Also works

-- But string comparisons are case-sensitive by default
SELECT * FROM users WHERE name = 'alice';  -- Won't match 'Alice'

-- Use COLLATE NOCASE for case-insensitive
SELECT * FROM users WHERE name COLLATE NOCASE = 'alice';

When to Switch from SQLite

Consider migrating when you hit these limits:

  • Database size exceeds 100GB
  • Concurrent write operations exceed 100/second
  • Multiple application servers need database access
  • Complex user permission requirements
  • Network latency to database is critical

Conclusion

SQLite is an excellent choice for local development because it:

  • Eliminates server setup and configuration
  • Provides fast iteration cycles
  • Works perfectly for testing
  • Keeps everything in a single portable file
  • Supports most SQL features you need

Don't underestimate SQLite - it powers billions of devices and can handle more than you might think. Use it for development, embrace its simplicity, and migrate to PostgreSQL/MySQL only when you truly need the extra features.

Manage SQLite with Ease

Download LunoDB for a beautiful interface to browse, query, and manage your SQLite databases. Perfect for local development with syntax highlighting and AI-powered query building.

Related Posts

The Ultimate Guide to Database Backups and Disaster Recovery
February 16, 2026

The Ultimate Guide to Database Backups and Disaster Recovery

Protect your data from disasters with a solid backup strategy. Learn backup types, best practices, and how to create a disaster recovery plan that actually works.

Full-Text Search in SQL Databases: MySQL and PostgreSQL Compared
February 9, 2026

Full-Text Search in SQL Databases: MySQL and PostgreSQL Compared

Build powerful search features directly in your database. Learn how to implement full-text search in MySQL and PostgreSQL, with practical examples and performance tips.

Working with Time-Series Data: Patterns and Best Practices
February 2, 2026

Working with Time-Series Data: Patterns and Best Practices

Master time-series data in your database. Learn how to store, query, and analyze timestamped data efficiently for metrics, logs, IoT sensors, and financial records.