API Fiddle

Run under Codespaces -- click here.

 

Welcome: Learn about APIs, using Flask and SQLAlchemy -- via a Codespaces "API Fiddle"

Background context

• RESTful APIs have become an accepted approach for networked database access
• JSON:API is an API standard for self-service APIs
• Microservice concepts stress that APIs should enforce the business logic for integrity and security

 

About this site

• What: Learn how to build such APIs, using Flask and SQLAlchemy

• Why: learn using a complete executable environment, a complement to conventional tutorials and docs:

  • Akin to a JS Fiddle - but here for a complete environment: running sample projects with live, updatable databases.

  • Test the API on the live database, with Swagger, cURL and an Admin App

    • Discover instant creation and logic enforcement, using API Logic Server
      
      

  • Explore the project code -- use the debugger, experiment with it.

• How: the enabling technology is Codespaces

  • It creates a cloud machine for these projects, and starts VSCode in your Browser. This eliminates install, configuration, and risk to your local machine.

 

What's in this Project

This contains 2 ready-to-run projects:

Project	What it is	Use it to explore...	Notes
1. Learn APIs using Flask SqlAlchemy	Northwind Database - Single Endpoint	Flask / SQLAlchemy basics	With HTTP, REST background
2. Learn JSON_API using API Logic Server	Northwind Database - All Endpoints - With Logic - With Admin App	JSON:API, and Rule-based business logic	You can start here if only interested in JSON:API
Next Steps	Create other sample databases	More examples - initial project creation from Database

 

These projects use the Northwind Sample Database (customers, orders, products).

Suggestion: close Welcome, above, to proceed.

 

 

 

---

 

1. Learn APIs using Flask SqlAlchemy -- Fully customizable, but slow

This first app (1. Learn Flask / SQLAlchemy) illustrates a typical framework-based approach for creating projects - a minimal project for seeing core Flask and SQLAlchemy services in action. Let's run/test it, then explore the code.

 

   Run / Test

 

To run the basic app:

1. Click Run and Debug (you should see 1. Learn APIs using Flask SqlAlchemy), and the green button to start the server

   • Do Not click Open in Browser
     
     

2. Copy the cURL text,
   Open the bash window, and
   Paste the cURL text

   • Observe the resulting response text
     
     

 

   Explore the Code

 

Open the readme to understand Flask / SQLAlchemy usage

• The readme also provides brief background on APIs, Flask, and SQLAlchemy

 

   --> Fully Customizable, but Faster Would Be Better

 

Frameworks like Flask are flexible, and leverage your existing dev environment (IDE, git, etc). But the manual effort is time-consuming, and complex. This minimal project does not provide:

• an API endpoint for each table

  • We saw above it's straightforward to provide a single endpoint. It's quite another matter -- weeks to months -- to provide endpoints for all the tables, with pagination, filtering, and related data access. That's a horse of an entirely different feather.
    
    

• a User Interface

• any security, or business logic (multi-table derivations and constraints).

Below, we'll see an approach that combines the flexibility of a framework with the speed of low-code.

 

When you are done, stop the server (Step 3).

 

You might want to close 1. Learn APIs using Flask SqlAlchemy..., above.

 

 

---

 

2. Learn JSON_API using API Logic Server -- Standard API, Logic Enabled, Declarative

   Project Overview

Project 2 is much more like a real server:

1. It implements a JSON:API -- a standard definition for filtering, sorting, pagination, and multi-table retrieval.

   • Such standards eliminate complex and time-consuming design

     • (Rest, unlike SQL, does not dictate syntax)
       
       

   • JSON:APIs are self-service, with consumer-defined response inclusion

     • Similar to GraphQL, clients declare what data to include, rather than relying on pre-defined resources.
       
       

2. It implements an Admin App (ReactAdmin)

3. It implements business logic

First, let's explore the service:   2.a) Start the Server,   2.b) Explore the JSON:API,   and 2.c) Explore JSON:API Update Logic.

Then, we'll see how to create it.

 

You might want to close Project Overview, above.

 

 

   2.a) Start the Server and Open the Admin App

 

1. Start the Server:

   1. Click Run and Debug
   2. Use the dropdown to select 2. Learn JSON_API using API Logic Server, and
   3. Click the green button to start the server
      
      

2. Open in Browser as shown below (you'll need to wait a moment for the server to restart for debug support).

   • This opens the Admin App, which provides access to Swagger.

 

   2.b) Explore JSON:API Get Using Swagger

Let's now use Swagger (automatically created) to explore the API.

 

      b.1) Open Swagger from the Admin App Home Page

 

Automatic Swagger: from the Home page of the Admin App, execute it like this:

1. Click the link: 2. API, with oas/Swagger
2. Click Customer
3. Click Get
4. Click Try it out
5. Click Execute:

 

      b.2) Consumer-defined response: the include argument

 

Note the include argument; you can specify:

OrderList,OrderList.OrderDetailList,OrderList.OrderDetailList.Product

You can paste the Customer response into tools like jsongrid, shown below. Note the response includes OrderDetail data:

 

      b.3) Additional Services

 

Servers often include non-JSON:API endpoints, such as the ServicesEndPoint - add_order to post an Order and its OrderDetails.

 

Suggestion: close 2.b) Explore JSON:API Get Using Swagger, above, to proceed.

 

 

   2.c) Explore JSON:API Patch Logic

 

APIs must ensure that updates adhere to business rules: multi-table derivations and constraints. Such business logic is not only critical, it's extensive: it often constitutes nearly half the code.

It's what makes an API a service.

 

Patch to test logic

This server implements such logic. Test it by patching the data below in the Terminal Window:

curl -X 'PATCH' \
  'http://localhost:5656/api/OrderDetail/1040/' \
  -H 'accept: application/vnd.api+json' \
  -H 'Content-Type: application/json' \
  -d '{
  "data": {
    "attributes": {
      "Quantity": 160
    },
    "type": "OrderDetail",
    "id": "1040"
  }
}'

We see that it fails - as it should. Note this is a non-trivial muti-table transaction - it must:

1. Get the price from the Product
2. Compute the amount (price * quantity), which requires we...
3. Adjust the Order amount, which requires we...
4. Adjust the Customer balance, which enables us to...
5. Check the credit limit - we see it's exceeded, so we roll the transaction back and return the error response

 

You might want to close 2.c) Explore JSON:API Patch Logic, above.

 

 

   Creation is Automated: Project, SQLAlchemy Models, API, Admin, Logic

You could code all this using Flask and SQLAlchemy... but it would take a long time.

In fact, this system was not coded by hand - it was created using API Logic Server -- an open source project providing:

• Automatic Creation: a single command creates the project from your database: SQLAlchemy Models, API, and the Admin App

• Customize with your IDE: declare spreadsheet-like business logic rules, and code extra API endpoints using the same Flask / SQLAlchemy techniques described in the first project

  • Rules are 40X more concise than code.
    
    

Use the Detailed Tutorial to further explore this app.

 

 

   Explore Creating New Projects

As noted above, you can create projects with a single command. To help you explore, there are several pre-installed sqlite sample databases:

cd API_Fiddle

ApiLogicServer create --db_url=sqlite:///sample_db.sqlite --project_name=nw

# that's a bit of a mouthful, so abbreviations are provided for pre-included samples
ApiLogicServer create --project_name=nw --db_url=nw                        # same sample as 2, above
ApiLogicServer create --project_name=nw- --db_url=nw-                      # no customization
ApiLogicServer create --project_name=chinook --db_url=chinook              # artists and albums
ApiLogicServer create --project_name=classicmodels --db_url=classicmodels  # customers, orders
ApiLogicServer create --project_name=todo --db_url=todo                    # 1 table database

Then, restart the server as above, using the pre-created Run Configuration for Execute <new project>.

Next, try it on your own databases: if you have a database, you can have an API and an Admin app in minutes.

Note: The system provides shorthand notations for the pre-installed sample databases above. For your own databases, you will need to provide a SQLAlchemy URI for the db_url parameter. These can be tricky - try ApiLogicServer examples, or, when all else fails, try the docs.

 

 

Key Takeaways: JSON:APIs -- Instantly, With Logic and Admin App; Standard Tools

JSON:APIs are worth a look:

• Eliminate design complexity and delays with standards
• Eliminate bottlenecks in backend development with Self-service APIs

API Logic Server creates JSON:API systems instantly:

1. Instantly executable projects with the ApiLogicServer create command, providing:

   • a JSON:API: end point for each table -- multi-table, filtering, sorting, pagination... ready for custom app dev
   • an Admin App: multi-page, multi-table apps... ready for business user agile collaboration
     
     

2. Leverage Standard Tools for development and deployment:

   • Dev: customize and debug with standard dev tools. Use your IDE (e.g. VSCode, PyCharm), Python, and Flask/SQLAlchemy to create new services. Manage projects with GitHub.

   • Deploy: containerize your project - deploy on-premise or to the cloud (Azure, AWS, etc).

   • Flexible as a framework, Faster then Low Code for Admin Apps

3. Declare security and multi-table constraint/validation logic, using declarative spreadsheet-like rules. Addressing the backend half of your system, logic consists of rules, extensible with Python event code.

   • 40X more concise than code - unique to API Logic Server
     
     

 

 

 

---

   

 

Appendix: What is API Logic Server

What is Installed

API Logic server installs with pip, in a docker container, or (here) in codespaces. As shown below, it consists of a:

• CLI: the ApiLogicServer create command you saw above
• Runtime Packages: for API, UI and Logic execution
  

 

Development Architecture

It operates as shown below:

• A) Create your database as usual

• B) Use the CLI to generate an executable project

  • E.g.: ApiLogicServer create --project_name=nw --db_url=nw-

  • The system reads your database to create an executable API Logic Project
     

• C) Customize and debug it in VSCode, PyCharm, etc.

  • Declare logic, code new endpoints, customize the data model

 

Standard, Scalable Modern Architecture

• A modern 3-tiered architecture, accessed by APIs
• Logic is automatically invoked, operating as a SQLAlchemy event listener
  • Observe logic is automatic re-used by web apps and custom services
• Containerized for scalable cloud deployment - the project includes a dockerfile to containerize it to DockerHub.

 

Appendix: Key Technology Concepts Review

Key Technology Concepts

Select a skill of interest, and
Click the link to see sample code

 

Tech Area	Skill	1. Learn APIs Example	2. Learn JSON:API Example	Notes
Flask	Setup	flask_basic.py	api_logic_server_run.py
	Events		ui/admin/admin_loader.py
API	Create End Point	api/end_points.py	api/customize_api.py	see def order():
	Call endpoint		test/.../place_order.py
Config	Config	config.py
	Env variables		config.py	os.getenv(...)
SQLAlchemy	Data Model Classes	database/models.py
	Read / Write	api/end_points.py	api/customize_api.py	see def order():
	Multiple Databases		database/bind_databases.py
	Events		security/system/security_manager.py
Logic	Business Rules	n/a	logic/declare_logic.py	Unique to API Logic Server
Security	Multi-tenant	n/a	security/declare_security.py
Behave	Testing		test/.../place_order.py
Alembic	Schema Changes		database/alembic/readme.md
Docker	Dev Env		.devcontainer/devcontainer.json	See also "For_VS_Code.dockerFile"
	Containerize Project		devops/docker/build-container.dockerfile