Open a database

This guide shows you how to open a SQLite database from a page, run queries with bound parameters, and group changes in a transaction.
Create a data directory beside your served site folder. Keep application data outside public assets.
notes.cow
<?js
import { sqlite } from 'cow:sqlite'
const db = await sqlite(__dirname + '/../data/site.sqlite')

db.exec('CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT)')
const notes = db.all('SELECT id, title FROM notes ORDER BY id DESC')
?>
<ul>
  <?js for (const note of notes) { ?>
    <li><?= h(note.title) ?></li>
  <?js } ?>
</ul>
The list is empty until you add a note in the next section.

Pass values as parameters

Parameterized queries
db.run('INSERT INTO notes (title) VALUES (?)', ['A little idea'])
const note = db.get('SELECT * FROM notes WHERE id = ?', [1])
Output — /notes, after the first insert, with blank lines removed
<ul>
    <li>A little idea</li>
</ul>
Warning: Use parameters for values instead of inserting user text into SQL strings. Protect write actions with the authorization and form checks that your application needs.

Group changes in a transaction

A transaction
await db.transaction(database => {
  database.run('INSERT INTO notes (title) VALUES (?)', ['First idea'])
  database.run('INSERT INTO notes (title) VALUES (?)', ['Another idea'])
})
The callback can be asynchronous. Failed transactions roll back. Request cleanup also releases managed transaction state. Database records are not reset between requests.

Change the schema with migrations

To change the schema after your application has data, list the changes as steps and call db.migrate(). Each step runs once, in order, and SQLite's PRAGMA user_version records how many have run.
_db.cow
<?js
import { sqlite } from 'cow:sqlite'

const db = await sqlite(new URL('../data/notes.sqlite', import.meta.url))

await db.migrate([
  'CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)',
  'ALTER TABLE notes ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0'
])

export default db
A step is a SQL string or a function that receives the database. Add new steps to the end of the list. Pending steps run in one transaction, so a failed step rolls back the whole batch. When nothing is pending, migrate() only reads the version, so a helper can call it on every request.
Warning: Never edit, reorder, or remove a step that has run. Cow counts steps, so a changed step does not run again on an existing database.

Everyday methods

  • all(sql, parameters) returns rows.
  • get(sql, parameters) returns a row or undefined.
  • run(sql, parameters) returns change and inserted-ID information, for example { lastInsertRowid: 1, changes: 1 }.
  • exec(sql) executes SQL without bound parameters.
SQLite is the bundled database adapter. Other database engines need a compatible adapter.