DEV Community

Cover image for 💾 Why I Chose SQLite for My Startup — The Most Underrated Database You're Probably Ignoring
Kinsly profile image Sergey Podgorny
Sergey Podgorny for Kinsly

Posted on

💾 Why I Chose SQLite for My Startup — The Most Underrated Database You're Probably Ignoring

#database #sqlite #backend #devops

For years, I ignored SQLite. I assumed it was only good for toy apps, quick experiments, or maybe some desktop utilities. Like many developers, I immediately jumped to Postgres or MySQL for any "serious" project. I even paid for a managed AWS RDS instance, believing I was preparing for scale.

But over time, I learned something that changed how I build small and medium-sized systems:

👉 For 90% of applications, SQLite is not only enough — it's often better.

My entire application runs on a single VPS, serving just a few requests per second. Most startups never reach the mythical "millions of requests per minute". For this scale, running a full database server is like renting a truck to deliver a pizza.

🧩 Why SQLite Is So Powerful

SQLite is a serverless, file-based, self-contained database engine. It's just a single file (e.g., data.db) that your app reads and writes to — no network connections, no daemons, no setup.

Yet it supports:

  • Full ACID transactions
  • Foreign keys, indexes, views, triggers
  • Concurrency (especially in WAL mode)
  • Gigabytes of data and millions of rows

And it's blazing fast when used correctly.

⚡ The Secret Sauce: WAL Mode

By default, SQLite stores changes using a rollback journal — it writes updates directly to the main database file and keeps a small backup journal during each transaction for safety.

WAL (Write-Ahead Logging) changes this strategy completely.

Instead of touching the main DB directly, all writes are appended to a separate .db-wal file. Readers keep reading from the main file while writers add to the WAL log. Later, the changes are merged automatically.

That means:

  • Readers and writers don't block each other.
  • Concurrent operations become much faster.
  • Crashes don't corrupt your main data file.

WAL mode can significantly boost performance in multi-threaded or multi-request apps, like Go web servers, where reads and writes happen at the same time.

But if your app is tiny and writes only occasionally, the default mode (DELETE) is perfectly fine. You can always switch later with:

PRAGMA journal_mode = WAL;
Enter fullscreen mode Exit fullscreen mode

Other journal modes include:

  • DELETE (default on many builds): makes a rollback file, then deletes it after writing. When you start a transaction, SQLite writes a rollback journal file (data.db-journal). After committing, it deletes that file. It's the safest and most widely compatible default. This is what you get if you don't set journal_mode at all.
  • TRUNCATE: same as DELETE, but instead of removing the journal file, it just truncates it to zero bytes and reuses it.
  • PERSIST: same idea, but keeps the journal file contents and just overwrites a header.
  • MEMORY: journal is only in RAM → faster, but data can be lost on crash.
  • OFF: no journaling at all → faster, but unsafe if the app or OS crashes, the database can be corrupted on crash.
  • WAL: the special one we just discussed.

⚙️ What Are PRAGMAs?

In SQLite, PRAGMAs are configuration switches that control database behavior — kind of like settings in a config file, but stored inside the DB engine itself.

You can enable them either by running SQL commands:

PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;
Enter fullscreen mode Exit fullscreen mode

Or pass them directly in the Go connection string:

dsn := "file:data.db?_pragma=busy_timeout(10000)&_pragma=foreign_keys(ON)"
Enter fullscreen mode Exit fullscreen mode

Here are a few useful ones:

  1. busy_timeout(10000) tells SQLite: if the database is locked, wait up to 10 seconds before giving up (instead of failing immediately with database is locked). Useful when multiple goroutines or processes may try to write at the same time. It is good for web apps; safer than retrying manually.

  2. journal_mode(WAL) enables Write-Ahead Logging instead of the default rollback journaling. It makes reads/writes more concurrent.

  3. journal_size_limit(200000000) sets a cap on the WAL file size (here: ~200 MB). Normally, the WAL file can grow until checkpointed (merged back into the main DB). This prevents it from growing forever. If your DB is small, it will never hit this size anyway.

  4. foreign_keys(ON) enables foreign key constraints (OFF by default in SQLite). If you define relations like user_id REFERENCES users(id), this ensures referential integrity. It is always good practice if you use relationships.

  5. temp_store(MEMORY) tells SQLite to keep temporary data (like for ORDER BY, GROUP BY, and indexes while querying) in RAM instead of on disk. Faster for queries, but uses more memory.

  6. cache_size(-16000) sets the page cache size. The negative number means "KB" instead of pages. Here: -16000 = about 16 MB of cache. This is how much SQLite will keep in memory for speeding up queries.

  7. synchronous(NORMAL) controls how careful SQLite is about flushing data to disk.

  • FULL (default): every write is forced to disk immediately → safest, but slowest.
  • NORMAL: doesn't flush on every step, but still durable enough for most apps (data is safe unless the OS/hardware crashes at a very unlucky moment).
  • OFF: fastest, but risk of corruption on crash.

ℹ️ NORMAL is a common compromise in web apps: faster inserts, still quite safe.

Not all are required — start simple and tune only when needed.

🧠 When You Should (and Shouldn't) Use SQLite

SQLite is perfect for:

  • Small to medium web apps
  • APIs on a single server
  • Prototypes, MVPs, side projects
  • Command-line or desktop tools
  • Local caching layers or analytics snapshots

But you'll hit limits if:

  • You need multiple servers writing to the same DB file
  • You expect thousands of writes per second
  • You require fine-grained access control
  • You need replication or clustering

That's when Postgres or MySQL makes sense. Until then, SQLite saves you time, money, and complexity.

🐹 Example: Using SQLite with Go

Let's set up a connection in Go using the excellent cgo-free driver modernc.org/sqlite:

$ go get modernc.org/sqlite
Enter fullscreen mode Exit fullscreen mode 
package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "modernc.org/sqlite"
)

func main() {
    // Open a database file (or ":memory:" for in-memory)
    dsn := "file:data.db?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)"
    db, err := sql.Open("sqlite", dsn)
    if err != nil {
        log.Fatalf("open: %v", err)
    }
    // Configure pool: keep a small pool for sqlite; tune for your workload
    db.SetMaxOpenConns(10)  // readers can have several
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(0)

    // Ping to verify connection
    ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil {
        log.Fatalf("ping: %v", err)
    }

    fmt.Println("DB open, WAL enabled and busy_timeout set (via DSN).")
}
Enter fullscreen mode Exit fullscreen mode

SQLite allows many concurrent readers but only one writer at a time. A common pattern:

  • Use one DB instance restricted to a single writer (SetMaxOpenConns(1)) for all writes.
  • Use a separate DB instance with a larger pool for readers. 
// writer
writerDSN := "file:test.db?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)"
writerDB, _ := sql.Open("sqlite", writerDSN)
writerDB.SetMaxOpenConns(1) // single writer connection
writerDB.SetMaxIdleConns(1)

// readers
readerDSN := "file:test.db?_pragma=busy_timeout(5000)"
readerDB, _ := sql.Open("sqlite", readerDSN)
readerDB.SetMaxOpenConns(20) // allow concurrent readers
readerDB.SetMaxIdleConns(10)
Enter fullscreen mode Exit fullscreen mode

This reduces SQLITE_BUSY when multiple goroutines try to write. Practical guides and community posts recommend separating writer/reader pools.

🧭 Navigating SQLite from the Terminal

SQLite also comes with a lightweight console client: sqlite3.

Open your database:

sqlite3 data.db
Enter fullscreen mode Exit fullscreen mode

Inside, these dot-commands make life easier:

.help             # list all available commands
.databases        # show loaded databases (usually just `main` pointing to your file)
.tables           # list all tables
.schema           # show schema of all tables
.schema table     # show schema of a specific table
.headers on       # turn on column headers in query output
.mode column      # format results in a nicely aligned table
.mode line        # show each row vertically, one field per line (great for wide tables)
.mode list         # results as plain text separated by | (default mode)
.width 20 30 15   # set column widths when using .mode column
.nullvalue NULL   # choose how NULLs are displayed
.quit or .exit    # leave the CLI
Enter fullscreen mode Exit fullscreen mode

Example session:

sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM users;
id  email             created_at
--  ----------------  ---------------------
1   test@example.com  2025-10-07 14:30:00
Enter fullscreen mode Exit fullscreen mode

Once you learn .headers on and .mode column, the CLI feels surprisingly pleasant.

If you prefer certain settings to be enabled by default, then it makes sense to customize the configuration file to suit your needs. If the initialization file ~/.sqliterc exists, sqlite3 will read it to set the configuration of the interactive environment. This file should generally only contain meta-commands.

.headers on
.mode column
.nullvalue NULL
Enter fullscreen mode Exit fullscreen mode

🧩 Conclusion

SQLite is like the pocketknife of databases — tiny, portable, and incredibly capable when used properly. For many apps, it's a smarter default than running a full database server.

For Kinsly's current stage, SQLite is the perfect choice:

  • It's blazing fast, even on cheap VPS hardware
  • It simplifies deployment
  • It requires zero configuration
  • It saves money (no RDS bills)
  • It's easy to move or back up (just copy the file)

When the time comes to scale, migration will be straightforward.
Until then, SQLite lets me focus on what matters: building the product, not managing infrastructure.

So before spinning up another managed Postgres instance, consider starting with SQLite. You might be surprised how far it takes you.

Top comments (0)