Database-Backed CRUD

1. Context

A domain entity needs standard persistence operations — create, read, update, delete, and a handful of lookups — exposed through a public API.

2. Problem

How do you connect a public API directly to persistent storage operations for a domain entity, without hand-writing repetitive create/read/update/ delete plumbing in every service?

3. Forces

• Consistency — create and update paths often need a domain rule applied before, or instead of, touching the database directly.
• Reuse — query and command shapes (find-by-field, list with paging) recur across most entities.
• Coupling — the API’s operation names and the database’s query and command names are independent vocabularies that the service must connect deliberately.
• Complexity — some operations are simple pass-throughs to storage; others need an expression in between for validation or transformation.

4. Solution

Declare the entity’s persistence surface in @database (key, indexes, query, command), declare the matching public operations in @api, and in @service connect each API operation either directly to a database query or command for simple pass-throughs, or through an intermediate @expression when a rule needs to run first.

5. Structure

flowchart LR
    TodoApi --> TodoService
    TodoService -- direct connect --> Query["TodoItem.findById"]
    TodoService -- via expression --> Create[CreateItem] --> Command["TodoItem.createTodoItem"]
    TodoService -- via expression --> Delete[DeleteItem] --> CommandD["TodoItem.deleteTodoItem"]
    Query --> TodoDB[(TodoDB)]
    Command --> TodoDB
    CommandD --> TodoDB

6. DSL Sketch

@database

Database TodoDB
    engine = postgres
    configType = DatabaseConfig

    Entity TodoItem
        key(id)
        query findById(id: String) : TodoItem
        command Transactional createTodoItem(item:TodoItem) : TodoItem
        command deleteTodoItem(id:String) : _

@api

TodoApi style: rest
    engine = gin
    configType = ApiConfig

    get    /todo/item/{id} getItem2(_)             : TodoItem
    post   /todo/item      createItem(item:TodoItem) : TodoItem
    delete /todo/item/{id} deleteItem(_)           : _

@service

TodoService
    use database TodoDB as appDb
    use expression CreateItem as crt
    use expression DeleteItem as dlt

    connect api.getItem2    -> TodoItem.findById
    connect api.createItem  -> crt
    connect api.deleteItem  -> dlt

This is a minimal illustrative fragment, not a complete duplicated example — see Working Examples for the full, runnable model.

7. Consequences

Benefits

• Read-only lookups can bypass the expression layer entirely when no rule applies.
• The service stays the single place where API operations are wired to storage.
• The pattern scales from a handful of fields to a full domain entity without changing shape.

Trade-offs

• As domain rules grow, more operations move from direct connect to expression-mediated connect, and deciding which is which becomes the service author’s job.
• This pattern does not by itself enforce lifecycle rules — see Entity-Backed FSM when not every state transition should be a valid update.

8. Alternatives

• If the entity has a constrained lifecycle where not every state transition is valid, layer Entity-Backed FSM on top of the entity’s status field rather than allowing arbitrary updates through command.

9. Related DSL

• Database DSL Reference — entities, queries, and commands.
• API DSL Reference — the public operations exposed to clients.
• Service DSL Reference — connecting API operations to database queries, commands, or expressions.

10. Working Examples

• Task Manager — a persistent task-management application with lifecycle rules and CRUD operations exposed through an API.
• Inventory Management — a larger, CRUD-heavy domain covering materials, locations, stock states, and movements.

11. Related Patterns

• Entity-Backed FSM — adds constrained lifecycle rules on top of an entity this pattern otherwise updates freely.
• Secure Secret Access — how the database credentials behind this pattern are typically supplied.
• Approval Workflow — a decision-driven lifecycle that typically sits alongside this pattern’s persistence and API surface for the same entity.
• Synchronous API Composition — another shape of service-level connection, for composition rather than persistence.