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

# Direct SQL execution
defmodule MyApp.Accounts do
  alias MyApp.Accounts.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

# File-based SQL
defmodule MyApp.Accounts.SQL do
  use SqlKit,
    otp_app: :my_app,
    repo: MyApp.Repo,
    dirname: "accounts",
    files: ["active_users.sql", "another_query.sql"]
end

defmodule MyApp.Accounts do
  alias MyApp.Accounts.SQL
  alias MyApp.Accounts.User

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

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

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, ClickHouse, and DuckDB

Supported Databases

Installation

Add sql_kit to your dependencies in mix.exs:

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

For DuckDB support, also add duckdbex:

def deps do
  [
    {:sql_kit, "~> 0.2.0"},
    {:duckdbex, "~> 0.3"}
  ]
end

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

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 (raises if no results)
SqlKit.query_one!(MyApp.Repo, "SELECT * FROM users WHERE id = $1", [1])
# => %{id: 1, name: "Alice", age: 30}

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

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

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

# 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 (raises if no results)
MyApp.Reports.SQL.query_one!("stats.sql", [user_id])
# => %{id: 1, name: "Alice", total_sales: 1000}

# Get a single row or nil (raises on multiple results)
MyApp.Reports.SQL.query_one("stats.sql", [user_id])
# => %{id: 1, name: "Alice", total_sales: 1000}

# You can also use query!/3 and query/3, which are aliases for query_one!/3 and 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..."

DuckDB

DuckDB is a high-performance analytical database. Unlike other supported databases, DuckDB is not an Ecto adapter—SqlKit provides direct integration via the duckdbex driver.

Direct Connection

For scripts, one-off analysis, or simple use cases:

# In-memory database
{:ok, conn} = SqlKit.DuckDB.connect(":memory:")
SqlKit.query_all(conn, "SELECT 1 as num", [])
# => [%{num: 1}]
SqlKit.DuckDB.disconnect(conn)

# File-based database
{:ok, conn} = SqlKit.DuckDB.connect("analytics.duckdb")
SqlKit.query_all(conn, "SELECT * FROM events", [])
SqlKit.DuckDB.disconnect(conn)

# With custom configuration
{:ok, conn} = SqlKit.DuckDB.connect("analytics.duckdb",
  config: %Duckdbex.Config{threads: 4})

Pooled Connection (Recommended for Production)

For production use, add the pool to your supervision tree:

# In your application.ex
def start(_type, _args) do
  children = [
    # ... other children
    {SqlKit.DuckDB.Pool,
      name: MyApp.AnalyticsPool,
      database: "priv/analytics.duckdb",
      pool_size: 4}
  ]

  Supervisor.start_link(children, strategy: :one_for_one)
end

Pool options:

• :name - Required. The name to register the pool under
• :database - Required. Path to database file or ":memory:"
• :pool_size - Number of connections. Default: 4
• :config - Optional Duckdbex.Config struct for advanced configuration (threads, memory limits, etc.)

Then query using the pool:

pool = SqlKit.DuckDB.Pool.pool(MyApp.AnalyticsPool)
SqlKit.query_all(pool, "SELECT * FROM events WHERE date > $1", [~D[2024-01-01]])
# => [%{id: 1, date: ~D[2024-01-15], ...}, ...]

File-Based SQL with DuckDB

Use the :backend option instead of :repo:

defmodule MyApp.Analytics.SQL do
  use SqlKit,
    otp_app: :my_app,
    backend: {:duckdb, pool: MyApp.AnalyticsPool},
    dirname: "analytics",
    files: ["daily_summary.sql", "user_activity.sql"]
end

# Usage
MyApp.Analytics.SQL.query_all("daily_summary.sql", [~D[2024-01-01]])

Loading Extensions

DuckDB extensions (Parquet, JSON, HTTPFS, etc.) are loaded via SQL:

pool = SqlKit.DuckDB.Pool.pool(MyApp.AnalyticsPool)
SqlKit.query_one!(pool, "INSTALL 'parquet';", [])
SqlKit.query_one!(pool, "LOAD 'parquet';", [])
SqlKit.query_all(pool, "SELECT * FROM 'data.parquet'", [])

Streaming Large Results

For memory-efficient processing of large result sets:

# Direct connection streaming
conn
|> SqlKit.DuckDB.stream!("SELECT * FROM large_table", [])
|> Stream.flat_map(& &1)
|> Enum.take(100)

# Pool streaming (callback-based)
SqlKit.DuckDB.Pool.with_stream!(pool, "SELECT * FROM events", [], fn stream ->
  stream |> Stream.flat_map(& &1) |> Enum.count()
end)

# File-based SQL streaming (DuckDB backends only)
MyApp.Analytics.SQL.with_stream!("large_query.sql", [], fn stream ->
  stream |> Stream.flat_map(& &1) |> Enum.take(1000)
end)

Pool Options

Pool operations accept these options:

• :timeout - Checkout timeout in milliseconds (default: 5000)
• :cache - Enable prepared statement caching (default: true)

SqlKit.DuckDB.Pool.query!(pool, sql, params, timeout: 10_000, cache: false)

Key Differences from Ecto-Based Databases

• Uses PostgreSQL-style $1, $2, ... parameter placeholders
• In-memory database: use ":memory:" string (not :memory atom)
• Pool uses NimblePool (connections share one database instance)
• Pool automatically caches prepared statements for repeated queries
• Hugeint values are automatically converted to Elixir integers
• Date/Time values are returned as tuples (e.g., {2024, 1, 15} for dates)

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.Accounts do
  alias MyApp.Accounts.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.Accounts do
  alias MyApp.Accounts.SQL # `use SqlKit` module
  alias MyApp.Accounts.User

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

# Usage
MyApp.Accounts.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 - The Ecto repo module to use for queries (required unless :backend is specified)
• :backend - Alternative to :repo for non-Ecto databases. Supports {:duckdb, pool: PoolName}
• :dirname (required) - Subdirectory within root_sql_dir for this module's SQL files
• :files (required) - List of SQL filenames to load

Note: You must specify either :repo or :backend, but not both.

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. Raises on query execution errors. Matches Ecto.Repo.all/2 semantics.

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

Matches Ecto.Repo.one!/2 semantics.

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_one(repo, sql, params \\ [], opts \\ [])

Executes SQL and returns one row or nil. Raises on query execution errors or multiple results. Matches Ecto.Repo.one/2 semantics.

• Returns result on exactly one result
• Returns nil on no results
• 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", [999])
# => nil

# 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.

File-Based Functions

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

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

Executes a query and returns all rows as a list of maps. Raises on query execution errors. Matches Ecto.Repo.all/2 semantics.

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"}]

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_one(filename, params \\ [], opts \\ [])

Executes a query and returns one row or nil. Raises on query execution errors or multiple results. Matches Ecto.Repo.one/2 semantics.

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

SQL.query_one("missing_user.sql", [999])
# => nil  # No results returns nil

# Raises SqlKit.MultipleResultsError on multiple results
SQL.query_one("all_users.sql", [])

# 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.

load!(filename)

Returns the SQL string for the given file.

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

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, ClickHouse, and DuckDB). 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 and DuckDB use local files/memory and don't require Docker.

Before Pull Request

Run mix check.

License

MIT License. See LICENSE.md for details.