~SQL

~SQL provides state-of-the-art, high-performance SQL integration for Elixir, built to handle extreme concurrency with unmatched expressiveness and ergonomic query composition. Write safe, composable, parameterized queries directly, without translating to Ecto or any ORM.

~SQL is a foreign language integration, letting you write SQL naturally while Elixir handles transactions, concurrency, and query planning for you. Unlike typical ORMs, SQL scales diagonally on the BEAM, fully leveraging multicore hardware under load.

Highlights

• Extreme Concurrency: Hundreds of processes can execute queries simultaneously without pool contention or mailbox bottlenecks.
• Diagonal Scaling: Utilizes BEAM scheduler affinity and C-level concurrency to maximize throughput far beyond traditional connection pool limits.
• SOTA Performance Across Languages: Reaches throughput and latency targets rarely seen outside low-level native drivers (Rust tokio-postgres, Go pgx, Java HikariCP, Python asyncpg) by eliminating Erlang reduction overhead and heap allocation churn.
• Deterministic Queue Control: Strict, configurable queue timeouts prevent cascading failures, head-of-line blocking, and connection pool starvation under load.
• Composable Queries: No need to remember SELECT vs FROM order—queries are fully composable via ~SQL.
• Safe Interpolation: Automatically parameterized queries; no need to manually handle fragments or ?.
• Ergonomic & Expressive: Intuitive syntax for queries, transactions, and mapping result sets.
• Streaming Large Datasets: Efficiently stream millions of rows without blocking memory or reducing concurrency.

Deterministic Queue Control & Load Shedding

Standard connection pools often suffer from head-of-line blocking and cascading latency spikes when starved under heavy load. ~SQL fundamentally avoids this by structurally enforcing strict, user-configurable queue timeouts.

Performance bounds and failure modes are entirely within your control. You can configure timeouts globally at the pool level or dynamically on a per-query basis. When the pool is exhausted, ~SQL respects your explicit queue limits and instantly sheds excess load exactly when instructed, protecting the BEAM node from resource exhaustion and keeping latency deterministic.

Examples

iex(1)> email = "john@example.com"
"john@example.com"
iex(2)> ~SQL[from users] |> ~SQL[where email = {{email}}] |> ~SQL"select id, email"
~SQL"""
select
  id,
  email
from
  users
where
  email = {{email}}
"""
iex(3)> sql = ~SQL[from users where email = {{email}} select id, email]
~SQL"""
select
  id,
  email
from
  users
where
  email = {{email}}
"""
iex(4)> to_sql(sql)
{"select id, email from users where email = ?", ["john@example.com"]}
iex(5)> to_string(sql)
"select id, email from users where email = ?"
iex(6)> inspect(sql)
"~SQL\"\"\"\nselect\n  id, \n  email\nfrom\n  users\nwhere\n  email = {{email}}\n\"\"\""

Transactions

SQL.transaction do
  Enum.to_list(~SQL"select 1")
end

Transactions automatically handle nested savepoints, rollback, and commit logic, even under extreme concurrency.

Mapping Results

For custom mapping of rows:

~SQL[from users select *]
|> SQL.map(fn row -> struct(User, row) end)
|> Enum.to_list()

Streaming

SQL.transaction do
  ~SQL"SELECT g, repeat(md5(g::text), 4) FROM generate_series(1, 5000000) AS g"
  |> SQL.stream()
  |> Stream.run()
end

Pool configuration

  config :sql, pools: [
    default: [
      username: "postgres",
      password: "postgres",
      hostname: "localhost",
      database: "sql",
      adapter: SQL.Adapters.Postgres,
      ssl: false
    ]
  ]

  defmodule MyApp.Accounts do
    use SQL, pool: :default
  
    def list_users() do
      ~SQL[from users select *]
      |> SQL.map(fn row -> struct(User, row) end)
      |> Enum.to_list()
    end
  end

  iex(1)> MyApp.Accounts.list_users()
  [%User{id: 1, email: "john@example.com"}, %User{id: 2, email: "jane@example.com"}]

Compile time warning

Run mix sql.get to generate your sql.lock file for error reporting.

  ==> myapp
  Compiling 1 file (.ex)
  warning:
    the relation OPS does not exist
    the relation email is mentioned 2 times but does not exist
    the relation users does not exist
    ~SQL"""
    select
      email,
      1 + "OPS"
    from
      users
    where
      email = 'john@example.com'
    """
    lib/myapp.ex:18: Myapp.list_users/0
    (sql 0.6.0) lib/sql.ex:225: SQL.__inspect__/3
    (sql 0.6.0) lib/sql.ex:115: SQL.build/4
    (elixir 1.20.0) src/elixir_dispatch.erl:263: :elixir_dispatch.expand_macro_fun/7
    (elixir 1.20.0) src/elixir_dispatch.erl:122: :elixir_dispatch.dispatch_import/6
    (elixir 1.20.0) src/elixir_clauses.erl:192: :elixir_clauses.def/3
    (elixir 1.20.0) src/elixir_def.erl:218: :elixir_def."-store_definition/10-lc$^0/1-0-"/3
    (elixir 1.20.0) src/elixir_def.erl:219: :elixir_def.store_definition/10

Benchmark: Real Concurrency & Bounded Latency (sql_bench)

Standard microbenchmarking tools often report flawed iterations per second by failing to distinguish between successful transactions and unhandled queue failures under connection starvation. To measure true database driver performance under real-world pressure, SQL is evaluated using the sql_bench suite.
Tests track total successful request throughput, p50/p99 latency distributions, and load-shedding error handling across variable worker concurrency levels (C=1 to C=500).
Hardware Setup: Apple M1 Max (10 CPU cores, 64 GB RAM) VM Configuration: Erlang/OTP 28 running 10 BEAM Schedulers

Benchmark Summary

Scenario	Concurrency (C)	SQL p50	Ecto p50	Throughput / Behavior
Empty Transaction (BEGIN; COMMIT;)	C=1	12 µs	80 µs	6.7x faster latency
Empty Transaction	C=100	1.1 ms	2.2 ms	+90.8k successful requests (~2x throughput)
Simple Transaction (BEGIN; SELECT 1; COMMIT;)	C=20	468 µs	770 µs	+35.5k successful requests (1.6x throughput)
Savepoints (Nested)	C=1	72 µs	165 µs	2.3x faster latency
Cursor Fetch (FETCH 100)	C=100	58.6 ms	227.4 ms	3.9x faster latency
Heavy Schema Query	C=500	111.2 ms	1,100 ms	Bounded latency via deterministic fast-fail

Key Benchmark Insights

• Microsecond Latency Floor: For single-worker operations (C=1), SQL achieves a p50 latency of 12 µs on empty transactions, eliminating VM term serialization overhead.
• Double High-Concurrency Throughput: At C=100, SQL processes 181,000+ successful transactions per benchmark run compared to Ecto's ~90,000, maintaining sub-2.5 ms latencies under heavy worker contention.
• Graceful Degradation vs Queue Ballooning: When fetching heavy metadata tables under extreme worker stress (C=500), standard drivers queue unboundedly, driving p50 latency to 1.1s and p99 past 2.0s. SQL strictly enforces queue timeout limits, shedding excess load instantly while preserving a 111.2 ms p50 latency for served queries.

You can find the benchmark suite here

Installation

The package can be installed by adding sql to your list of dependencies in mix.exs:

def deps do
  [
    {:sql, "~> 0.6.0"}
  ]
end

Documentation can be found at https://hexdocs.pm/sql.