Skip to content

Database

David Ciulla edited this page Nov 7, 2026 · 2 revisions

Database

Laravex supports three database adapters — SQLite, MySQL/MariaDB, and PostgreSQL — all via CLI tools; there are no Hex runtime dependencies. The connection layer (Laravex.Database.Connection) delegates to whichever adapter is selected by database.default.


Configuration

# config/database.exs
%{
  "default" => Laravex.Env.get("DB_CONNECTION", "sqlite"),
  "connections" => %{
    "sqlite" => %{
      "driver"   => "sqlite",
      "database" => Laravex.Env.get("DB_DATABASE", "database/database.sqlite")
    },
    "mysql" => %{
      "driver"   => "mysql",
      "host"     => Laravex.Env.get("DB_HOST", "127.0.0.1"),
      "port"     => Laravex.Env.get("DB_PORT", "3306"),
      "database" => Laravex.Env.get("DB_DATABASE", "laravex"),
      "username" => Laravex.Env.get("DB_USERNAME", "root"),
      "password" => Laravex.Env.get("DB_PASSWORD", "")
    },
    "pgsql" => %{
      "driver"   => "pgsql",
      "host"     => Laravex.Env.get("DB_HOST", "127.0.0.1"),
      "port"     => Laravex.Env.get("DB_PORT", "5432"),
      "database" => Laravex.Env.get("DB_DATABASE", "laravex"),
      "username" => Laravex.Env.get("DB_USERNAME", "root"),
      "password" => Laravex.Env.get("DB_PASSWORD", "")
    }
  }
}

Set DB_CONNECTION in your .env to switch adapters:

DB_CONNECTION Adapter CLI required
sqlite (default) Laravex.Database.Adapters.Sqlite sqlite3
mysql / mariadb Laravex.Database.Adapters.Mysql mysql
pgsql / postgres Laravex.Database.Adapters.Postgres psql

Paths and credentials are resolved from the matching connection map. For SQLite the path is relative to base_path/0; absolute paths are also accepted.


The CLI Adapters

SQLite

All queries are executed by spawning:

sqlite3 -json <path> <sql>    # SELECT → JSON rows
sqlite3 <path> <sql>          # INSERT/UPDATE/DELETE/DDL

Ensure sqlite3 is available on PATH:

sqlite3 --version
# SQLite 3.x.x ...

If the database file does not exist, Connection.ensure_database!/0 creates it (along with any missing parent directories) automatically on the first query.

MySQL / MariaDB

Queries are executed via the mysql CLI client:

mysql -h <host> -P <port> -u <user> -D <database> --batch --raw -e "<sql>"

The password is passed via the MYSQL_PWD environment variable (not as a CLI flag). Set DB_SOCKET to use a UNIX socket instead of TCP.

PostgreSQL

Queries are executed via the psql CLI client. SELECT queries are wrapped in a json_agg(row_to_json(...)) envelope to return JSON rows:

psql -h <host> -p <port> -U <user> -d <database> -t -A -c "<sql>"

The password is passed via PGPASSWORD. Use DB_CONNECTION=postgres or DB_CONNECTION=pgsql — both are accepted.


Connection Module

Laravex.Database.Connection is the unified facade:

alias Laravex.Database.Connection

# Which adapter is active?
Connection.adapter_name()   # => "sqlite" | "mysql" | "mariadb" | "pgsql" | "postgres"
Connection.adapter()        # => the adapter module atom

# SELECT — returns a list of string-keyed maps
rows = Connection.query("SELECT * FROM users WHERE active = ?", [1])

# DML / DDL
Connection.execute("UPDATE users SET name = ? WHERE id = ?", ["Alice", 42])

# Last inserted row ID (adapter-aware)
id = Connection.last_insert_id()

Bindings are positional (?). Values are safely escaped by the connection layer:

Elixir type SQL
nil NULL
true / false 1 / 0
%DateTime{} ISO-8601 string
integer / float numeric literal
binary '…' (single quotes escaped)

Transactions

{:ok, result} = Connection.transaction(fn ->
  Connection.execute("INSERT INTO posts (title) VALUES (?)", ["Hello"])
  Connection.last_insert_id()
end)

A transaction wraps the closure in BEGIN / COMMIT. On exception the connection issues ROLLBACK and re-raises.


The DB Facade

Laravex.Database.DB is a convenience facade for the most common operations:

alias Laravex.Database.DB

# Fluent query builder (see [[Query-Builder]])
DB.table("users")

# Raw SELECT
DB.select("SELECT COUNT(*) AS n FROM users")

# Raw statement
DB.statement("PRAGMA journal_mode=WAL")

# Transaction
DB.transaction(fn -> ... end)

Schema Builder

Laravex.Database.Schema provides a Laravel-style schema facade.

Creating Tables

alias Laravex.Database.Schema
alias Laravex.Database.Blueprint

Schema.create("posts", fn bp ->
  bp
  |> Blueprint.id()
  |> Blueprint.string("title")
  |> Blueprint.text("body", nullable: true)
  |> Blueprint.foreign_id("user_id") |> Blueprint.constrained("users")
  |> Blueprint.soft_deletes()
  |> Blueprint.timestamps()
end)

Altering Tables

Both Schema.table/2 and Schema.alter/2 modify an existing table:

Schema.table("users", fn bp ->
  bp
  |> Blueprint.string("avatar_url", nullable: true)
  |> Blueprint.index(["email"])
end)

SQLite note: SQLite does not support DROP COLUMN before version 3.35. Blueprint.drop_column/2 is a no-op on older SQLite builds. MySQL and PostgreSQL support it normally.

Renaming Tables

Schema.rename("old_table", "new_table")

Introspection

Schema.has_table?("users")                           # => true | false
Schema.has_column?("users", "email")                 # => true | false
Schema.has_index?("users", "users_email_unique")     # => true | false

Blueprint Column Types

Helper SQL type emitted
id(bp) INTEGER PRIMARY KEY AUTOINCREMENT
string(bp, name) TEXT NOT NULL
text(bp, name) TEXT NOT NULL
integer(bp, name) INTEGER NOT NULL
big_integer(bp, name) INTEGER NOT NULL
boolean(bp, name) INTEGER NOT NULL
timestamp(bp, name) TEXT NOT NULL
timestamps(bp) created_at TEXT, updated_at TEXT (nullable)
foreign_id(bp, name) INTEGER NOT NULL
remember_token(bp) remember_token TEXT (nullable)

These types map to their closest SQLite affinity. For MySQL/PostgreSQL-native types use Blueprint.add(bp, "column_name TYPE CONSTRAINT").

Polymorphic Columns

# Adds `{name}_type TEXT NOT NULL` + `{name}_id INTEGER NOT NULL` + composite index
Blueprint.morphs(bp, "taggable")

# Same but nullable
Blueprint.nullable_morphs(bp, "taggable")

# UUID-based: `{name}_type TEXT NOT NULL` + `{name}_id TEXT NOT NULL` + composite index
Blueprint.uuid_morphs(bp, "taggable")

Soft Deletes

Blueprint.soft_deletes(bp)
# Equivalent to:
Blueprint.timestamp(bp, "deleted_at", nullable: true)

Query Builder

For the full fluent API see Query-Builder. Key additions beyond standard SELECT:

Upsert

alias Laravex.Database.Query

# INSERT … ON CONFLICT(email) DO UPDATE SET name = excluded.name
Query.upsert("users",
  [%{"email" => "alice@example.com", "name" => "Alice"}],
  ["email"],       # unique_by columns
  ["name"]         # columns to update on conflict
)

Insert or Ignore

# INSERT OR IGNORE (SQLite) / INSERT IGNORE (MySQL) / INSERT … ON CONFLICT DO NOTHING
Query.insert_or_ignore("tags", %{"name" => "elixir"})

Limitations

  • No connection pooling. Each query starts a new OS process (sqlite3, mysql, psql). This is suitable for modest workloads and development; for high-throughput production use a connection pool proxy (e.g. PgBouncer for PostgreSQL) in front of the CLI.
  • SQLite-biased Blueprint. Column definitions are written in SQLite DDL syntax. MySQL/PostgreSQL silently accept them in most cases, but native types (JSONB, ENUM, SERIAL, etc.) must be specified raw.
  • No prepared statement reuse. Bindings are interpolated client-side before the SQL string is handed to the CLI.

See Also

  • Query-Builder — Fluent SQL builder (Laravex.Database.Query)
  • Migrations — Schema creation and versioning
  • Eloquex — Model-based ORM built on this connection layer
  • Limitations — Full driver support matrix

Clone this wiki locally