Home › API › SIMPLE_SQL_DATABASE

SIMPLE_SQL_DATABASE

The core database connection class. Provides query execution, transactions, and factory methods for builders.

View Source

Creation Procedures

make (a_path: READABLE_STRING_GENERAL)

Create database connection to file at a_path. Creates file if it doesn't exist.

make_memory

Create an in-memory database. Data is lost when connection closes.

make_with_flags (a_path: READABLE_STRING_GENERAL; a_flags: INTEGER)

Create connection with custom SQLite flags (SQLITE_OPEN_READONLY, etc.).

Query Execution

execute (a_sql: READABLE_STRING_8)

Execute SQL that doesn't return results (DDL, INSERT, UPDATE, DELETE).

query (a_sql: READABLE_STRING_8): SIMPLE_SQL_RESULT

Execute SQL that returns results (SELECT). Returns all rows in memory.

execute_with_args (a_sql: READABLE_STRING_8; a_args: ARRAY [detachable ANY])

Execute parameterized SQL with positional arguments. Use ? placeholders.

query_with_args (a_sql: READABLE_STRING_8; a_args: ARRAY [detachable ANY]): SIMPLE_SQL_RESULT

Query with positional arguments. Prevents SQL injection.

Fluent Query Builders

select_from (a_table: READABLE_STRING_8): SIMPLE_SQL_SELECT_BUILDER

Start building a SELECT query. Chain .columns(), .where(), .order_by(), etc.

insert_into (a_table: READABLE_STRING_8): SIMPLE_SQL_INSERT_BUILDER

Start building an INSERT query. Chain .value() calls.

update (a_table: READABLE_STRING_8): SIMPLE_SQL_UPDATE_BUILDER

Start building an UPDATE query. Chain .set() and .where() calls.

delete_from (a_table: READABLE_STRING_8): SIMPLE_SQL_DELETE_BUILDER

Start building a DELETE query. Chain .where() call.

Advanced Builders

eager_loader: SIMPLE_SQL_EAGER_LOADER

Create an eager loader to prevent N+1 queries. Use .from_table().include().execute.

paginator (a_table: READABLE_STRING_8): SIMPLE_SQL_PAGINATOR

Create a cursor-based paginator. Use .order_by().page_size().first_page.

Transactions

begin_transaction

Start a transaction. All subsequent operations are grouped until commit/rollback.

commit

Commit the current transaction. Makes all changes permanent.

rollback

Rollback the current transaction. Discards all changes since begin_transaction.

in_transaction: BOOLEAN

Is there an active transaction?

Query Monitoring

query_monitor: detachable SIMPLE_SQL_QUERY_MONITOR

The active query monitor (if enabled). Use to detect N+1 patterns.

enable_query_monitor

Enable query monitoring. All queries are recorded for N+1 detection.

disable_query_monitor

Disable query monitoring and clear recorded queries.

Streaming

query_cursor (a_sql: READABLE_STRING_8): SIMPLE_SQL_CURSOR

Execute query returning a lazy cursor. Rows fetched one-at-a-time. Memory efficient.

query_stream (a_sql: READABLE_STRING_8; a_callback: PROCEDURE [SIMPLE_SQL_ROW])

Execute query with callback for each row. Rows not held in memory.

Metadata

last_insert_rowid: INTEGER_64

The rowid of the most recently inserted row.

changes: INTEGER

Number of rows affected by the most recent INSERT, UPDATE, or DELETE.

is_open: BOOLEAN

Is the database connection open?

path: READABLE_STRING_GENERAL

Path to the database file (or ":memory:" for in-memory).

Lifecycle

Close the database connection. Always call when done.

Examples

Basic Usage

local
    db: SIMPLE_SQL_DATABASE
do
    create db.make ("app.db")
    db.execute ("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")
    db.insert_into ("users").value ("name", "Alice").execute
    print ("Inserted ID: " + db.last_insert_rowid.out)
    db.close
end

Parameterized Query

local
    result: SIMPLE_SQL_RESULT
do
    result := db.query_with_args ("SELECT * FROM users WHERE name LIKE ? AND active = ?", <<"%Alice%", 1>>)
    across result as row loop
        print (row.string_value ("name"))
    end
end

Transaction with Error Handling

db.begin_transaction
if attached attempt_operation as err then
    db.rollback
    print ("Error: " + err)
else
    db.commit
end

Design by Contract

All features have preconditions and postconditions. For example, query requires is_open. Check the source code for full contract specifications.