fn pub static new[T: ToString](path: ref T) -> Result[Database, Error] {
  open(path, flags: SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)
}

fn pub static new[T: ToString](path: ref T) -> Result[Database, Error]

Opens a new database connection to the database located at path.

If path is set to :memory: then an in-memory database is opened.

The database is opened with read and write permissions, and is created if it doesn't exist.

Defaults

The SQLite defaults are a little outdated and can cause problems for concurrent applications. To improve things, this method automatically applies a set of reasonable defaults based on the following articles:

• https://kerkour.com/sqlite-for-servers
• https://mort.coffee/home/sqlite-editions/
• https://fractaledmind.com/2023/09/07/enhancing-rails-sqlite-fine-tuning/

In particular, the following settings are applied:

• WAL mode is enabeld
• The busy timeout is set to 5 seconds
• Synchronous mode is set to "NORMAL"
• The cache size is set to 8 MiB instead of the default 2 MiB
• Foreign key enforcement is enabled
• Temporary tables are stored in memory

Errors

Opening a database may fail for any number of reasons, such as insufficient file permissions. In addition, it's possible for a Error.Busy error to be produced when opening a database as mentioned in this article.

Examples

Opening an in-memory database:

import sqlite (Database)

Database.new(':memory:').or_panic

Opening a database stored on disk:

import sqlite (Database)

Database.new('example.db').or_panic

fn pub static read_only[T: ToString](path: ref T) -> Result[Database, Error] {
  open(path, flags: SQLITE_OPEN_READONLY)
}

fn pub static read_only[T: ToString](path: ref T) -> Result[Database, Error]

Opens a new read-only database connection to the database located at path.

This method expects that the database already exists and produces an error if this isn't the case.

For more details, refer to the documentation of Database.new.

Examples

Opening an in-memory database in read-only mode (which isn't terribly useful, but technically possible):

import sqlite (Database)

Database.read_only(':memory:').or_panic

Opening a database stored on disk in read-only mode (this requires that the database already exists):

import sqlite (Database)

Database.read_only('example.db').or_panic

fn pub blob(
  database: String,
  table: String,
  column: String,
  row: Int,
  write: Bool,
) -> Result[Blob, Error] {
  let rw = write.to_int as Int32
  let handle = null
  let db = database.pointer
  let tbl = table.pointer
  let col = column.pointer
  let res = sqlite3_blob_open(@handle, db, tbl, col, row, rw, mut handle)

  match res as Int {
    case SQLITE_OK -> {}
    case v -> throw Error.new(v)
  }

  Result.Ok(Blob.new(handle))
}

fn pub blob(database: String, table: String, column: String, row: Int, write: Bool) -> Result[Blob, Error]

Returns a Blob for the given table, column and row ID.

The database argument is the symbolic name of the database. Usually this is just "main", though the use of the MAIN_DB constant for this is preferred in case this ever changes.

The table argument is the name of the table storing the blob, while column is the name of the BLOB column.

The row argument is the rowid of the row. You can use Database.last_inserted_row_id to acquire such an ID after an INSERT.

The write argument specifies if the blob should be opened in read-only mode (false) or in read-write mode (true).

Errors

If the blob can't be opened, such as when the table or column doesn't exist, an Error.Generic is returned. For more details, refer to the SQLite documentation.

Examples

import sqlite (Database, MAIN_DB)

let db = Database.new(':memory:').or_panic
let _ = db.execute('CREATE TABLE files (name TEXT, data BLOB)').or_panic
let st = db.prepare('INSERT INTO files VALUES (:name, :data)').or_panic
let val = 'This is an example'

st.bind(':name', 'README.md').or_panic
st.bind(':data', val.to_byte_array).or_panic

let _ = st.execute.or_panic
let id = db.last_inserted_row_id

db
  .blob(MAIN_DB, table: 'files', column: 'data', row: id, write: false)
  .or_panic

fn pub changes -> Int {
  sqlite3_changes64(@handle) as Int
}

fn pub changes -> Int

Returns the number of rows modified by the most recently executed statement.

Examples

import sqlite (Database)

let db = Database.new(':memory:').or_panic

db.execute('CREATE TABLE users (name TEXT)')     # => Result.Ok(0)
db.execute('INSERT INTO users VALUES ("Alice")') # => Result.Ok(1)

db.changes # => 1

fn pub mut execute[B: Bytes](query: ref B) -> Result[Int, Error] {
  let st = try prepare(query)

  st.execute
}

fn pub mut execute[B: Bytes](query: ref B) -> Result[Int, Error]

Executes the statement and returns the number of modified (e.g. inserted) rows.

Refer to the documentation of Database.prepare and Statement.execute for more details.

Examples

import sqlite (Database)

let db = Database.new(':memory:').or_panic

db.execute('CREATE TABLE users (name TEXT)')     # => Result.Ok(0)
db.execute('INSERT INTO users VALUES ("Alice")') # => Result.Ok(1)

fn pub last_inserted_row_id -> Int {
  sqlite3_last_insert_rowid(@handle)
}

fn pub last_inserted_row_id -> Int

Returns the "rowid" value of the last inserted row, or zero if no row is inserted yet.

Examples

import sqlite (Database)

let db = Database.new(':memory:').or_panic

db.execute('CREATE TABLE users (name TEXT)')     # => Result.Ok(0)
db.execute('INSERT INTO users VALUES ("Alice")') # => Result.Ok(1)

db.last_inserted_row_id # => 1

fn pub mut prepare[B: Bytes](query: ref B) -> Result[Statement, Error] {
  let len = query.size

  if len > I32_MAX { throw Error.TooLarge }

  let handle = null
  let len = len as Int32
  let res = sqlite3_prepare_v2(@handle, query.pointer, len, mut handle, null)

  match res as Int {
    case SQLITE_OK -> Result.Ok(Statement(db: self, handle: handle))
    case v -> throw Error.new(v)
  }
}

fn pub mut prepare[B: Bytes](query: ref B) -> Result[Statement, Error]

Prepares a new SQL statement using the provided SQL string.

You must not include user-provided input in the query argument as this exposes you to SQL injection attacks. Instead, prepare a query using placeholders/parameters and bind values to those parameters using Statement.bind.

Errors

If the query value is greater than 2³²-1, an Error.TooLarge error is returned. In addition, SQLite may produce an error for different reasons such as when the SQL string is invalid.

If the input string is invalid SQL, an Error.Generic is produced.

Examples

import sqlite (Database)

let db = Database.new(':memory:').or_panic
let st = db.prepare('SELECT 1').or_panic