Problem: Imagine that you are given an input as:

  { 
    "city": "Los Angeles"
  }

You are asked to prepare a query to output a list of customer objects as:

  [{
    "customerId": 12,
    "customerName": "John Smith",
    "addresses": [{
        "street": "1638 Grand Ave.",
        "city": "Los Angeles",
        "State": "CA"
    }],
    "paymentMethods": [{
        "methodType": "CREDITCARD",
        "cardNumber": "26153659890218398",
        "expireDate": "2025/05"
    }]
  }]

or something that is even more complex.

Solution: You could simply

1. Prepare a SQL query as:

     SELECT c.customer_id, c.customer_name, 
             a.street, a.city, a.state,
             pm.method_type, pm.card_number, pm.expire_date
       FROM customer c
       LEFT JOIN address a ON a.customer_id = c.customer_id
       LEFT JOIN payment_method pm ON pm.customer_id = c.customer_id
       WHERE a.city = :city
   

2. Click a button to bind the query to the input and output.

A query service is ready for test!

The query service is for retrieving ad hoc DTOs. It always runs a single SQL statement to retrieve the output object(s), as opposed to the N+1 query technique ORM would use, even for very complex output object.

A complete example

More examples

Problem: Imagine that you are given a DDD order aggregate, or something event more complex, as:

  {
    "orderId": 123,
    "customer": {
      "customerId": 12
      "customerName": "John Smith"
    }
    "items": [{
        "itemId":1
        "product": {
          "prodId": 32,
          "prodName": "Phone",
          "price": {
            "currency": "USD",
            "amount": 999.99,
          }
        }
    }],
    "totalAmount": 999.99
  }

where customer and product are read-only value objects.

You are asked to prepare a repository for the aggregate. The repository shall support read by orderId, read by customerId and read all, depending on whether the query input is:

  { 
    "orderId": 123,
  }

or

  { 
    "customerId": 345
  }

or

  {}

Solution: You could simply

1. Prepare a SQL query to read from the order, order_item, customer and product tables, as:

     SELECT  o.order_id, o.customer_id
             _c.customer_name, 
             oi.item_id, oi.prod_id
             _p.prod_name, _p.price_currency, _p.price_amount,
             sum(_p.price_amount) as total_amount
       FROM order o
       JOIN customer _c ON _c.customer_id = o.customer_id
       LEFT JOIN order_item oi ON oi.order_id = o.order_id
       LEFT JOIN product _p ON _p.prod_id = oi.prod_id
       WHERE 1 = 1
         AND o.order_id = :order_id
         AND o.customer = :customer_id
   

2. Click a button to bind the query to the input and object for read.




3. Click another button to bind the aggregate object to the order and order_item tables for write.

A repository service is ready for test!

The repository service is for CRUD operations of domain objects. On the read side, it is a dynamic query service. On the write side, it supports asymmetric write by design. In this example, it only writes into the order and order_item tables, rather than all the order, order_item, customer and product tables it reads from.

A complete example

More examples

Problem: Imagine that you are given an order event as:

  {
    "eventId": "f47ac10b-58cc-4372-a567-0e02b2c3d479",
    "eventType": "OrderPlaced"
    "eventTimestamp": "2022-03-06T13:45:30.123"
    "orderId": 123,
    "data": "..."
  }

where data is the event data in the form of a JSON string:

  {
    "orderId": 123,
    "customerId": 12
    "items": [{
        "itemId":1
        "prodId": 32,
        "qty": 100
    }]
  }
  

to be saved in a JSONB column.

You are asked to prepare a command to save the order event into your PostgreSQL event store and update the order state model in the same ACID transaction.

Solution: You could simply

1. Prepare a sequence of DML statements as:

     INSERT INTO event {
       event_id, event_type, event_timestamp, order_id, data 
     }
     VALUES (
       :eventId, :eventType, :eventTimestamp, :orderId, CAST(:data) 
     )
     ;
     INSERT INTO order (
       order_id, customer_id
     )
     SELECT
       FROM event
       WHERE event_id = :eventId
     ;
     INSERT INTO order_item (
       item_id, order_id, prod_id, price, qty
     )
     SELECT 
       FROM event
       WHERE event_id = :eventId
     

2. Click a button to bind the SQL statement to the input event record.

A command service is ready for test!

The SQL command service is for ad hoc data manipulation. In this example, it inserts the event record in the event store and then use the INSERT...SELECT statements to create the current order state.

A complete example

More examples