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:")?
  Sqlite.execute(
    db,
    "CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL)",
    []
  )?
  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 ?, and a statement takes exactly as many parameters as it has placeholders. Values in direct-query row maps are strings, and a NULL column reads as "" (Sqlite.query_values tells the two apart); decode them manually or apply a struct's generated from_row function with List.map. The size limits under Binary and NULL Values apply here too.

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.

A URL is postgres://user:password@host:port/database (an IPv6 host in brackets, [::1]), with sslmode and sslrootcert in its query. Mesh verifies every TLS server's certificate and name, so require, verify-ca and verify-full all mean what libpq calls verify-full; prefer (the default) and allow use TLS, verified the same way, when the server offers it, and disable never does. A server whose certificate comes from a private CA (a cloud provider's, or your own) needs that CA's PEM file: ?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem. Any other sslmode is an error. The server may ask for SCRAM-SHA-256, md5 or a cleartext password. The server has 30 seconds to answer the connection's handshake; a statement then runs as long as it needs (a server-side statement_timeout bounds it).

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.

Pg.transaction commits when the callback returns Ok and rolls back when it returns Err or panics. A statement that failed inside it, even one whose error the callback ignored, makes the commit fail too.

In Pg.query and Pool.query rows, a NULL column reads as "" (Pg.query_values tells the two apart). The size limits under Binary and NULL Values apply to every query.

Pool API ​

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

Binary and NULL Values ​

The string APIs above bind every parameter as text and return every column as a String. To store Bytes or distinguish SQL NULL, use the _values variants. They take and return DbValue, a built-in type with the variants Text(String), Binary(Bytes), and Null:

fn save_avatar(db :: SqliteConn, user_id :: String, image :: Bytes) -> Int!String do
  Sqlite.execute_values(
    db,
    "INSERT INTO avatars (user_id, image, caption) VALUES (?, ?, ?)",
    [Text(user_id), Binary(image), Null]
  )
end

fn load_avatar(db :: SqliteConn, user_id :: String) -> Bytes!String do
  let rows = Sqlite.query_values(
    db,
    "SELECT image FROM avatars WHERE user_id = ?",
    [Text(user_id)]
  )?
  case rows do
    [] -> Err("no avatar")
    row :: _ -> case Map.get(row, "image") do
      Binary(image) -> Ok(image)
      _ -> Err("image is not a BLOB")
    end
  end
end

Binary parameters bind as SQLite BLOBs or PostgreSQL binary-format values (such as bytea), and Null binds SQL NULL; an empty Binary is a zero-length value, not NULL. In results, SQL NULL is Null, SQLite BLOB columns and PostgreSQL bytea columns are Binary, and every other column is Text in the database's text form, so an integer column reads as Text("1"). PostgreSQL text that is not valid UTF-8 is an error; SQLite replaces invalid UTF-8.

Each value is limited to 16 MiB, and a result to 100,000 rows and 64 MiB. A statement accepts at most 32,766 SQLite or 32,767 PostgreSQL parameters, and SQLite requires exactly as many parameters as the statement has placeholders.

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 values of type Query:

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 ​

In a fragment, ? stands for the next parameter ($1, $2, … name them by position); inside quoted text or a quoted identifier it is just a character. Query.fragment and Query.where_raw take exactly as many parameters as the placeholders ask for (the count of ?, or the highest $N); any other number is a panic. Prefer structured builders first. Keep database-specific SQL visible when a shape genuinely needs a raw fragment.

Structured SQL Expressions ​

Expressions are values of type Expr. 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. Every operation returns a Result: rows come back as Map<String, String> (a list of them for several), counts as Int, and failures as Err(message), except the changeset operations, whose error is the changeset itself.

Reads ​

Writes ​

The _where writes refuse a query without conditions, so a forgotten filter cannot rewrite or empty a whole table.

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 (values of type Changeset) 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, and so is a changeset with no changes, its _base error "has no changes". 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:

A table name may be schema-qualified (audit.events), as Query and Repo read it; an index lives in its table's schema, and its derived name is idx_<table>_<columns> with the table's own name. Column definitions use name:TYPE or name:TYPE:CONSTRAINTS, for example id:UUID:PRIMARY KEY; one without a colon is SQL as written (a table constraint, say). 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, in its current schema (a table name here is one identifier):

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