I’m giving another talk soon. This time about how a REST API relates to CRUD operations on a database. Same audience of folks, gonna cheat again and use a post as my speaker notes.
Menus
What is an API. An Application Programming Interface. An interface to access the application through programming.
Both an incredibly descriptive definition, but also useless for most people.
The go to analogy for an API is a restaurant menu.
The kitchen is similar to your backend server. The wait staff is your frontend client. And the menu is the API. You can read it (documentation) for what the kitchen can produce, and you interact with the waiter/waitress to place the order.
Databases
A SQL database is going to be our program.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
segment TEXT NOT NULL,
region TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('open', 'shipped', 'cancelled')),
total_amount REAL NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE order_items (
id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL,
product_name TEXT NOT NULL,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price REAL NOT NULL CHECK (unit_price >= 0),
FOREIGN KEY (order_id) REFERENCES orders(id)
);A simple database with three tables, customers, orders, and order_items.
It even provides a simple way to interact with it.
SELECT
customers.id,
customers.name,
customers.segment,
customers.region
FROM customers
ORDER BY customers.name;This kind of SQL command lets us query all the customers, return their names, segments, and regions, and sort them by name.
Programming
Unfortunately everywhere can’t directly run SQL. But we can use other programming languages to call it.
import json
from pathlib import Path
import sqlite3
DATABASE_PATH = Path(__file__).resolve().parents[1] / "data" / "orders.db"
def list_customers() -> list[dict]:
sql = """
SELECT
customers.id,
customers.name,
customers.segment,
customers.region
FROM customers
ORDER BY customers.name
"""
connection = sqlite3.connect(DATABASE_PATH)
connection.row_factory = sqlite3.Row
rows = connection.execute(sql).fetchall()
connection.close()
return [dict(row) for row in rows]
def main() -> None:
print(json.dumps(list_customers(), indent=2))
if __name__ == "__main__":
main()
Great, we can now run the SQL to query the database. But what if I don’t want to run a Python script every time?
API
So now we get to APIs.
In this case, we’re going to use webserver with the Python package FastAPI Flask.
from flask import Flask
from flask.views import MethodView
from flask_smorest import Api, Blueprint
app = Flask(__name__)
api = Api(app)
customers_blp = Blueprint(
"customers",
__name__,
url_prefix="/customers",
description="Customer read operations",
)This lets us create a Python Web Server (Flask application). That is a special kind of program that listens for incoming requests (commands or instructions), processes them, and provides output.
We then use a Blueprint to group together common functionalities by feature.
@customers_blp.route("")
class CustomersResource(MethodView):
@customers_blp.response(200, CustomerSchema(many=True))
def get(self) -> list[dict]:
return queries.list_customers()We can register a route for those requests to go in our Web Server, when yourURL/customers is hit, it runs this. Because this belongs to the customers_blp, it inherits the /customers route.
def list_customers(db_path: Path = DATABASE_PATH) -> list[dict]:
sql = """
SELECT
customers.id,
customers.name,
customers.segment,
customers.region
FROM customers
ORDER BY customers.name
"""
with connect(db_path) as connection:
rows = connection.execute(sql).fetchall()
return [row_to_dict(row) for row in rows]Notice that query from anywhere?
Explaining what a REST API is specifically is probably out of scope, play it by ear.
Next steps
How do folks actually use the API?
Two predominant methods.
The one that I prefer.
curl yourURL/customersIt’s simple and and I can run it from the Terminal.
Though the output isn’t the easiest to read
[{"id":1,"name":"Acme Corp","segment":"Enterprise","region":"Midwest"},{"id":12,"name":"Atlas Manufacturing","segment":"Enterprise","region":"Midwest"},{"id":13,"name":"Bluebird Travel","segment":"Small Business","region":"West"},{"id":2,"name":"Brightside Health","segment":"Mid-Market","region":"West"},{"id":10,"name":"Crescent Telecom","segment":"Enterprise","region":"South"},{"id":6,"name":"Evergreen Bank","segment":"Enterprise","region":"Northeast"},{"id":9,"name":"Harbor Supply Co","segment":"Small Business","region":"Northeast"},{"id":14,"name":"Keystone Energy","segment":"Enterprise","region":"South"},{"id":4,"name":"Lakeside Retail","segment":"Small Business","region":"Midwest"},{"id":19,"name":"Maple Home Goods","segment":"Small Business","region":"Northeast"},{"id":7,"name":"Metro Pharmacy","segment":"Mid-Market","region":"South"},{"id":3,"name":"Northstar Logistics","segment":"Enterprise","region":"South"},{"id":15,"name":"Oak Street Dental","segment":"Small Business","region":"Northeast"},{"id":8,"name":"Pioneer Insurance","segment":"Enterprise","region":"West"},{"id":17,"name":"Prairie Grocers","segment":"Mid-Market","region":"Midwest"},{"id":16,"name":"Redwood Software","segment":"Mid-Market","region":"West"},{"id":11,"name":"Riverbend Credit Union","segment":"Mid-Market","region":"Midwest"},{"id":18,"name":"Sterling Auto Group","segment":"Enterprise","region":"South"},{"id":5,"name":"Summit Foods","segment":"Mid-Market","region":"Northeast"},{"id":20,"name":"Vertex Labs","segment":"Mid-Market","region":"West"}]% The normal method is with a web client where a separate UI assembles the web page with the JSON (less common is the demo’s variant sending HTML over the wire).
Remember to hit F12 and show the network tab in the demo.
Edit May 27, 2026. FastAPI had a supply chain attack via Starlette. Updated the demo to use Flask. Also added GraphQL to illustrate that REST isn’t the only kind of API on the web.