@spec attach(String.t(), atom() | String.t(), keyword()) :: iodata()

Builds an ATTACH statement that attaches path as the database name.

Options become DuckDB attach options, such as :type, :read_only, or a DuckLake :data_path. Atoms are written as keywords, other values as literals:

alias QuackDB.DDL

DDL.attach("ducklake:metadata.ducklake", :lake, data_path: "s3://bucket/lake/")
DDL.attach("s3://bucket/events", :events, type: :delta)
DDL.attach("s3://bucket/events", :events_v5, type: :delta, version: 5)

Supports if_not_exists: true.

Builds one inline CHECK constraint from an expression.

Bare identifiers refer to columns. Pin runtime values with ^; use field("column name") or field(^name) for other column names. Supports comparisons (==, !=, >, >=, <, <=), and, or, not, and is_nil/1. Arithmetic, arbitrary function calls, and raw SQL are rejected.

import QuackDB.DDL, only: [create_table: 3, check: 1]

maximum = 100
create_table(:events, [score: :integer],
  check(score >= 0 and score < ^maximum)
)

Return values are composable: pass a single check or a list of checks as create_table/3's third argument. Table options can follow checks in that list: [check(score >= 0), temporary: true].

@spec create_schema(
  QuackDB.SQL.Fragment.schema(),
  keyword()
) :: iodata()

Builds a CREATE SCHEMA statement.

A list names a nested schema, which DuckDB 2.0 creates inside its parent. In an Ecto migration:

alias QuackDB.DDL

def up do
  execute DDL.create_schema(:finance)
  execute DDL.create_schema([:finance, :reports])
  execute DDL.create_table({[:finance, :reports], :ledger}, id: :integer)
end

Supports if_not_exists: true.

@spec create_sequence(
  QuackDB.SQL.Fragment.table(),
  keyword()
) :: iodata()

Builds a standalone CREATE SEQUENCE statement.

Names may be atoms, strings, or {prefix, name} tuples. Supported options are :start (integer), :increment (nonzero integer), and :if_not_exists (boolean). Other options raise rather than being silently ignored. Sequences can have gaps; do not use them when a gapless transactional counter is required.

alias QuackDB.{DDL, Sequence}

QuackDB.query!(conn, DDL.create_sequence(:task_keys, start: 1))
Sequence.next_values(conn, :task_keys, 3)
#=> [1, 2, 3]

@spec create_table(module()) :: iodata()

Builds a CREATE TABLE statement from an Ecto schema module.

@spec create_table(
  String.t() | atom(),
  module() | [column()],
  QuackDB.DDL.Check.t() | [create_table_option()]
) :: iodata()

Builds a CREATE TABLE statement.

QuackDB.DDL.create_table("events", id: :integer, name: :varchar)
QuackDB.DDL.create_table("events", [id: :integer], temporary: true)
QuackDB.DDL.create_table("temp_events", EventSchema, temporary: true)

Pass a single check/1 value or a list for inline constraints:

import QuackDB.DDL, only: [create_table: 3, check: 1]

create_table(:events, [{:score, :integer, null: false}],
  [check(score >= 0), check(score < 100)]
)

Checks also work with schema-derived columns. Column names are quoted and values use the SQL literal codec, not raw predicate strings. CHECK permits NULL when its expression is unknown; use null: false to require a value. Checks cannot be combined with :as. DuckDB does not preserve supplied CHECK names reliably, so this API does not name checks or promise Ecto changeset constraint mapping. ALTER ADD/DROP CONSTRAINT remains unsupported.

Pass :as to build CREATE TABLE AS from iodata or an Ecto query without pinned params:

QuackDB.DDL.create_table("docs", as: query, temporary: true)
QuackDB.DDL.create_table("docs", as: query, or_replace: true)

@spec create_trigger(
  QuackDB.SQL.Fragment.identifier_name(),
  QuackDB.SQL.Fragment.table(),
  iodata(),
  keyword()
) :: iodata()

Builds a DuckDB 2.0 CREATE TRIGGER statement that runs statement, a SQL statement such as an INSERT or UPDATE, when event changes table.

Triggers are schema, so they usually live in an Ecto migration. The body is DuckDB SQL because it reads the transition table:

alias QuackDB.DDL

def up do
  execute DDL.create_trigger(:events_count, :events,
    "UPDATE event_stats SET total = total + (SELECT count(*) FROM inserted)",
    event: :insert,
    referencing: [new_table: :inserted]
  )
end

def down, do: execute(DDL.drop_trigger(:events_count, :events))

Options:

• :event (required) - :insert, :delete, :update, or {:update, columns} for UPDATE OF columns.
• :timing - :after (default) or :before.
• :for_each - :statement (default) or :row. Row triggers refer to the changed row as NEW and OLD.
• :referencing - transition tables as [old_table: alias, new_table: alias].
• :or_replace and :if_not_exists - booleans.

DuckDB 2.0 does not run BEFORE ... FOR EACH ROW or UPDATE ... FOR EACH ROW triggers yet, and a table cannot mix row and statement triggers; the server rejects those.

@spec detach(
  atom() | String.t(),
  keyword()
) :: iodata()

Builds a DETACH statement. Supports if_exists: true.

@spec drop_schema(
  QuackDB.SQL.Fragment.schema(),
  keyword()
) :: iodata()

Builds a DROP SCHEMA statement. Supports if_exists: true and cascade: true.

@spec drop_sequence(
  QuackDB.SQL.Fragment.table(),
  keyword()
) :: iodata()

Builds a DROP SEQUENCE statement, optionally with if_exists: true.

@spec drop_table(
  String.t() | atom(),
  keyword()
) :: iodata()

Builds a DROP TABLE statement.

@spec drop_trigger(
  QuackDB.SQL.Fragment.identifier_name(),
  QuackDB.SQL.Fragment.table(),
  keyword()
) :: iodata()

Builds a DROP TRIGGER name ON table statement, optionally with if_exists: true.