Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 33 ยท Using SQLite from Code

SQLite from Python, JavaScript and Other Languages

Use SQLite in your programs: Python's sqlite3 module, Node's node:sqlite, better-sqlite3, Go, Java, C and more, with parameter binding and transactions.

So far you have talked to SQLite in SQL, one statement at a time. Real applications talk to it from code: a program opens a database, runs statements, reads rows back into variables and closes the connection. The good news is that almost every language has a SQLite driver and they all work the same way. Once you know the five-step pattern, switching languages takes minutes.

This lesson shows the pattern with working Python (running live in your browser, because Python ships with SQLite built in) and Node.js, then maps it to Go, Java, C and other languages. The most important habit it teaches is parameter binding: sending values to SQLite separately from the SQL text, which keeps your queries correct and safe.

The five-step pattern

Connect Open a file or :memory:.
Prepare Give SQL with placeholders.
Bind Pass values separately.
Step Run it and read the rows.

Then commit if you changed data, and close.

1 / 2

Python: the sqlite3 module

Python includes sqlite3 in its standard library, so nothing needs installing. The example below creates an in-memory database, inserts rows with ? placeholders, and reads them back with a parameterized query. It runs in your browser, in a real Python interpreter, with a real SQLite behind it.

Python Python sqlite3 basics

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

You should see Atlas of Birds: 24.5, Learning SQL: 31.0 and rows: 2. Things to notice:

  • ? marks where a value goes, and the values travel in a separate tuple (note the trailing comma in (min_price,): a one-element tuple). The SQL text is never built by gluing strings together.
  • connect(":memory:") makes a throwaway database. For a file, pass a path.
  • execute() runs one statement; executemany() runs one statement for each tuple in a list, which is also the fast way to insert many rows.
  • By default the module opens a transaction before an INSERT, UPDATE or DELETE and leaves it open until you call con.commit() (or rollback()). Forgetting commit() is the classic "my data vanished" bug.

Named parameters, row access and transactions

Instead of positional ?, you can use :name and pass a dictionary, which reads better with many values. Setting row_factory = sqlite3.Row lets you read columns by name. And using the connection as a context manager (with con:) commits when the block succeeds and rolls back if it raises:

Python Named parameters, rows and rollback

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

After the first transfer you see ada 70 and alan 50. The second transfer raises IntegrityError (the CHECK fails for alan), the message starts with CHECK constraint failed, and the rollback leaves the balances at [('ada', 70), ('alan', 50)]. cursor.rowcount reports the number of rows changed, and cursor.lastrowid the id of the last inserted row.

Other Python facts worth knowing: a connection has a timeout (default 5 seconds) that acts as the busy timeout, connections are tied to the thread that made them unless you pass check_same_thread=False, and Python 3.12 added an autocommit setting for people who want the driver to leave transactions entirely to explicit BEGIN and COMMIT. For asyncio, the aiosqlite package wraps the same API.

Node.js: node:sqlite

Modern Node.js (22.5 and later) has a built-in node:sqlite module with a synchronous API: DatabaseSync for the connection and prepare() for statements. It may print a one-line experimental warning on some versions, which is harmless. The example uses the Node.js runner, so it runs on the site's server instead of in your browser.

Node.js node:sqlite basics

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The output is last id: 3 changes: 1, then Atlas of Birds: 24.5 and Learning SQL: 31, then rolled back: changed my mind and count: 3. The pattern is: prepare once, run many times. A prepared statement is parsed a single time and reused, which is both faster and the natural place to bind values. run() is for statements that change data, get() returns the first row, and all() returns every row as an array of objects. Named parameters (:min) are passed in an object. Transactions are plain BEGIN, COMMIT and ROLLBACK statements, as in the SQL lessons.

better-sqlite3 and other Node drivers

Before node:sqlite, the standard choice was the npm package better-sqlite3. It is also synchronous (which is usually fine for SQLite, as calls take microseconds) and adds some conveniences. This is read-only, because npm packages are not available on the site:

JavaScript
import Database from 'better-sqlite3';

const db = new Database('app.db');
db.pragma('journal_mode = WAL');

const addBook = db.prepare('INSERT INTO books (title, price) VALUES (@title, @price)');
const addMany = db.transaction((books) => {      // wraps the function in BEGIN ... COMMIT
  for (const book of books) addBook.run(book);   // any thrown error rolls the whole batch back
});

addMany([{ title: 'Atlas', price: 24.5 }, { title: 'Habits', price: 12 }]);
console.log(db.prepare('SELECT * FROM books WHERE price > ?').all(20));

The db.transaction() helper is a nice design: you write the loop, the library supplies the BEGIN/COMMIT/ROLLBACK. Other options include sqlite3 (older, callback-based), libsql and the Bun and Deno built-in SQLite modules. Whichever you use, the SQL is identical.

The same pattern in other languages

Language Common driver Notes
Go database/sql with modernc.org/sqlite (pure Go) or mattn/go-sqlite3 (needs cgo) db.Query("... WHERE price > ?", 20); set db.SetMaxOpenConns(1) for simple write-heavy apps
Java org.xerial:sqlite-jdbc standard JDBC: PreparedStatement ps = conn.prepareStatement("... WHERE price > ?") then ps.setDouble(1, 20)
C / C++ the SQLite C library itself sqlite3_open, sqlite3_prepare_v2, sqlite3_bind_*, sqlite3_step, sqlite3_column_*, sqlite3_finalize
PHP PDO with the sqlite driver, or SQLite3 $stmt = $pdo->prepare('... WHERE price > ?'); $stmt->execute([20]);
C# Microsoft.Data.Sqlite command.Parameters.AddWithValue("$min", 20) with $min in the SQL
Rust rusqlite `conn.prepare(...)?.query_map([20],
Ruby the sqlite3 gem db.execute("... WHERE price > ?", [20])

Every one of them wraps the same C functions. Even the ones with big frameworks on top (Django, Rails, Laravel, Entity Framework) eventually call prepare, bind and step. Learn the C names once and the other APIs make sense: prepare compiles SQL into a statement, bind attaches values to placeholders, step produces one row at a time, and finalize (or close) frees it.

Parameter binding in detail

A placeholder stands in for a value in the SQL text. SQLite supports ? (positional), ?NNN (numbered), and named forms :name, @name and $name. Different drivers prefer different forms, so check the docs, but the principle never changes: the SQL is compiled first, and the values are attached afterwards as plain data. Because of that, a bound value can never be mistaken for SQL, which is what prevents injection (Parameters and SQL Injection takes this further).

Some things placeholders cannot do:

  • Identifiers. You cannot bind a table or column name (SELECT * FROM ? fails). If a name must vary, choose it from a fixed list in your own code and quote it, never from user input.
  • A list for IN. WHERE id IN (?) with one value for a list does not expand. Generate one placeholder per element (IN (?, ?, ?)) and pass the list.
  • Parts of a string. LIKE '%?%' does not work, because the placeholder is inside a string literal. Bind the whole pattern instead: LIKE ? with '%' + word + '%'.

The SQL below mimics a parameter by joining against a one-row VALUES table. It is not what a driver does, but it shows the shape: one fixed query, the changing parts supplied as data. This is runnable SQL, so you can edit the "parameters" and watch the same text give different answers:

SQL Fixed SQL, changing values ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The query lists up to five books whose price lies between the two parameter values. Change (10, 20) to (30, 50) and the same SQL returns different rows. A real driver does the same at a lower level: it keeps the compiled statement and swaps the bound values between runs.

Habits that work in every language

  1. Always bind values; never build SQL with string formatting or concatenation.
  2. Reuse prepared statements in loops; do not re-prepare for every row.
  3. Wrap batches in one transaction. A thousand inserts in one transaction can be hundreds of times faster than a thousand auto-committed ones.
  4. Run your per-connection PRAGMAs (foreign_keys, busy_timeout, and so on) right after every connect.
  5. Use one connection per thread, and keep write transactions short.
  6. Close what you open: statements, cursors and connections, ideally with with, try/finally, defer or using.
  7. Check how your driver handles transactions. Some begin one implicitly (Python's default), some are in autocommit until you say BEGIN.
  8. Store dates and booleans in the forms you learned (Dates, Times and Booleans); drivers will not necessarily convert them for you.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why use con.execute("SELECT * FROM books WHERE price > ?", (20,)) instead of building the query with an f-string?

    Binding keeps data and SQL apart. That prevents injection and also handles quoting and types for you.
  2. 2. In Python's sqlite3 module with default settings, what do you need to do after an INSERT to make sure it is saved?

    The module opens a transaction before data-changing statements and keeps it open until you commit or roll back. A with con block commits on success.
  3. 3. Which of these can NOT be replaced by a bound parameter?

    Placeholders stand for values only. Table and column names must be written in the SQL text, chosen from a trusted list.
  4. 4. What does a prepared statement let you do?

    Preparing parses and plans the SQL once; each run only binds new values and steps through the rows.
  5. 5. In node:sqlite, which method returns every matching row as an array?

    run() executes a changing statement, get() returns the first row, all() returns all rows, and exec() runs SQL text without bound values.

Summary

  • Every language follows the same pattern: connect, prepare, bind, step through rows, commit if needed, close.
  • Python's built-in sqlite3 uses ? or :name placeholders, needs commit() (or with con:) after writes, and offers row_factory = sqlite3.Row; Node's node:sqlite has prepare() with run(), get() and all().
  • Go, Java, C, PHP, C#, Rust and Ruby all wrap the same prepare, bind and step calls of the C library.
  • Bind values, never paste them into SQL; placeholders cannot stand for identifiers, whole IN lists or the inside of a string literal.
  • Reuse prepared statements, batch writes in one transaction, set per-connection PRAGMAs after connecting, and use one connection per thread.

Next: Parameters and SQL Injection looks at exactly how string-built queries go wrong and how binding stops it.