sql.DB Is Not a Connection

Your Tasks API keeps everything in a map, which forgets everything the moment the process restarts. Over this book you'll replace that store with a real database — using nothing but the standard library, because database/sql is enough and understanding it makes every ORM you meet later legible.

Start with the thing everyone misreads:

db, err := sql.Open("sqlite", ":memory:")
if err != nil {
	return nil, err
}

sql.Open has not connected to anything. It validated the driver name, remembered the DSN, and returned a pool. No network traffic, no file opened, no error if the database doesn't exist. That is why err here is almost never the error you care about.

Ping is where the truth arrives

Because Open is lazy, the first sign that your credentials are wrong or the server is down is a query, possibly minutes later, possibly in a request handler. Ping forces the issue at startup, where you can still fail loudly:

if err := db.Ping(); err != nil {
	return nil, fmt.Errorf("connect: %w", err)
}

A program that opens a database and never pings it is a program that has decided to find out about misconfiguration from its users.

A *sql.DB is a pool, and you keep exactly one

Since it's a pool, it is safe for concurrent use and it is meant to be shared. Opening one per request is the classic mistake: you get connection churn, you blow past the server's connection limit, and the pool's whole purpose is defeated. Open it once at startup, hand it to whatever needs it, and close it when the program ends.

The pool also explains a rule that looks arbitrary until you know why.

The in-memory SQLite footgun

:memory: gives each connection its own private database. A pool that opens a second connection therefore lands in a second, empty database — so a table you created a moment ago is suddenly missing, intermittently, depending on which connection you were handed.

db.SetMaxOpenConns(1)

One connection means one database. Throughout this book, in-memory SQLite is always capped at one connection, and this is why. It is a perfect illustration of the general point: sql.DB is a pool, and forgetting that produces bugs that look like magic.

The driver import

import _ "modernc.org/sqlite"

The blank identifier is doing real work. You never call anything in that package — you import it so its init registers the driver name "sqlite" with database/sql. Drop the underscore and the compiler rejects an unused import; drop the import entirely and sql.Open fails at run time with "unknown driver".

Try it

Open an in-memory SQLite database, prove it's really there, and create the tasks table the rest of this book will use.