Mastering SQLite for Production Web Applications

For a long time, developers were taught that SQLite was strictly for development, mobile apps, or low-traffic sites. If you were going to build a "real" web application, you *had* to deploy Postgres or MySQL.

This is a massive misconception. Modern SQLite, running on standard NVMe SSDs, is incredibly fast. Because it resides in the same process space as your Go or Node server, there is **zero network latency** per query.

Let's look at how to tune SQLite for production workloads!

### 1. Enabling WAL Mode (Write-Ahead Logging)

By default, SQLite uses Rollback Journal mode, which locks the entire database file during writes. This means concurrent reads are blocked.

To enable massive performance, run this command immediately after opening your database:

```sql
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
```

**Write-Ahead Logging (WAL)** splits reads and writes into separate logs. With WAL mode enabled, **multiple concurrent readers can query the database while a writer is modifying it**, dramatically increasing read-write concurrency!

### 2. Tuning Concurrency in Go

In Go, because it runs a highly concurrent scheduler, we need to carefully manage the database pool settings for SQLite. Set these limits inside your configuration:

```go
db, err := sql.Open("sqlite3", "cms.db")
if err != nil {
log.Fatal(err)
}

// WAL mode handles multiple readers, but ONLY 1 active writer is allowed.
// Enforce single connection for writers, or set a high busy_timeout.
db.SetMaxOpenConns(1)
```

Wait, if we set `MaxOpenConns(1)`, do we bottle-neck performance?
Actually, because database read operations in WAL mode take micro-seconds (since they bypass the network stack entirely), a single thread can easily handle **thousands of database transactions per second**!

### 3. Setting Busy Timeout

If multiple goroutines attempt to write concurrently, SQLite will immediately throw a `database is locked` error. To prevent this, configure a busy timeout:

```go
db, err := sql.Open("sqlite3", "file:cms.db?_busy_timeout=5000&_journal_mode=WAL")
```

Setting a `busy_timeout=5000` tells SQLite to automatically wait up to **5 seconds** for an active transaction to release its lock before returning an error. This simple tweak makes SQLite incredibly stable and smooth under high workloads.