Please read this document to learn how to create and configure a PHP REST API Web service to access MySQL database records.

PHP CRUD API Generator

Expose your MySQL/MariaDB database as a secure, flexible, and instant REST-like API. Features optional authentication (API key, Basic Auth, JWT, OAuth-ready), OpenAPI (Swagger) docs, and zero code generation.

? Features

• Auto-discovers tables and columns
• Full CRUD endpoints for any table
• Bulk operations - Create or delete multiple records efficiently
• Configurable authentication (API Key, Basic Auth, JWT, or none)
• Rate limiting - Prevent API abuse with configurable request limits
• Request logging - Comprehensive logging for debugging and monitoring
• Advanced query features: - Field selection - Choose specific columns to return - Advanced filtering - Support for multiple comparison operators (eq, neq, gt, gte, lt, lte, like, in, notin, null, notnull) - Sorting - Multi-column sorting with ascending/descending order - Pagination - Efficient pagination with metadata
• Input validation - Comprehensive validation to prevent SQL injection and invalid inputs
• RBAC: per-table role-based access control
• Admin panel (minimal)
• OpenAPI (Swagger) JSON endpoint for instant docs
• Clean PSR-4 codebase
• PHPUnit tests and extensible architecture

? See detailed enhancement documentation ? ? Rate Limiting Documentation ? ? Request Logging Documentation ? ? Quick Start (5 minutes) ? ? Integration with upMVC Framework ? - NEW! Full-stack power combo ?? Comparison with PHP-CRUD-API v2 ? - NEW! Detailed feature comparison and when to use each ?? Feature Roadmap ? - NEW! Upcoming features and integrations

? SECURITY WARNING

?? CRITICAL: The admin dashboard (dashboard.html) and health endpoint (health.php) expose sensitive information and MUST BE PROTECTED before deploying to production!

These files reveal: - API statistics, error rates, and performance metrics - Authentication failures and security threats - System information (memory, CPU, disk usage)

?? SECURE YOUR DASHBOARD NOW ? - Complete protection guide

Quick Fix (5 minutes): Add IP whitelist to .htaccess (Apache 2.4+):

<Files "dashboard.html">
  # Allow only localhost by default
  Require ip 127.0.0.1 ::1

  # To allow your public IP, add an extra line like:
  # Require ip YOUR.PUBLIC.IP.ADDRESS
</Files>

? Installation

Option 1: Install as Library (Recommended) ?

Just 4 simple steps:

# 1. Install via Composer
composer require bitshost/php-crud-api-generator

# 2. Copy 3 files to your project root
copy vendor/bitshost/php-crud-api-generator/public/index.php index.php
copy vendor/bitshost/php-crud-api-generator/dashboard.html dashboard.html
copy vendor/bitshost/php-crud-api-generator/health.php health.php

# 3. ? SECURE admin files (IMPORTANT!)
# Add this to .htaccess in your project root:
echo '<Files "dashboard.html">' >> .htaccess
echo '    Order Deny,Allow' >> .htaccess
echo '    Deny from all' >> .htaccess
echo '    Allow from 127.0.0.1' >> .htaccess
echo '</Files>' >> .htaccess

# 4. Edit index.php - Change 2 lines (point config paths to vendor)
# On line ~51, change:
#   $dbConfig = require __DIR__ . '/../config/db.php';
#   $apiConfig = require __DIR__ . '/../config/api.php';
# To:
#   $dbConfig = require __DIR__ . '/vendor/bitshost/php-crud-api-generator/config/db.php';
#   $apiConfig = require __DIR__ . '/vendor/bitshost/php-crud-api-generator/config/api.php';

# 5. Configure & run
notepad vendor/bitshost/php-crud-api-generator/config/db.php
notepad vendor/bitshost/php-crud-api-generator/config/api.php
php -S localhost:8000

That's it! Total modifications: 2 lines of code ?

? 5-Minute Quick Start Guide ? ? Secure Your Dashboard ? ? DO THIS BEFORE PRODUCTION!

Option 2: Standalone Project (Even Simpler!)

Download complete ready-to-use project:

composer create-project bitshost/php-crud-api-generator my-api
cd my-api

# Configure
cp config/db.example.php config/db.php
cp config/api.example.php config/api.php
notepad config/db.php
notepad config/api.php

# Run
php -S localhost:8000

That's it! Everything in one folder, ready to run. 0 lines to modify ?

?? Configuration

If installed as library (via composer require):

Edit config files in vendor directory:

notepad vendor/bitshost/php-crud-api-generator/config/db.php
notepad vendor/bitshost/php-crud-api-generator/config/api.php

If standalone project (via composer create-project):

Copy and edit config files:

cp config/db.example.php config/db.php
cp config/api.example.php config/api.php

Config file structure:

Edit config/db.php:

return [
    'host' => 'localhost',
    'dbname' => 'your_database',
    'user' => 'your_db_user',
    'pass' => 'your_db_password',
    'charset' => 'utf8mb4'
];

Edit config/api.php:

return [
    'auth_enabled' => false, // true to require authentication
    'auth_method' => 'apikey', // 'apikey', 'basic', 'jwt', 'oauth'
    'api_keys' => ['changeme123'], // API keys for 'apikey'
    'basic_users' => ['admin' => 'secret'], // Users for 'basic' and 'jwt'
    'jwt_secret' => 'YourSuperSecretKey',
    'jwt_issuer' => 'yourdomain.com',
    'jwt_audience' => 'yourdomain.com',
    
    // Rate limiting (recommended for production)
    'rate_limit' => [
        'enabled' => true,
        'max_requests' => 100,      // 100 requests
        'window_seconds' => 60,     // per 60 seconds (1 minute)
    ],
    
    // Request logging (recommended for production)
    'logging' => [
        'enabled' => true,
        'log_dir' => __DIR__ . '/../logs',
        'log_level' => 'info',      // debug, info, warning, error
    ],
];

Environment variables (.env)

For easier secret management and 12-factor style deployments, the project also supports a root-level .env file.

• Copy `.env.example` to `.env` and adjust values for your environment.
• The following keys override values from `config/db.php` and `config/api.php` when defined: - `DB_HOST`, `DB_NAME`, `DB_USER`, `DB_PASS`, `DB_CHARSET` - `API_AUTH_METHOD` - `API_KEYS` (comma-separated list) - `BASIC_ADMIN_PASSWORD`, `BASIC_USER_PASSWORD` - `JWT_SECRET`, `JWT_EXPIRATION`, `JWT_ISSUER`, `JWT_AUDIENCE`
• The public entrypoint loads `.env` before configs, and `.htaccess` protects `.env` from direct web access.

? Security Setup (Production)

?? IMPORTANT: This framework ships with example credentials for development. You MUST change these before deploying to production!

Quick Security Setup:

# 1. Generate secure secrets (JWT secret + API keys)
php scripts/generate_secrets.php

# 2. Update config/api.php with generated secrets

# 3. Create admin user in database
php scripts/create_user.php admin [email protected] YourSecurePassword123! admin

What to Change:

• [ ] `jwt_secret` - Generate with: `php scripts/generate_jwt_secret.php`
• [ ] `api_keys` - Use long random strings (64+ characters)
• [ ] Default admin password in `sql/create_api_users.sql`
• [ ] Database credentials in `config/db.php`

? Full security guide: docs/AUTHENTICATION.md

? Authentication Modes

Configure in config/api.php:

• No auth: `'auth_enabled' => false`
• API Key: `'auth_enabled' => true, 'auth_method' => 'apikey'` Client: `X-API-Key` header or `?api_key=...`
• Basic Auth: `'auth_method' => 'basic'` Client: HTTP Basic Auth (username:password)
• JWT: `'auth_method' => 'jwt'` (Recommended for production) 1. POST to `/index.php?action=login` with credentials 2. Use returned token as `Authorization: Bearer <token>`
• OAuth (future): `'auth_method' => 'oauth'`

? Complete Authentication Guide ? - Detailed examples with Postman, HTTPie, cURL (JSON, Form Data, Multipart)

? API Endpoints

All requests go through public/index.php with action parameter.

| Action | Method | Usage Example | |--------------|--------|-------------------------------------------------------------| | tables | GET | /index.php?action=tables | | columns | GET | /index.php?action=columns&table=users | | list | GET | /index.php?action=list&table=users | | count | GET | /index.php?action=count&table=users | | read | GET | /index.php?action=read&table=users&id=1 | | create | POST | /index.php?action=create&table=users (form POST or JSON) | | update | POST | /index.php?action=update&table=users&id=1 (form POST or JSON) | | delete | POST | /index.php?action=delete&table=users&id=1 | | bulk_create | POST | /index.php?action=bulk_create&table=users (JSON array) | | bulk_delete | POST | /index.php?action=bulk_delete&table=users (JSON with ids) | | openapi | GET | /index.php?action=openapi | | login | POST | /index.php?action=login (JWT only) |

? Example curl Commands

# List tables
curl http://localhost/index.php?action=tables

# List users with API key
curl -H "X-API-Key: changeme123" "http://localhost/index.php?action=list&table=users"

# JWT login
curl -X POST -d "username=admin&password=secret" http://localhost/index.php?action=login

# List with JWT token
curl -H "Authorization: Bearer <token>" "http://localhost/index.php?action=list&table=users"

# Basic auth
curl -u admin:secret "http://localhost/index.php?action=list&table=users"

# Bulk create
curl -X POST -H "Content-Type: application/json" \
  -d '[{"name":"Alice","email":"[email protected]"},{"name":"Bob","email":"[email protected]"}]' \
  "http://localhost/index.php?action=bulk_create&table=users"

# Bulk delete
curl -X POST -H "Content-Type: application/json" \
  -d '{"ids":[1,2,3]}' \
  "http://localhost/index.php?action=bulk_delete&table=users"

? Bulk Operations

The API supports bulk operations for efficient handling of multiple records:

Bulk Create

Create multiple records in a single transaction. If any record fails, the entire operation is rolled back.

Endpoint: POST /index.php?action=bulk_create&table=users

Request Body (JSON array):

[
  {"name": "Alice", "email": "[email protected]", "age": 25},
  {"name": "Bob", "email": "[email protected]", "age": 30},
  {"name": "Charlie", "email": "[email protected]", "age": 35}
]

Response:

{
  "success": true,
  "created": 3,
  "data": [
    {"id": 1, "name": "Alice", "email": "[email protected]", "age": 25},
    {"id": 2, "name": "Bob", "email": "[email protected]", "age": 30},
    {"id": 3, "name": "Charlie", "email": "[email protected]", "age": 35}
  ]
}

Bulk Delete

Delete multiple records by their IDs in a single query.

Endpoint: POST /index.php?action=bulk_delete&table=users

Request Body (JSON):

{
  "ids": [1, 2, 3, 4, 5]
}

Response:

{
  "success": true,
  "deleted": 5
}

? Count Records

Get the total count of records in a table with optional filtering. This is useful for analytics and doesn't include pagination overhead.

Endpoint: GET /index.php?action=count&table=users

Query Parameters: - filter - (Optional) Same filter syntax as the list endpoint

Examples:

# Count all users
curl "http://localhost/index.php?action=count&table=users"

# Count active users
curl "http://localhost/index.php?action=count&table=users&filter=status:eq:active"

# Count users over 18
curl "http://localhost/index.php?action=count&table=users&filter=age:gt:18"

# Count with multiple filters
curl "http://localhost/index.php?action=count&table=users&filter=status:eq:active,age:gte:18"

Response:

{
  "count": 42
}

? Advanced Query Features (Filtering, Sorting, Pagination, Field Selection)

The list action endpoint now supports advanced query parameters:

| Parameter | Type | Description | |--------------|---------|---------------------------------------------------------------------------------------------------| | filter | string | Filter rows by column values. Format: filter=col:op:value or filter=col:value (backward compatible). Use , to combine multiple filters. | | sort | string | Sort by columns. Comma-separated. Use - prefix for DESC. Example: sort=-created_at,name | | page | int | Page number (1-based). Default: 1 | | page_size | int | Number of rows per page (max 100). Default: 20 | | fields | string | Select specific fields. Comma-separated. Example: fields=id,name,email |

Filter Operators

| Operator | Description | Example | |----------|-------------|---------| | eq or : | Equals | filter=name:eq:Alice or filter=name:Alice | | neq or ne | Not equals | filter=status:neq:deleted | | gt | Greater than | filter=age:gt:18 | | gte or ge | Greater than or equal | filter=price:gte:100 | | lt | Less than | filter=stock:lt:10 | | lte or le | Less than or equal | filter=discount:lte:50 | | like | Pattern match | filter=email:like:%@gmail.com | | in | In list (pipe-separated) | filter=status:in:active|pending | | notin or nin | Not in list | filter=role:notin:admin|super | | null | Is NULL | filter=deleted_at:null: | | notnull | Is NOT NULL | filter=email:notnull: |

Examples:

• Basic filtering: `GET /index.php?action=list&table=users&filter=name:Alice`
• Advanced filtering: `GET /index.php?action=list&table=users&filter=age:gt:18,status:eq:active`
• Field selection: `GET /index.php?action=list&table=users&fields=id,name,email`
• Sorting: `GET /index.php?action=list&table=users&sort=-created_at,name`
• Pagination: `GET /index.php?action=list&table=users&page=2&page_size=10`
• Combined query: `GET /index.php?action=list&table=users&filter=email:like:%gmail.com&sort=name&page=1&page_size=5&fields=id,name,email`
• IN operator: `GET /index.php?action=list&table=orders&filter=status:in:pending|processing|shipped`
• Multiple conditions: `GET /index.php?action=list&table=products&filter=price:gte:10,price:lte:100,stock:gt:0`

Response:

{
  "data": [ ... array of rows ... ],
  "meta": {
    "total": 47,
    "page": 2,
    "page_size": 10,
    "pages": 5
  }
}

? OpenAPI Documentation (Swagger)

Your API automatically generates OpenAPI 3.0 documentation!

Get the OpenAPI Specification (JSON)

# Access the auto-generated OpenAPI spec
curl http://localhost:8000/index.php?action=openapi

# Or visit in browser:
http://localhost:8000/index.php?action=openapi

View Interactive Documentation (Swagger UI)

Option 1: Online Swagger Editor (Quick & Easy) 1. Copy JSON from: http://localhost:8000/index.php?action=openapi 2. Paste into: https://editor.swagger.io/ 3. See beautiful interactive documentation!

Option 2: Use dashboard.html (Recommended) Your project includes dashboard.html which has API documentation built-in:

http://localhost:8000/dashboard.html

Example OpenAPI Path Structure

This is what the specification includes for /index.php?action=list&table={table}:

get:
  summary: List rows in {table} with optional filtering, sorting, and pagination
  parameters:
    - name: table
      in: query
      required: true
      schema: { type: string }
    - name: filter
      in: query
      required: false
      schema: { type: string }
      description: |
        Filter rows by column values. Example: filter=name:Alice,email:%gmail.com
    - name: sort
      in: query
      required: false
      schema: { type: string }
      description: |
        Sort by columns. Example: sort=-created_at,name
    - name: page
      in: query
      required: false
      schema: { type: integer, default: 1 }
      description: Page number (1-based)
    - name: page_size
      in: query
      required: false
      schema: { type: integer, default: 20, maximum: 100 }
      description: Number of rows per page (max 100)
  responses:
    '200':
      description: List of rows with pagination meta
      content:
        application/json:
          schema:
            type: object
            properties:
              data:
                type: array
                items: { type: object }
              meta:
                type: object
                properties:
                  total: { type: integer }
                  page: { type: integer }
                  page_size: { type: integer }
                  pages: { type: integer }

Note: The YAML above is just an example of the structure. The actual API returns JSON format.

?? Security Notes

• Enable authentication for any public deployment!
• Enable rate limiting in production to prevent abuse
• Enable request logging for security auditing and debugging
• Never commit real credentials?use `.gitignore` and example configs.
• Restrict DB user privileges.
• Input validation: All user inputs (table names, column names, IDs, filters) are validated to prevent SQL injection and invalid queries.
• Parameterized queries: All database queries use prepared statements with bound parameters.
• RBAC enforcement: Role-based access control is enforced at the routing level before any database operations.
• Rate limiting: Configurable request limits prevent API abuse and DoS attacks.
• Sensitive data redaction: Passwords, tokens, and API keys are automatically redacted from logs.

? Rate Limiting Documentation ? ? Request Logging Documentation ?

? Running Tests

./vendor/bin/phpunit

? Working with Related Data (Client-Side Joins)

Your API provides all the data you need - it's up to the client to decide how to combine it. This approach gives you maximum flexibility and control.

Current approach: Fetch related data in separate requests and combine on the client side.

Quick Example: Get User with Posts

// 1. Fetch user
const user = await fetch('/api.php?action=read&table=users&id=123')
  .then(r => r.json());

// 2. Fetch user's posts
const posts = await fetch('/api.php?action=list&table=posts&filter=user_id:123')
  .then(r => r.json());

// 3. Combine however you want
const userData = {
  ...user,
  posts: posts.data
};

Optimization: Use IN Operator for Batch Fetching

// Get multiple related records in one request
const postIds = '1|2|3|4|5';  // IDs from previous query
const comments = await fetch(
  `/api.php?action=list&table=comments&filter=post_id:in:${postIds}`
).then(r => r.json());

// Group by post_id on client
const commentsByPost = comments.data.reduce((acc, comment) => {
  acc[comment.post_id] = acc[comment.post_id] || [];
  acc[comment.post_id].push(comment);
  return acc;
}, {});

Parallel Fetching for Performance

// Fetch multiple resources simultaneously
const [user, posts, comments] = await Promise.all([
  fetch('/api.php?action=read&table=users&id=123').then(r => r.json()),
  fetch('/api.php?action=list&table=posts&filter=user_id:123').then(r => r.json()),
  fetch('/api.php?action=list&table=comments&filter=user_id:123').then(r => r.json())
]);

// All requests happen at once - much faster!

? See complete client-side join examples ?

Why this approach? - ? Client decides what data to fetch and when - ? Easy to optimize with caching and parallel requests - ? Different clients can have different data needs - ? Standard REST API practice - ? No server-side complexity for joins

Future: Auto-join/expand features may be added based on user demand.

?? Roadmap

• Client-side joins ? (Current - simple and flexible!)
• Relations / Linked Data (auto-join, populate, or expand related records) - Future, based on demand
• API Versioning (when needed)
• OAuth/SSO (if targeting SaaS/public)
• More DB support (Postgres, SQLite, etc.)
• Analytics & promotion endpoints

? License

MIT

? Credits

Built by BitHost. PRs/issues welcome!