Databases

functions for creating and query databases

Functions

The Databases module offers straightforward database operations with built-in SQLite support. It provides a simple yet powerful interface for managing databases directly from your Arturo code.

Key Concepts

Native SQLite support
Simple database connection management
Parametrized queries
Query results as blocks of rows

Basic Usage

Opening & Closing Connections

; create/open database
db: open.sqlite "mydata.db"

; when done
close db

Simple Queries

query db {!sql
    CREATE TABLE users (
        id INTEGER PRIMARY KEY, 
        name TEXT, 
        age INTEGER
    )
}

query db "INSERT INTO users (name, age) VALUES ('John', 25)"

; get results
users: query db "SELECT * FROM users"
print users   ; [1 John 25]

Tip
If you are using a text editor that supports this syntax (e.g. VSCode), using !sql in front of {..}-enclosed blocks will enable proper SQL highlighting. Totally non-compulsory, but still very handy!

Parametrized Queries

; safer way to insert data with parameters
age: 30
name: "Alice"
query.with: @[name age] db "INSERT INTO users (name, age) VALUES (?,?)" 

; parametrized select
minAge: 20
results: query.with: @[minAge] db "SELECT * FROM users WHERE age > ?"
loop results 'user ->
    print ["User:" user\1 "Age:" user\2]    
; User: Alice Age: 30

Important
Always use parametrized queries when dealing with user input to prevent SQL injection attacks.

Common Patterns

Transaction Handling

; do multiple operations
query db [
    "INSERT INTO users (name, age) VALUES ('Bob', 35)"
    "UPDATE users SET age = 31 WHERE name = 'Alice'"
]

> [!NOTE] This method of performing multiple operations is the equivalent of using `BEGIN TRANSACTION` blocks in SQL, although in much more... practical and Arturo-friendly way! ;-)

Getting Last Insert ID

; insert and get the ID
query.id.with: ["Carol" 28] db {!sql 
    INSERT INTO users (name, age) VALUES (?,?)
}