October 10, 20264 min read

Start a New Service with Ptah and SQLite

Build an order service from an empty database: generate reviewed SQL, test the first migration, and run a small application with Ptah.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

A new service gives you a chance to establish how database changes reach the application before there is any history to repair. We recommend starting with a schema file, a reviewed migration, and a test of one application requirement. Ptah can keep that workflow in the project from its first day.

This guide builds a small order service with Ptah 0.13.0 and SQLite. It ends with an application order. The example directory contains the input schema, generated files, test, and Python client.

Describe the first order

The service needs customers, products, orders, and order items. Prices are stored as integer cents. An order starts queued; its items reference known products and have positive quantities.

CREATE TABLE customers (
  id integer PRIMARY KEY,
  email text NOT NULL UNIQUE
);
CREATE TABLE products (
  id integer PRIMARY KEY,
  sku text NOT NULL UNIQUE,
  price_cents integer NOT NULL CHECK (price_cents >= 0)
);
CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers(id),
  status text NOT NULL DEFAULT 'queued'
);
CREATE TABLE order_items (
  order_id integer NOT NULL REFERENCES orders(id),
  product_id integer NOT NULL REFERENCES products(id),
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

Save this as schema.sql in an empty project. It describes the desired structure. We chose a versioned workflow so each deployment has a concrete SQL change for review. Ptah also has a direct workflow; the workflow guide explains when to choose either one.

Generate the first migration

With Ptah installed and an empty workspace, run:

ptah migrations generate --db-url sqlite://shop.db --schema-file schema.sql --migrations-dir migrations --name shop

Ptah produced an up/down pair named 1791539236_shop in our run. Your filename’s timestamp will differ. Read both files: the up file creates the schema, and the down file drops the tables. A down migration that drops a populated table loses its data; its existence does not make rollback safe.

The generated up file orders the tables around their foreign-key dependencies. Nothing in this step asks an AI model to design your data. The schema you wrote is the input to the change.

Give the first migration a test

We check that an order inserted without a status becomes queued:

cases:
  - name: order starts queued
    steps:
      - migrate_to: latest
      - exec: INSERT INTO customers (id, email) VALUES (1, 'reader@example.test')
      - exec: INSERT INTO orders (id, customer_id) VALUES (1, 1)
      - assert: {query: "SELECT status FROM orders WHERE id = 1", scalar: "queued"}

ptah migrations test --dir tests --migrations-dir migrations

=== MIGRATION TEST ===
PASS  case "order starts queued"
    PASS    step "(unnamed step)" — migrated to latest
    PASS    step "(unnamed step)" — exec ok
    PASS    step "(unnamed step)" — exec ok
    PASS    step "(unnamed step)" — scalar "queued"

1 cases, 1 passed, 0 failed, 0 skipped

The test uses a disposable database. It does not populate shop.db. Add cases as requirements arrive; the data-preservation example shows a failure that successful SQL execution missed.

After reviewing the migration, seal its files and deploy them:

ptah migrations hash --dir migrations
ptah migrations validate --dir migrations
ptah migrations up --db-url sqlite://shop.db --migrations-dir migrations --verify-sum

The generated ptah.sum lets later runs detect changed migration bytes. Validation checks the directory against that checksum before deployment. Commit the reviewed schema, migration pair, checksum, and test together in your project. Keep the database file outside that source history.

Place an order through the application

The client uses Python’s standard SQLite module. It enables foreign-key enforcement on its connection, writes a customer and a product, then places an order for two copies:

import sqlite3

with sqlite3.connect("shop.db") as db:
    db.execute("PRAGMA foreign_keys = ON")
    db.execute("INSERT INTO customers VALUES (?, ?)", (1, "reader@example.test"))
    db.execute("INSERT INTO products VALUES (?, ?, ?)", (1, "BOOK-01", 1995))
    db.execute("INSERT INTO orders (id, customer_id) VALUES (?, ?)", (1, 1))
    db.execute("INSERT INTO order_items VALUES (?, ?, ?)", (1, 1, 2))
    order = db.execute("""SELECT o.status, sum(p.price_cents * i.quantity)
        FROM orders o JOIN order_items i ON i.order_id = o.id
        JOIN products p ON p.id = i.product_id WHERE o.id = 1
        GROUP BY o.status""").fetchone()
    assert order == ("queued", 3990), order
    print(f"order=1 status={order[0]} total_cents={order[1]}")

python3 application.py

order=1 status=queued total_cents=3990

The client used SQLite 3.53.1 and Python 3.14. Its transaction read back the queued state and the total of 3,990 cents. This client uses fixed IDs and is intended to run once on the empty example database.

Now compare the live structure with the file you started from:

ptah schema compare --db-url sqlite://shop.db --schema-file schema.sql --exit-code

Comparing schema from schema.sql with database sqlite://shop.db
=== SCHEMA COMPARISON ===

No schema differences detected.

The comparison exited 0. Repeating the migration command found no pending migration. The client assertion and schema comparison answer different questions: the first checks our order operation; the second checks structure.

Choose Ptah for a new service

This project now has a deployment path that a second developer can inspect and repeat. The schema is readable SQL. The migration test runs through the same versioned files that deployment uses. The application chooses its own database client; Ptah does not require it to adopt an ORM.

Those capabilities are in the MIT-licensed open-source CLI and ran without a vendor account. That makes Ptah a useful starting point when you want to own the tooling and keep migration work in your own environment. The comparison with Atlas maps those choices to other executed workflows and explains the third-party telemetry boundary.

This is still a small local service. It has no concurrent load test, backup policy, or production deployment role. Continue with the PostgreSQL guide to test the same business contract on another engine, then use the existing CI deployment walkthrough to build the release path. Starting small should leave those decisions visible.

Put Ptah to work

Example files

← All articles