Chapter 5: Database#

1. From Arrays to Real Data#

In Chapters 2 and 3, all your data lived in Ruby arrays. Server restart. Data gone. Fine for learning routing. Useless for production.

This chapter covers Tina4's database layer: raw queries, parameterised queries, transactions, schema inspection, helper methods, and migrations.

Tina4 speaks to five database engines: SQLite, PostgreSQL, MySQL, Microsoft SQL Server, and Firebird. The API is identical across all five. Switch databases by changing one line in .env.

2. Connecting to a Database#

The Default: SQLite#

When you scaffold with tina4 init, Tina4 creates a SQLite database at data/app.db. The default .env contains:

TINA4_DEBUG=true

No explicit TINA4_DATABASE_URL? Tina4 defaults to sqlite:///data/app.db. The health check at /health shows "database": "connected" with zero configuration.

Connection Strings for Other Databases#

Set TINA4_DATABASE_URL in .env to use a different engine:

# SQLite (explicit)TINA4_DATABASE_URL=sqlite:///data/app.db​# PostgreSQLTINA4_DATABASE_URL=postgres://localhost:5432/myapp​# MySQLTINA4_DATABASE_URL=mysql://localhost:3306/myapp​# Microsoft SQL ServerTINA4_DATABASE_URL=mssql://localhost:1433/myapp​# FirebirdTINA4_DATABASE_URL=firebird://localhost:3050/path/to/database.fdb

Firebird URL Forms#

Firebird is the awkward one: every other engine has a server-side database name (postgres://host:port/dbname), but Firebird wants either an absolute file path on the server, a Windows drive-letter path, or an alias. The classic URI form needs a double slash to keep the leading / of an absolute path through URI.parse, which is unintuitive.

Tina4 normalises five equivalent forms. Pick whichever reads best:

# Classic double-slash absolute path -- the URL spec wayTINA4_DATABASE_URL=firebird://SYSDBA:masterkey@localhost:3050//firebird/data/app.fdb​# Single-slash absolute path -- what most people instinctively typeTINA4_DATABASE_URL=firebird://SYSDBA:masterkey@localhost:3050/firebird/data/app.fdb​# Windows drive-letter path (also accepts /C%3A/Data/app.fdb)TINA4_DATABASE_URL=firebird://SYSDBA:masterkey@host:3050/C:/Data/app.fdb​# Firebird alias (single token, no slashes)TINA4_DATABASE_URL=firebird://SYSDBA:masterkey@localhost:3050/employee

For ops setups that keep the server URL and database location in separate config layers -- or for Windows backslash paths -- set TINA4_DATABASE_FIREBIRD_PATH:

TINA4_DATABASE_FIREBIRD_PATH=C:\firebird\data\app.fdbTINA4_DATABASE_URL=firebird://SYSDBA:masterkey@localhost:3050/ignored

Firebird Connection Charset#

Firebird connects as UTF8 by default. A database created under the NONE charset stores raw bytes, and reading UTF-8 text out of it as UTF8 double-encodes every non-ASCII character. Override the charset with a ?charset= query on the URL or the TINA4_DATABASE_CHARSET environment variable. The URL query wins, then the env var, then the UTF8 default, so existing connections are unchanged.

# Connection URL queryTINA4_DATABASE_URL=firebird://SYSDBA:masterkey@localhost:3050/app.fdb?charset=NONE​# Or via the environment variable (applies to every Firebird connection)TINA4_DATABASE_CHARSET=WIN1252

The env override wins over whatever path is in the URL.

Separate Credentials#

If you prefer to keep credentials out of the connection string (recommended for production), use separate environment variables:

TINA4_DATABASE_URL=postgres://localhost:5432/myappTINA4_DATABASE_USERNAME=myuserTINA4_DATABASE_PASSWORD=secretpassword

Tina4 merges these with the connection string at startup. The credentials in the separate variables take precedence over any embedded in the URL.

Programmatic Connection#

You can also create a database connection directly in Ruby code:

db = Tina4::Database.new("sqlite://app.db", username: nil, password: nil)

Connection Pooling#

For applications that handle many concurrent requests, enable connection pooling with the pool parameter:

db = Tina4::Database.new("postgres://localhost/mydb", pool: 5)

The pool parameter controls how many database connections are maintained:

• pool: 0 (the default) -- a single connection is used for all queries
• pool: N (where N > 0) -- N connections are created and rotated round-robin across queries

Pooled connections are thread-safe. Each query is dispatched to the next available connection in the pool. This eliminates contention when multiple route handlers query the database simultaneously.

Verifying the Connection#

After updating .env, restart the server and check:

curl http://localhost:7147/health

{  "status": "ok",  "database": "connected",  "uptime_seconds": 3,  "version": "3.0.0",  "framework": "tina4-ruby"}

If the database is not reachable, you will see "database": "disconnected" with an error message.

3. Getting the Database Object#

In your route handlers, access the database via the Tina4::Database class:

Tina4::Router.get("/api/test-db") do |request, response|  db = Tina4.database​  result = db.fetch("SELECT 1 + 1 AS answer")​  response.json(result)end

curl http://localhost:7147/api/test-db

{"answer": 2}

Tina4.database returns the active database connection. You call methods like fetch, execute, and fetch_one on this object.

4. Raw Queries#

fetch -- Get Multiple Rows#

db = Tina4.database​# Returns an array of hashesproducts = db.fetch("SELECT * FROM products WHERE price > 50")

Each row is a hash with column names as keys:

# products looks like:[  { "id" => 1, "name" => "Keyboard", "price" => 79.99 },  { "id" => 4, "name" => "Standing Desk", "price" => 549.99 }]

DatabaseResult#

fetch returns a DatabaseResult object. It behaves like an array but carries extra metadata about the query.

Properties#

result = db.fetch("SELECT * FROM users WHERE active = ?", [1])​result.records      # [{ "id" => 1, "name" => "Alice" }, { "id" => 2, "name" => "Bob" }]result.columns      # ["id", "name", "email", "active"]result.count        # total number of matching rowsresult.limit        # query limit (if set)result.offset       # query offset (if set)

Iteration#

A DatabaseResult is enumerable. Use it directly in loops:

result.each do |user|  puts user["name"]end

Index Access#

Access rows by index like a regular array:

first_user = result[0]

Countable#

length works on the result:

puts result.length  # number of records in this result set

Conversion Methods#

result.to_json       # JSON string of all recordsresult.to_csv        # CSV string with column headersresult.to_array      # plain array of hashesresult.to_paginate   # { "records" => [...], "count" => 42, "limit" => 10, "offset" => 0 }

to_paginate is designed for building paginated API responses. It bundles the records with the total count, limit, and offset in a single hash.

Schema Metadata with column_info#

column_info returns detailed metadata about the columns in the result set. The data is lazy-loaded -- it only queries the database schema when you call the method for the first time:

info = result.column_info# [#     { "name" => "id", "type" => "INTEGER", "size" => nil, "decimals" => nil, "nullable" => false, "primary_key" => true },#     { "name" => "name", "type" => "TEXT", "size" => nil, "decimals" => nil, "nullable" => false, "primary_key" => false },#     { "name" => "email", "type" => "TEXT", "size" => 255, "decimals" => nil, "nullable" => true, "primary_key" => false },#     ...# ]

Each column entry contains:

This is useful for building dynamic forms, generating documentation, or validating data before insert.

fetch_one -- Get a Single Row#

product = db.fetch_one("SELECT * FROM products WHERE id = 1")# Returns: { "id" => 1, "name" => "Keyboard", "price" => 79.99 }

If no row matches, fetch_one returns nil.

execute -- Run a Statement#

For INSERT, UPDATE, DELETE, and DDL statements that do not return rows:

db.execute("INSERT INTO products (name, price) VALUES ('Widget', 9.99)")db.execute("UPDATE products SET price = 89.99 WHERE id = 1")db.execute("DELETE FROM products WHERE id = 5")db.execute("CREATE TABLE IF NOT EXISTS logs (id INTEGER PRIMARY KEY, message TEXT, created_at TEXT)")

Full Example: A Simple Query Route#

Tina4::Router.get("/api/products") do |request, response|  db = Tina4.database​  products = db.fetch("SELECT * FROM products ORDER BY name")​  response.json({    products: products,    count: products.length  })end

curl http://localhost:7147/api/products

{  "products": [    {"id": 1, "name": "Keyboard", "price": 79.99, "in_stock": 1},    {"id": 2, "name": "Mouse", "price": 29.99, "in_stock": 1},    {"id": 3, "name": "Monitor", "price": 399.99, "in_stock": 0}  ],  "count": 3}

5. Parameterised Queries#

Never concatenate user input into SQL strings. That road leads to SQL injection:

# NEVER do this:db.fetch("SELECT * FROM products WHERE name = '#{user_input}'")

Instead, use parameterised queries. Pass parameters as the second argument:

db = Tina4.database​# Positional parameters with ?product = db.fetch_one(  "SELECT * FROM products WHERE id = ?",  [42])​# Multiple parametersproducts = db.fetch(  "SELECT * FROM products WHERE price BETWEEN ? AND ? ORDER BY price",  [10.00, 100.00])

The database driver handles escaping. Your input is never part of the SQL string.

A Safe Search Endpoint#

Tina4::Router.get("/api/products/search") do |request, response|  db = Tina4.database​  q = request.params["q"] || ""  max_price = (request.params["max_price"] || 99999).to_f​  if q.empty?    return response.json({ error: "Query parameter 'q' is required" }, 400)  end​  products = db.fetch(    "SELECT * FROM products WHERE name LIKE ? AND price <= ? ORDER BY name",    ["%#{q}%", max_price]  )​  response.json({    query: q,    max_price: max_price,    results: products,    count: products.length  })end

curl "http://localhost:7147/api/products/search?q=key&max_price=100"

{  "query": "key",  "max_price": 100,  "results": [    {"id": 1, "name": "Wireless Keyboard", "price": 79.99, "in_stock": 1}  ],  "count": 1}

6. Transactions#

Multiple operations must succeed or fail together. Transactions enforce that contract:

Tina4::Router.post("/api/orders") do |request, response|  db = Tina4.database  body = request.body​  begin    db.transaction do |tx|      # Create the order      tx.execute(        "INSERT INTO orders (customer_id, total, status) VALUES (?, ?, 'pending')",        [body["customer_id"], body["total"]]      )​      # Get the new order ID      order = tx.fetch_one("SELECT last_insert_rowid() AS id")      order_id = order["id"]​      # Create order items      body["items"].each do |item|        tx.execute(          "INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)",          [order_id, item["product_id"], item["quantity"], item["price"]]        )​        # Decrease stock        tx.execute(          "UPDATE products SET stock = stock - ? WHERE id = ?",          [item["quantity"], item["product_id"]]        )      end    end​    response.json({ order_id: order_id, status: "created" }, 201)  rescue => e    response.json({ error: "Order failed: #{e.message}" }, 500)  endend

If any step inside the block fails, the transaction is automatically rolled back. If the block completes without error, the transaction is automatically committed. The database never sits in a half-finished state.

7. Schema Inspection#

Tina4 provides methods to inspect your database structure at runtime:

tables#

db = Tina4.databasetables = db.tables

Returns an array of table names:

["orders", "order_items", "products", "users"]

columns#

columns = db.columns("products")

Returns an array of column definitions:

[  { "name" => "id", "type" => "INTEGER", "nullable" => false, "primary" => true },  { "name" => "name", "type" => "TEXT", "nullable" => false, "primary" => false },  { "name" => "price", "type" => "REAL", "nullable" => true, "primary" => false },  { "name" => "in_stock", "type" => "INTEGER", "nullable" => true, "primary" => false }]

table_exists?#

if db.table_exists?("products")  # Table exists, safe to queryend

Returns true if the table exists, false otherwise.

driver_name#

db.driver_name# "sqlite", "postgres", "mysql", "mssql", or "firebird"

Returns a lowercase string identifying the active database engine. This matters when you write engine-specific SQL in a multi-database setup.

A Schema Info Endpoint#

Tina4::Router.get("/api/schema") do |request, response|  db = Tina4.database  tables = db.tables​  schema = {}  tables.each do |table|    schema[table] = db.columns(table)  end​  response.json({ tables: schema })end

8. Batch Operations with execute_many#

Insert or update many rows efficiently:

db = Tina4.database​params_list = [  ["Widget A", 9.99],  ["Widget B", 14.99],  ["Widget C", 19.99],  ["Widget D", 24.99]]​db.execute_many(  "INSERT INTO products (name, price) VALUES (?, ?)",  params_list)

execute_many prepares the statement once and executes it for each item in the array. This is significantly faster than calling execute in a loop because the SQL only needs to be parsed once.

9. Helper Methods: insert, update, delete#

Tina4 provides shorthand methods so you do not have to write SQL for simple operations.

insert#

db = Tina4.database​# Insert a single rowdb.insert("products", {  name: "Wireless Mouse",  price: 34.99,  in_stock: 1})​# Insert multiple rowsdb.insert("products", [  { name: "USB Cable", price: 9.99, in_stock: 1 },  { name: "HDMI Cable", price: 14.99, in_stock: 1 },  { name: "DisplayPort Cable", price: 19.99, in_stock: 0 }])

update#

# Update rows matching a filterdb.update("products", { price: 39.99, in_stock: 1 }, { id: 7 })

The second argument is the data to set, and the third is a filter hash for the WHERE clause. Each key-value pair becomes an AND condition.

delete#

# Delete rows matching a filterdb.delete("products", { id: 7 })

What they return#

insert, update, and delete each return a Tina4::DatabaseResult. The object is truthy, responds to success?, and carries .affected_rows and .last_id. The new row id lands on .last_id after an insert; it is nil for update and delete.

result = db.insert("products", { name: "Wireless Mouse", price: 34.99 })result.last_id        # the new row idresult.affected_rows  # 1result.success?       # true​changed = db.update("products", { price: 39.99 }, { id: 7 })changed.affected_rows # rows updated

A failed write raises. It never returns a falsy value, so rescue the error rather than testing the return value.

These helpers generate SQL for you. Convenient for simple CRUD. Raw queries still own complex joins, subqueries, and aggregations.

10. Migrations#

Migrations are versioned scripts that evolve your schema over time. No manual CREATE TABLE statements. Write migration files. Tina4 applies them in order.

Ruby's migration system is unique among Tina4 implementations: it supports both .sql and .rb migration files.

File Naming#

Two naming patterns are accepted:

Pick one pattern and stick with it. Do not mix sequential and timestamp naming in the same project.

Generating a Migration#

Use the CLI to scaffold migration files:

tina4 generate migration create_products_table

Created migration: migrations/20260324120000_create_products_table.sqlCreated migration: migrations/20260324120000_create_products_table.down.sql

The generator creates both the up and down files. The timestamp prefix ensures migrations always run in chronological order.

SQL Migrations#

Edit the generated file migrations/20260324120000_create_products_table.sql:

-- migrations/20260324120000_create_products_table.sqlCREATE TABLE products (    id INTEGER PRIMARY KEY AUTOINCREMENT,    name TEXT NOT NULL,    category TEXT NOT NULL DEFAULT 'Uncategorized',    price REAL NOT NULL DEFAULT 0.00,    in_stock INTEGER NOT NULL DEFAULT 1,    created_at TEXT DEFAULT CURRENT_TIMESTAMP,    updated_at TEXT DEFAULT CURRENT_TIMESTAMP);

The down migration goes in the separate .down.sql file:

-- migrations/20260324120000_create_products_table.down.sqlDROP TABLE IF EXISTS products;

The .down.sql file is optional. If it does not exist, rollback for that migration is skipped.

The SQL parser supports $$ delimited stored procedures and block comments, so you can include complex database objects in a single migration file.

Ruby Class Migrations#

As an alternative to SQL files, you can write migrations as Ruby classes. This is useful when you need conditional logic, loops, or data transformations during a migration:

# migrations/20260324120000_create_products_table.rbclass CreateProductsTable < Tina4::MigrationBase  def up    execute "CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL DEFAULT 0.00)"  end​  def down    execute "DROP TABLE IF EXISTS products"  endend

For .rb migrations, the down method handles rollback directly in the same file. No separate .down.sql file is needed.

Running Migrations#

tina4 migrate

Running migrations...  [APPLIED] 20260324120000_create_products_table.sqlMigrations complete. 1 applied.

Automatic Migrations on Startup#

Tina4 runs your pending migrations the moment the server boots. It checks for a migrations/ folder (with at least one migration file) and a resolvable database, then applies anything outstanding before the first request lands. No extra deploy step. Ship the code, start the service, and the schema catches up.

This runs the same migration engine as the CLI, so the result matches tina4 migrate. The startup hook fires after routes and the database bind, just before the server accepts traffic.

Startup migration is non-breaking. If a migration fails, Tina4 logs the error and the service still boots:

Startup auto-migration failed: <error> - the service is starting anyway. Run `tina4 migrate` to retry.

A successful run logs how many it applied:

Applied 2 pending migration(s) on startup

The explicit tina4 migrate CLI stays fail-fast: it exits non-zero on failure, so CI keeps a real exit code to gate on. Only the startup hook swallows the error to keep the service available.

Set TINA4_AUTO_MIGRATE=false (or 0, no, off) to turn the hook off. The default is true.

Multi-instance caveat. When several instances boot at once against one database, they race to apply the same migrations. For multi-instance production, disable the hook and migrate as a separate deploy step:

TINA4_AUTO_MIGRATE=false   # in each instance's environmenttina4 migrate              # run once, before rolling out the new instances

Checking Migration Status#

tina4ruby migrate:status

Migration                                    Status     Applied At---------                                    ------     ----------20260324120000_create_products_table.sql      applied    2026-03-24 12:00:0020260324130000_create_orders_table.sql        pending    -

Rolling Back#

tina4ruby migrate:rollback

Rolling back last batch...  [ROLLED BACK] 20260324130000_create_orders_table.sqlRollback complete. 1 rolled back.

Rollback undoes the entire last batch of migrations. If you applied three migrations in one tina4 migrate run, all three are rolled back together. You can configure the number of steps if you need finer control.

For .sql migrations, rollback runs the corresponding .down.sql file. For .rb migrations, rollback calls the down method.

A Real Migration Sequence#

Here is what a typical project's migrations look like:

migrations/├── 20260324120000_create_products_table.sql├── 20260324120000_create_products_table.down.sql├── 20260324121000_create_users_table.sql├── 20260324121000_create_users_table.down.sql├── 20260324122000_create_orders_table.rb└── 20260325091500_add_email_index_to_users.sql

Notice the mix of .sql and .rb files. Both types can coexist in the same project. The create_orders_table.rb file contains both up and down methods, so it does not need a separate down file.

The last SQL migration might look like:

-- migrations/20260325091500_add_email_index_to_users.sqlCREATE INDEX idx_users_email ON users (email);

With its down file:

-- migrations/20260325091500_add_email_index_to_users.down.sqlDROP INDEX IF EXISTS idx_users_email;

Migration Tracking#

Migrations apply in numeric-prefix order -- the leading number drives the sort, so 9_create_x.sql runs before 10_create_y.sql (a plain alphabetical sort would put 10 first). Files without a numeric or timestamp prefix sort last and log a warning, since their order is undefined. Each migration file is wrapped in its own transaction. Per-file atomicity is real only on engines with transactional DDL (PostgreSQL): there a failed statement rolls the whole file back. MySQL, Firebird, and SQLite auto-commit DDL, so a half-applied file leaves its earlier statements in place. Keep one logical change per file. A failed migration stops the run and raises -- already-applied files stay applied; fix the SQL and re-run.

CREATE TABLE and ALTER TABLE ... ADD are idempotent on Firebird and MSSQL, which lack IF NOT EXISTS: the runner checks whether the table or column already exists and skips that statement on a re-run instead of erroring. Only a genuine already-exists is skipped -- every other error still raises. SQLite, MySQL, and PostgreSQL use native IF NOT EXISTS.

Each migration runs once. Tina4 tracks applied migrations in a tina4_migration table with these columns:

A migration counts as applied when a row exists for it with passed = 1. The runner writes only passed = 1 rows: a failed file rolls back and writes no row, so the next run retries it. The batch system is what makes rollback work: migrate:rollback undoes all migrations in the highest batch number.

11. Query Caching#

For read-heavy applications, enable query caching:

TINA4_DB_CACHE=true

When enabled, Tina4 caches the results of fetch and fetch_one calls. Identical queries with identical parameters return cached results instead of hitting the database again.

The cache is automatically invalidated when you call execute, insert, update, or delete on the same table.

You can also control caching per-query:

# Force a fresh query (bypass cache)products = db.fetch("SELECT * FROM products", [], false) # third arg = use cache​# Clear the entire cachedb.cache_clear

12. Exercise: Build a Notes App#

Build a notes application backed by SQLite. Create the database table via a migration and build a full CRUD API.

Requirements#

1. Create a migration that creates a notes table with columns:
   • id -- integer, primary key, auto-increment
   • title -- text, not null
   • content -- text, not null
   • tag -- text, default "general"
   • created_at -- text, default current timestamp
   • updated_at -- text, default current timestamp
2. Build these API endpoints:

Test with:#

# Createcurl -X POST http://localhost:7147/api/notes \  -H "Content-Type: application/json" \  -d '{"title": "Shopping List", "content": "Milk, eggs, bread", "tag": "personal"}'​# List allcurl http://localhost:7147/api/notes​# Searchcurl "http://localhost:7147/api/notes?search=shopping"​# Filter by tagcurl "http://localhost:7147/api/notes?tag=personal"​# Get onecurl http://localhost:7147/api/notes/1​# Updatecurl -X PUT http://localhost:7147/api/notes/1 \  -H "Content-Type: application/json" \  -d '{"title": "Updated Shopping List", "content": "Milk, eggs, bread, butter"}'​# Deletecurl -X DELETE http://localhost:7147/api/notes/1

13. Solution#

Migration#

Generate the migration:

tina4 generate migration create_notes_table

Edit migrations/20260324120000_create_notes_table.sql:

CREATE TABLE notes (    id INTEGER PRIMARY KEY AUTOINCREMENT,    title TEXT NOT NULL,    content TEXT NOT NULL,    tag TEXT NOT NULL DEFAULT 'general',    created_at TEXT DEFAULT CURRENT_TIMESTAMP,    updated_at TEXT DEFAULT CURRENT_TIMESTAMP);

Edit migrations/20260324120000_create_notes_table.down.sql:

DROP TABLE IF EXISTS notes;

Run the migration:

tina4 migrate

Running migrations...  [APPLIED] 20260322143000_create_notes_table.sqlMigrations complete. 1 applied.

Routes#

Create src/routes/notes.rb:

# List all notes with optional filtersTina4::Router.get("/api/notes") do |request, response|  db = Tina4.database​  tag = request.params["tag"] || ""  search = request.params["search"] || ""​  sql = "SELECT * FROM notes"  params = []  conditions = []​  unless tag.empty?    conditions << "tag = ?"    params << tag  end​  unless search.empty?    conditions << "(title LIKE ? OR content LIKE ?)"    params << "%#{search}%"    params << "%#{search}%"  end​  sql += " WHERE #{conditions.join(' AND ')}" unless conditions.empty?  sql += " ORDER BY updated_at DESC"​  notes = db.fetch(sql, params)​  response.json({    notes: notes,    count: notes.length  })end​# Get a single noteTina4::Router.get("/api/notes/{id:int}") do |request, response|  db = Tina4.database  id = request.params["id"]​  note = db.fetch_one("SELECT * FROM notes WHERE id = ?", [id])​  if note.nil?    response.json({ error: "Note not found", id: id }, 404)  else    response.json(note)  endend​# Create a noteTina4::Router.post("/api/notes") do |request, response|  db = Tina4.database  body = request.body​  # Validate  errors = []  errors << "Title is required" if body["title"].nil? || body["title"].empty?  errors << "Content is required" if body["content"].nil? || body["content"].empty?​  unless errors.empty?    return response.json({ errors: errors }, 400)  end​  db.execute(    "INSERT INTO notes (title, content, tag) VALUES (?, ?, ?)",    [body["title"], body["content"], body["tag"] || "general"]  )​  note = db.fetch_one("SELECT * FROM notes WHERE id = last_insert_rowid()")​  response.json(note, 201)end​# Update a noteTina4::Router.put("/api/notes/{id:int}") do |request, response|  db = Tina4.database  id = request.params["id"]  body = request.body​  existing = db.fetch_one("SELECT * FROM notes WHERE id = ?", [id])​  if existing.nil?    return response.json({ error: "Note not found", id: id }, 404)  end​  db.execute(    "UPDATE notes SET title = ?, content = ?, tag = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",    [      body["title"] || existing["title"],      body["content"] || existing["content"],      body["tag"] || existing["tag"],      id    ]  )​  note = db.fetch_one("SELECT * FROM notes WHERE id = ?", [id])​  response.json(note)end​# Delete a noteTina4::Router.delete("/api/notes/{id:int}") do |request, response|  db = Tina4.database  id = request.params["id"]​  existing = db.fetch_one("SELECT * FROM notes WHERE id = ?", [id])​  if existing.nil?    return response.json({ error: "Note not found", id: id }, 404)  end​  db.execute("DELETE FROM notes WHERE id = ?", [id])​  response.json(nil, 204)end

Expected output for create:

{  "id": 1,  "title": "Shopping List",  "content": "Milk, eggs, bread",  "tag": "personal",  "created_at": "2026-03-22 14:30:00",  "updated_at": "2026-03-22 14:30:00"}

(Status: 201 Created)

Expected output for list:

{  "notes": [    {      "id": 1,      "title": "Shopping List",      "content": "Milk, eggs, bread",      "tag": "personal",      "created_at": "2026-03-22 14:30:00",      "updated_at": "2026-03-22 14:30:00"    }  ],  "count": 1}

Expected output for search:

{  "notes": [    {      "id": 1,      "title": "Shopping List",      "content": "Milk, eggs, bread",      "tag": "personal",      "created_at": "2026-03-22 14:30:00",      "updated_at": "2026-03-22 14:30:00"    }  ],  "count": 1}

Expected output for validation error:

{"errors": ["Title is required", "Content is required"]}

(Status: 400 Bad Request)

14. Seeder -- Generating Test Data#

Testing with an empty database tells you nothing. Testing with hand-typed rows is slow and brittle. The FakeData class generates realistic test data. seed_orm and seed_table insert it in bulk.

FakeData#

fake = Tina4::FakeData.new​fake.name        # "Grace Lopez"fake.email       # "bob.anderson123@demo.net"fake.phone       # "+1 (547) 382-9104"fake.sentence    # "Magna exercitation lorem ipsum dolor sit."fake.paragraph   # Three sentences of filler textfake.integer     # 7342fake.numeric     # 481.29fake.date        # "2023-07-14"fake.uuid        # "a3f1b2c4-d5e6-f7a8-b9c0-d1e2f3a4b5c6"fake.address     # "742 Oak Avenue"fake.boolean     # 0 or 1fake.city        # "Tokyo"fake.country     # "Germany"fake.company     # "TechGlobal Inc"fake.url         # "https://example.com/some-random-slug"fake.password    # "xK9mPq2nRt5vWz8a"

Every method draws from built-in word banks -- no network calls, no external packages.

Deterministic Output#

Pass a seed to get reproducible results. The same seed produces the same sequence every time:

fake = Tina4::FakeData.new(seed: 42)fake.name   # Always the same name with seed 42fake.email  # Always the same email with seed 42

Deterministic data means deterministic assertions. This matters for tests.

Seeding with ORM#

seed_orm combines FakeData with your ORM models. It reads the model's field definitions and generates appropriate data for each column:

Tina4.seed_orm(User, count: 100)

This inserts 100 rows into the users table. FakeData inspects each column name and type to generate matching data -- email columns get email addresses, phone columns get phone numbers, name columns get names.

Overrides#

Static values or custom generators apply through the overrides hash:

Tina4.seed_orm(User, count: 50, overrides: {  role: "member",  active: 1,  email: ->(fake) { fake.email }})

Every row gets role = "member" and active = 1. Lambda values receive the FakeData instance for custom generation.

Seeding a Raw Table#

When you have no ORM class, use seed_table with a column map:

Tina4.seed_table("users", {  name: :string,  email: :string,  phone: :string,  bio: :text,  age: :integer}, count: 100)

The column types tell FakeData what kind of data to generate for each field.

Batch Seeding#

Seed multiple tables in order with seed_batch. Pass clear: true to delete existing records first (in reverse order, respecting foreign keys):

Tina4.seed_batch([  { orm_class: User, count: 20 },  { orm_class: Order, count: 100, overrides: { status: "pending" } }], clear: true)

Seed Files#

Place seed scripts in a seeds/ folder. Each file is a Ruby script that runs seed_orm or seed_table:

# seeds/001_users.rbTina4.seed_orm(User, count: 50, seed: 1)​# seeds/002_orders.rbTina4.seed_orm(Order, count: 200, overrides: {  status: ->(f) { f.choice(%w[pending shipped delivered]) }})

Run all seed files:

Tina4.seed(seed_folder: "seeds")

Files run in alphabetical order. Prefix with numbers to control sequence. Files starting with _ are skipped.

When to Use It#

• Populating a development database with realistic data
• Writing integration tests that need rows in the database
• Load testing with thousands of records
• Demos and screenshots that look real without using real data

15. Gotchas#

1. Forgetting the transaction block#

Problem: You run multiple related queries but they are not atomic -- some succeed while others fail, leaving the database in an inconsistent state.

Cause: Without wrapping them in a transaction block, each query is committed independently.

Fix: Use db.transaction { |tx| ... } to wrap related operations. The block automatically commits on success and rolls back on any exception.

2. Connection String Formats#

Problem: The database will not connect and you see a cryptic error about the connection string.

Cause: Each database engine expects a specific URL format. A common mistake is using mysql://user:pass@host/db when the engine expects the port.

Fix: Always include the port: mysql://localhost:3306/mydb. Here are the default ports:

3. SQLite File Paths#

Problem: SQLite creates a new empty database instead of using the existing one.

Cause: The path in TINA4_DATABASE_URL is relative and resolves to the wrong directory, or you used sqlite:// (two slashes) instead of sqlite:/// (three slashes).

Fix: Use three slashes for a relative path: sqlite:///data/app.db. For an absolute path, use four slashes: sqlite:////var/data/app.db. The third slash separates the scheme from the path; the fourth starts the absolute path.

4. Parameterised Queries with LIKE#

Problem: WHERE name LIKE ? with ["%search%"] works, but WHERE name LIKE '%?%' does not.

Cause: Parameters inside quotes are treated as literal text, not as placeholders.

Fix: Include the % wildcards in the parameter value, not in the SQL: ["%#{search}%"]. The SQL should be WHERE name LIKE ?.

5. Boolean Values in SQLite#

Problem: You insert true or false but the database stores 1 or 0. When you read it back, you get integers, not booleans.

Cause: SQLite does not have a native boolean type. It stores booleans as integers.

Fix: Cast in your Ruby code: row["in_stock"] == 1 or use !!row["in_stock"] to convert to a boolean. Ruby treats 0 as truthy (unlike some other languages), so be explicit with comparisons.

6. Migration Already Applied#

Problem: You edited a migration file and ran tina4 migrate again, but nothing changed.

Cause: Tina4 tracks applied migrations by filename in the tina4_migration table. Once applied, a migration will not run again even if you change its contents.

Fix: Create a new migration for schema changes. Do not edit applied migrations. If you are in early development and want to start fresh, use tina4ruby migrate:rollback to undo the last batch and then tina4 migrate to reapply. Use tina4ruby migrate:status to see which migrations are applied and which are pending.

7. fetch Returns Empty Array, Not Nil#

Problem: You check if result.nil? but it never matches, even when the table is empty.

Cause: fetch always returns an array. An empty result is [], not nil. Only fetch_one returns nil when no row matches.

Fix: Check with if result.empty? or if result.length == 0.

8. SQL Injection Through String Interpolation#

Problem: Your application is vulnerable to SQL injection attacks.

Cause: You used string interpolation to build SQL queries with user input: "WHERE name = '#{name}'".

Fix: Use parameterised queries: "WHERE name = ?", [name]. This is the single most important security practice for database code. Tina4 handles escaping and quoting for you.