MySQL REST API Starter

Category: Backend & APIs · 📦 1 install

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

A production-shaped CRUD API over MySQL — users, posts, and comments, with optional JWT validation. Works with MySQL 8.x, MariaDB 10.5+, and PlanetScale. No ORM, no boilerplate: point AirPipe at your MySQL connection string and you have an API.

The relational companion to the REST API Starter pack (Postgres) and the MongoDB Atlas Backend pack. Same shape, MySQL dialect.

What's included

File	Purpose
`schema.sql`	MySQL tables, keys, and foreign keys
`users.yml`	User CRUD — bcrypt-hashed passwords
`posts.yml`	Post CRUD — slug-keyed, with 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 — single statement per action

Endpoints

Users

Method	Path	Body	Description
`GET`	`/api/users`	—	List users
`POST`	`/api/users/get`	`{username}`	Get user by username
`POST`	`/api/users/create`	`{email, username, password}`	Create user
`PUT`	`/api/users/update`	`{username, email}`	Update user email
`DELETE`	`/api/users/delete`	`{username}`	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`	`{slug}`	Get post + author + comment count
`POST`	`/api/posts/create`	`{user_id, slug, title, body, status}`	Create post
`PUT`	`/api/posts/update`	`{slug, title, body, status}`	Update post
`DELETE`	`/api/posts/delete`	`{slug}`	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, returns the thread
`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. Provision MySQL

Any MySQL works — a local Docker container, a managed instance (RDS, Cloud SQL), MariaDB, or a free hosted database. Free hosted options that work for testing: Aiven for MySQL (real MySQL 8, free plan, no credit card), TiDB Cloud Serverless (MySQL-compatible, free, no credit card), or db4free.net (test-only). Grab a connection string of the form:

mysql://user:password@host:3306/dbname

TLS: AirPipe's MySQL driver defaults ssl-mode to disabled, so for any hosted database you must append ?ssl-mode=required (case-insensitive). Use verify_ca / verify_identity with &ssl-ca=/path/to/ca.pem if you want certificate verification.

mysql://user:password@host:3306/dbname?ssl-mode=required

Network access: if your provider restricts connections by IP, allow the IP of wherever AirPipe runs. For hosted AirPipe, whitelist its published egress ranges from ip-ranges.airpipe.io rather than opening the database to everything.

2. Set the managed variable

In the AirPipe dashboard, add a managed variable:

Name	Value
`MYSQL_URL`	your MySQL connection string
`JWT_SECRET`	(optional) 32+ character secret, only for `auth.yml`

3. Create the tables

Either run the schema directly:

mysql -h host -u user -p dbname < 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 https://your-airpipe-host/api/seed
# → { "users": 3, "posts": 5, "comments": 5 }

4. Deploy the configs

Point AirPipe at the config files. Each name: is unique, so all files deploy together.

Quick start: curl walkthrough

BASE=https://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,"slug":"hello-world","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 '{"slug":"hello-world"}'

How this differs from the Postgres starter

The two packs are deliberately parallel, but MySQL is not Postgres, and the configs reflect that:

? placeholders, not $1. MySQL binds parameters positionally with ?. The params: list order is the bind order.
No RETURNING. Postgres can INSERT … RETURNING * and assert on the result in one statement. MySQL can't, so the write endpoints use a validate → check-exists → write → read-back flow: a SELECT decides 404s and returns the row, while the INSERT/UPDATE/DELETE just runs. Control flow never depends on what a write statement returns.
Keys. Users are keyed by username and posts by slug (both UNIQUE), so a row can be read back after insert without relying on LAST_INSERT_ID() across pooled connections.
Dialect. AUTO_INCREMENT instead of BIGSERIAL, ENUM(...) for status, ON UPDATE CURRENT_TIMESTAMP for updated_at, ENGINE=InnoDB for foreign keys.
VARCHAR, not TEXT, for body. The MySQL driver returns TEXT/BLOB-family columns as a binary value, which breaks JSON decoding (a single-row read errors with "invalid type: byte array"; a multi-row read errors with "rows_affected > 1"). VARCHAR decodes cleanly, so posts.body is VARCHAR(8000) and comments.body is VARCHAR(2000). If you need longer-than-VARCHAR content, keep the column as TEXT but wrap every read in SUBSTRING(body, 1, N) (a plain CAST(... AS CHAR) is not enough) — verified against MySQL 8 on Aiven.
Seed. One statement per action — the driver executes a single statement per query, so tables, clears, and inserts are separate steps (clears run child → parent to respect foreign keys).

JWT validation (optional)

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

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

Add the JWT_SECRET managed 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 (InnoDB foreign keys).
Use a different driver. The query shape is standard SQL — switch driver: mysql to driver: postgres/sqlite/mssql and adjust placeholders (? vs $1) and dialect to reuse these configs elsewhere.

Configuration

auth.yml

name: MySqlAuth

docs: true

# JWT validation middleware pattern (MySQL edition).
#
# AirPipe 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": "alice", "username": "alice", "exp": 9999999999 }
#      (here `sub` is the username this pack looks users up by)
#   4. Set the secret to match JWT_SECRET below
#   5. Copy the encoded token, then pass it as { "token": "<jwt>" }
#
# Required managed variables:
#   MYSQL_URL  — MySQL connection string
#   JWT_SECRET — 32+ character secret used to sign tokens

global:
  databases:
    main:
      driver: mysql
      conn_string: "a|ap_var::MYSQL_URL|"

interfaces:

  # POST /api/me
  # JWT-protected current-user fetch.
  # 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, reads the `sub` claim as a
      username, and returns that user's profile.
    tags: [auth]
    request_example:
      token: "<your-jwt-here>"
    response_example:
      id: 1
      email: [email protected]
      username: alice
      created_at: "2026-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 username = ?;
        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: MySqlComments

docs: true

# Comments over MySQL. A comment belongs to a post and a user. Because MySQL has
# no RETURNING, create inserts then returns the refreshed comment thread for the
# post (a SELECT), which is more useful than echoing one row.
#
# Required managed variable:
#   MYSQL_URL — MySQL connection string

global:
  databases:
    main:
      driver: mysql
      conn_string: "a|ap_var::MYSQL_URL|"

interfaces:

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

    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.post_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; returns the refreshed thread.
  # Body: { "post_id": 1, "user_id": 2, "body": "Nice!" }
  api/comments/create:
    output: http
    method: POST
    summary: Add comment
    description: >
      Add a comment to a post. `post_id`, `user_id`, and `body` are required.
      Returns the full comment thread for the post after insertion.
    tags: [comments]
    notes: |
      `post_id` and `user_id` must reference existing rows. Run POST /api/seed to
      load sample data first.
    request_example:
      post_id: 1
      user_id: 2
      body: "Nice write-up!"
    response_example:
      - id: 1
        post_id: 1
        body: "Great post!"
        author: bob
      - id: 4
        post_id: 1
        body: "Nice write-up!"
        author: carol

    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
        hide_data_on_success: true
        query: |
          INSERT INTO comments (post_id, user_id, body)
          VALUES (?, ?, ?);
        params:
          - a|ValidateBody::post_id|
          - a|ValidateBody::user_id|
          - a|ValidateBody::body|

      - name: FetchThread
        run_when_succeeded:
          actions: [CreateComment]
          http_code_on_error: 500
        database: main
        query: |
          SELECT c.id, c.post_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|

  # 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. 404 if not found.
    tags: [comments]
    request_example:
      id: 1
    response_example:
      deleted: 1

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

      - name: CheckExists
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        hide_data_on_success: true
        query: |
          SELECT id FROM comments WHERE id = ?;
        params:
          - a|ValidateBody::id|
        assert:
          http_code_on_error: 404
          error_message: "Comment not found"
          tests:
            - value: count()
              is_equal_to: 1

      - name: DeleteComment
        run_when_succeeded:
          actions: [CheckExists]
          http_code_on_error: 404
        database: main
        hide_data_on_success: true
        query: |
          DELETE FROM comments WHERE id = ?;
        params:
          - a|ValidateBody::id|

      - name: Confirm
        run_when_succeeded:
          actions: [DeleteComment]
          http_code_on_error: 500
        input: a|ValidateBody|
        post_transforms:
          - add_attribute:
              deleted: a|ValidateBody::id|
          - remove_keys:
              - id

posts.yml

name: MySqlPosts

docs: true

# Posts CRUD over MySQL. Posts are keyed by a unique `slug` and reference a
# user by `user_id`. List/get JOIN the users table for the author name and
# (on get) aggregate the comment count.
#
# Required managed variable:
#   MYSQL_URL — MySQL connection string

global:
  databases:
    main:
      driver: mysql
      conn_string: "a|ap_var::MYSQL_URL|"

interfaces:

  # GET /api/posts
  # List published posts with author username.
  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
        slug: hello-world
        title: Hello World
        status: published
        author: alice

    actions:
      - name: ListPosts
        database: main
        query: |
          SELECT p.id, p.slug, 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, with author username.
    tags: [posts]
    response_example:
      - id: 2
        slug: draft-post
        title: Draft Post
        status: draft
        author: bob

    actions:
      - name: ListAllPosts
        database: main
        query: |
          SELECT p.id, p.slug, 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/get
  # Fetch a single post by slug, with author and comment count.
  # Body: { "slug": "hello-world" }
  api/posts/get:
    output: http
    method: POST
    summary: Get post
    description: Fetch a single post by slug, with author and comment count. 404 if not found.
    tags: [posts]
    request_example:
      slug: hello-world
    response_example:
      id: 1
      slug: hello-world
      title: Hello World
      body: "My first post."
      status: published
      author: alice
      comment_count: 3

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

      - name: GetPost
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          SELECT
            p.id, p.slug, 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.slug = ?
          GROUP BY p.id, u.username;
        params:
          - a|ValidateBody::slug|
        assert:
          http_code_on_error: 404
          error_message: "Post not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # POST /api/posts/create
  # Create a new post.
  # Body: { "user_id": 1, "slug": "...", "title": "...", "body": "...", "status": "draft" }
  api/posts/create:
    output: http
    method: POST
    summary: Create post
    description: >
      Create a new post. `user_id`, `slug`, and `title` are required; `body` and
      `status` are required too (MySQL has no RETURNING, so the row is read back
      by slug after insert).
    tags: [posts]
    notes: |
      `user_id` must reference an existing user. Run POST /api/seed first to load
      sample users (ids 1–3), or use the id from POST /api/users/create.
    request_example:
      user_id: 1
      slug: hello-world
      title: Hello World
      body: "My first post."
      status: published
    response_example:
      id: 1
      slug: hello-world
      title: Hello World
      status: published
      author: alice

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

      - name: CreatePost
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO posts (user_id, slug, title, body, status)
          VALUES (?, ?, ?, ?, ?);
        params:
          - a|ValidateBody::user_id|
          - a|ValidateBody::slug|
          - a|ValidateBody::title|
          - a|ValidateBody::body|
          - a|ValidateBody::status|

      - name: FetchCreated
        run_when_succeeded:
          actions: [CreatePost]
          http_code_on_error: 500
        database: main
        query: |
          SELECT p.id, p.slug, 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.slug = ?;
        params:
          - a|ValidateBody::slug|
        post_transforms:
          - extract_value: "[0]"

  # PUT /api/posts/update
  # Update a post's title, body, and status.
  # Body: { "slug": "hello-world", "title": "...", "body": "...", "status": "published" }
  api/posts/update:
    output: http
    method: PUT
    summary: Update post
    description: Update a post's title, body, and status. `slug` identifies the row.
    tags: [posts]
    request_example:
      slug: hello-world
      title: Hello World (edited)
      body: "Updated body."
      status: published
    response_example:
      id: 1
      slug: hello-world
      title: Hello World (edited)
      status: published

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

      - name: CheckExists
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        hide_data_on_success: true
        query: |
          SELECT id FROM posts WHERE slug = ?;
        params:
          - a|ValidateBody::slug|
        assert:
          http_code_on_error: 404
          error_message: "Post not found"
          tests:
            - value: count()
              is_equal_to: 1

      - name: UpdatePost
        run_when_succeeded:
          actions: [CheckExists]
          http_code_on_error: 404
        database: main
        hide_data_on_success: true
        query: |
          UPDATE posts SET title = ?, body = ?, status = ? WHERE slug = ?;
        params:
          - a|ValidateBody::title|
          - a|ValidateBody::body|
          - a|ValidateBody::status|
          - a|ValidateBody::slug|

      - name: FetchUpdated
        run_when_succeeded:
          actions: [UpdatePost]
          http_code_on_error: 500
        database: main
        query: |
          SELECT id, slug, title, status, updated_at
          FROM posts
          WHERE slug = ?;
        params:
          - a|ValidateBody::slug|
        post_transforms:
          - extract_value: "[0]"

  # DELETE /api/posts/delete
  # Delete a post by slug (cascades to comments).
  # Body: { "slug": "hello-world" }
  api/posts/delete:
    output: http
    method: DELETE
    summary: Delete post
    description: Permanently delete a post by slug. Cascades to its comments. 404 if absent.
    tags: [posts]
    request_example:
      slug: hello-world
    response_example:
      deleted: hello-world

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

      - name: CheckExists
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        hide_data_on_success: true
        query: |
          SELECT id FROM posts WHERE slug = ?;
        params:
          - a|ValidateBody::slug|
        assert:
          http_code_on_error: 404
          error_message: "Post not found"
          tests:
            - value: count()
              is_equal_to: 1

      - name: DeletePost
        run_when_succeeded:
          actions: [CheckExists]
          http_code_on_error: 404
        database: main
        hide_data_on_success: true
        query: |
          DELETE FROM posts WHERE slug = ?;
        params:
          - a|ValidateBody::slug|

      - name: Confirm
        run_when_succeeded:
          actions: [DeletePost]
          http_code_on_error: 500
        input: a|ValidateBody|
        post_transforms:
          - add_attribute:
              deleted: a|ValidateBody::slug|
          - remove_keys:
              - slug

seed.yml

name: MySqlSeed
description: Creates the tables (idempotent) and loads sample users, posts, and comments. Safe to re-run.

docs: true

# POST /api/seed
# Creates the three tables if absent, clears them (child -> parent order to
# respect foreign keys), and loads sample data with explicit ids so references
# are deterministic. Each action runs a single statement (the driver does not
# execute multi-statement queries).
#
# Required managed variable:
#   MYSQL_URL — MySQL connection string

global:
  databases:
    main:
      driver: mysql
      conn_string: "a|ap_var::MYSQL_URL|"

interfaces:

  api/seed:
    output: http
    method: POST
    summary: Seed the database
    description: Creates tables, clears them, and loads sample data. Returns row counts.
    tags: [seed]

    actions:
      - name: CreateUsersTable
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS users (
            id            BIGINT       NOT NULL AUTO_INCREMENT,
            email         VARCHAR(255) NOT NULL,
            username      VARCHAR(100) NOT NULL,
            password_hash VARCHAR(255) NOT NULL,
            created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_users_email (email),
            UNIQUE KEY uq_users_username (username)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

      - name: CreatePostsTable
        run_when_succeeded: [CreateUsersTable]
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS posts (
            id         BIGINT       NOT NULL AUTO_INCREMENT,
            user_id    BIGINT       NOT NULL,
            slug       VARCHAR(200)  NOT NULL,
            title      VARCHAR(255)  NOT NULL,
            body       VARCHAR(8000),
            status     ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
            created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_posts_slug (slug),
            KEY idx_posts_user_id (user_id),
            KEY idx_posts_status (status),
            CONSTRAINT fk_posts_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

      - name: CreateCommentsTable
        run_when_succeeded: [CreatePostsTable]
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS comments (
            id         BIGINT    NOT NULL AUTO_INCREMENT,
            post_id    BIGINT        NOT NULL,
            user_id    BIGINT        NOT NULL,
            body       VARCHAR(2000) NOT NULL,
            created_at TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_comments_post_id (post_id),
            CONSTRAINT fk_comments_post FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
            CONSTRAINT fk_comments_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

      - name: ClearComments
        run_when_succeeded: [CreateCommentsTable]
        database: main
        hide_data_on_success: true
        query: DELETE FROM comments;

      - name: ClearPosts
        run_when_succeeded: [ClearComments]
        database: main
        hide_data_on_success: true
        query: DELETE FROM posts;

      - name: ClearUsers
        run_when_succeeded: [ClearPosts]
        database: main
        hide_data_on_success: true
        query: DELETE FROM users;

      - name: InsertUsers
        run_when_succeeded: [ClearUsers]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO users (id, email, username, password_hash) VALUES
            (1, '[email protected]', 'alice', '$2b$12$seededPlaceholderHashForDemoUseOnly000000000000000000'),
            (2, '[email protected]',   'bob',   '$2b$12$seededPlaceholderHashForDemoUseOnly000000000000000000'),
            (3, '[email protected]', 'carol', '$2b$12$seededPlaceholderHashForDemoUseOnly000000000000000000');

      - name: InsertPosts
        run_when_succeeded: [InsertUsers]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO posts (id, user_id, slug, title, body, status) VALUES
            (1, 1, 'getting-started-with-airpipe', 'Getting Started with AirPipe', 'AirPipe builds APIs from config files.', 'published'),
            (2, 1, 'advanced-patterns',            'Draft: Advanced Patterns',     'Work in progress.',                       'draft'),
            (3, 2, 'my-first-post',                'My First Post',                'Hello from Bob!',                         'published'),
            (4, 2, 'rest-apis-made-easy',          'REST APIs Made Easy',          'CRUD without the boilerplate.',           'published'),
            (5, 3, 'archived-post',                'Archived Post',                'No longer relevant.',                     'archived');

      - name: InsertComments
        run_when_succeeded: [InsertPosts]
        database: main
        hide_data_on_success: true
        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:
          actions: [InsertComments]
          http_code_on_error: 500
        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: MySqlUsers

docs: true

# Users CRUD over MySQL.
#
# MySQL uses `?` positional placeholders (not $1/$2) and has no RETURNING clause,
# so writes follow a validate -> check-exists -> write -> read-back pattern:
# control flow depends only on SELECT results, never on what INSERT/UPDATE/DELETE
# return. Users are keyed by their unique `username`.
#
# Required managed variable:
#   MYSQL_URL — MySQL connection string
#               e.g. mysql://user:pass@host:3306/dbname

global:
  databases:
    main:
      driver: mysql
      conn_string: "a|ap_var::MYSQL_URL|"

interfaces:

  # GET /api/users
  # List the 100 most recent users (password_hash never selected).
  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: "2026-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/get
  # Fetch a single user by username.
  # Body: { "username": "alice" }
  api/users/get:
    output: http
    method: POST
    summary: Get user
    description: Fetch a single user by username. Returns 404 if not found.
    tags: [users]
    request_example:
      username: alice
    response_example:
      id: 1
      email: [email protected]
      username: alice
      created_at: "2026-01-01T12:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          tests:
            - value: username
              is_not_null: true
              is_not_empty: 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 username = ?;
        params:
          - a|ValidateBody::username|
        assert:
          http_code_on_error: 404
          error_message: "User not found"
          tests:
            - value: count()
              is_equal_to: 1
        post_transforms:
          - extract_value: "[0]"

  # POST /api/users/create
  # Create a new user. Password is bcrypt-hashed before storage.
  # Body: { "email": "...", "username": "...", "password": "..." }
  api/users/create:
    output: http
    method: POST
    summary: Create user
    description: >
      Create a new user. The 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: 4
      email: [email protected]
      username: jane
      created_at: "2026-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
        hide_data_on_success: true
        query: |
          INSERT INTO users (email, username, password_hash)
          VALUES (?, ?, ?);
        params:
          - a|ValidateBody::email|
          - a|ValidateBody::username|
          - a|HashPassword::password|

      - name: FetchCreated
        run_when_succeeded:
          actions: [CreateUser]
          http_code_on_error: 500
        database: main
        query: |
          SELECT id, email, username, created_at
          FROM users
          WHERE username = ?;
        params:
          - a|ValidateBody::username|
        post_transforms:
          - extract_value: "[0]"

  # PUT /api/users/update
  # Update a user's email address.
  # Body: { "username": "alice", "email": "[email protected]" }
  api/users/update:
    output: http
    method: PUT
    summary: Update user
    description: Update a user's email. `username` identifies the row; send `email` to set it.
    tags: [users]
    request_example:
      username: alice
      email: [email protected]
    response_example:
      id: 1
      email: [email protected]
      username: alice

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

      - name: CheckExists
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        hide_data_on_success: true
        query: |
          SELECT id FROM users WHERE username = ?;
        params:
          - a|ValidateBody::username|
        assert:
          http_code_on_error: 404
          error_message: "User not found"
          tests:
            - value: count()
              is_equal_to: 1

      - name: UpdateUser
        run_when_succeeded:
          actions: [CheckExists]
          http_code_on_error: 404
        database: main
        hide_data_on_success: true
        query: |
          UPDATE users SET email = ? WHERE username = ?;
        params:
          - a|ValidateBody::email|
          - a|ValidateBody::username|

      - name: FetchUpdated
        run_when_succeeded:
          actions: [UpdateUser]
          http_code_on_error: 500
        database: main
        query: |
          SELECT id, email, username, updated_at
          FROM users
          WHERE username = ?;
        params:
          - a|ValidateBody::username|
        post_transforms:
          - extract_value: "[0]"

  # DELETE /api/users/delete
  # Delete a user by username (cascades to their posts and comments).
  # Body: { "username": "alice" }
  api/users/delete:
    output: http
    method: DELETE
    summary: Delete user
    description: Permanently delete a user by username. Cascades to posts and comments. 404 if absent.
    tags: [users]
    request_example:
      username: alice
    response_example:
      deleted: alice

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

      - name: CheckExists
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        hide_data_on_success: true
        query: |
          SELECT id FROM users WHERE username = ?;
        params:
          - a|ValidateBody::username|
        assert:
          http_code_on_error: 404
          error_message: "User not found"
          tests:
            - value: count()
              is_equal_to: 1

      - name: DeleteUser
        run_when_succeeded:
          actions: [CheckExists]
          http_code_on_error: 404
        database: main
        hide_data_on_success: true
        query: |
          DELETE FROM users WHERE username = ?;
        params:
          - a|ValidateBody::username|

      - name: Confirm
        run_when_succeeded:
          actions: [DeleteUser]
          http_code_on_error: 500
        input: a|ValidateBody|
        post_transforms:
          - add_attribute:
              deleted: a|ValidateBody::username|
          - remove_keys:
              - username

schema.sql

-- MySQL schema for the REST API pack.
-- Compatible with MySQL 8.x, MariaDB 10.5+, and PlanetScale.
-- Run directly:  mysql --defaults-file=... dbname < schema.sql
-- or call POST /api/seed (seed.yml creates these tables idempotently).

CREATE TABLE IF NOT EXISTS users (
  id            BIGINT       NOT NULL AUTO_INCREMENT,
  email         VARCHAR(255) NOT NULL,
  username      VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  UNIQUE KEY uq_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS posts (
  id         BIGINT       NOT NULL AUTO_INCREMENT,
  user_id    BIGINT       NOT NULL,
  slug       VARCHAR(200)  NOT NULL,
  title      VARCHAR(255)  NOT NULL,
  body       VARCHAR(8000),
  status     ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_posts_slug (slug),
  KEY idx_posts_user_id (user_id),
  KEY idx_posts_status (status),
  CONSTRAINT fk_posts_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS comments (
  id         BIGINT    NOT NULL AUTO_INCREMENT,
  post_id    BIGINT        NOT NULL,
  user_id    BIGINT        NOT NULL,
  body       VARCHAR(2000) NOT NULL,
  created_at TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_comments_post_id (post_id),
  CONSTRAINT fk_comments_post FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
  CONSTRAINT fk_comments_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

MySQL REST API Starter

What's included​

Endpoints​

Users​

Posts​

Comments​

Auth (optional) & Seed​

Setup​

1. Provision MySQL​

2. Set the managed variable​

3. Create the tables​

4. 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​