SqlKit (sql_kit v0.2.0)

View Source

Execute raw SQL in strings or .sql files, get maps and structs back.

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

Direct SQL Execution

Execute SQL strings directly with any Ecto repo:

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

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

File-Based SQL

For larger queries, keep SQL in dedicated files with compile-time embedding:

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

MyApp.Reports.SQL.query_one!("stats.sql", [report_id])

DuckDB File-Based SQL

For DuckDB, use the :backend option instead of :repo. The pool must be started in your supervision tree with the database configuration:

# In your application.ex supervision tree:
children = [
  {SqlKit.DuckDB.Pool,
    name: MyApp.AnalyticsPool,
    database: "priv/analytics.duckdb",
    pool_size: 4}
]

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

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

Supported Databases

Any Ecto adapter returning a result map containing rows and columns should work. The test suite covers the following adapters:

DatabaseEcto AdapterDriver
PostgreSQLEcto.Adapters.PostgresPostgrex
SQLiteEcto.Adapters.SQLite3Exqlite
MySQLEcto.Adapters.MyXQLMyXQL
MariaDBEcto.Adapters.MyXQLMyXQL
SQL ServerEcto.Adapters.TdsTds
ClickHouseEcto.Adapters.ClickHouseCh

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

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. Currently supports:
    • {:duckdb, pool: PoolName} - Use a DuckDB connection pool
  • :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.

Summary

Types

backend()

A backend for executing SQL queries.

Functions

query(backend, sql, params \\ [], opts \\ [])

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

query!(backend, sql, params \\ [], opts \\ [])

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

query_all(backend, sql, params \\ [], opts \\ [])

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

query_one(backend, sql, params \\ [], opts \\ [])

Executes a SQL query and returns one row as a map or struct, or nil if no results.

query_one!(backend, sql, params \\ [], opts \\ [])

Executes a SQL query and returns exactly one row as a map or struct.

Types

backend()

@type backend() :: Ecto.Repo.t() | struct() | atom()

A backend for executing SQL queries.

Can be:

Functions

query(backend, sql, params \\ [], opts \\ [])

@spec query(backend(), String.t(), list() | map(), keyword()) ::
  map() | struct() | nil

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

query!(backend, sql, params \\ [], opts \\ [])

@spec query!(backend(), String.t(), list() | map(), keyword()) :: map() | struct()

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

query_all(backend, sql, params \\ [], opts \\ [])

@spec query_all(backend(), String.t(), list() | map(), keyword()) :: [
  map() | struct()
]

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

Raises on query execution errors. This matches Ecto.Repo.all/2 semantics.

Backend

The first argument can be:

Options

Examples

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])
# => [%{id: 1, name: "Alice", age: 30}]

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

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

# With DuckDB pool
SqlKit.query_all(MyApp.DuckDBPool, "SELECT * FROM events", [])

query_one(backend, sql, params \\ [], opts \\ [])

@spec query_one(backend(), String.t(), list() | map(), keyword()) ::
  map() | struct() | nil

Executes a SQL query and returns one row as a map or struct, or nil if no results.

Returns the result directly, or nil on no results. Raises SqlKit.MultipleResultsError if more than one row is returned. Raises on query execution errors. This matches Ecto.Repo.one/2 semantics.

See query_all/4 for backend documentation.

Options

  • :as - Struct module to cast result 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 (defaults to truncated SQL)

Examples

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

query_one!(backend, sql, params \\ [], opts \\ [])

@spec query_one!(backend(), String.t(), list() | map(), keyword()) :: map() | struct()

Executes a SQL query and returns exactly one row as a map or struct.

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

See query_all/4 for backend documentation.

Options

  • :as - Struct module to cast result 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 (defaults to truncated SQL)

Examples

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