Databases ​

Mesh supports two deliberately different storage paths:

• Sqlite is a direct embedded connection for local and single-node applications.
• Pg and Pool connect to PostgreSQL. A pool is the normal choice for a shared, deployable service.

Both drivers accept parameterized SQL and return errors through Result. The higher-level Query, Expr, Repo, Changeset, and Migration modules target PostgreSQL pools. PostgreSQL-specific types and operators remain explicit under Pg; Mesh does not pretend every backend has the same capabilities.

Clustered applications: Do not place one SQLite file behind several application nodes. Use PostgreSQL when nodes must share application state. Continue with Autonomous Clusters for clustered routing and continuity.

SQLite ​

Sqlite.open(":memory:") creates an in-memory database; a filesystem path creates or opens a local database file.

fn run() -> Int!String do
  let db = Sqlite.open(":memory:")?
  let _ = Sqlite.execute(
    db,
    "CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL)",
    []
  )?
  let _ = Sqlite.execute(
    db,
    "INSERT INTO notes (id, body) VALUES (?, ?)",
    ["1", "hello"]
  )?

  let rows = Sqlite.query(db, "SELECT id, body FROM notes WHERE id = ?", ["1"])?
  println(Map.get(List.head(rows), "body"))
  Sqlite.close(db)
  Ok(0)
end

SQLite placeholders are ?. Values in direct-query row maps are strings; decode them manually or apply a struct's generated from_row function with List.map.

PostgreSQL Connections and Pools ​

For a one-off connection, use Pg.connect. For an HTTP service or other concurrent application, open a bounded pool:

fn main() do
  let url = Env.get("DATABASE_URL", "")
  case Pool.open(url, 1, 8, 5_000) do
    Ok(pool) -> do
      println("database ready")
      Pool.close(pool)
    end
    Err(error) -> IO.eprintln("database unavailable: #{error}")
  end
end

Pool.open(url, min_size, max_size, checkout_timeout_ms) eagerly creates the minimum connections and bounds concurrent checkouts at the maximum.

Direct PostgreSQL API ​

PgConn is affine: assignments and ordinary function parameters move it, database operations borrow it, and Pg.close consumes it. It cannot cross an actor boundary. PostgreSQL placeholders are $1, $2, and so on.

Pool API ​

Pool leases are runtime-internal so their provenance cannot be forged. Use the scoped Pool.query, Pool.execute, typed value variants, or Repo APIs.

Struct Row Decoding ​

deriving(Row) generates Type.from_row(Map<String, String>) -> Result<Type, String>. It validates required columns and converts String, Int, Float, Bool, and optional forms.

struct User do
  id :: String
  name :: String
  age :: Int
  bio :: Option<String>
end deriving(Row)

let decoded = Pool.query_as(
  pool,
  "SELECT id, name, age, bio FROM users ORDER BY name",
  [],
  User.from_row
)

The outer Result reports query failure. Each element's inner Result reports a row-decoding failure, so callers decide whether one malformed row should reject the whole result.

Pg.query_as and Pool.query_as call the decoder automatically. SQLite has no query_as shortcut: after Sqlite.query, use List.map(rows, User.from_row).

Schema Metadata ​

deriving(Schema) creates database metadata from a struct. It can be combined with Row:

struct User do
  table "people"
  primary_key :uuid
  timestamps true

  uuid :: String
  name :: String
  has_many :posts, Post
end deriving(Schema, Row)

struct Post do
  id :: String
  user_id :: String
  title :: String
  belongs_to :user, User
end deriving(Schema, Row)

Schema metadata drives query construction and Repo.preload; it does not run migrations automatically.

Query Builder ​

Queries are immutable and pipe-friendly:

let query = Query.from(User.__table__())
  |> Query.select(User.__fields__())
  |> Query.where(:active, "true")
  |> Query.where_op(:age, :gte, "18")
  |> Query.order_by(:name, :asc)
  |> Query.limit(50)

Filtering ​

Selection and Shape ​

Explicit SQL Fragments ​

Prefer structured builders first. Keep database-specific SQL visible when a shape genuinely needs a raw fragment.

Structured SQL Expressions ​

Expr.value creates a bound value; it does not interpolate text into SQL. Expr.column creates an identifier reference.

Expr.alias remains available as a compatibility synonym; use Expr.label in new code.

let query = Query.from("accounts")
  |> Query.select_exprs([
    Expr.label(
      Expr.coalesce([Expr.column("nickname"), Expr.value("anonymous")]),
      "display_name"
    ),
    Expr.label(Expr.add(Expr.column("balance"), Expr.value("10")), "next_balance")
  ])

Repository Operations ​

Repo executes queries against a PoolHandle.

Reads ​

Writes ​

Expression writes make updates such as counters and server-side timestamps atomic:

let query = Query.from("counters") |> Query.where(:id, "primary")
Repo.update_where_expr(
  pool,
  "counters",
  %{
    "value" => Expr.add(Expr.column("value"), Expr.value("1")),
    "touched_at" => Expr.fn_call("now", [])
  },
  query
)

Repo.transaction always returns its checked-out connection to the pool. The callback must declare its PostgreSQL parameter as borrow PgConn and return a Result; a callback failure or runtime exception rolls the transaction back.

Raw Repository Escape Hatches ​

Changesets ​

Changesets whitelist input fields, convert values, accumulate validation errors, and feed repository writes.

let changeset = Changeset.cast(%{}, params, [:name, :email])
  |> Changeset.validate_required([:name, :email])
  |> Changeset.validate_length(:name, 2, 80)
  |> Changeset.validate_format(:email, "@")

if Changeset.valid(changeset) do
  Repo.insert_changeset(pool, "users", changeset)
else
  println(Json.encode(Changeset.errors(changeset)))
end

Repo.insert_changeset and Repo.update_changeset return Result<Map<String, String>, Changeset>. Invalid input is returned as the Err changeset without executing SQL. PostgreSQL unique, foreign-key, and not-null violations are mapped back to field errors; other database failures become a _base error.

Migrations ​

The Migration module executes common PostgreSQL DDL through a pool:

Column definitions use name:TYPE or name:TYPE:CONSTRAINTS, for example id:UUID:PRIMARY KEY. Index columns may end in :ASC or :DESC. Index options accept unique:true, name:index_name, and a final where:predicate.

Use meshc migrate generate <name> to create a timestamped file exporting up(pool) and down(pool). meshc migrate up, meshc migrate down, and meshc migrate status read DATABASE_URL and track applied versions in PostgreSQL. Migration names may contain lowercase ASCII letters, digits, and underscores.

PostgreSQL-Specific Helpers ​

Typed expression helpers keep vendor-specific choices visible:

PostgreSQL schema helpers operate on a pool:

Low-Level SQL Builders ​

The Orm functions return quoted PostgreSQL SQL strings with numbered placeholders:

WHERE entries use forms such as "name =", "age >", or "deleted_at IS NULL"; order entries use forms such as "name ASC". These fragments must come from trusted application schema, not request text. Most applications should compose Query and execute it through Repo; use Orm when another layer needs generated SQL text itself.

What's Next? ​

• Autonomous Clusters — shared PostgreSQL topology, routing, scaling, and continuity
• Web — use pools and repositories from HTTP handlers
• Type System — structs, Result, and deriving