SQLite Database

Opening a database

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.

Simple queries

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.

Creating tables and inserting data

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.

Querying data

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.

Prepared statements with parameters

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.

Debugging SQL

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.