🐿️ SquirrElix — type-safe SQL in Elixir

SquirrElix (package squirr_elix)

SquirrElix is a simple database interface that aims to reduce cognitive load when working with SQL through Postgres and Postgrex.

You stay close to the database by writing plain .sql files, which are converted into typed Elixir modules. You write one query per file; the generator discovers those files, resolves parameter and return types, and writes a sibling sql.ex module with @spec-annotated functions that run the queries through Postgrex.

For many applications that is enough: generated convenience functions and typing, full control over your SQL, no extra abstraction to learn, and easier visibility when you need to optimise queries.

SquirrElix requires Elixir ~> 1.18 (stdlib JSON). Elixir 1.20+ is recommended for the compiler’s gradual typing and typechecking. Generated Dialyzer @specs remain part of the codegen story on all supported versions.

SquirrElix is an independent Elixir library inspired by Gleam Squirrel. It reimplements similar SQL-file discovery, inference, and codegen ideas in native Elixir with Elixir-native typing and idioms. It is not affiliated with, endorsed by, or maintained by the Gleam Squirrel project.

What's SquirrElix?

Database access in Elixir is often handled using an ORM such as Ecto, or by writing something like this:

def find_user(conn, id) do
  Postgrex.query!(
    conn,
    "select name, age from users where id = $1",
    [id]
  )
  |> Map.fetch!(:rows)
  |> Enum.map(fn [name, age] -> %{name: name, age: age} end)
end

That works for a few queries, but it grows painful: SQL is a plain string (no editor SQL niceties), you cannot easily run the query with external tools, and row decoding drifts out of sync with the select list.

SquirrElix takes a different approach: instead of hiding SQL, it embraces it. Write queries in plain *.sql files; SquirrElix generates the corresponding typed functions.

-- lib/my_app/accounts/sql/find_user.sql
-- Find a user and their age given their id.
select
  name,
  age
from
  users
where
  id = $1

mix squirrelix.gen --infer

alias MyApp.Accounts.SQL

rows = SQL.find_user(conn, 42)
# => [%{name: "Ada", age: 36}]

You keep plain SQL files (editor support, explain, external tools) while SquirrElix keeps encoders, decoders, and @specs in sync.

When SquirrElix may not be the right fit

SquirrElix is opinionated: Postgres only, one query per file, convention over configuration, and typed SQL codegen (default: Postgrex). Optional --runner ecto is for Repo connection ownership only — not schemas or an ORM replacement.

Consider something else when:

• You need a database other than Postgres.
• Most of your data access is CRUD through schemas, changesets, and associations (prefer Ecto).
• You need dynamic query construction at runtime (prefer Ecto.Query).
• You need schema structs, changesets, or Ecto.Multi from generated SQL (out of scope).
• Your team wants the data layer to hide SQL — SquirrElix keeps SQL front and centre.
• You only have a handful of queries and are happy with small Postgrex.query/3 helpers.

Alternatives:Ecto / Ecto.Query, direct Postgrex, or Gleam Squirrel on Gleam. Using SquirrElix alongside Ecto (migrations/schemas via Ecto; typed .sql via SquirrElix) is a common Phoenix setup — see the Phoenix + CI Cookbook.

Requirements

• Elixir ~> 1.18 (recommend 1.20+ for gradual typing / compiler typechecking)
• Postgrex ~> 0.22 (generated query modules and optional --infer)
• PostgreSQL >= 16 (when using --infer)

Installation

Add SquirrElix as a dev/test dependency and keep Postgrex as a runtime dependency:

def deps do
  [
    {:squirr_elix, "~> 0.5.0", only: [:dev, :test], runtime: false},
    {:postgrex, "~> 0.22"},
    # Optional — only for `mix squirrelix.gen --watch`
    {:file_system, "~> 1.0", only: [:dev, :test], runtime: false}
  ]
end

Then mix deps.get. Full docs: https://hexdocs.pm/squirr_elix (or mix docs locally).

Quick start

Put one SQL query per file under a sql/ directory:

lib/my_app/accounts/sql/find_user.sql   →  MyApp.Accounts.SQL.find_user/2
lib/my_app/accounts/sql.ex              # generated

mix squirrelix.gen --infer --database my_app_dev
mix squirrelix.check --infer --database my_app_dev   # CI drift check

Copy-pasteable GitHub Actions workflows live in examples/github-actions/. Walkthrough: Getting Started.

Mix tasks

• mix squirrelix.gen — generate typed sql.ex modules (--watch optional; needs file_system)
• mix squirrelix.check — fail CI when generated code is stale

Both accept the same query-source / connection options (--infer, --metadata, DATABASE_URL, PG*, optional --repo / --runner, …). Details: Configuration.

Guides

FAQ

What flavour of SQL does SquirrElix support?

Postgres only, versions >= 16 (for --infer).

Why isn't SquirrElix highly configurable?

Convention over configuration: the same sql/ layout and sql.ex modules everywhere, less bike-shedding about where queries live.

Can SquirrElix read my .env file?

No. Use your shell, direnv, or similar so the environment owns env vars — not the application.

How do I deal with nullable query parameters?

Postgres does not expose parameter nullability. See Writing Queries for workarounds.

Why aren't Postgres composite types supported?

Intentional reject-with-hints policy (flat required/1 row maps). Select fields or cast in SQL — see Types.

Does SquirrElix integrate with Ecto Repo?

Optionally, for connection ownership only — not as an ORM:

• --repo MyApp.Repo with --infer reuses Repo config for the database URL/host.
• --runner ecto generates Repo-first functions via Ecto.Adapters.SQL so calls share checkout, Repo.transaction/2, and Sandbox.

It does not produce schema structs, changesets, or Multi. Default remains Postgrex. See What the optional Repo integration is for.

What is intentionally out of scope?

Product non-goals for 1.0 (for example PostGIS, ULID, a built-in SQL formatter) are listed in the roadmap — intentional boundaries, not a backlog.

Errors and troubleshooting

Generation is project-wide atomic (query errors refuse all writes; write pass uses temp + rename with rollback). See Configuration.

Relationship to Gleam Squirrel

SquirrElix is an independent project inspired by Gleam Squirrel by Giacomo Cavalieri. It is not an official port of that project and is not affiliated with its authors or maintainers. SquirrElix follows similar query conventions — one query per file, sql/ directory layout, comment-to-doc mapping, parameter name inference, and Postgres type inference — while producing idiomatic Elixir output:

• Row results are plain maps, not Gleam records.
• Types use stdlib typespecs (String.t(), integer(), map() with required/1).
• Postgres enums map to String.t(), not generated enum ADTs.
• Generated modules use Postgrex, not pog.

Both projects are licensed under Apache 2.0. See LICENSE and NOTICE for attribution. This package also draws inspiration from yesql and sqlx.

Contributing

Maintainer notes live under docs/ on GitHub (not published on HexDocs):

• Contributing — mix precommit, CI gate, tool pins
• Roadmap — path to 1.0
• Performance — mix bench process
• Release checklist — Hex publish

License

SquirrElix is licensed under the Apache License 2.0. See LICENSE.