I just recently went back to my sqlite3 bindings library and spruced the user experience quite a bit. I added support for nullable types, and scalar/aggregate/window UDFs!
As an introduction, I’ve dumped the quick start part of the readme.
The API will be improved once Extensions and Parametric Traits are completed and released!
Quick Start
Opening a Connection
from slight.connection import Connection
fn main() raises:
# Open an in-memory database
var db = Connection.open_in_memory()
# Or open a file-based database
var db = Connection.open("my_database.db")
Creating Tables and Inserting Data
from slight.connection import Connection
fn main() raises:
var db = Connection.open_in_memory()
# Execute a single statement
_ = db.execute("""
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE
)
""")
# Execute multiple statements at once
db.execute_batch("""
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
""")
Querying Data
from slight.connection import Connection
from slight import Int, String
fn main() raises:
var db = Connection.open_in_memory()
db.execute_batch("""
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
INSERT INTO users VALUES (1, 'Alice', 30);
INSERT INTO users VALUES (2, 'Bob', 25);
""")
# Prepare and execute a query
var stmt = db.prepare("SELECT * FROM users")
for row in stmt.query():
print("ID:", row.get[Int](0))
print("Name:", row.get[String](1))
print("Age:", row.get[Int](2))
Using Parameters
from slight.connection import Connection
from slight import Int, String
fn main() raises:
var db = Connection.open_in_memory()
db.execute_batch("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
# Positional parameters with a list
_ = db.execute("INSERT INTO users (name) VALUES (?1)", ["Alice"])
# Named parameters with a dictionary
_ = db.execute("INSERT INTO users (name) VALUES (:name)", {":name": "Bob"})
# Query with parameters
var stmt = db.prepare("SELECT * FROM users WHERE name = ?1")
for row in stmt.query(["Alice"]):
print("Found:", row.get[String](1))
Transforming Rows
from slight.connection import Connection
from slight.row import Row
from slight import Int, String
@fieldwise_init
struct User(Writable):
var id: Int
var name: String
fn write_to[W: Writer, //](self, mut writer: W):
writer.write("User(id=", self.id, ", name=", self.name, ")")
fn main() raises:
var db = Connection.open_in_memory()
db.execute_batch("""
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users VALUES (1, 'Alice');
INSERT INTO users VALUES (2, 'Bob');
""")
fn to_user(row: Row) raises -> User:
return User(id=row.get[Int](0), name=row.get[String](1))
# Map rows to User structs
var stmt = db.prepare("SELECT * FROM users")
for user in stmt.query[to_user]():
print(user)
# Reset the statement to get users using Struct reflection.
stmt.reset()
for user in stmt.query[User]():
print(user)
# Get a single row
var user = db.one_row[to_user]("SELECT * FROM users WHERE id = ?1", [1])
print("Found:", user)
Transactions
from slight.connection import Connection
from slight.transaction import TransactionBehavior
fn main() raises:
var db = Connection.open_in_memory()
db.execute_batch("CREATE TABLE accounts (name TEXT, balance REAL)")
# Basic transaction with context manager
with db.transaction() as tx:
_ = tx.conn[].execute("INSERT INTO accounts VALUES (?1, ?2)", ("Alice", 1000.0))
_ = tx.conn[].execute("INSERT INTO accounts VALUES (?1, ?2)", ("Bob", 500.0))
tx.commit() # Explicitly commit; otherwise rolls back on scope exit
# Transaction with specific behavior
with db.transaction(TransactionBehavior.IMMEDIATE) as tx:
_ = tx.conn[].execute("UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice'")
_ = tx.conn[].execute("UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob'")
tx.commit()
Savepoints
from slight.connection import Connection
fn main() raises:
var db = Connection.open_in_memory()
db.execute_batch("CREATE TABLE log (message TEXT)")
with db.transaction() as tx:
_ = tx.conn[]execute("INSERT INTO log VALUES (?1)", ["Step 1"])
# Create a savepoint for a risky operation
with tx.savepoint() as sp:
_ = sp.conn[]execute("INSERT INTO log VALUES (?1)", ["Risky step"])
# Rollback just this savepoint if something goes wrong
sp.rollback()
# Try again
_ = sp.conn[].execute("INSERT INTO log VALUES (?1)", ["Safe step"])
sp.commit()
tx.commit()
Scalar Functions
Register custom SQL functions that operate on a single row:
from slight.connection import Connection
from slight.functions import Context, FunctionFlags
from slight.row import Row
fn halve(ctx: Context) raises -> Float64:
return ctx.get_double(0) / 2.0
fn main() raises:
var db = Connection.open_in_memory()
# Register a scalar function named "halve" that takes 1 argument
db.create_scalar_function[halve](
"halve",
n_arg=1,
)
fn get_result(row: Row) raises -> Float64:
return row.get[Float64](0)
print(db.one_row[get_result]("SELECT halve(10.0)")) # 5.0
Aggregate Functions
Register custom SQL aggregate functions that process multiple rows into a single result:
from slight.connection import Connection
from slight.functions import Context, FunctionFlags
from slight.row import Row
fn sum_init(mut ctx: Context) raises -> Int64:
return 0
fn sum_step(mut ctx: Context, mut acc: Int64) raises:
acc += ctx.get_int64(0)
fn sum_finalize(mut ctx: Context, acc: Int64) raises -> Int64:
return acc
fn main() raises:
var db = Connection.open_in_memory()
db.execute_batch("""
CREATE TABLE numbers (value INTEGER);
INSERT INTO numbers VALUES (1);
INSERT INTO numbers VALUES (2);
INSERT INTO numbers VALUES (3);
""")
db.create_aggregate_function[sum_init, sum_step, sum_finalize](
"my_sum",
n_arg=1,
flags=FunctionFlags.UTF8 | FunctionFlags.DETERMINISTIC,
)
fn get_result(row: Row) raises -> Int64:
return row.get[Int64](0)
print(db.one_row[get_result]("SELECT my_sum(value) FROM numbers")) # 6
Window Functions
Register custom SQL window functions that operate over a sliding frame of rows. Window functions extend aggregate functions with `inverse` (to remove a row leaving the frame) and `value` (to return the current result without finalizing) callbacks:
from slight.connection import Connection
from slight.functions import Context, FunctionFlags
from slight.row import Row
fn sum_init(mut ctx: Context) raises -> Int64:
return 0
fn sum_step(mut ctx: Context, mut acc: Int64) raises:
acc += ctx.get_int64(0)
fn sum_finalize(mut ctx: Context, acc: Int64) raises -> Optional[Int64]:
return acc
fn sum_inverse(mut ctx: Context, mut acc: Int64) raises:
acc -= ctx.get_int64(0)
fn sum_value(acc: Optional[Int64]) raises -> Optional[Int64]:
return acc.copy()
fn main() raises:
var db = Connection.open_in_memory()
db.execute_batch("""
CREATE TABLE numbers (value INTEGER);
INSERT INTO numbers VALUES (1);
INSERT INTO numbers VALUES (2);
INSERT INTO numbers VALUES (3);
INSERT INTO numbers VALUES (4);
INSERT INTO numbers VALUES (5);
""")
db.create_window_function[sum_init, sum_step, sum_finalize, sum_value, sum_inverse](
"my_sum",
n_arg=1,
flags=FunctionFlags.UTF8 | FunctionFlags.DETERMINISTIC,
)
fn get_row(row: Row) raises -> String:
return t"{row.get[Int64](0)} | {row.get[Int64](1)}"
# Sliding window: sum of current row and the one before it
var stmt = db.prepare("""
SELECT value,
my_sum(value) OVER (ORDER BY value ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
FROM numbers
""")
for row in stmt.query[get_row]():
print(row)
# Output: 1|1, 2|3, 3|5, 4|7, 5|9
Supported Types
Reading from SQL (FromSQL)
| SQLite Type | Mojo Type |
|---|---|
| INTEGER | Int, Int8, Int16, Int32, Int64, UInt, UInt8, UInt16, UInt32, UInt64 |
| REAL | Float16, Float32, Float64 |
| TEXT | String |
| INTEGER (0/1) | Bool |
| BLOB | List[Byte] |
| NULL | None |
| NULLABLE COLUMN | Option[T] where T refers to the sqlite to mojo type mappings above |
Writing to SQL (ToSQL)
| Mojo Type | SQLite Type |
|---|---|
Int, Int8, Int16, Int32, Int64 |
INTEGER |
UInt, UInt8, UInt16, UInt32, UInt64 |
INTEGER |
Float16, Float32, Float64 |
REAL |
String, StringLiteral, StringSlice |
TEXT |
Bool |
INTEGER (0/1) |
None |
NULL |
Option[T] |
NULLABLE COLUMN refers to the sqlite to mojo type mappings above |
Parameter Binding (Params)
For parameter binding only Tuples support heterogeneous types. Lists and Dicts require all parameters to be of the same type, because we do not have Trait objects yet.
| Mojo Type | Binding Style | Heterogeneous Supported? |
|---|---|---|
Tuple |
Positional parameters (?1, ?2, etc.) |
Yes |
List |
Positional parameters (?1, ?2, etc.) |
No |
Dict |
Named parameters (:name, @name, $name ) |
No |
