SQLite REST API Starter

Category: Backend & APIs · 🔒 Self-hosted only

This page is generated from the Air Pipe marketplace. Browse it live to install into your organization.

A production-shaped CRUD API over SQLite — users, posts, and comments, with optional JWT validation. Zero setup: there is no database server to provision. Point Air Pipe at a file path and you have an API backed by a single, portable .db file.

The zero-config, file-based member of the REST API Starter family. The relational shape of the REST API Starter pack (Postgres) and the MySQL REST API Starter pack — same endpoints, SQLite dialect, no server to run.

When to use SQLite

SQLite is the right backend for local-first apps, prototypes, edge / single-binary deployments, CLI tools, and read-heavy workloads where the whole dataset lives next to the app. The database is one file you can copy, version, or ship inside a container image.

Self-hosted only. SQLite runs inside the Air Pipe process and writes to the local filesystem, so it is not available on managed (hosted) Air Pipe — managed mode rejects the sqlite driver. Run this pack with a self-hosted Air Pipe agent (airpipe server). For a hosted database, use the Postgres or MySQL starter instead.

What's included

File	Purpose
`schema.sql`	SQLite tables, keys, and foreign keys
`users.yml`	User CRUD — bcrypt-hashed passwords
`posts.yml`	Post CRUD — author join and comment count
`comments.yml`	Comment list / add / delete
`auth.yml`	JWT validation pattern (optional)
`seed.yml`	Creates tables and loads sample data

Endpoints

Users

Method	Path	Body	Description
`GET`	`/api/users`	—	List users
`POST`	`/api/users/get`	`{id}`	Get user by id
`POST`	`/api/users/create`	`{email, username, password}`	Create user
`PUT`	`/api/users/update`	`{id, email?, username?}`	Update user
`DELETE`	`/api/users/delete`	`{id}`	Delete user (cascades)

Posts

Method	Path	Body	Description
`GET`	`/api/posts`	—	List published posts
`GET`	`/api/posts/all`	—	List all posts
`POST`	`/api/posts/get`	`{id}`	Get post + author + comment count
`POST`	`/api/posts/create`	`{user_id, title, body?, status?}`	Create post
`PUT`	`/api/posts/update`	`{id, title?, body?, status?}`	Update post
`DELETE`	`/api/posts/delete`	`{id}`	Delete post (cascades)

Comments

Method	Path	Body	Description
`POST`	`/api/comments/list`	`{post_id}`	List comments for a post
`POST`	`/api/comments/create`	`{post_id, user_id, body}`	Add a comment
`DELETE`	`/api/comments/delete`	`{id}`	Delete a comment

Auth (optional) & Seed

Method	Path	Body	Description
`POST`	`/api/me`	`{token}`	JWT-protected current-user fetch
`POST`	`/api/seed`	—	Create tables and load sample data

Setup

1. Choose a database file

No server, no connection string to a remote host — just a file path. Every config points at:

sqlite://airpipe.db

This creates airpipe.db in the Air Pipe working directory on first connection. To store it elsewhere, edit the conn_string in each *.yml to an absolute path, e.g. sqlite:///var/lib/airpipe/app.db.

2. Create the tables

Either apply the schema directly with the sqlite3 CLI:

sqlite3 airpipe.db < schema.sql

…or deploy seed.yml and call the seed endpoint — it creates the tables and loads sample users, posts, and comments in one step:

curl -X POST http://your-airpipe-host/api/seed
# → { "users": 3, "posts": 5, "comments": 5 }

3. Deploy the configs

Point Air Pipe at the config files. Each name: is unique, so all files deploy together and share the same airpipe.db.

Quick start: curl walkthrough

BASE=http://your-airpipe-host

# Seed sample data
curl -X POST $BASE/api/seed

# List published posts
curl $BASE/api/posts

# Create a user (password is bcrypt-hashed before storage)
curl -X POST $BASE/api/users/create \
  -H "Content-Type: application/json" \
  -d '{"email":"[email protected]","username":"jane","password":"Secret1!"}'

# Create a post
curl -X POST $BASE/api/posts/create \
  -H "Content-Type: application/json" \
  -d '{"user_id":1,"title":"Hello World","body":"First post.","status":"published"}'

# Get it back with its author and comment count
curl -X POST $BASE/api/posts/get -H "Content-Type: application/json" -d '{"id":1}'

How this differs from the Postgres starter

The packs are deliberately parallel, but SQLite is its own dialect. The good news: SQLite supports RETURNING, so the write endpoints keep the clean Postgres-style "write and read back in one statement" flow.

? placeholders, not $1. SQLite binds parameters positionally with anonymous ?. The params: list order is the order each ? appears in the SQL text — so on UPDATE … WHERE id = ?, the id param comes last, after the SET columns.
RETURNING works (SQLite 3.35+). INSERT … RETURNING … reads the new row back in the same statement, exactly like Postgres — no LAST_INSERT_ROWID() round-trip needed.
TEXT columns are fine. Unlike MySQL (where TEXT/BLOB columns decode as binary and break JSON), SQLite returns TEXT cleanly, so posts.body and comments.body stay TEXT.
INTEGER PRIMARY KEY AUTOINCREMENT instead of BIGSERIAL.
No NOW(). Timestamps default to strftime('%Y-%m-%dT%H:%M:%SZ','now') (ISO-8601, UTC) and are stored as TEXT. updated_at is set with the same expression on update.
Foreign keys. SQLite enforces foreign keys per-connection; Air Pipe's SQLite driver enables PRAGMA foreign_keys = ON, so ON DELETE CASCADE fires (deleting a user removes their posts and comments).
No TRUNCATE. The seed clears with DELETE FROM (child → parent) and resets sqlite_sequence so re-seeding always produces the same ids.

JWT validation (optional)

Air Pipe validates JWTs but does not generate them. Generate a test token at jwt.io:

Algorithm: HS256
Payload: {"sub": "1", "username": "alice", "exp": 9999999999} — sub is the user id this pack looks users up by
Secret: the value of JWT_SECRET

Set the JWT_SECRET variable, deploy auth.yml, then:

curl -X POST $BASE/api/me -H "Content-Type: application/json" -d '{"token":"<your-jwt>"}'

To protect any other endpoint, copy the ValidateToken action as the first action and add run_when_succeeded: { actions: [ValidateToken], http_code_on_error: 401 } to every subsequent action.

Notes

Password hashing. users/create runs a bcrypt post-transform (cost 12) before the insert; the raw password is never stored, and no endpoint selects password_hash.
Cascades. Deleting a user cascades to their posts and comments; deleting a post cascades to its comments.
Concurrency. SQLite serialises writes (one writer at a time). It is excellent for local-first and read-heavy workloads; for high write concurrency, use the Postgres or MySQL starter.
Use a different driver. The query shape is standard SQL — switch driver: sqlite to driver: postgres / mysql / mssql and adjust placeholders (? vs $1) and dialect to reuse these configs elsewhere.

Configuration

auth.yml

name: SqliteRestApiAuth

docs: true

# JWT validation middleware pattern.
#
# Air Pipe validates JWTs but does not generate them. To get a token for testing:
#   1. Go to https://jwt.io
#   2. Set algorithm to HS256
#   3. Set payload: { "sub": "1", "username": "alice", "exp": <future timestamp> }
#   4. Set "your-256-bit-secret" to match JWT_SECRET below
#   5. Copy the encoded token
#
# Then pass the token as { "token": "<jwt>" } alongside your request data.
#
# Required managed variable:
#   JWT_SECRET — 32+ character secret used to sign tokens

global:
  databases:
    main:
      driver: sqlite
      conn_string: "sqlite://airpipe.db"

interfaces:

  # POST /api/me
  # Example of a JWT-protected endpoint.
  # Body: { "token": "<jwt>" }
  #
  # To protect any endpoint: copy the ValidateToken action and add
  # run_when_succeeded: { actions: [ValidateToken], http_code_on_error: 401 }
  # to every subsequent action.
  api/me:
    output: http
    method: POST
    summary: Get current user
    description: >
      JWT-protected endpoint. Validates the token and returns the authenticated user's profile.
      Pass the token in the request body alongside any other fields.
    tags: [auth]
    request_example:
      token: "<your-jwt-here>"
    response_example:
      id: 1
      email: [email protected]
      username: alice
      created_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ValidateToken
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 401
          error_message: "Invalid or missing token"
          tests:
            - value: token
              is_not_null: true
              is_valid_jwt: a|ap_var::JWT_SECRET|
        post_transforms:
          - extract_value: jwt_claims

      - name: GetCurrentUser
        run_when_succeeded:
          actions: [ValidateToken]
          http_code_on_error: 401
        database: main
        query: |
          SELECT id, email, username, created_at
          FROM users
          WHERE id = ?;
        params:
          - a|ValidateToken::sub|
        assert:
          http_code_on_error: 404
          error_message: "User not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

comments.yml

name: SqliteRestApiComments

docs: true

global:
  databases:
    main:
      driver: sqlite
      conn_string: "sqlite://airpipe.db"

interfaces:

  # POST /api/comments/list
  # List comments for a post.
  # Body: { "post_id": 1 }
  api/comments/list:
    output: http
    method: POST
    summary: List comments
    description: List all comments for a post, oldest first.
    tags: [comments]
    request_example:
      post_id: 1
    response_example:
      - id: 1
        body: "Great post!"
        created_at: "2024-01-01T13:00:00Z"
        author: bob

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: post_id
              is_not_null: true

      - name: ListComments
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          SELECT c.id, c.body, c.created_at, u.username AS author
          FROM comments c
          JOIN users u ON u.id = c.user_id
          WHERE c.post_id = ?
          ORDER BY c.created_at ASC;
        params:
          - a|ValidateBody::post_id|

  # POST /api/comments/create
  # Add a comment to a post.
  # Body: { "post_id": 1, "user_id": 2, "body": "Great post!" }
  api/comments/create:
    output: http
    method: POST
    summary: Create comment
    description: Add a comment to a post.
    tags: [comments]
    notes: |
      `post_id` and `user_id` must reference existing records. Run `POST /api/seed` first to populate sample data, or use ids returned from the create endpoints.
    request_example:
      post_id: 1
      user_id: 2
      body: "Great post!"
    response_example:
      id: 1
      post_id: 1
      user_id: 2
      body: "Great post!"
      created_at: "2024-01-01T13:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: post_id
              is_not_null: true
            - value: user_id
              is_not_null: true
            - value: body
              is_not_null: true
              is_not_empty: true

      - name: CreateComment
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          INSERT INTO comments (post_id, user_id, body)
          VALUES (?, ?, ?)
          RETURNING id, post_id, user_id, body, created_at;
        params:
          - a|ValidateBody::post_id|
          - a|ValidateBody::user_id|
          - a|ValidateBody::body|
        post_transforms:
          - extract_value: "[0]"

  # DELETE /api/comments/delete
  # Delete a comment by ID.
  # Body: { "id": 1 }
  api/comments/delete:
    output: http
    method: DELETE
    summary: Delete comment
    description: Permanently delete a comment by ID.
    tags: [comments]
    request_example:
      id: 1
    response_example:
      id: 1

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: DeleteComment
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          DELETE FROM comments WHERE id = ? RETURNING id;
        params:
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "Comment not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

posts.yml

name: SqliteRestApiPosts

docs: true

global:
  databases:
    main:
      driver: sqlite
      conn_string: "sqlite://airpipe.db"

interfaces:

  # GET /api/posts
  # List all published posts.
  api/posts:
    output: http
    summary: List published posts
    description: Returns the 100 most recent published posts with author username.
    tags: [posts]
    response_example:
      - id: 1
        title: Hello World
        status: published
        created_at: "2024-01-01T12:00:00Z"
        author: alice

    actions:
      - name: ListPosts
        database: main
        query: |
          SELECT p.id, p.title, p.status, p.created_at, u.username AS author
          FROM posts p
          JOIN users u ON u.id = p.user_id
          WHERE p.status = 'published'
          ORDER BY p.created_at DESC
          LIMIT 100;

  # GET /api/posts/all
  # List all posts regardless of status (admin view).
  api/posts/all:
    output: http
    summary: List all posts
    description: Returns all posts regardless of status. Useful for admin views.
    tags: [posts]
    response_example:
      - id: 1
        title: Hello World
        status: published
        created_at: "2024-01-01T12:00:00Z"
        author: alice
      - id: 2
        title: Draft post
        status: draft
        created_at: "2024-01-02T08:00:00Z"
        author: bob

    actions:
      - name: ListAllPosts
        database: main
        query: |
          SELECT p.id, p.title, p.status, p.created_at, u.username AS author
          FROM posts p
          JOIN users u ON u.id = p.user_id
          ORDER BY p.created_at DESC
          LIMIT 100;

  # POST /api/posts/create
  # Create a new post.
  # Body: { "user_id": 1, "title": "Hello World", "body": "...", "status": "draft" }
  api/posts/create:
    output: http
    method: POST
    summary: Create post
    description: Create a new post. Status defaults to draft if not provided.
    tags: [posts]
    notes: |
      `user_id` must reference an existing user. Run `POST /api/seed` first to populate sample users (ids 1–3), or use the id returned from `POST /api/users/create`.
    request_example:
      user_id: 1
      title: Hello World
      body: "My first post."
      status: published
    response_example:
      id: 1
      user_id: 1
      title: Hello World
      status: published
      created_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: user_id
              is_not_null: true
            - value: title
              is_not_null: true
              is_not_empty: true

      - name: CreatePost
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          INSERT INTO posts (user_id, title, body, status)
          VALUES (?, ?, ?, COALESCE(?, 'draft'))
          RETURNING id, user_id, title, status, created_at;
        params:
          - a|ValidateBody::user_id|
          - a|ValidateBody::title|
          - a|ValidateBody::body->default(null)|
          - a|ValidateBody::status->default(null)|
        post_transforms:
          - extract_value: "[0]"

  # POST /api/posts/get
  # Fetch a single post with its comment count.
  # Body: { "id": 1 }
  api/posts/get:
    output: http
    method: POST
    summary: Get post
    description: Fetch a single post with its author and comment count.
    tags: [posts]
    request_example:
      id: 1
    response_example:
      id: 1
      title: Hello World
      body: "My first post."
      status: published
      created_at: "2024-01-01T12:00:00Z"
      updated_at: "2024-01-01T12:00:00Z"
      author: alice
      comment_count: 3

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: GetPost
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          SELECT
            p.id, p.title, p.body, p.status, p.created_at, p.updated_at,
            u.username AS author,
            COUNT(c.id) AS comment_count
          FROM posts p
          JOIN users u ON u.id = p.user_id
          LEFT JOIN comments c ON c.post_id = p.id
          WHERE p.id = ?
          GROUP BY p.id, u.username;
        params:
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "Post not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # PUT /api/posts/update
  # Update a post's title, body, or status.
  # Body: { "id": 1, "title": "Updated title", "status": "published" }
  api/posts/update:
    output: http
    method: PUT
    summary: Update post
    description: Update a post's title, body, or status. Omit a field to leave it unchanged.
    tags: [posts]
    request_example:
      id: 1
      title: Updated title
      status: published
    response_example:
      id: 1
      title: Updated title
      status: published
      updated_at: "2024-01-02T09:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: UpdatePost
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          UPDATE posts
          SET
            title      = COALESCE(?, title),
            body       = COALESCE(?, body),
            status     = COALESCE(?, status),
            updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now')
          WHERE id = ?
          RETURNING id, title, status, updated_at;
        params:
          - a|ValidateBody::title->default(null)|
          - a|ValidateBody::body->default(null)|
          - a|ValidateBody::status->default(null)|
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "Post not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # DELETE /api/posts/delete
  # Delete a post (cascades to comments).
  # Body: { "id": 1 }
  api/posts/delete:
    output: http
    method: DELETE
    summary: Delete post
    description: Permanently delete a post. Cascades to all its comments.
    tags: [posts]
    request_example:
      id: 1
    response_example:
      id: 1

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: DeletePost
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          DELETE FROM posts WHERE id = ? RETURNING id;
        params:
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "Post not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

seed.yml

name: SqliteRestApiSeed
description: Creates all tables (idempotent) and loads sample users, posts, and comments. Safe to re-run — clears all tables and resets ids before inserting.

docs: true

global:
  databases:
    main:
      driver: sqlite
      conn_string: "sqlite://airpipe.db"

interfaces:

  # POST /api/seed
  # Creates all required tables and indexes then loads sample data.
  # Safe to re-run — clears all tables and resets auto-increment ids before inserting,
  # so seeded users are always ids 1–3, posts 1–5, comments 1–5.
  api/seed:
    output: http
    method: POST

    actions:
      - name: CreateUsersTable
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS users (
            id            INTEGER  PRIMARY KEY AUTOINCREMENT,
            email         TEXT     UNIQUE NOT NULL,
            username      TEXT     UNIQUE NOT NULL,
            password_hash TEXT     NOT NULL,
            created_at    TEXT     NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
            updated_at    TEXT     NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
          );

      - name: CreatePostsTable
        run_when_succeeded: [CreateUsersTable]
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS posts (
            id         INTEGER  PRIMARY KEY AUTOINCREMENT,
            user_id    INTEGER  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            title      TEXT     NOT NULL,
            body       TEXT,
            status     TEXT     NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
            created_at TEXT     NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
            updated_at TEXT     NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
          );

      - name: CreateCommentsTable
        run_when_succeeded: [CreatePostsTable]
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS comments (
            id         INTEGER  PRIMARY KEY AUTOINCREMENT,
            post_id    INTEGER  NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
            user_id    INTEGER  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            body       TEXT     NOT NULL,
            created_at TEXT     NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
          );

      - name: CreateIndexes
        run_when_succeeded: [CreateCommentsTable]
        database: main
        hide_data_on_success: true
        query: |
          CREATE INDEX IF NOT EXISTS idx_posts_user_id    ON posts    (user_id);
          CREATE INDEX IF NOT EXISTS idx_posts_status     ON posts    (status);
          CREATE INDEX IF NOT EXISTS idx_comments_post_id ON comments (post_id);

      - name: ClearTables
        run_when_succeeded: [CreateIndexes]
        database: main
        hide_data_on_success: true
        # SQLite has no TRUNCATE. Delete child rows first, then reset the
        # AUTOINCREMENT counters so re-seeding always yields the same ids.
        query: |
          DELETE FROM comments;
          DELETE FROM posts;
          DELETE FROM users;
          DELETE FROM sqlite_sequence WHERE name IN ('users', 'posts', 'comments');

      - name: InsertUsers
        run_when_succeeded: [ClearTables]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO users (email, username, password_hash) VALUES
            ('[email protected]', 'alice', 'hashed_pw_1'),
            ('[email protected]',   'bob',   'hashed_pw_2'),
            ('[email protected]', 'carol', 'hashed_pw_3');

      - name: InsertPosts
        run_when_succeeded: [InsertUsers]
        database: main
        hide_data_on_success: true
        # user_id values reference the ids assigned above (alice=1, bob=2, carol=3).
        query: |
          INSERT INTO posts (user_id, title, body, status) VALUES
            (1, 'Getting Started with Air Pipe', 'Air Pipe lets you build APIs from config files.', 'published'),
            (1, 'Draft: Advanced Patterns',      'Work in progress.',                               'draft'),
            (2, 'My First Post',                 'Hello from Bob!',                                 'published'),
            (2, 'REST APIs Made Easy',           'CRUD without the boilerplate.',                   'published'),
            (3, 'Archived Post',                 'This one is no longer relevant.',                 'archived');

      - name: InsertComments
        run_when_succeeded: [InsertPosts]
        database: main
        hide_data_on_success: true
        # post_id / user_id reference the ids assigned above.
        query: |
          INSERT INTO comments (post_id, user_id, body) VALUES
            (1, 2, 'Really helpful, thanks Alice!'),
            (1, 3, 'Bookmarked this.'),
            (3, 1, 'Welcome Bob!'),
            (4, 3, 'This saved me hours.'),
            (4, 1, 'Great write-up.');

      - name: SeedSummary
        run_when_succeeded: [InsertComments]
        database: main
        query: |
          SELECT
            (SELECT COUNT(*) FROM users)    AS users,
            (SELECT COUNT(*) FROM posts)    AS posts,
            (SELECT COUNT(*) FROM comments) AS comments;
        post_transforms:
          - extract_value: "[0]"

users.yml

name: SqliteRestApiUsers

docs: true

global:
  databases:
    main:
      driver: sqlite
      conn_string: "sqlite://airpipe.db"

interfaces:

  # GET /api/users
  # List all users (paginated — extend with query params as needed).
  api/users:
    output: http
    summary: List users
    description: Returns the 100 most recently created users.
    tags: [users]
    response_example:
      - id: 1
        email: [email protected]
        username: alice
        created_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ListUsers
        database: main
        query: |
          SELECT id, email, username, created_at
          FROM users
          ORDER BY created_at DESC
          LIMIT 100;

  # POST /api/users/create
  # Create a new user.
  # Body: { "email": "...", "username": "...", "password": "..." }
  api/users/create:
    output: http
    method: POST
    summary: Create user
    description: >
      Create a new user. Password is bcrypt-hashed (cost 12) before storage —
      the raw value is never persisted or returned.
    tags: [users]
    request_example:
      email: [email protected]
      username: jane
      password: "Secret1!"
    response_example:
      id: 6
      email: [email protected]
      username: jane
      created_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: email
              is_not_null: true
              is_not_empty: true
            - value: username
              is_not_null: true
              is_not_empty: true
            - value: password
              is_not_null: true
              is_not_empty: true

      - name: HashPassword
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        input: a|ValidateBody|
        hide_data_on_success: true
        hide_data_on_error: true
        post_transforms:
          - bcrypt:
              value: password
              cost: 12

      - name: CreateUser
        run_when_succeeded:
          actions: [HashPassword]
          http_code_on_error: 400
        database: main
        query: |
          INSERT INTO users (email, username, password_hash)
          VALUES (?, ?, ?)
          RETURNING id, email, username, created_at;
        params:
          - a|ValidateBody::email|
          - a|ValidateBody::username|
          - a|HashPassword::password|
        post_transforms:
          - extract_value: "[0]"

  # POST /api/users/get
  # Fetch a single user by ID.
  # Body: { "id": 1 }
  api/users/get:
    output: http
    method: POST
    summary: Get user
    description: Fetch a single user by ID.
    tags: [users]
    request_example:
      id: 1
    response_example:
      id: 1
      email: [email protected]
      username: alice
      created_at: "2024-01-01T12:00:00Z"
      updated_at: "2024-01-01T12:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: GetUser
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          SELECT id, email, username, created_at, updated_at
          FROM users
          WHERE id = ?;
        params:
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "User not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # PUT /api/users/update
  # Update email and/or username for a user.
  # Body: { "id": 1, "email": "[email protected]", "username": "newname" }
  # Omit a field to leave it unchanged.
  api/users/update:
    output: http
    method: PUT
    summary: Update user
    description: Update email and/or username. Omit a field to leave it unchanged.
    tags: [users]
    request_example:
      id: 1
      email: [email protected]
      username: newname
    response_example:
      id: 1
      email: [email protected]
      username: newname
      updated_at: "2024-01-02T09:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: UpdateUser
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          UPDATE users
          SET
            email      = COALESCE(?, email),
            username   = COALESCE(?, username),
            updated_at = strftime('%Y-%m-%dT%H:%M:%SZ','now')
          WHERE id = ?
          RETURNING id, email, username, updated_at;
        params:
          - a|ValidateBody::email->default(null)|
          - a|ValidateBody::username->default(null)|
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "User not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # DELETE /api/users/delete
  # Delete a user by ID.
  # Body: { "id": 1 }
  api/users/delete:
    output: http
    method: DELETE
    summary: Delete user
    description: Permanently delete a user. Cascades to their posts and comments.
    tags: [users]
    request_example:
      id: 1
    response_example:
      id: 1

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: id
              is_not_null: true

      - name: DeleteUser
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          DELETE FROM users
          WHERE id = ?
          RETURNING id;
        params:
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "User not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

schema.sql

-- SQLite REST API Starter schema
--
-- SQLite needs no server: the database is a single file, created on first
-- connection. Apply this schema once with the sqlite3 CLI:
--   sqlite3 airpipe.db < schema.sql
-- …or just deploy seed.yml and call POST /api/seed, which runs the same DDL.
--
-- Foreign-key enforcement is per-connection in SQLite. AirPipe's SQLite driver
-- enables it automatically (PRAGMA foreign_keys = ON), so the ON DELETE CASCADE
-- rules below fire at runtime.

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
  id            INTEGER  PRIMARY KEY AUTOINCREMENT,
  email         TEXT     UNIQUE NOT NULL,
  username      TEXT     UNIQUE NOT NULL,
  password_hash TEXT     NOT NULL,
  created_at    TEXT     NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
  updated_at    TEXT     NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE TABLE IF NOT EXISTS posts (
  id         INTEGER  PRIMARY KEY AUTOINCREMENT,
  user_id    INTEGER  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title      TEXT     NOT NULL,
  body       TEXT,
  status     TEXT     NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
  created_at TEXT     NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
  updated_at TEXT     NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE TABLE IF NOT EXISTS comments (
  id         INTEGER  PRIMARY KEY AUTOINCREMENT,
  post_id    INTEGER  NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id    INTEGER  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  body       TEXT     NOT NULL,
  created_at TEXT     NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE INDEX IF NOT EXISTS idx_posts_user_id    ON posts    (user_id);
CREATE INDEX IF NOT EXISTS idx_posts_status     ON posts    (status);
CREATE INDEX IF NOT EXISTS idx_comments_post_id ON comments (post_id);

SQLite REST API Starter

When to use SQLite​

What's included​

Endpoints​

Users​

Posts​

Comments​

Auth (optional) & Seed​

Setup​

1. Choose a database file​

2. Create the tables​

3. Deploy the configs​

Quick start: curl walkthrough​

How this differs from the Postgres starter​

JWT validation (optional)​

Notes​

Configuration​

auth.yml​

comments.yml​

posts.yml​

seed.yml​

users.yml​

schema.sql​