SQLite databases are file-based and easy to work with. Use the open function with a sqlite:// URI to create or connect to a database.
What’s new: Open sqlite-schema
rye .Needs { sqlite }
db: Open sqlite://sample.s3db
The open function dispatches on the URI scheme type. For SQLite, it returns a database connection that you can use for queries and operations.
Start with basic queries to test your connection. SQLite has built-in functions you can use without needing tables.
What’s new: Query display
; Test connection with current date
db .Query { select date() }
|display
;
; | date() |
; +--------------+
; | 2025-01-07 |
; Query multiple values and expressions
db .Query { select date() as today , 123 + 234 as sum , "Hello" as greeting }
|display
;
; | today | sum | greeting |
; +-----------------------------+
; | 2025-01-08 | 357 | Hello |
The Query function returns a table object that can be displayed or processed further.
Use Exec for SQL statements that modify the database structure or data but don’t return results.
What’s new: Exec
; Create a table
db .Exec { create table pets ( id integer primary key , name varchar(40) , species varchar(20) ) }
; Insert single record
db .Exec { insert into pets values ( 1 , "Toto" , "dog" ) }
; Insert multiple records at once
db .Exec { insert into pets values ( 2 , "Hedwig" , "owl" ) ,
( 3 , "Nemo" , "fish" ) ,
( 4 , "Hooch" , "dog" ) }
The Exec function executes SQL statements and returns the database connection, allowing for method chaining.
Once you have data, you can query it with various conditions and operations.
; Select all records
db .Query { select * from pets }
|display
;
; | id | name | species |
; +-----------------------+
; | 1 | Toto | dog |
; | 2 | Hedwig | owl |
; | 3 | Nemo | fish |
; | 4 | Hooch | dog |
; Filter with WHERE clause
db .Query { select * from pets where species = "dog" }
|display
; Use LIKE for pattern matching
db .Query { select name from pets where name like "H%" }
|display
Queries return table objects that can be further processed with Rye’s table functions.
Note: SQL NULL values are returned as Rye Void (_).
See also: the Tables cookbook for working with query results: ../working-with/tables/
Rye’s SQL dialect supports safe parameter embedding using get-words. This prevents SQL injection and makes queries more readable.
What’s new: get-words in SQL blocks
; Set variables for parameters
pet-name: "Fluffy"
pet-species: "cat"
min-id: 2
; Use get-words (?variable) for parameters
db .Exec { insert into pets values ( null , ?pet-name , ?pet-species ) }
; Parameters work in queries too
db .Query { select * from pets where id > ?min-id and species = ?pet-species }
|display
The ?variable syntax automatically creates prepared statements with proper parameter binding, making your code both safe and readable.
For quick one-offs, you can also pass SQL as a string followed by positional parameters inside a block. The first value must be a string (the SQL with ? placeholders); the rest are the argument values.
; Query with positional parameters
db .Query { "select * from pets where id >= ? and species = ?" 2 "dog" }
|display
; Exec with positional parameters
db .Exec { "insert into pets ( name , species ) values ( ? , ? )" "Milo" "dog" }
Commas inside the block are optional and may be used for readability:
db .Query { "select * from pets where id >= ? and species = ?" , 2 , "dog" }
Use Show-SQL to see the generated SQL and parameter values without executing the query.
What’s new: Show-SQL
search-term: "dog"
min-id: 1
; See what SQL is generated
db .Show-SQL { select * from pets where species = ?search-term and id >= ?min-id }
; Returns: "SELECT * FROM pets WHERE species = ? AND id >= ?
; -- Parameters: ? = dog, ? = 1"
This is useful for debugging complex queries and understanding how parameters are bound.
You can wrap multiple operations in a simple transaction.
db .Exec { begin }
; ... your inserts/updates ...
db .Exec { insert into pets values ( null , "Buddy" , "dog" ) }
db .Exec { commit }
; Use rollback if something goes wrong
; db .Exec { rollback }
When you’re done with the connection, close it to free resources.
db .Close