Databases and APIs for Developers

Programme overview

Applications keep their data in databases and expose it through APIs. In this course you build both for Tallybook's invoicing, in Python with SQLite and Flask, all runnable in Colab. You'll design a schema that stores each fact once, add named constraints and watch the database refuse every invoice-level problem in the raw export on its own, query safely with parameters (and see an f-string query break on a customer called Chi's Bakery), import bank payments in transactions so a bad row can't leave half a batch saved, read query plans and add the index that turns a scan into a search, change the schema with numbered migrations, design REST routes with meaningful status codes and pagination, and test the API with pytest fixtures that give every test a fresh database. Every query, plan and test in the lessons is run and checked.

Curriculum

1. 01Why Applications Need Databases15 min

   • Why applications need databasesWhy an application's data belongs in a database rather than files, what a relational database gives you (structure, rules, safe concurrent changes and fast lookups), and your first SQLite database from Python.
2. 02Designing a Schema25 min

   • Designing a schemaDesign tables for an application (one table per kind of thing, keys that link them, no repeated facts), write the schema for Tallybook's customers, invoices, lines and payments, and load the clean data into it.
3. 03Constraints and Integrity15 min

   • Constraints and integrityLet the database enforce the rules (primary keys, foreign keys, NOT NULL and CHECK constraints), see it refuse every kind of bad row in Tallybook's raw export, and handle the refusals in code.
4. 04Queries from Code25 min

   • Queries from codeRun SQL from Python safely with parameters instead of building query strings, turn query results into useful structures, and calculate invoice totals and balances with joins and aggregation.
5. 05Transactions25 min

   • TransactionsGroup changes that must happen together into a transaction, so a failure halfway leaves nothing half-done, and see the difference on a batch of bank payments with one bad row.
6. 06Indexes and Query Plans15 min

   • Indexes and query plansSee how the database finds rows by reading its query plan, add an index so a lookup searches instead of scanning, and know what indexes cost.
7. 07Migrations25 min

   • MigrationsChange a live database's schema safely with numbered, versioned migrations that run once each, in order, inside transactions, and that every environment applies the same way.
8. 08REST API Design25 min

   • REST API designDesign an API around resources (customers, invoices, payments), choose routes, methods and status codes that clients can rely on, paginate lists, and build it in Flask on the database.
9. 09Testing the API15 min

   • Testing the APITest an API and its database together with pytest, using fixtures that give every test a fresh, small database, so tests are fast, independent and repeatable.
10. 10Final Project20 min

    • Final project: Tallybook's invoicing database and APIPlan your final project, a migrated, constrained database and a tested REST API for invoices and payments, with every rule enforced in the database and every endpoint tested.

Assessment and certificate

You are assessed with a final assessment (pass mark 60%) and a final project. Your certificate is awarded when you meet those requirements. Paying for the programme does not award it on its own.