Tren

Use SQL files as first-class Crystal methods.

Tren reads SQL files at compile time and generates native Crystal methods from them.
You keep SQL in .sql files, then call it like regular Crystal code.

Why Tren?

• Write SQL where it belongs: in SQL files.
• Keep typed method signatures in metadata.
• Get Crystal overload checks at compile time.
• Compose SQL snippets without repeating yourself.

30-Second Example

Create queries/users.sql:

-- name: get_users(name : String, age : Int32)
SELECT * FROM users WHERE name = '{{ name }}' AND age = {{ age }}

Load and call it:

require "tren"

Tren.load("./queries/*.sql")

sql = get_users("john", 42)
# => "SELECT * FROM users WHERE name = 'john' AND age = 42"

That method (get_users) is generated by Tren during compilation.

Installation

Add this to your shard.yml:

dependencies:
  tren:
    github: sdogruyol/tren

Then install dependencies:

shards install

Using with crystal-db

Tren generates plain SQL strings. Any driver built on crystal-db can run them with DB#query, DB#exec, DB#scalar, and friends—no special adapter is required.

Add db plus a driver (SQLite, PostgreSQL, MySQL, etc.) to shard.yml:

dependencies:
  tren:
    github: sdogruyol/tren
  db:
    github: crystal-lang/crystal-db
  sqlite3:
    github: crystal-lang/crystal-sqlite3

Example SQL (queries/users.sql):

-- name: users_named(name : String)
SELECT id, name FROM users WHERE name = '{{ name }}'

-- name: insert_user(name : String, age : Int32)
INSERT INTO users (name, age) VALUES ('{{ name }}', {{ age }})

Example app code:

require "db"
require "sqlite3"
require "tren"

Tren.load("./queries/*.sql")

struct User
  include DB::Serializable

  property id : Int32
  property name : String
end

DB.open "sqlite3://./data.db" do |db|
  # Many rows, mapped with DB::Serializable
  rows = db.query_all users_named("Ada"), as: User

  # Iterate without a mapping type
  db.query users_named("Ada") do |rs|
    rs.each do
      puts "#{rs.read(Int32)} — #{rs.read(String)}"
    end
  end

  # Statements that do not return rows
  db.exec insert_user("Bob", 40)
end

With PostgreSQL, the pattern is the same: require "pg", then DB.open "postgres://user:pass@localhost/dbname" do |db| ... end.

Note: Tren builds the final SQL at compile time / call time with its own escaping. That is separate from crystal-db’s ? placeholders. For untrusted input, rely on Tren’s {{ x }} escaping (see Security Notes) or use prepared statements and raw SQL where appropriate.

SQL File Format

Each query must start with metadata:

-- name: method_name(arg : Type, ...)

After that line, write the SQL body:

-- name: find_user(id : Int32)
SELECT * FROM users WHERE id = {{ id }}

Parameter Rules

• {{ value }}: escaped parameter (default, safer).
• {{! value }}: raw parameter (not escaped).

-- name: by_name(name : String)
SELECT * FROM users WHERE name = '{{ name }}'

-- name: with_clause(clause : String)
SELECT * FROM users {{! clause }}

Overloading

Multiple SQL entries can share the same method name with different signatures:

-- name: get_users(name : String, surname : String)
SELECT * FROM users WHERE name = '{{ name }}' AND surname = '{{ surname }}'

-- name: get_users(name : String, age : Int32)
SELECT * FROM users WHERE name = '{{ name }}' AND age = {{ age }}

Crystal resolves overloads and reports errors if arguments do not match.

Composing Queries

You can reuse generated SQL methods to build larger queries:

-- name: filter_user(name : String, surname : String)
WHERE name = '{{ name }}' AND surname = '{{ surname }}'

-- name: get_users(name : String, surname : String)
SELECT * FROM users {{! filter_user(name, surname) }}

Escaping Behavior

String parameters are escaped by default.
Non-string values are passed through as-is.

You can customize the escape behavior:

Tren.escape_character = "\\"
# => escapes both quotes and backslashes with a backslash prefix (default)

Tren.escape_character = "\\'"
# => PostgreSQL-style single-quote escaping ("I'm" => "I''m")

Security Notes

• Prefer {{ value }} over raw interpolation.
• Use {{! ... }} only for trusted SQL fragments.
• If your driver supports prepared statements, prefer them for user input.

Error Messages

Tren now fails with clearer parse errors (including file and line) for invalid metadata or malformed placeholders.

Expected metadata format:

-- name: method_name(args)

Development

Run tests:

crystal spec

Run format check:

crystal tool format --check src spec

Contributing

1. Fork it (github.com/sdogruyol/tren/fork)
2. Create your feature branch (git checkout -b my-new-feature)
3. Commit your changes (git commit -am "Add some feature")
4. Push to the branch (git push origin my-new-feature)
5. Open a Pull Request

Contributors

• f Fatih Kadir Akın - creator, maintainer
• sdogruyol Serdar Doğruyol - creator, maintainer

Built on a TREN from Ankara to Istanbul.