Connecting to SQLite from Applications: Python, Node, Go, Java

A Connection Is Just an Open File

SQLite has no server. There's no daemon listening on a port, no host to dial, no credentials to negotiate. "Connecting" means your driver opens a file on disk and starts reading and writing pages of it. That's the whole mental model.

Every language has a driver that wraps the SQLite C library. The shapes differ, but the moving parts are the same: a path to the database file, an open call, a handle you run statements on, and a close call when you're done.

-- Conceptually, every driver does this:
-- 1. Open or create the file at the given path.
-- 2. Acquire a handle.
-- 3. Run SQL via prepared statements.
-- 4. Close the handle.

The rest of this page is what that looks like in real code, and the few settings worth setting before your first query.

Python: sqlite3 in the Standard Library

Python ships with sqlite3 — no install needed. The basic shape:

-- Python
import sqlite3

conn = sqlite3.connect("app.db")
conn.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
conn.execute("INSERT INTO notes (body) VALUES (?)", ("first note",))
conn.commit()

for row in conn.execute("SELECT id, body FROM notes"):
    print(row)

conn.close()

A few things worth knowing:

• sqlite3.connect("app.db") creates the file if it doesn't exist. Pass ":memory:" for a database that lives only in RAM.
• sqlite3.connect("file:app.db?mode=ro", uri=True) opens read-only via the URI form.
• The ? in the SQL is a placeholder — use parameter binding, never string concatenation. The next chapter goes deeper on that.
• conn.commit() is required unless you use a context manager (with conn:) which auto-commits.

For a long-running app, set a busy timeout so concurrent writers wait instead of erroring:

-- Python
conn.execute("PRAGMA busy_timeout = 5000")   -- wait up to 5s
conn.execute("PRAGMA journal_mode = WAL")    -- better concurrency

Node.js: better-sqlite3

The Node ecosystem has a few options, but better-sqlite3 is the one most teams reach for. It's synchronous (which sounds wrong for Node but is actually faster for SQLite, since the queries return in microseconds).

-- Node.js
const Database = require("better-sqlite3");
const db = new Database("app.db");

db.exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");

const insert = db.prepare("INSERT INTO notes (body) VALUES (?)");
insert.run("first note");

const rows = db.prepare("SELECT id, body FROM notes").all();
console.log(rows);

db.close();

db.prepare(...) returns a reusable statement object. .run() is for writes, .all() returns all rows, .get() returns one. Same pattern as most SQL drivers.

Set pragmas on startup:

-- Node.js
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("foreign_keys = ON");   -- off by default, almost always wanted

foreign_keys = ON is worth calling out: SQLite doesn't enforce foreign keys unless you ask it to, per connection. If you forget, your REFERENCES clauses are decorative.

Go: database/sql with a Driver

Go's standard database/sql package is driver-agnostic. For SQLite, modernc.org/sqlite (pure Go, no CGO) and github.com/mattn/go-sqlite3 (CGO) are the common choices.

-- Go
import (
    "database/sql"
    _ "modernc.org/sqlite"
)

db, err := sql.Open("sqlite", "app.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")
if err != nil { panic(err) }
defer db.Close()

_, err = db.Exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
_, err = db.Exec("INSERT INTO notes (body) VALUES (?)", "first note")

rows, _ := db.Query("SELECT id, body FROM notes")
defer rows.Close()
for rows.Next() {
    var id int; var body string
    rows.Scan(&id, &body)
    fmt.Println(id, body)
}

The query string after the file name is how this driver passes pragmas at connection time — the format varies by driver, so check the docs for whichever one you pick.

sql.Open doesn't actually open a connection; the first query does. db is a connection pool. For SQLite, a small pool (or even db.SetMaxOpenConns(1) for write-heavy workloads) is usually right.

Java: JDBC

The org.xerial:sqlite-jdbc driver is the standard. JDBC URLs look like jdbc:sqlite:<path>:

-- Java
import java.sql.*;

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db")) {
    try (Statement st = conn.createStatement()) {
        st.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
        st.execute("PRAGMA journal_mode = WAL");
        st.execute("PRAGMA busy_timeout = 5000");
    }

    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO notes (body) VALUES (?)")) {
        ps.setString(1, "first note");
        ps.executeUpdate();
    }

    try (PreparedStatement ps = conn.prepareStatement("SELECT id, body FROM notes");
         ResultSet rs = ps.executeQuery()) {
        while (rs.next()) System.out.println(rs.getInt(1) + " " + rs.getString(2));
    }
}

In-memory: jdbc:sqlite::memory:. Read-only: append ?open_mode=1 or use a SQLiteConfig object.

PHP: PDO

PDO's SQLite DSN is sqlite:<path>:

-- PHP
$db = new PDO("sqlite:app.db");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
$db->exec("PRAGMA journal_mode = WAL");
$db->exec("PRAGMA busy_timeout = 5000");

$stmt = $db->prepare("INSERT INTO notes (body) VALUES (?)");
$stmt->execute(["first note"]);

foreach ($db->query("SELECT id, body FROM notes") as $row) {
    echo $row["id"] . " " . $row["body"] . "\n";
}

sqlite::memory: for an in-memory database. Always set ATTR_ERRMODE to exceptions — silent failures are rough to debug.

Connection Strings and File Paths

Across drivers, you'll see two flavors of "connection string":

• Plain path: app.db, ./data/app.db, /var/lib/myapp/app.db. Relative paths are relative to the process's working directory — which is rarely what you want in production. Prefer absolute paths.
• URI form: file:app.db?mode=rwc&cache=shared. Lets you set flags like mode=ro (read-only), mode=rwc (read-write-create, the default), cache=shared, and nolock=1.

Special values you'll meet:

• :memory: — a private in-memory database. Each connection gets its own.
• file::memory:?cache=shared — an in-memory database that multiple connections in the same process can share.
• "" (empty string) — a private, temporary on-disk database that's deleted when closed.

JDBC prefixes the URI with jdbc:sqlite:. PDO uses sqlite:. Go drivers and Python's sqlite3 accept the path or URI directly.

What About Connection Pools?

SQLite is a single-writer database. At any moment, exactly one connection holds the write lock; everyone else waits. Pooling many writers doesn't make writes faster — it just gives you more contenders for the same lock.

That said, a small pool is useful for:

• Concurrent reads in WAL mode, where readers don't block each other or the writer.
• Avoiding head-of-line blocking where one slow query stalls the whole app.

Reasonable defaults for a web app:

• WAL mode on.
• busy_timeout of a few seconds so contention waits politely instead of erroring.
• A pool size of 1 writer + N readers, or just one shared connection if traffic is light.
• Foreign keys turned on, on every connection.

-- Apply these on every new connection:
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;   -- safe with WAL; faster than FULL

synchronous = NORMAL is the typical WAL pairing — durable across app crashes, slightly looser across OS crashes, noticeably faster than the default FULL.

Closing Connections (and Why It Matters)

Every driver has a close call: conn.close(), db.Close(), db.close(). Not closing leaks file descriptors and can leave the WAL file growing.

In long-running services, the more common pattern is one connection (or pool) for the lifetime of the process, not open-close around each request. Opening a SQLite connection is cheap, but reapplying pragmas every time is wasteful and easy to forget.

-- Python — connection per process, reused across requests
DB = sqlite3.connect("app.db", check_same_thread=False)
DB.execute("PRAGMA journal_mode = WAL")
DB.execute("PRAGMA busy_timeout = 5000")
DB.execute("PRAGMA foreign_keys = ON")

For Python specifically, check_same_thread=False is needed if you'll use the connection from multiple threads — and you'll want a lock or a pool to serialize calls.

A Checklist Before Shipping

Before pointing real traffic at a SQLite database:

• Use an absolute path for the database file.
• Enable WAL mode (PRAGMA journal_mode = WAL).
• Set a busy_timeout of 2-10 seconds.
• Turn foreign keys on, every connection.
• Use prepared statements with parameter binding — never string interpolation.
• Make sure the directory containing the database is writable by the process (SQLite writes a -wal and -shm file alongside the main file in WAL mode).
• Think about backups before you need them — VACUUM INTO and the .backup command are covered later.

Next: Migrations

Connecting is the easy part. The harder part is evolving your schema over time without hand-editing production databases. Migrations are how you turn ALTER TABLE into a repeatable, version-controlled process — that's the next page.