Skip to content

database

2 posts with the tag “database”

Why sqlc — not an ORM

The Go ecosystem has ORMs — GORM, ent, Pop. Laju Go uses none of them. It uses sqlc, a compiler that reads SQL and generates type-safe Go. The reasoning is simple: SQL is the right language for database work, and Go is the right language for application logic. An ORM forces you to write SQL in a worse language and then translates it back to SQL at runtime.

The input is a .sql file with named queries. Here’s the entire password-reset query set in Laju Go:

-- queries/password_reset.sql
-- name: CreatePasswordReset :exec
INSERT INTO password_resets (token, user_id, email, expires_at, created_at)
VALUES (?, ?, ?, ?, ?);
-- name: GetPasswordReset :one
SELECT * FROM password_resets WHERE token = ? AND used = 0 AND expires_at > ?;
-- name: MarkPasswordResetUsed :exec
UPDATE password_resets SET used = 1 WHERE token = ?;
-- name: DeleteExpiredPasswordResets :exec
DELETE FROM password_resets WHERE expires_at < CURRENT_TIMESTAMP;

npm run db:generate runs sqlc, which produces Go code in app/queries/ — typed query constants, parameter structs, and methods on a Queries struct. The generated code for CreateUser looks like this:

user.sql
// Code generated by sqlc. DO NOT EDIT.
const createUser = `-- name: CreateUser :one
INSERT INTO users (email, name, password, role, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?)
RETURNING id
`
type CreateUserParams struct {
Email string
Name string
Password sql.NullString
Role string
CreatedAt time.Time
UpdatedAt time.Time
}
func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (int64, error) {
row := q.db.QueryRowContext(ctx, createUser,
arg.Email, arg.Name, arg.Password, arg.Role, arg.CreatedAt, arg.UpdatedAt,
)
var id int64
err := row.Scan(&id)
return id, err
}

The service layer calls it like a normal function:

id, err := s.querier.CreateUser(ctx, queries.CreateUserParams{
Email: email,
Name: name,
Password: sql.NullString{String: hash, Valid: true},
Role: "user",
CreatedAt: time.Now(),
UpdatedAt: time.Now(),
})

No string building. No .Where("email = ?", email).First(&user). No struct tags that secretly control SQL generation. The SQL is the source of truth and the Go is derived from it.

If you rename a column in the schema and forget to update a query, sqlc fails at generation time — before the app compiles. If you pass the wrong type to a parameter, the Go compiler catches it. There is no interface{} reflection at runtime, no “query returned 3 columns but struct has 4” panic in production.

The app/queries/ directory is generated and committed. The rule in AGENTS.md is explicit: never edit app/queries/ manually. You edit the .sql file, run db:generate, and the Go updates. This keeps the SQL as the single source of truth and prevents the generated code from drifting.

GORM and ent use reflection and struct tags to build queries at runtime. That means allocations, interface boxing, and a non-trivial CPU cost per query. sqlc generates plain QueryRowContext / QueryContext calls with the SQL as a string constant. The runtime cost is the database driver and nothing else. For a high-throughput SaaS, that’s measurable.

This is the part that matters in 2026. LLMs are excellent at SQL and mediocre at ORM DSLs. When the data layer is plain SQL, an AI agent can write a new query — -- name: GetUserByEmail :one\nSELECT * FROM users WHERE email = ? — and sqlc generates the Go. The agent doesn’t need to know GORM’s chainable API, ent’s schema definition syntax, or which struct tag means “primary key.” It writes SQL, which is the thing it’s best at, and the compiler does the rest.

The three-tier rule in Laju Go enforces this: the Query layer is the only layer that executes SQL. Services call s.querier.*. Handlers never touch the database. This means an AI agent adding a feature writes SQL + a service method + a handler, and each layer has a clear contract. The ORM version of this task requires the agent to understand the ORM’s mental model, its migration system, and its query builder — all of which are worse than SQL for the database part.

sqlc doesn’t do migrations (Laju Go uses Goose for that). It doesn’t do relationships — there’s no .Preload("orders"). If you want eager loading, you write a JOIN in SQL, which is what you should do anyway. And it’s SQL-first, so if you don’t want to write SQL, it’s not for you. But if you’re building a SaaS and you’re willing to write SQL, sqlc gives you type safety, compile-time checking, zero runtime overhead, and an AI-friendly data layer — all at once.

Why SQLite — not Postgres

Postgres is the default database for a SaaS starter. It’s also the default reason a starter is hard to deploy: you need a server, a port, credentials, a connection pooler, and a backup strategy that isn’t pg_dump on a cron. Laju Go uses SQLite, and for a starter that targets a single-instance VPS, the tradeoff is almost always in SQLite’s favor.

SQLite is a library, not a server process. The database is a single file on disk. There is no postgres daemon to install, no port to open, no DATABASE_URL to configure, no connection pool to tune against a remote host. Laju Go opens it with one line:

db, err := sql.Open("sqlite3", cfg.DBPath)

The DSN is file:data.db?_foreign_keys=on — a path, not a network address. This means the database lives and dies with the app binary. Deploy is scp laju-go data.db server:. Backup is cp data.db data.db.bak. There is no separate failure mode for “the database is up but the app can’t reach it.”

The old knock on SQLite was write concurrency — a single writer lock. WAL (Write-Ahead Logging) mode fixes this. Readers and writers don’t block each other; writes append to a WAL file and checkpoint asynchronously. Laju Go enables it explicitly, along with a set of PRAGMAs tuned for a single-instance NVMe VPS:

func initDatabase(dbPath string) (*sql.DB, error) {
db, err := sql.Open("sqlite3", dbPath)
// ...
db.SetMaxOpenConns(15)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(30 * time.Second)
db.Exec("PRAGMA foreign_keys = ON")
db.Exec("PRAGMA journal_mode = WAL")
db.Exec("PRAGMA synchronous = NORMAL")
db.Exec("PRAGMA cache_size = -16000") // 16 MB cache
db.Exec("PRAGMA mmap_size = 268435456") // 256 MB memory-mapped I/O
db.Exec("PRAGMA temp_store = MEMORY")
db.Exec("PRAGMA busy_timeout = 5000") // wait 5s for locks
db.Exec("PRAGMA wal_autocheckpoint = 1000")
return db, nil
}

synchronous = NORMAL trades strict durability (FULL) for a ~2x write speedup while remaining safe against power loss in WAL mode. mmap_size = 256MB lets SQLite memory-map the file so reads bypass the page cache syscall path. busy_timeout = 5000 means writers wait for a lock instead of failing immediately.

On a modern NVMe drive with WAL mode, SQLite sustains tens of thousands of writes per second and far more reads — more than enough for a SaaS starter’s first 10,000 users. The bottleneck for a typical SaaS is not the database’s raw throughput; it’s the application’s query patterns and indexing. SQLite with the right PRAGMAs handles the load that a single-instance app can generate.

Backing up Postgres is a production concern: pg_dump, WAL archiving, PITR, a replica. Backing up SQLite is:

Terminal window
sqlite3 data.db ".backup data.db.bak"
# or, if the app is stopped:
cp data.db data.db.bak

The .backup command uses the online backup API — it works while the app is running and produces a consistent snapshot. For a starter, this is a one-line cron job. There is no replication slot to fill up, no WAL archive to fill the disk.

SQLite is single-instance. The moment you need horizontal scale — two app servers hitting the same database — you need Postgres. Laju Go’s architecture is built for that day: the query layer is generated by sqlc from plain SQL, so swapping SQLite for Postgres is a matter of changing the driver, the schema types, and regenerating. The three-tier rule (Handler → Service → Query) means no business logic touches the database driver directly.

But most SaaS starters never reach that day. Most never reach the point where a single VPS can’t handle the load. SQLite gets you to revenue without a database server, and the migration path is open when you need it.

SQLite is the wrong choice if you need multi-writer concurrency across nodes, if you need Postgres-specific features (JSONB operators, full-text search via tsvector, logical replication), or if you’re deploying on a network filesystem where file locks are unreliable. It is the right choice for a starter that wants to ship on one box, back up with cp, and not run a database server until the business pays for one.