NexBase

A fluent, Supabase-inspired database query builder for Elixir. Supports PostgreSQL and SQLite. Schema-less, chainable, built on Ecto.

Features

Fluent API — Chainable query builder, reads like natural language
Multi-database — PostgreSQL and SQLite, auto-detected from URL scheme
Multi-connection — Connect to multiple databases simultaneously
Schema-less — Query any table by name, no Ecto schemas needed
Raw SQL — NexBase.sql/2 for JOINs and complex queries, returns [%{"col" => val}]
Built on Ecto — Production-ready, connection pooling, type safety
One-line init — NexBase.init(url: "...") handles all config internally

Installation

def deps do
  [
    {:nex_base, "~> 0.3.0"},
    # Add the driver for your database:
    {:postgrex, "~> 0.19"},       # for PostgreSQL
    # {:ecto_sqlite3, "~> 0.17"}, # for SQLite
  ]
end

Quick Start

1. Initialize

The adapter is auto-detected from the URL scheme:

# In your application.ex
def start(_type, _args) do
  # PostgreSQL
  NexBase.init(url: "postgres://localhost/mydb", ssl: true)

  # SQLite
  # NexBase.init(url: "sqlite:///path/to/mydb.db")
  # NexBase.init(url: "sqlite::memory:")

  children = [{NexBase.Repo, []}]
  Supervisor.start_link(children, strategy: :one_for_one)
end

Multiple Databases

Connect to multiple databases simultaneously:

def start(_type, _args) do
  main = NexBase.init(url: "postgres://localhost/main")
  analytics = NexBase.init(url: "postgres://analytics-host/analytics")
  cache = NexBase.init(url: "sqlite::memory:")

  children = [
    {NexBase.Repo, main},
    {NexBase.Repo, analytics},
    {NexBase.Repo, cache},
  ]
  Supervisor.start_link(children, strategy: :one_for_one)
end

# Query specific databases by piping the connection:
main |> NexBase.from("users") |> NexBase.run()
analytics |> NexBase.from("events") |> NexBase.run()
cache |> NexBase.from("sessions") |> NexBase.run()

# Raw SQL with specific connection:
main |> NexBase.sql("SELECT * FROM users WHERE id = $1", [1])

2. Query

# Select
{:ok, users} = NexBase.from("users")
|> NexBase.eq(:active, true)
|> NexBase.order(:name, :asc)
|> NexBase.limit(10)
|> NexBase.run()

# Insert
{:ok, _} = NexBase.from("users")
|> NexBase.insert(%{name: "Alice", email: "alice@example.com"})
|> NexBase.run()

# Update
{:ok, _} = NexBase.from("users")
|> NexBase.eq(:id, 1)
|> NexBase.update(%{name: "Bob"})
|> NexBase.run()

# Delete
{:ok, _} = NexBase.from("users")
|> NexBase.eq(:id, 1)
|> NexBase.delete()
|> NexBase.run()

3. Raw SQL

For JOINs, aggregations, and complex queries:

{:ok, rows} = NexBase.sql("""
  SELECT u.name, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  WHERE u.active = $1
  GROUP BY u.id
  ORDER BY post_count DESC
""", [true])

# Returns: [%{"name" => "Alice", "post_count" => 42}, ...]

API Reference

Initialization

Function	Description
`NexBase.init(opts)`	Configure database connection, returns `%NexBase.Conn{}`

Options for init/1:

:url — Database URL (or falls back to DATABASE_URL env var)
:ssl — Enable SSL with verify: :verify_none for cloud databases (default: false)
:pool_size — Connection pool size (default: 10)
:start — Start the Repo in-process, for scripts (default: false)

Query Builder

Function	Description	Example
`from(table)`	Start a query	`NexBase.from("users")`
`select(q, cols)`	Select columns	`\|> NexBase.select([:id, :name])`
`eq(q, col, val)`	Equal	`\|> NexBase.eq(:status, "active")`
`neq(q, col, val)`	Not equal	`\|> NexBase.neq(:role, "admin")`
`gt(q, col, val)`	Greater than	`\|> NexBase.gt(:age, 18)`
`gte(q, col, val)`	Greater or equal	`\|> NexBase.gte(:score, 90)`
`lt(q, col, val)`	Less than	`\|> NexBase.lt(:price, 100)`
`lte(q, col, val)`	Less or equal	`\|> NexBase.lte(:qty, 50)`
`like(q, col, pat)`	LIKE (case-sensitive)	`\|> NexBase.like(:name, "%john%")`
`ilike(q, col, pat)`	ILIKE (case-insensitive)	`\|> NexBase.ilike(:email, "%@gmail%")`
`in_list(q, col, vals)`	IN list	`\|> NexBase.in_list(:id, [1, 2, 3])`
`is(q, col, val)`	IS NULL / IS TRUE	`\|> NexBase.is(:deleted_at, nil)`
`order(q, col, dir)`	ORDER BY	`\|> NexBase.order(:created_at, :desc)`
`limit(q, n)`	LIMIT	`\|> NexBase.limit(10)`
`offset(q, n)`	OFFSET	`\|> NexBase.offset(20)`
`range(q, from, to)`	Supabase-style range	`\|> NexBase.range(0, 9)`
`single(q)`	Limit to 1 result	`\|> NexBase.single()`

Mutations

Function	Description	Example
`insert(q, data)`	Insert row(s)	`\|> NexBase.insert(%{name: "Alice"})`
`update(q, data)`	Update matching rows	`\|> NexBase.update(%{name: "Bob"})`
`delete(q)`	Delete matching rows	`\|> NexBase.delete()`
`upsert(q, data)`	Insert or replace	`\|> NexBase.upsert(%{id: 1, name: "Alice"})`

Execution

Function	Description
`run(q)`	Execute query, returns `{:ok, result}` or `{:error, reason}`
`sql(sql, params)`	Raw SQL, returns `{:ok, [%{"col" => val}]}`
`query(sql, params)`	Raw SQL, returns raw driver result
`query!(sql, params)`	Raw SQL, raises on error
`rpc(func, params)`	Call a stored procedure (PostgreSQL only)

Usage in Scripts

For seeds, migrations, or one-off scripts, use start: true:

NexBase.init(url: System.get_env("DATABASE_URL"), ssl: true, start: true)

NexBase.from("tags")
|> NexBase.insert(%{name: "Web", slug: "web"})
|> NexBase.run()

Error Handling

All operations return {:ok, result} or {:error, reason}:

case NexBase.from("users") |> NexBase.eq(:id, 123) |> NexBase.run() do
  {:ok, [user]} -> user
  {:ok, []} -> nil
  {:error, reason} -> Logger.error("Query failed: #{inspect(reason)}")
end

License

MIT