# Elixir

Connect to ParticleDB from Elixir, run parameterized queries, handle transactions and errors.

Source: https://particledb.ai/sdks/elixir/

Use the configured address for your client. See [connection addresses](/installation/ports/) and [security](/guides/security/). The examples use the SQL client address unless they explicitly select the HTTP API or ParticleDB JDBC driver.

Use [Postgrex](https://hexdocs.pm/postgrex/), the standard Elixir PostgreSQL driver, over the
SQL client interface (port 5432). [Ecto](https://hexdocs.pm/ecto_sql/) uses Postgrex underneath.

The Postgrex query and transaction examples below were executed against ParticleDB. Ecto uses additional schema and migration queries; validate your application migrations separately.

## Install

```elixir
# mix.exs
defp deps do
  [
    {:postgrex, "~> 0.19.0"}
    # with Ecto: {:ecto_sql, "~> 3.12"}
  ]
end
```

## Connect and query

The examples connect as `postgres` with the password `secret`, for a server started with
`particledb start --auth-method scram-sha-256 --pg-password secret`. With the server's default
`--auth-method password`, pass `ssl: true`; see
[TLS and authentication](/connectivity/tls/#authentication).

```elixir
{:ok, pid} = Postgrex.start_link(
  hostname: "localhost",
  port: 5432,
  database: "particledb",
  username: "postgres",
  password: "secret"
)

Postgrex.query!(pid, "CREATE TABLE products (id BIGINT PRIMARY KEY, name TEXT, price DOUBLE PRECISION)", [])

Postgrex.query!(pid,
  "INSERT INTO products (id, name, price) VALUES ($1, $2, $3)",
  [1, "Webcam", 79.99]
)

result = Postgrex.query!(pid, "SELECT name, price FROM products WHERE price > $1", [50.0])
for [name, price] <- result.rows, do: IO.puts("#{name}: #{price}")
```

## Transactions

```elixir
Postgrex.query!(pid, "CREATE TABLE accounts (id BIGINT PRIMARY KEY, balance BIGINT)", [])
Postgrex.query!(pid, "INSERT INTO accounts VALUES (1, 500), (2, 0)", [])

Postgrex.transaction(pid, fn conn ->
  Postgrex.query!(conn, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", [100, 1])
  Postgrex.query!(conn, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", [100, 2])
end)
# {:ok, _} on commit; an exception inside the function rolls the transaction back
```

## Errors

Server errors carry PostgreSQL SQLSTATEs, which Postgrex maps to atoms:

```elixir
case Postgrex.query(pid, "INSERT INTO products (id, name, price) VALUES ($1, $2, $3)", [1, "Duplicate", 10.0]) do
  {:ok, _} -> :inserted
  {:error, %Postgrex.Error{postgres: %{code: :unique_violation}}} -> :duplicate
  {:error, %Postgrex.Error{postgres: %{code: :undefined_table}}} -> :no_table
end
```

## Ecto

Configure an Ecto repo with the Postgres adapter as usual:

```elixir
config :my_app, MyApp.Repo,
  hostname: "localhost",
  port: 5432,
  database: "particledb",
  username: "postgres",
  password: "secret",
  pool_size: 10
```

## Next steps

- [SQL Reference](/sql/overview/). SQL syntax and examples
- [ORM compatibility](/connectivity/orm-compatibility/). other ORMs and drivers
- [SQL client connections](/connectivity/pg-wire/). connection settings and compatibility
