MySQL CRUD

Category: Backend & APIs

Get this pack →

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

Overview

Full CRUD for a products table backed by MySQL (create, list, update, delete). Demonstrates the mysql driver with ? placeholders and input: a|params| pagination.

Endpoints

• POST products/create — create a product
• GET products/list — list products, paginated
• PUT products/update — update a product by id
• DELETE products/delete — delete a product by id

Required variables

• ap_var::DATABASE_URL — MySQL connection string

Configuration

mysql_crud.yml

name: MysqlCrud
description: >
  Full CRUD for a products table backed by MySQL — create, list, update and
  delete. Demonstrates the mysql driver with ? placeholders and MySQL-dialect
  SQL, reading the request body vs. query params via input: body / input: params.

docs: true

# Required managed variables:
#   DATABASE_URL  — MySQL connection string (mysql://user:pass@host:3306/db)

global:
  databases:
    main:
      driver: mysql
      conn_string: "a|ap_var::DATABASE_URL|"
interfaces:
  products/create:
    output: http
    method: POST
    summary: Create a product
    actions:
      - name: ReadBody
        input: a|body|
        assert:
          error_message: "name and price are required"
          http_code_on_error: 400
          tests:
            - value: name
              is_not_empty: true
            - value: price
              is_not_null: true
      - name: InsertProduct
        run_when_succeeded: [ReadBody]
        database: main
        query: |-
          INSERT INTO products (name, price) VALUES (?, ?)
        params:
          - a|ReadBody::name|
          - a|ReadBody::price|
      - name: Respond
        run_when_succeeded: [InsertProduct]
        json_output: |-
          { "status": "created" }
  products/list:
    output: http
    method: GET
    summary: List products, paginated via query params
    actions:
      - name: ReadQuery
        input: a|params|
      - name: ListProducts
        run_when_succeeded: [ReadQuery]
        database: main
        query: |-
          SELECT id, name, price
          FROM products
          ORDER BY id DESC
          LIMIT ? OFFSET ?
        params:
          - a|ReadQuery::limit->default(20)|
          - a|ReadQuery::offset->default(0)|
  products/update:
    output: http
    method: PUT
    summary: Update a product's name and price by id
    actions:
      - name: ReadBody
        input: a|body|
        assert:
          error_message: "id, name and price are required"
          http_code_on_error: 400
          tests:
            - value: id
              is_not_null: true
            - value: name
              is_not_empty: true
            - value: price
              is_not_null: true
      - name: UpdateProduct
        run_when_succeeded: [ReadBody]
        database: main
        query: |-
          UPDATE products SET name = ?, price = ? WHERE id = ?
        params:
          - a|ReadBody::name|
          - a|ReadBody::price|
          - a|ReadBody::id|
      - name: Respond
        run_when_succeeded: [UpdateProduct]
        json_output: |-
          { "status": "updated" }
  products/delete:
    output: http
    method: DELETE
    summary: Delete a product by id (query param)
    actions:
      - name: ReadQuery
        input: a|params|
        assert:
          error_message: "id is required"
          http_code_on_error: 400
          tests:
            - value: id
              is_not_null: true
      - name: DeleteProduct
        run_when_succeeded: [ReadQuery]
        database: main
        query: |-
          DELETE FROM products WHERE id = ?
        params:
          - a|ReadQuery::id|
      - name: Respond
        run_when_succeeded: [DeleteProduct]
        json_output: |-
          { "status": "deleted" }

seed.yml

name: MysqlCrudSeed
description: Creates the products table (MySQL) used by the CRUD endpoints and loads a couple of sample rows. Safe to re-run.

docs: true

# Required managed variables:
#   DATABASE_URL — MySQL connection string

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

interfaces:
  api/seed:
    output: http
    method: POST
    summary: Create, clear and seed the products table
    actions:
      - name: CreateProductsTable
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS products (
            id    INT           NOT NULL AUTO_INCREMENT PRIMARY KEY,
            name  VARCHAR(255)  NOT NULL,
            price DECIMAL(10,2) NOT NULL
          );
      - name: ClearProducts
        run_when_succeeded: [CreateProductsTable]
        database: main
        hide_data_on_success: true
        query: DELETE FROM products;
      - name: SeedProducts
        run_when_succeeded: [ClearProducts]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO products (name, price) VALUES
            ('Widget', 9.99),
            ('Gadget', 19.95);
      - name: Respond
        run_when_succeeded: [SeedProducts]
        json_output: |-
          { "status": "seeded", "products": 2 }