Create Customizable Database App Systems With One Command

Instead of enormous amounts of tedious, repetitive code, new open-source technology now enables you to create customizable, model-driven systems with a single command:

ApiLogicServer create --project_name=/localhost/ApiLogicProject --db_url=

This creates a working, customizable system, directly from your database:

• A React-based admin app, for back-office data maintenance and end-user collaboration.
• An API, with Swagger, for custom app dev and application integration.
• The API enforces backend logic, using unique spreadsheet-like rules that are remarkably more concise than code.

In this tutorial, we will explore:

• Key requirements
• The creation process
• Architecture
• The created system
• Customization
• Technology considerations

Key Requirements: Fast and Simple Is Not Enough

As developers, we recognize and appreciate the need for business agility, to create and iterate systems fast.  Certainly, getting a running system with 1 command - ready to engage stakeholders - is a great start.

But it's not enough.  We've all seen systems that provide a great start, but don't enable us to finish the project, or maintain it. This section lists some key requirements.

Extensible Automation

As desirable as automation is, it is simply not feasible for all behavior to be automated.  It's not even desirable - full automation would require 1000's options.  

The right approach is extensible automation. Some key requirements are described below.

Standard Languages, Tools (IDEs, Source Control)

Automation needs to create file-based projects that can utilize familiar tools, including:

• Standard languages
• IDEs, for code editing and debugging
• Source control

For example, storing critical behavior in database/cloud settings makes projects difficult to manage.

Models, Not Code

Customization should not be based on altering generated code.  Even a small app we present here involves hundreds to thousands of lines of quite complicated code Python, JavaScript, HTML, etc., and generated code is often hard to read.

In fact, generating code is ill-advised, since it dramatically reduces the level of abstraction when you need to understand it or customize/extend it.  Ideally, the creation process creates models - simple to understand,  customize and extend, without training.

Modern Architecture: API, Logic, Scalable Containers

Modern systems require an API to support mobile apps and application integration.  So any automated creation must include the API.

APIs must operate as services - not "JDBC on the wire", but include enforcement of business logic such as multi-table derivations and constraints, and actions such as sending mail or messages.

Finally, modern systems leverage containers, such as Docker, for isolated development and deployment.

Business Logic - Automation, Not "Your Code Goes Here"

Backend logic is often nearly half the system.  Low-code approaches either fail to address such logic or employ "your code goes here" on a UI event.  This has 2 significant drawbacks:

• Poor abstraction - this approach results in significant logic code; we'll see later how a simple requirement explodes in hundreds of lines of code.
• Incorrect partitioning - code on UI buttons is hard to re-use, resulting in duplication, inconsistent results, and costly maintenance.

Effective automation requires us:

• Provide automation to maintain a high level of abstraction for the front-end and backend.
• Partition the logic out of UI events so it can be automatically re-used - with automatic re-use of logic across all applications.

Creation Process

The create command above creates an executable project by introspecting your database, designated by the --db_url argument. The blank value above uses the pre-supplied sample database (Northwind - Customers, Orders, etc.). To use your own database, specify an SQLAlchemy URI (database type, server, schema, credentials, etc).

You can open the created project in your IDE to customize and debug. The project elements are files, so you can manage projects with your existing tools for source control, code management, etc. We'll examine the project more closely, below: 

Modern Architecture - n-tiered, API-Driven

The system is installed via pip install, or (as pictured above) a docker container.  It executes as a standard multi-tier application (shown below):

1. The admin app is implemented not as enormous amounts of complex JavaScript and HTML, but rather a model - a description of what the app should do, not how to do it. In this case, the model is represented as an easy-to-edit YAML file located in your ApiLogicProject.
2. The admin app executes in the Browser using react-admin.  It uses the automatically created JSON:API (SAFRS) to read and write data.
3. The API uses SQLAlchemy to read/write data.  The business logic to maintain database integrity is declared in declare_logic.py.  This logic expresses multi-table derivations and constraints, and actions such as sending email or messages.  This is also a model file consisting of rules and Python, which we'll see below.  It is used by the Logic Bank rule engine, which listens for SQLAlchemy before_flush events and enforces your logic.

Note this approach of "API with Logic" is valuable for all development (e.g., custom apps and application integration), by factoring out shared logic and ensuring its re-use.

Created System

Let's examine the system built from the create command.

Models, Not Code

Below is the created ApiLogicProject, opened in VS Code.  The tree shows a standard, flask-based project.  Beyond usual configuration files, the key files are shown below:

1. expose_api_models.py: this model file defines your API; for each table you expose, the api engine provides endpoints for data read/write.
2. admin.yaml: this model file defines your admin app.  As for the API, it's easy to understand and modify
3. models.py: these classes (eg, Customer) are used by SQLAlchemy to define your data model.  They enable code completion in the IDE.
4. api_logic_server_run.py: this is the class that you actually execute to set up the API and start the server.

So, that's a quick look at the created model files.  Now let's see what they do.

React Admin Application - Multi-Page, Multi-Table, Auto-joins

As shown in the diagram below, two pages are created for each table: a list page and a show page.  So, in our Northwind example, our create command produces a 30-page application.

Lists are a key aspect of any application; they support multi-field search, sort by clicking column headers, and pagination for good large-result performance. The diagram below illustrates support for multi-page applications: clicking the customer zooms into its show page.

Pages are multi-table, automatically including related data, such as the Placed Orders. Similarly, you can click an Order to see its details, including a list of its Order Details. You can thus "walk the database relationships" in the admin app.

Observe the Employee (Sales Rep) for the Order. It is an automatic join. Each Order has a foreign key of EmployeeId, a number that makes no sense for users.  So the system has automatically joined the Employee.Name. Popup dialogs are also provided to see the full set of related data.

Update Support - Lookups, Logic

The update includes important support for Lookups, to fill in foreign keys by name, not Id. In the screenshot below, OrderDetails relate to their Product by ProductId  Not only does the system provide the automatic join, but also a lookup to filter/select a Product (Chai, Chang, etc.); the system fills in the foreign key.

The save button fires logic to recalculate the Order AmountTotal. This logic was via a few rules - not code - which we'll see shortly.

Key Take-away: not a page-by-page "low code" screen painter, but the single create command builds a complete, multi-page/multi-table application.

API - With Swagger

The system also automatically creates an API, which is used by the app, and for custom apps and application integration.  The API supports filtering, pagination, and related data access.

We can examine the API via automatically created Swagger. An endpoint for every table, with verbs for retrieval and update.

Key Take-away: it's not so hard to create a single "hello world" endpoint, and even return some SQL data. It's quite another matter for the create command to build endpoints for every table, with filtering, sorting, pagination, and related data access.

Customization - Python and Your IDE

Everything shown so far was automatically built from the create command.  A great start, but it's critical to be able to customize the system, utilizing standard languages and tools.

The create command builds an ApiLogicProject that you can customize in your IDEs, such as PyCharm or VS Code. Shown below in VS Code, the project is built-to-customize: the model, the API, the logic, and the application. Pre-built configurations enable the use of Docker or pip install, and the use of IDE tools like the code editor and debugger.

The sections below take a brief look at customizations for the model, the app, the API, and the logic.

Customize Application - Column Order and Labels

As noted above, the admin app is implemented not as enormous amounts of complex JavaScript and HTML, but rather a model - a description of what the app should do, not how to do it. In this case, the model is represented as an easy-to-edit YAML file located in your ApiLogicProject.

The lower left pane above shows the YAML file.  It's trivial to reorder the columns, specify labels, and make other basic changes.

Custom Apps - Use API / Logic

This instant application is suitable for instant collaboration, and for back-office data maintenance.  For customer-facing custom applications, use your tools of choice in conjunction with the API for data retrieval and update.

Your custom apps operate in exactly the same manner as the Admin app shown in Figure 1 - Architecture, above. Importantly, this API:

• Enforces your business logic (described below) with remarkable conciseness.
• Provides automatic re-use of logic by encapsulating it in the API, rather than each UI controller -- the improved quality and simpler development.

Customize API - Add End-Points

We can also customize the API.  This code below (lines 124-130) defines a new endpoint, addOrder. Lines 108-119 are used to generate the Swagger sample data.

The code is short since all the derivation and constraint logic is declared in rules as we'll see in a moment. These rules are automatically re-used, whether transactions originate in the created API, or our customizations.

You can use your IDE to debug your customizations.  Modern IDEs enable you to inspect variables, set watches, execute step-by-step, etc.  In the diagram below:

• We use the Swagger (right pane) for our custom endpoint to submit an order consisting of an Order and a set of Order Details.
• We set a breakpoint in our custom endpoint, line 124.

Declare Logic to Address the Backend Half of the System

For the transactional systems targeted by ApiLogicServer, it is common that the backend logic is nearly half the app: multi-table derivations and constraints, and actions such as sending mail or messages.

The problem is that a simple "cocktail napkin" specification of 5 lines explodes into 200 lines of code as shown below:

Instead of writing all the code, you can use ApiLogicServer to declare spreadsheet-like rules for multi-table derivations and constraints.  These 5 rules shown below (lines 49-66) represent the same logic as 200 lines of Python - an executable representation of the cocktail napkin spec above.

Experience has shown that rules address 95% of backend logic.  This is profound: you are reducing the back-end half of your system by 40:1 (200 lines of code / 5 rules).

The ApiLogicServer rules engine loads these rules when the server starts.  It listens for SQLAlchemy update events and automatically enforces your logic as API updates are issued. 

Execution includes automation of ordering and optimization.  This automation is repeated as your alter logic during iteration/maintenance, so you continue to derive value over the life of the project.

Key Take-away: back-end logic is a key element for transactional systems.  It is often ignored by No/Low-Code approaches, or addressed with "your code goes here".  ApiLogicServer not only creates projects in moments but enables you to customize them, using declarative rules that dramatically increase business agility.

Customize Logic: Logic = Rules + Python

Rules are no exception to our general approach: maximize automation, but provide for extensibility.  95% automation is great, but of no value unless we can address the last 5%.

So, logic is not just rules, but also Python.  The diagram below shows the same place-order transaction, stopped at a breakpoint in our Python logic:

• Code completion is helpful in declaring rules (line 37)
• The rule on line 81 declares an event; the Python event handler (congratulate_sales_rep) is shown by the arrow
• Within your Python code, you can set breakpoints (line 73), and examine variables (left pane)
• In addition to the debugger, the logic engine creates a logic log - it shows the execution of each rule (bottom pane), with the column values, and indents to show multi-table chaining

Key Take-away: rules are declared in Python, with code completion. Rules can invoke Python code with full debugger support, providing flexibility to perform operations not automated by rules.

Iteration/Maintenance Cycles - Rebuild Command

In addition to the create command, the system provides a rebuild command so you can address changes to the database schema.  This operates on existing projects, preserving customizations.

Technology Considerations

This section summarizes the key aspects of the technology and addresses some questions we are sometimes asked.

Key Aspects: Automation, Model-Driven, Customizable

Perhaps the most striking thing about ApiLogicServer is automation: the create command builds a complete admin app and API, in moments.  And automation addresses not just the client - you can declare rules for automatically re-used backend logic.

But it's also important to consider what is built from the create command.  Not just a project you can customize and debug in your IDE... the created artifacts are models that declare "what, not how".  These models are far simpler to understand, modify and maintain than enormous amounts of generated code.

Finally, projects are customizable - use your IDE, Python, and source control tools.

Low Code/No Code?

ApiLogicServer differs from many Low Code Systems:

• Screen creation: unlike systems that require you to paint each screen, ApiLogicServer creates a complete multi-page application, automatically.
• Backend logic: unlike systems that omit this requirement, ApiLogicServer provides logic - rules extensible with Python - to dramatically reduce backend logic, and ensure its re-use.
• Architecture: unlike cloud-based systems, ApiLogicServer can run on your desktop, in standard servers, and in containers.  Unlike systems that embed business logic in screen functions, ApiLogicServer provides an API that encapsulates the logic for automatic re-use.
• IDE-friendly: unlike proprietary development environments, ApiLogicServer integrates naturally with your IDE and tools environment.
• Visual Programming: ApiLogicServer focuses on a declarative approach, which provides significant advances over procedural code: automatic ordering, automatic re-use, automatic dependency management.  Visual programming is a procedural approach that does not provide this automation.  

Learning Curve?

Learning a new technology is overhead that must be weighed against the gain.  Let's look at the gains and the elements that must be learned.

For transactional systems, the gain is considerable -- the create command represents weeks or months of saved effort.  Rules are far more concise than code, easier to maintain, and automate re-use for quality. Let's examine the overhead.

The create command requires no particular background in Python, just basic development and database skills.  That leaves 2 things to learn: Python (if you don't already know it), and rules.

Python is required for customization.  Importantly, this is not "from scratch" coding -- the create command builds your project structure and a working system.  So you're always adding code to a working base - much simpler than a cold start. And the amount of Python you need for basic customizations is easily mastered in a day or so, particularly given the examples.

Rules are certainly new.  There are 10 rules, so learning them is pretty quick.  The real trick is learning to "think spreadsheet" - instead of code, declaring multiple rules to address a requirement.  These examples should help nail down the key patterns.  Experience suggests 2 days should be sufficient to ramp up on rules.

Custom Apps?

The admin app is optimized for instant creation with a pre-defined type of application, without attempting to address all possible UI requirements.  The API is provided for custom apps; significantly, it enforces your business logic with automatic re-use, a far better solution than embedding business logic in each UI controller.

Proven Technology?

There's a great deal of innovation here - automated construction of apps and APIs, and rules.  It's a natural question whether it stands up to serious applications.

As it turns out, this is in fact a proven technology.  It began with the Wang PACE system, installed at over 6500 sites.  The technology evolution continued at Versata, with over 700 sites, a multi-billion dollar IPO, and backed by the founders of Microsoft, SAP, Informix, and Ingres.  Customers paid around $50k per computer for these products.

Why Not Rete?

Some have asked why there is a specialized rules engine instead of a Rete engine.  While good for decision logic, Rete has proven to be a poor choice for transaction processing due to poor multi-table performance.  In particular, chained aggregates result in many high-cost SQL commands.

For example, consider substituting a different Product for an Order Detail - the system must recompute the order total and customer balance to ensure it does not exceed the credit limit.  A Rete engine would add up all the Orders, and all their Order Details, to compute the new Balance.

The transaction logic engine in ApiLogicServer was designed specifically to address this problem, with automatic pruning and optimization.  The key is integrating logic into the persistence mechanism (SQLAlchemy), which enables the system to compare old/new values on an update.  This enables the system to prune rules for unchanged data, and use deltas to replace chained aggregates with a single adjustment update.  This has been shown to reduce transaction times from minutes to sub-second.

The example here presumes the use of stored aggregates. It is a design choice whether aggregates are stored or virtual; altering the schema will automatically re-optimize the transaction logic. This enables you to change your design, late in the project, without having to recode all the related logic.

Summary

So there you have it. In a few moments, you can create a complete system, ready for Agile collaboration and near-instant iteration. You can customize and debug the app, API, and logic in your IDE, and manage it with your existing source control systems.

We saw how the system is mainly models - executable descriptions of what we want, not the detailed code that expresses the how. Much simpler to get started, customize and maintain. And it runs in an isolated container, for clean development and deployment.

To explore it further:

• Open Source: the GitHub site contains install instructions, samples, documentation, and tutorials
• Explore the app: you can explore this application and API,  running on PythonAnywhere
• Video: watch how this application was built on YouTube

Acknowledgments

Much of the work here is through the efforts of Thomas Pollet.  In particular, it's his work behind the API and the admin app. I also appreciate the help of long-time friend Max Tardiveau, who guided me through some rocky Docker moments.

Thanks for reading!