Skip to content

Data Access Service and BackLogic Platform

What Is Data Access Service

Data access service (DAS) is a concept for flexible and efficient relational data access. It has advantage over ORM, raw SQL and query builder technologies in a number of areas:

  • It provides developer with the flexibility of retrieving relational data into any shape and persisting object of any complexity, as the application needs; no more comprise on either object or data model.
  • It provides developer with a streamlined SQL-centric approach, where the focus of data access development is on developing SQLs, the cumbersome object-relational transformation is abstracted away, and the tedious data mapping is automated.
  • It is designed to be deployed and run in cloud environment and provides native support for microservice data management patterns. ACID saga, strongly consistent CQRS and event sourcing, efficient DDD aggregate repository, and shared connection pools are all possibilities with DAS.
  • It offers a uniform SQL-only data access layer to all client applications, be it a Java, C#, node or python application. All required skills to build this layer are SQL plus JSON.

Architecturally, DAS serves a dual purpose. On one hand, it provides data access as a backing service to the client application. On the other hand, it functions as a document-oriented gateway to the relational database. The data access service not only implements the data access or SQL logic but is also responsible for object-relational transformation. This allows the client application to access a relational database like a document store.

data-access-service

Philosophy

DAS follows a different philosophy than ORM, raw SQL and query builder and is driven by a number of important principles, namely separation, decoupling, abstraction and automation.

Separation

SQL is traditionally embedded in a host language, explicitly or implicitly, which creates paradigm mismatch and shifts the focus from SQL to the host language. However, the essence of relational data access development is SQL development (all the rest are boilerplate code). The development paradigm should be aligned with SQL instead of the host language.

DAS separates the data access development from the application development and turns it into a pure SQL development in a database-centric environment. The host language is abstracted away from data access development to eliminate the language impedance between SQL and host language.

Decoupling

ORM ties the database tables to a single object model and requires the objects to have similar structures to the tables. On one hand, this creates strong coupling between the object model and the data model, often forcing developers to compromise on either the object model, the data model, or both. On the other hand, due to different query needs, an application often requires multiple representations of the same data model.

To address this issue, tables should not be statically mapped to objects on a one-to-one basis. Instead, SQL should be used to encapsulate the data model from the object model, allowing the two to be developed and evolve independently.

DAS offers a repository service for persistence of domain objects and a query service for DTOs (data transfer objects) of different shapes. Both rely on SQL mapping, rather than table mapping, to create dynamic relationships between objects and tables.

Abstraction

Abstraction is the key to simplifying relational data access. DAS abstracts the broad data access patterns into three generic data access services: query, command and repository, and abstracts each of these services into a set of SQL and JSON components. The result is three componentized data access services that can be specified simply with SQL and JSON:

The query service is for retrieving ad hoc DTOs from database. A query service comprises:

  • input
  • output
  • query
  • input bindings, and
  • output bindings

where query is a single SELECT statement returning a relational data set for populating the output. The hard relational-to-object transformation problem is abstracted away and reduced to a simple data mapping problem.

The command service is for manipulating data in database. A SQL command service (aka SQL service) comprises:

  • input
  • sqls
  • input bindings
  • optional output
  • optional query, and
  • optional output bindings

where sqls is a sequence of DML statements to be executed against the database. The optional query is for returning updated data if desired.

The repository service is for CRUD operations of domain aggregates. A repository service comprises:

  • object
  • read
    • input
    • query
    • input bindings
    • output bindings
  • write
    • table bindings
    • column bindings

where the read component is essentially a dynamic query service supporting read operations; while the write component is a set of table and column bindings in place of the trivial insert, update and delete statements for write operations.

Automation

Data mapping should be automated. The various data bindings of data access services can largely be derived from the data and SQL components of respective data access services, that is

  • The input and output bindings can be generated from the input, output and query components for query service.
  • The input and output bindings can be generated from the input, output, sqls and query components for SQL service.
  • The input and output bindings can be generated from the object, input and query components for the read component of repository service.
  • The table and column bindings can be generated from the read component for the write component of repository service, along with database dictionary.
  • The complete simple repository services can be generated from database tables.

The developer should not be hand-coding these data binding components from scratch, but generating them using the development tool and reviewing and editing them, if needed.

Sample Services

The following are sample query, SQL and repository services for illustration purpose. They are coded with plain SQL and JSON. The data bindings are generated with Service Builder.

The following are the various components of a sample query service getCustomerById for illustration purpose. 

{
    "customerId": 123
}

{
    "customerId": 123,
    "customerName": "Joe",
    "address": {
        "address": "1145 Main St.",
        "city": "Los Angeles",
        "State": "CA"
    }
}

SELECT c.customer_id, c.customer_name,
    a.address, a.city, a.state
FROM customers c
JOIN addresses a ON a.customer_id = c.customer_id
WHERE c.customerId = :customerId

[
    {"parameter": "customerId", "field": ".customerId"}
]

[
    {"field": ".customerId", "column": "customer_id"},
    {"field": ".customerName", "column": "customer_name"},
    {"field": ".address.address", "column": "address"},
    {"field": ".address.city", "column": "city"},
    {"field": ".address.state", "column": "state"}
]

The input and output bindings are generated directly in Service Builder.

The following are the various components of a sample SQL service updateCustomerAddress for illustration purpose.

{
    "customerId": 123,
    "address": "1145 Main St.",
    "city": "Los Angeles",
    "State": "CA"
}

UPDATE addresses
SET address = :address,
    city = :city,
    state = :state
WHERE customer_id = :customerId

[
    {"parameter": "customerId", "field": ".customerId"},
    {"parameter": "address", "field": ".address"},
    {"parameter": "city", "field": ".city"},
    {"parameter": "state", "field": ".customerId"}
]

The input bindings are generated directly in Service Builder.

The following are the various components of a sample repository service Order for illustration purpose. The read operation supports: getOrderById, getOrderByCustomerId and getAllOrders.

{
    "orderId": 1,
    "customerId": 2,
    "orderLines": [{
        "lineId": 1,
        "productId": 2,
        "qty": 3
    }]
}

{
    "orderId": 1,
    "customerId": 2
}

SELECT o.order_id, o.customer_id,
       l.line_id, l.product_id, l.qty
 FROM orders o
 LEFT JOIN order_lines l on l.order_id = o.order_id
WHERE 1 = 1
  AND o.order_id = :orderId
  AND o.customer_id = :customerId

[
    {"parameter": "orderId", "field": ".orderId"},
    {"parameter": "customerId", "field": ".customerId"}
]

[
    {"field": ".orderId", "column": "order_id"},
    {"field": ".customerId", "column": "customer_id"},
    {"field": ".orderLine.lineId", "column": "line_id"},
    {"field": ".orderLine.productId", "column": "product_id"},
    {"field": ".orderLine.qty", "column": "qty"}
]

[
    {
        "name": "orders",
        "alias": "o",
        "object": ".",
        "rootTable": true,
        "mainTable": true,
        "columns": "./orders.o.columns.json"
    },
    {
        "name": "orderDetails",
        "alias": "l",
        "object": ".orderLines..",
        "rootTable": false,
        "mainTable": false,
        "columns": "./order_lines.l.columns.json"
    }
]

The following shows the column bindings for orders table, abbreviated for brevity. The column bindings for order_lines table are skipped.

[
    {
        "position": 1,
        "column": "order_id",
        "field": ".orderId",
        "key": true,
        "autoGenerate": false,
        "inputField": ".orderId",
        "insertValue": null,
        "updateValue": null,
        "version": false,
        "softDelete": false,
        "dataType": "number",
        "notNull": true,
        "keyEligible": true,
        "versionEligible": true,
        "softDeleteEligible": false
    },
    {
        "position": 2,
        "column": "customer_id",
        "field": ".customerId",
        "key": false,
        "autoGenerate": false,
        "inputField": null,
        "insertValue": null,
        "updateValue": null,
        "version": false,
        "softDelete": false,
        "dataType": "number",
        "notNull": true,
        "keyEligible": true,
        "versionEligible": true,
        "softDeleteEligible": false
    },
    ...
]

The input, output, table and column bindings are all generated directly in Service Builder.

Use of Data Access Service

DAS sits between the database and the client application that needs data access. The primary target of DAS is serverless function and microservice application, which stand to profit the most from externalizing data access layer and taking advantage of the cloud-native nature of DAS.

The second target of DAS is front application, Angular application for example, that chooses to hold light business logic in the front end and access database directly.

Lastly, DAS can help any application that likes to defer data access logic to backend service and take advantage of the simplicity and efficiency of data access services.

As DAS is deployed as HTTP API, it is a cross-platform solution for client applications authored in any programming language, as long as it can send HTTP request. However, DAS does have specialized data access client implemented for Java and planned for TypeScript and Python, to further simplify data access in these languages.

What Is BackLogic

BackLogic is the platform for developing and running data access service and consist of a number components as described in the following. It is such named as it provides data access logic as a backing service to the application.

Service Builder

DAS has Service Builder as the tool for data access development. It is implemented as a VS Code extension, so that developer can take advantage of the rich set of SQL and JSON tools available in the VS Code ecosystem. Service Builder provides functions for

  • Creating data access applications, modules, services and tests.
  • Generating input, output, table and column bindings; and
  • Deploying data access applications, modules and services into remote workspace on the development server.

Development Server

Service Builder is an online development tool, meaning that it must connect to a Development Server to work. The Development Server provides backend service to the Service Builderand hosts the development workspace for users.

The Development Server is best be launched as a development platform in the cloud with a container service such as AWS fargate, for a team of data access and client application developers to work collaboratively. However, it can also be launched on local machine to support local development.

Workspace

Runtime Server

Runtime Server is the platform for deploying and running data access applications in production. It is designed to be launched as a runtime data access platform next to the database, with a container service such as Kubernetes or AWS fargate.

The data access application is deployed to the Runtime Server by uploading it to a S3 bucket or code repository. The Runtime Server polls the S3 bucket periodically to sync its local code repository with the S3 repository.

Running Instance

Data Access Client

HTTP Client

Data access service is deployed as HTTP service. Therefore, it can be accessed with any HTTP client.

Java Client

The Java client is a light-weight wrapper of HTTP client. It allows the user to use the familiar DAO and repository design pattern with data access service. For example, to create a repository for Order object, the developer would simply write an annotated interface for the repository:

@RepositoryService("/../Order")
public interface OrderRepository {
    @read
    public Order getOrderByOrderNumber(int orderNumber);

    @read
    public List<Order> getOrderByCustomerNumber(int customerNumber);

    @create
    public Order create(Order order);

    @update
    public List<Order> save(List<Order> orders);

    @delete
    public void delete(int orderNumber);
}

The Java data access client would provide a proxy implementation behind the scene and invokes the data access service when any of the interface methods is called.

The proxy implementation can be obtained by the service class from the DataAccessClient class as in the following example:

@RestController
public class OrderController {
    private OrderRepository repository;

    public OrderService(DataAccessClient client) {
        this.repository = client.get(OrderRepository.class);
    }

    @GetMapping("/getOrderByCustomer/{customerNumber}")
    public List<Order> getOrder(@PathVariable int customerNumber) {
        return this.repository.getOrderByCustomerNumber(customerNumber);
    }
}

It can also be auto-injected into the service class by a framework, as in the case of Spring Boot application.