How to Connect to PostgreSQL with a Connection Pool
Set up a HikariCP pool for PostgreSQL, build a JdbcTransactor on it, size it for virtual threads and close it with a Scope.
How to Test Database Code Against SQLite
Give every test its own SQLite database, create the schema from code, seed fixtures and roll back writes in ZIO Test specs.
SQL Transactions: Isolation, Read-Only and Savepoints
This guide covers ZIO Blocks' SQL transaction system: how transact works under the hood, which isolation levels are available on each database, and how nested transactions use savepoints. To set up a connection pool, see How to Connect to PostgreSQL with a Connection Pool.
sql(table) Checked Interpolation
Note: Checked form is StringContext(...).sql(table, ...)(...); no sqlChecked alias (removed pre-1.0).
How to Write Joins and Inspect the Generated SQL
Join related tables with SqlQuery and Rel, read the rows as tuples, and print or dump the SQL each join generates.
How to Upsert Rows and Paginate with Keyset Cursors
Insert or update rows in one statement, choose which columns an upsert changes, and walk large tables page by page with keyset cursors.
How to Build a Query from Optional Search Filters
Turn a search form with optional fields into one parameterized SQL query with a WHERE clause, ordering and paging, using Frag.
How to Map Custom Column Types
Store newtypes, opaque types, enums, JSON documents, PostgreSQL arrays and timestamps in SQL columns with the ZIO Blocks sql module.
How to Stream Large Query Results
Read a large SQL result set as a stream of row chunks, aggregate it, export it to a file and handle its errors without loading every row.
How to Migrate Table Rows to a New Version Online
Convert every row of a live table to a new row type with SmallMigrator or LargeMigrator while the application keeps reading and writing.
How to Store and Search Vector Embeddings in PostgreSQL
Store embeddings in a pgvector column with a DbCodec built on String, search by nearest neighbour, pick a distance operator and add an HNSW index.
How to Use the sql Module from a ZIO Application
Provide a ZIO Blocks transactor as a ZLayer, run queries off ZIO's main threads, and mix database writes with other effects in one transaction.