# 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

```elixir
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:

```elixir
# 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:

```elixir
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

```elixir
# 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:

```elixir
{: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`:

```elixir
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}`:

```elixir
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