Elixir

ParticleDB works with any Elixir PostgreSQL driver. Postgrex is the standard driver, and Ecto provides a full-featured ORM and query DSL on top of it.

Installation

Postgrex only

Add to your mix.exs:

defp deps do
  [
    {:postgrex, "~> 0.19"}
  ]
end

Then fetch dependencies:

mix deps.get

Ecto + Postgrex

Add to your mix.exs:

defp deps do
  [
    {:ecto_sql, "~> 3.12"},
    {:postgrex, "~> 0.19"}
  ]
end

Then fetch dependencies:

mix deps.get

Connecting

With Postgrex

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

result = Postgrex.query!(pid, "SELECT version()", [])
IO.inspect(result.rows)

Connection string

{:ok, pid} = Postgrex.start_link(
  url: "postgresql://localhost:5432/particledb"
)

With Ecto

Define a Repo module:

lib/my_app/repo.ex

defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres
end

Configure the connection in config/config.exs:

config :my_app, MyApp.Repo,
  hostname: "localhost",
  port: 5432,
  database: "particledb",
  username: "particledb",
  password: nil,
  pool_size: 10

Start the Repo in your application supervisor:

lib/my_app/application.ex

children = [
  MyApp.Repo
]

Test the connection:

result = MyApp.Repo.query!("SELECT version()")
IO.inspect(result.rows)

Basic Queries

Parameterized SELECT

result = Postgrex.query!(pid,
  "SELECT name, price FROM products WHERE price > $1 AND price < $2",
  [50.0, 200.0]
)

for [name, price] <- result.rows do
  IO.puts("#{name}: $#{price}")
end

Single value

result = Postgrex.query!(pid, "SELECT COUNT(*) FROM products", [])
[[count]] = result.rows
IO.puts("Total products: #{count}")

INSERT, UPDATE, DELETE

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

# Update
result = Postgrex.query!(pid,
  "UPDATE products SET price = $1 WHERE id = $2",
  [69.99, 5]
)
IO.puts("Rows updated: #{result.num_rows}")

# Delete
Postgrex.query!(pid, "DELETE FROM products WHERE id = $1", [5])

With Ecto schemas and queries

lib/my_app/product.ex

defmodule MyApp.Product do
  use Ecto.Schema

  schema "products" do
    field :name, :string
    field :price, :float
  end
end

import Ecto.Query

# Select
products =
  MyApp.Product
  |> where([p], p.price > ^50.0)
  |> order_by([p], desc: p.price)
  |> MyApp.Repo.all()

for p <- products do
  IO.puts("#{p.name}: $#{p.price}")
end

# Insert
MyApp.Repo.insert!(%MyApp.Product{id: 5, name: "Webcam", price: 79.99})

# Update
product = MyApp.Repo.get!(MyApp.Product, 5)
MyApp.Repo.update!(Ecto.Changeset.change(product, price: 69.99))

# Delete
MyApp.Repo.delete!(MyApp.Repo.get!(MyApp.Product, 5))

Transactions

With Postgrex

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]
  )

  IO.puts("Transfer complete")
end)
# Returns {:ok, _} on commit, {:error, _} on rollback

With Ecto

MyApp.Repo.transaction(fn ->
  MyApp.Repo.query!(
    "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
    [100, 1]
  )

  MyApp.Repo.query!(
    "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
    [100, 2]
  )
end)
# Auto-committed on success, auto-rolled-back on exception

Ecto Multi for composable transactions

alias Ecto.Multi

Multi.new()
|> Multi.run(:debit, fn repo, _changes ->
  repo.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [100, 1])
end)
|> Multi.run(:credit, fn repo, _changes ->
  repo.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [100, 2])
end)
|> MyApp.Repo.transaction()
|> case do
  {:ok, _} -> IO.puts("Transfer complete")
  {:error, step, reason, _} -> IO.puts("Failed at #{step}: #{inspect(reason)}")
end

Vector Search

# Create a table with a vector column
Postgrex.query!(pid, """
  CREATE TABLE documents (
      id         BIGINT PRIMARY KEY,
      title      VARCHAR NOT NULL,
      embedding  VECTOR(384)
  )
""", [])

# Build a vector string: [0.0,0.01,0.02,...]
vec = Enum.map(0..383, fn i -> i * 0.01 end)
vec_str = "[" <> Enum.join(vec, ",") <> "]"

# Insert with an embedding
Postgrex.query!(pid,
  "INSERT INTO documents (id, title, embedding) VALUES ($1, $2, $3::vector)",
  [1, "ParticleDB Guide", vec_str]
)

# Search by cosine distance
result = Postgrex.query!(pid,
  "SELECT title, embedding <=> $1::vector AS distance FROM documents ORDER BY distance LIMIT 5",
  [vec_str]
)

for [title, distance] <- result.rows do
  IO.puts("#{title}: distance=#{distance}")
end

Error Handling

case Postgrex.query(pid,
  "INSERT INTO products (id, name, price) VALUES ($1, $2, $3)",
  [1, "Duplicate", 10]
) do
  {:ok, _result} ->
    IO.puts("Inserted successfully")

  {:error, %Postgrex.Error{postgres: %{code: :unique_violation}}} ->
    IO.puts("Primary key already exists")

  {:error, %Postgrex.Error{postgres: %{code: :undefined_table}}} ->
    IO.puts("Table does not exist")

  {:error, %Postgrex.Error{} = error} ->
    IO.puts("Database error: #{Exception.message(error)}")
end

Next Steps

• SQL Reference — Full SQL syntax
• Vector Search — Vector search SQL syntax
• ORM Compatibility — Ecto with ParticleDB
• PostgreSQL Wire Protocol — Protocol-level details