When I was building the backend for my portfolio (which you can read about in Why I Chose Go + HTMX + SQLite Instead of React for My Portfolio), choosing the database was the easiest decision—until I had to configure it for production.
One of the most common pushbacks developers give when they see a non-distributed stack is: "Is SQLite only for local development?"
The short answer is no. SQLite is a robust, incredibly fast production database for small-to-medium web applications, content sites, and SaaS products. Here is how I set it up to run smoothly in a real-world Go environment.
1. The Go Ecosystem Advantage
Working with SQLite in Go is a dream largely due to the ecosystem's flexibility. Depending on your build constraints, you have stellar options:
- Traditional CGO-reliant drivers like
mattn/go-sqlite3. - Pure Go / CGO-free alternatives like
ncruces/go-sqlite3(which leverages WebAssembly). - Ecosystem options like
tursodatabase/libsqlif you plan to branch out into edge replication later.
The ultimate win here is operational simplicity: compiling everything into a single, static binary with zero external database dependencies. You don't need to install Postgres on your server, manage external connection daemons, or leak environment variables for remote database clusters. Your web app and its entire state live in one neat executable and one .db file.
2. Concurrency Handling: Conquering the Myths
The primary fear surrounding SQLite is that it "locks under heavy write load." While it’s true SQLite uses a database-level lock rather than row-level locking, configuring it properly for a web application completely neutralizes this bottleneck for typical workloads.
To get production-ready concurrency in Go, you need to handle two things:
- Connection Pooling (
SetMaxOpenConns(1)): Because SQLite handles concurrent reads exceptionally well, but serializes writes, restricting your Go application to a single open connection (db.SetMaxOpenConns(1)) prevents "database is locked" errors under standard web traffic. Go handles queuing incoming requests gracefully. - Write-Ahead Logging (WAL Mode): By default, SQLite uses a rollback journal. Enabling WAL mode allows multiple readers to query the database while a write operation is happening. You can turn this on via a simple pragma execution upon startup:
_, err := db.Exec("PRAGMA journal_mode=WAL;")
- Migrations & Schema Design Without a Heavy ORMMany developers assume that using a relational database means adopting a heavy, magical ORM like GORM or Ent. For my portfolio workspace, I preferred keeping things transparent with raw SQL and straightforward structs.Why raw SQL + structs shine: Writing explicit queries gives you total control over performance. You see the exact SQL executing, and scanning results into Go structs using database/sql is boilerplate-light.Migration management: Instead of massive migration frameworks, you can manage schema versions using lightweight tools like golang-migrate or write a minimal custom script that reads embedded .sql migration files on application startup.Data integrity check: Remember that foreign keys are disabled by default in SQLite for backward compatibility. Always ensure you explicitly enable them when opening your database connection:
_, err := db.Exec("PRAGMA foreign_keys = ON;")
Combine this with strict typing and CHECK constraints to keep your data model bulletproof.
- Designing the "Private Workspace" Data Layer Because my portfolio app doubles as an authenticated workspace/CRM system, the schema needed to securely segment public portfolio details from private user management, project notes, and backend utilities.
Schema breakdown: The database features a clean layout tracking users, sessions, projects, tags, and private notes/todos.
Indexing for performance: Even though a portfolio database is compact, adding proper indexes to foreign keys and timestamp columns ensures query performance remains blazing fast (sub-millisecond).
Security at the DB layer: Keeping private data safe comes down to strict parameterization in your queries and compartmentalizing database service layers so that public endpoints physically cannot invoke queries hitting private tables.
- Why I Ditched Cloud Databases for a Single File on a $4 VPS Opting for SQLite changes your infrastructure economics entirely:
Zero operational overhead: No secret managers for remote connection strings, no provisioning serverless scaling tiers, and no terrifying, unexpected cloud computing bills.
Portability: The entire state of your web application lives in a single .db file. Moving servers, replicating local development parity, and running automated tests become trivial because you can just copy or snapshot the file instantly.
Backups and disaster recovery: Backing up a file-based database is remarkably straightforward. You can write a tiny cron script to safely hot-backup the database using SQLite's online backup API, or drop in a utility like Litestream to stream incremental changes to object storage like S3 in real time.
When not to use it: To stay realistic: if you are architecting a massive multi-region SaaS application with millions of concurrent global users writing data simultaneously to the same tables, use a distributed SQL database like Postgres. But for a portfolio, a tool suite, a content platform, or an internal dashboard? SQLite is absolute king.
Summary Moving away from complex database orchestration let me focus entirely on building software that solves my actual problems. If you're building a side project or portfolio, don't let cloud-native hype force you into over-engineered stacks. Give SQLite a chance—you might be surprised at how far a single file can take you.