SqlKit

Hex | GitHub | Documentation

Execute raw SQL in strings or .sql files, get maps and structs back. Built on top of ecto_sql.

SqlKit provides two ways to execute SQL with automatic result transformation:

1. Direct SQL execution - Execute SQL strings directly with any Ecto repo
2. File-based SQL - Keep SQL in dedicated files with compile-time embedding

Why?

Sometimes raw SQL is the right tool for the job. Complex analytical queries, reports with intricate joins, or database-specific features often demand SQL that's awkward to express through an ORM.

You can do this already with Repo.query, however this returns a result struct with separate columns and rows lists. SqlKit handles this for you, returning maps [%{id: 1, name: "Alice"}, ...] or structs [%User{id: 1, name: "Alice"}, ...] directly.

For file-based SQL, keeping queries in .sql files brings other practical benefits like syntax highlighting, and SQL formatter support. It also makes your codebase more accessible to SQL-fluent team members who can read, review, and contribute queries without needing to learn Elixir first. How .sql files are loaded is configurable by environment: Reading from disk in development for fast iteration, and embedding at compile time in production to eliminate unnecessary I/O.

Features

• Just SQL: No DSL or special syntax to learn.
• Automatic result transformation: Query results returned as maps or structs, not raw columns/rows
• Two APIs: Execute SQL strings directly or load from files
• Compile-time embedding: File-based SQL read once at compile time and stored as module attributes
• Dynamic loading in dev/test: Edit SQL files without recompiling
• Multi-database support: Works with PostgreSQL, MySQL/MariaDB, SQLite, SQL Server, and ClickHouse

Supported Databases

Installation

Add sql_kit to your dependencies in mix.exs:

def deps do
  [
    {:sql_kit, "~> 0.1.0"}
  ]
end

Quick Start

Direct SQL Execution

Execute SQL strings directly with any Ecto repo:

# Get all rows as a list of maps
SqlKit.query_all!(MyApp.Repo, "SELECT * FROM users WHERE age > $1", [21])
# => [%{id: 1, name: "Alice", age: 30}, %{id: 2, name: "Bob", age: 25}]

# Get a single row
SqlKit.query_one!(MyApp.Repo, "SELECT * FROM users WHERE id = $1", [1])
# => %{id: 1, name: "Alice", age: 30}

# Cast results to structs
SqlKit.query_all!(MyApp.Repo, "SELECT * FROM users", [], as: User)
# => [%User{id: 1, name: "Alice", age: 30}, ...]

# Non-bang variants return {:ok, result} or {:error, reason}
SqlKit.query_one(MyApp.Repo, "SELECT * FROM users WHERE id = $1", [1])
# => {:ok, %{id: 1, name: "Alice"}}

# ClickHouse uses named parameters as a map
SqlKit.query_all!(ClickHouseRepo, "SELECT * FROM users WHERE age > {age:UInt32}", %{age: 21})
# => [%{id: 1, name: "Alice", age: 30}, ...]

File-Based SQL

For larger queries or better organization, keep SQL in dedicated files:

1. Create SQL files

SQL files are housed in subdirectories under the root SQL directory. This is priv/repo/sql by default but is configurable via :root_sql_dir config option. The priv/ directory is recommended because these files are included in Mix releases by default.

-- priv/repo/sql/reports/stats.sql
SELECT id, name, total_sales
FROM users
WHERE id = $1

2. Define a SQL module

defmodule MyApp.Reports.SQL do
  use SqlKit,
    otp_app: :my_app,
    repo: MyApp.Repo,
    dirname: "reports",
    files: ["stats.sql", "activity.sql"]
end

3. Execute queries

# Get a single row as a map
MyApp.Reports.SQL.query_one!("stats.sql", [user_id])
# => %{id: 1, name: "Alice", total_sales: 1000}

# You can also use query!/3, which is an alias for query_one!/3
MyApp.Reports.SQL.query!("stats.sql", [user_id])
# => %{id: 1, name: "Alice", total_sales: 1000}

# Get all rows
MyApp.Reports.SQL.query_all!("activity.sql", [company_id])
# => [%{id: 1, ...}, %{id: 2, ...}]

# Cast results to structs
MyApp.Reports.SQL.query_one!("stats.sql", [id], as: UserStats)
# => %UserStats{id: 1, name: "Alice", total_sales: 1000}

# Load the raw SQL string
MyApp.Reports.SQL.load!("stats.sql")
# => "SELECT id, name, total_sales..."

Configuration

# config/config.exs
config :my_app, SqlKit,
  root_sql_dir: "priv/repo/sql"  # default

# config/dev.exs and config/test.exs
config :my_app, SqlKit,
  load_sql: :dynamic  # read from disk at runtime

# config/prod.exs (or rely on default)
config :my_app, SqlKit,
  load_sql: :compiled  # use compile-time embedded SQL

Parameter Syntax by Database

Each database uses different parameter placeholder syntax:

ClickHouse Named Parameters

ClickHouse uses named parameters with explicit types. Pass parameters as a map:

# SQL file: user_by_id.sql
# SELECT * FROM users WHERE id = {id:UInt32}

ClickHouseSQL.query_one!("user_by_id.sql", %{id: 1})

Named Parameters for Other Databases

For databases using positional parameters, wrap SqlKit calls in functions to get named parameter ergonomics:

# SQL string
defmodule MyApp.Users do
  alias MyApp.Users.User

  def get_active_users(company_id, min_age) do
    SqlKit.query_all!(MyApp.Repo, """
      SELECT id, name, email, age
      FROM users
      WHERE company_id = $1
        AND age >= $2
        AND active = true
      ORDER BY name
    """, [company_id, min_age], as: User)
  end
end

# SQL file
defmodule MyApp.Users do
  alias MyApp.Users.User

  def get_active_users(company_id, min_age) do
    MyApp.Users.SQL.query_all!("active_users.sql", [company_id, min_age], as: User)
  end
end

# Usage
MyApp.Users.get_active_users(123, 21)
# => [%User{id: 1, name: "Alice", email: "alice@example.com", age: 30}, ...]

This pattern gives you named parameters through Elixir function arguments while keeping queries as plain SQL.

Use SqlKit Options

• :otp_app (required) - Your application name
• :repo (required) - The Ecto repo module to use for queries
• :dirname (required) - Subdirectory within root_sql_dir for this module's SQL files
• :files (required) - List of SQL filenames to load

API Reference

Standalone Functions

These functions are defined directly on the SqlKit module and work with any Ecto repo:

SqlKit.query_all!(repo, sql, params \\ [], opts \\ [])

Executes SQL and returns all rows as a list of maps.

SqlKit.query_all!(MyApp.Repo, "SELECT * FROM users")
# => [%{id: 1, name: "Alice"}, %{id: 2, name: "Bob"}]

SqlKit.query_all!(MyApp.Repo, "SELECT * FROM users WHERE age > $1", [21], as: User)
# => [%User{id: 1, name: "Alice"}, ...]

# ClickHouse uses named parameters as a map
SqlKit.query_all!(ClickHouseRepo, "SELECT * FROM users WHERE age > {age:UInt32}", %{age: 21})
# => [%{id: 1, name: "Alice"}, ...]

SqlKit.query_one!(repo, sql, params \\ [], opts \\ [])

Executes SQL and returns exactly one row as a map.

• Raises SqlKit.NoResultsError if no rows returned
• Raises SqlKit.MultipleResultsError if more than one row returned

SqlKit.query_one!(MyApp.Repo, "SELECT * FROM users WHERE id = $1", [1])
# => %{id: 1, name: "Alice"}

SqlKit.query_one!(MyApp.Repo, "SELECT * FROM users WHERE id = $1", [1], as: User)
# => %User{id: 1, name: "Alice"}

# ClickHouse uses named parameters as a map
SqlKit.query_one!(ClickHouseRepo, "SELECT * FROM users WHERE id = {id:UInt32}", %{id: 1})
# => %{id: 1, name: "Alice"}

SqlKit.query!(repo, sql, params \\ [], opts \\ [])

Alias for SqlKit.query_one!/4. See SqlKit.query_one!/4 documentation.

SqlKit.query_all(repo, sql, params \\ [], opts \\ [])

Returns {:ok, results} on success, {:error, exception} on failure.

SqlKit.query_all(MyApp.Repo, "SELECT * FROM users")
# => {:ok, [%{id: 1, name: "Alice"}, ...]}

# ClickHouse uses named parameters as a map
SqlKit.query_all(ClickHouseRepo, "SELECT * FROM users WHERE age > {age:UInt32}", %{age: 21})
# => {:ok, [%{id: 1, name: "Alice"}, ...]}

SqlKit.query_one(repo, sql, params \\ [], opts \\ [])

Returns {:ok, result} on one result, {:ok, nil} on no results, or {:error, exception} on multiple results or errors.

SqlKit.query_one(MyApp.Repo, "SELECT * FROM users WHERE id = $1", [1])
# => {:ok, %{id: 1, name: "Alice"}}

SqlKit.query_one(MyApp.Repo, "SELECT * FROM users WHERE id = $1", [999])
# => {:ok, nil}

# ClickHouse uses named parameters as a map
SqlKit.query_one(ClickHouseRepo, "SELECT * FROM users WHERE id = {id:UInt32}", %{id: 1})
# => {:ok, %{id: 1, name: "Alice"}}

SqlKit.query(repo, sql, params \\ [], opts \\ [])

Alias for SqlKit.query_one/4. See SqlKit.query_one/4 documentation.

File-Based Functions

These functions are generated by use SqlKit and available on your SQL modules:

query_one!(filename, params \\ [], opts \\ [])

Executes a query and returns a single row as a map.

• Raises SqlKit.NoResultsError if no rows returned
• Raises SqlKit.MultipleResultsError if more than one row returned

SQL.query_one!("user.sql", [user_id])
# => %{id: 1, name: "Alice"}

SQL.query_one!("user.sql", [user_id], as: User)
# => %User{id: 1, name: "Alice"}

# ClickHouse uses named parameters as a map
ClickHouseSQL.query_one!("user.sql", %{user_id: 1})
# => %{id: 1, name: "Alice"}

query!(filename, params \\ [], opts \\ [])

Alias for query_one!/3. See query_one!/3 documentation.

query_all!(filename, params \\ [], opts \\ [])

Executes a query and returns all rows as a list of maps.

SQL.query_all!("users.sql", [company_id])
# => [%{id: 1, name: "Alice"}, %{id: 2, name: "Bob"}]

SQL.query_all!("users.sql", [company_id], as: User)
# => [%User{id: 1, name: "Alice"}, %User{id: 2, name: "Bob"}]

# ClickHouse uses named parameters as a map
ClickHouseSQL.query_all!("users.sql", %{company_id: 123})
# => [%{id: 1, name: "Alice"}, %{id: 2, name: "Bob"}]

load!(filename)

Returns the SQL string for the given file.

SQL.load!("users.sql")
# => "SELECT * FROM users"

query_one(filename, params \\ [], opts \\ [])

SQL.query_one("user.sql", [user_id])
# => {:ok, %{id: 1, name: "Alice"}}

SQL.query_one("missing_user.sql", [999])
# => {:ok, nil}  # No results returns nil, not an error

SQL.query_one("all_users.sql", [])
# => {:error, %SqlKit.MultipleResultsError{count: 10}}

# ClickHouse uses named parameters as a map
ClickHouseSQL.query_one("user.sql", %{user_id: 1})
# => {:ok, %{id: 1, name: "Alice"}}

query(filename, params \\ [], opts \\ [])

Alias for query_one/3. See query_one/3 documentation.

query_all(filename, params \\ [], opts \\ [])

SQL.query_all("users.sql", [company_id])
# => {:ok, [%{id: 1, name: "Alice"}, ...]}

# ClickHouse uses named parameters as a map
ClickHouseSQL.query_all("users.sql", %{company_id: 123})
# => {:ok, [%{id: 1, name: "Alice"}, ...]}

load(filename)

SQL.load("users.sql")
# => {:ok, "SELECT * FROM users"}

Options

• :as - Struct module to cast results into
• :unsafe_atoms - If true, uses String.to_atom/1 instead of String.to_existing_atom/1 for column names. Default: false
• :query_name - Custom identifier for exceptions (standalone API only; defaults to truncated SQL)

Contributing

Prerequisites

• ASDF (Elixir + Erlang version management)
• Docker (for database containers via docker compose up)
• SQLite3 (installed locally)

Setup

1. Clone the repository

2. Run asdf install

3. Install dependencies and compile:

   mix do deps.get, deps.compile, compile
   

4. Start the database containers:

   docker compose up
   

5. Run the tests:

   mix test
   

The test suite runs against all supported databases (PostgreSQL, MySQL, SQLite, SQL Server, and ClickHouse). All databases must be running for the full test suite to pass.

Database Ports

• PostgreSQL: 5432
• MySQL: 3306
• SQL Server: 1433
• ClickHouse: 8123, 9000

SQLite uses a local file and doesn't require Docker.

Before Pull Request

Run mix check.

License

MIT License. See LICENSE.md for details.