Database.Table

Database

Declare tables, indexes, foreign keys, and row-level security.

You write a database module, bind tables with :=, and call access / insert / update / remove from functions that run inside Repo.transaction. Security tokens (often request cookies) decide which rows you may touch; see [Database.Security].

update requires **exactly one** matching row (fails on 0 or 2+); remove may delete many. For bulk field changes, update each row by primary key.

database module Todos exposing (list)

import Database.Rows exposing (..)
import Database.Security as Security
import Database.Table exposing (..)

type alias Todo =
    { id : String
    , title : String
    , owner : String
    }

todos :=
    table
        { primary = .id
        , security =
            Security.policy
                { access = \user todo -> todo.owner == user
                , insert = \user todo -> todo.owner == user
                , update = \user before after -> before.owner == user && after.owner == user
                , remove = \user todo -> todo.owner == user
                }
        , indexes = [ btree NotUnique .owner ]
        , constraints = []
        }

list : String -> Transaction (Rows { id : String, title : String })
list user =
    access todos user
        |> map (\todo -> { id = todo.id, title = todo.title })
        |> selectAll

Every select / selectAll needs a map first (see [Database.Rows]). Full apps: `examples/03_todo_app`.

Types

Table

type Table token row = Table

A table with security token token and row type row.

Examples

todos : Table String Todo
todos :=
    table
        { primary = .id
        , security = Security.unrestricted
        , indexes = []
        , constraints = []
        }

Index

type Index row = Index

An index declaration on a table's rows.

Examples

indexes = [ btree NotUnique .owner, concurrently (btree Unique .email) ]

Constraint

type Constraint row = Constraint

A table constraint such as a foreign key.

Examples

constraints = [ foreignKey .owner (references users .id) ]

References

type References a = References

A foreign-key target.

Examples

references users .id

Uniqueness

type Uniqueness
= Unique
| NotUnique

Whether an index enforces uniqueness.

Examples

btree Unique .email
btree NotUnique .owner

Values

table

table : { primary : row -> key , security : Policy token row , indexes : List (Index row) , constraints : List (Constraint row) } -> Description (Table token row)

Declare a database table (bound with := in a database module).

Examples

todos :=
    table
        { primary = .id
        , security = Security.unrestricted
        , indexes = [ btree NotUnique .owner ]
        , constraints = []
        }

access

access : Table token row -> token -> Rows row

Open a table for reading under a security token. Pipe into [Database.Rows] (`filter`, `map`, `selectAll`, and so on).

Examples

access todos token
    |> map (\todo -> { id = todo.id })
    |> selectAll

insert

insert : Table token row -> token -> row -> Transaction ()

Insert a row. The row must satisfy the table's insert policy for token (include owner fields the policy checks).

Examples

insert todos user { id = id, owner = user, title = title, completed = False }

update

update : Table token row -> token -> (row -> Bool) -> (row -> row) -> Transaction ()

Update **exactly one** matching row. The update policy must allow both the before and after row for token.

The predicate should identify a single row (usually by primary key). If zero or two-plus rows match, the transaction fails and rolls back (UPDATE 0, UPDATE 2, …). Prefer keying on id:

Examples

update todos user
    (\todo -> todo.id == id)
    (\todo -> { todo | completed = True })

To change many rows, update each by primary key (fold/andThen), or remove then re-insert. Unlike remove, there is no bulk multi-row update.

remove

remove : Table token row -> token -> (row -> Bool) -> Transaction ()

Remove every matching row allowed by the remove policy (zero or more).

Unlike update, this may match many rows in one call.

Examples

remove todos user (\todo -> todo.id == id)

btree

btree : Uniqueness -> (row -> a) -> Index row

Declare a btree index on a row projection.

Examples

btree Unique .email
btree NotUnique .owner

concurrently

concurrently : Index row -> Index row

Build this index concurrently when migrating (CREATE INDEX CONCURRENTLY on Postgres).

Examples

indexes = [ concurrently (btree NotUnique .owner) ]

foreignKey

foreignKey : (row -> a) -> References a -> Constraint row

Declare a foreign-key constraint from a local field to references.

Examples

constraints = [ foreignKey .owner (references users .id) ]

references

references : Table token row -> (row -> a) -> References a

Point a foreign key at a column on another table.

Examples

foreignKey .owner (references users .id)