SqliteDatabase

Inherits from: Object

A native SQLite database connection backed by modernc.org/sqlite (pure Go).

SqliteDatabase provides persistent storage with SQL queries, transactions, prepared statements, migrations, and WAL mode support.

Example

"Open an in-memory database, create a table, insert and query"
| db <SqliteDatabase> row <Dictionary> |
db := SqliteDatabase openMemory.
db execute: 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)'.
db execute: 'INSERT INTO users (name, age) VALUES (?, ?)' with: #('Alice' 30).
row := db queryRow: 'SELECT * FROM users WHERE name = ?' with: #('Alice').
row at: 'name'

Example

"File-based database with WAL mode"
| db <SqliteDatabase> |
db := SqliteDatabase open: '/tmp/app.db'.
db enableWAL.
db execute: 'CREATE TABLE IF NOT EXISTS config (key TEXT PRIMARY KEY, val TEXT)'.
db close

Class Methods

primitives

class primOpen:

class primOpenMemory

uncategorized

class open:

Open a SQLite database at the given file path. Creates the file if it does not exist.

Example

| db <SqliteDatabase> |
db := SqliteDatabase open: '/tmp/myapp.db'.
db close

class openMemory

Open an in-memory SQLite database. Data is lost when the database is closed.

Example

| db <SqliteDatabase> |
db := SqliteDatabase openMemory.
db execute: 'SELECT sqlite_version()'

Instance Methods

primitives

primBeginTransaction

primClose

primCommitTransaction

primEnableWAL

primExecute:

primExecuteWith:params:

primIsClosed

primLastInsertId

primMigrate:version:

primMigrationVersion

primPath

primPragma:

primPragmaSet:value:

primPrepare:

primQuery:

primQueryAll:

primQueryAllWith:params:

primQueryRow:

primQueryRowWith:params:

primQueryWith:params:

primRollbackTransaction

primTableExists:

primTables

primVersion

uncategorized

beginTransaction

Begin a new transaction. Returns the database for chaining.

Example

db beginTransaction.
db execute: 'INSERT INTO t (val) VALUES (''inside-tx'')'.
db commitTransaction

Close the database connection.

commitTransaction

Commit the current transaction.

enableWAL

Enable WAL (Write-Ahead Logging) journal mode for better concurrent read performance.

Example

| db <SqliteDatabase> |
db := SqliteDatabase open: '/tmp/app.db'.
db enableWAL

execute:

Execute SQL that returns no rows (CREATE, INSERT, UPDATE, DELETE). Returns the number of rows affected.

Example

| db <SqliteDatabase> |
db := SqliteDatabase openMemory.
db execute: 'CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)'.
db execute: 'INSERT INTO t (val) VALUES (''hello'')'

execute:with:

Execute SQL with positional parameters. Parameters are passed as an Array and bound to ? placeholders in order.

Example

| db <SqliteDatabase> |
db := SqliteDatabase openMemory.
db execute: 'CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)'.
db execute: 'INSERT INTO t (name, age) VALUES (?, ?)' with: #('Alice' 30)

isClosed

Check if the database connection is closed.

lastInsertId

Get the last inserted row ID.

migrate:version:

Run a migration SQL if the given version has not been applied. Returns true if the migration was applied, false if already applied.

Example

db migrate: 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)' version: 1.
db migrate: 'ALTER TABLE users ADD COLUMN age INTEGER' version: 2

migrationVersion

Get the highest applied migration version number. Returns 0 if no migrations have been applied.

path

Get the file path of this database, or ':memory:' for in-memory databases.

pragma:

Get a PRAGMA value by name.

Example

db pragma: 'journal_mode'

pragma:set:

Set a PRAGMA value.

Example

db pragma: 'cache_size' set: '-2000'

prepare:

Prepare a SQL statement for repeated execution. Returns a SqliteStatement.

Example

| stmt <SqliteStatement> |
stmt := db prepare: 'INSERT INTO users (name, age) VALUES (?, ?)'.
stmt executeWith: #('Alice' 30).
stmt executeWith: #('Bob' 25).
stmt close

printString

query:

Execute a SQL query and return a SqliteRows cursor for iteration.

Example

| db <SqliteDatabase> rows <SqliteRows> |
db := SqliteDatabase openMemory.
db execute: 'CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT)'.
db execute: 'INSERT INTO t (val) VALUES (''a'')'.
rows := db query: 'SELECT * FROM t'.
[rows next] whileTrue: [rows asDict].
rows close

query:with:

Execute a SQL query with parameters, returning a SqliteRows cursor.

Example

| rows <SqliteRows> |
rows := db query: 'SELECT * FROM users WHERE age > ?' with: #(25).
[rows next] whileTrue: [rows asDict].
rows close

queryAll:

Execute a query and return all rows as an Array of Dictionaries.

Example

db queryAll: 'SELECT * FROM users ORDER BY name'

queryAll:with:

Execute a query with parameters and return all rows as an Array of Dictionaries.

Example

db queryAll: 'SELECT * FROM users WHERE age > ?' with: #(25)

queryRow:

Execute a query and return the first row as a Dictionary, or nil if no rows match.

Example

| db <SqliteDatabase> |
db := SqliteDatabase openMemory.
db execute: 'CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)'.
db execute: 'INSERT INTO t (name) VALUES (''Alice'')'.
db queryRow: 'SELECT * FROM t WHERE id = 1'

queryRow:with:

Execute a query with parameters and return the first row as a Dictionary, or nil if no rows match.

Example

db queryRow: 'SELECT * FROM users WHERE name = ?' with: #('Alice')

rollbackTransaction

Rollback the current transaction.

tableExists:

Check if a table exists by name. Returns true or false.

Example

db tableExists: 'users'

tables

List all table names as an Array of Strings.

Example

db tables

version

Get the SQLite version string.

withTransaction:

Execute a block inside a transaction. If the block raises an exception, the transaction is rolled back automatically. Otherwise it is committed.

Example

db withTransaction: [
    db execute: 'INSERT INTO users (name) VALUES (?)' with: #('Alice').
    db execute: 'INSERT INTO users (name) VALUES (?)' with: #('Bob')
]