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

Overview

SqlKit came about when I wanted to execute SQL inside .sql files at work without having to pollute my context functions with File I/O and Repo.query result transformation.

In making it into a library I’ve expanded it’s usefulness to accommodate raw SQL strings, and support a wide array of databases:

• PostgreSQL
• MySQL/MariaDB
• SQLite
• SQL Server
• ClickHouse
• DuckDB via duckdbex.

With the exception of DuckDB, this support comes via ecto_sql and the relevant drivers.

Some example code from the README:

# 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 # `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}, ...]

Differences to other libraries

SQLx

SQLx is a very popular database driver in the Rust ecosystem which definitely served as inspiration for SqlKit, however at this stage it is much more sophisticated with things compile-time guarantees of your queries. Bringing SqlKit closer to SQLx in features is something I’d be interested in exploring eventually if it proves useful to enough people.

AyeSQL

AyeSQL is an excellent library with similar goals, which truthfully I wasn’t aware of until I was about to release v0.1.0 of sql_kit. The primary difference is SqlKit just works with plain SQL and does less function generation - it doesn’t do any parsing of your queries to facilitate named parameters or utilise magic comments to house multiple queries in a single file.

On the other hand, using plain SQL and leaning on Ecto means SqlKit supports more databases, and IMO is a bit simpler to adopt.

SQL

SQL is another great library focusing on bringing compile-time guarantees to raw SQL queries similar to SQLx via an ~SQL"" sigil. AFAIK it doesn’t lean on Ecto at all, and apparently gets a huge performance/concurrency boost as a result. Definitely one to watch.

Other Notes

This is early in development. It’s well tested across all the databases it claims to support, but I’ve only used this myself in production on Postgres.

Though I wrote the original use macro entirely by hand, in making this into a library I’ve made significant use of Claude Code using Opus 4.5 which has been great, in particular for lightening the load ensuring this has high quality docs and tests. I’m calling it out because I know for some that’s a deal breaker, but FWIW I’ve been reviewing everything along the way.

On DuckDB

I’ve not used DuckDB a great deal myself, so I’m very open to feedback regarding how SqlKit supports it with the connection pooling etc and how that could be better. Just raise an issue with your thoughts.

Links

• Hex
• HexDocs
• GitHub

https://github.com/tylerbarker/sql_kit