Backend · 1 of 4

Save to MySQL

PDO + prepared statements with a SQLite fallback so it boots zero-config. One driver swap and the same handler talks to MySQL, MariaDB, or PostgreSQL.

Walkthrough

1
Bootstrap the schema

Run php migrate.php once in this directory. With no DB_DSN env var set, it provisions storage.db (SQLite) right next to the example. The shipped schema.sql also includes commented-out MySQL + PostgreSQL DDL — copy that block into your DB tool of choice when you migrate to production.
Bash
```
# Zero-config dev (SQLite)
php migrate.php
# → Migrated 2 statements via DSN: sqlite (default)

# Production (MySQL)
export DB_DSN='mysql:host=localhost;dbname=builderjs;charset=utf8mb4'
export DB_USER='your_user'
export DB_PASS='your_password'
mysql -u$DB_USER -p$DB_PASS builderjs < schema.sql
```
2
Validate every $_POST through the shared validator

Keep the validator dependencies tight: slug is regex-bounded so the storage key can never carry traversal characters; html + data have explicit byte caps that fire before a multi-megabyte body hits PDO. Every validation failure becomes a uniform 422 response with field-level errors.
PHP
```
use DemoBuilder\Validator;

$payload = Validator::make($_POST)
    ->required('slug')->regex('/^[a-z0-9-]+$/i')->maxLength(120)
    ->required('html')->maxLength(5_000_000)
    ->required('data')->maxLength(10_000_000)
    ->validateOrFail();
```
3
Upsert via a single prepared statement

The handler uses INSERT … ON CONFLICT DO UPDATE (SQLite + Postgres) or INSERT … ON DUPLICATE KEY UPDATE (MySQL). The driver name is read once from PDO and the right SQL is dispatched. No second SELECT-then-INSERT round-trip; one network hop per save.
PHP
```
$stmt = $pdo->prepare($sql);   // dispatch by driver, prepared once
$stmt->execute([
    $payload['slug'],
    $payload['html'],
    $payload['data'],
]);
```

Wire the builder

In demo/builder.php, change one line under the endpoints block: endpoints.save → your mysql-save.php URL. Reload the builder; every Save click now lands in your DB. Round-trip on the next page load by reading the same row back into builder.load().

// demo/builder.php — top of file (the ONE labelled config block)
$builderConfig = [
    'themesRoot' => __DIR__ . '/themes',
    'endpoints' => [
        'save'         => '/examples/backend/1-mysql/mysql-save.php',  // ← here
        'assetUpload'  => '/backend/asset-upload.php',
        // …
    ],
    // …
];

Live demo

demo-mini-builder--minimal

The whole snippet

Click Copy on any file to grab it byte-identical, or Expand to read inline (capped at ~520 px so the page stays scannable). Multi-file snippets surface a side-by-side grid — copy only the files you need.

2 files · 71 lines · 2.7 KB

mysql-save.php PHP 48 lines · 1.8 KB

<?php
declare(strict_types=1);

require_once __DIR__ . '/../../../backend/_lib/Validator.php';
require_once __DIR__ . '/../../../backend/_lib/JsonResponse.php';
require_once __DIR__ . '/../../../backend/_lib/Db.php';

use DemoBuilder\Db;
use DemoBuilder\JsonResponse;
use DemoBuilder\Validator;

if (($_SERVER['REQUEST_METHOD'] ?? 'GET') !== 'POST') {
    JsonResponse::error('Method not allowed', 405);
}

// ─── BUYER CONFIG ─────────────────────────────────────────────────────
// Resolution order: explicit env var → SQLite in this dir.
$pdo = Db::connect(sqliteDir: __DIR__);

$payload = Validator::make($_POST)
    ->required('slug')->regex('/^[a-z0-9-]+$/i')->maxLength(120)
    ->required('html')->maxLength(5_000_000)
    ->required('data')->maxLength(10_000_000)
    ->validateOrFail();

$driver = (string) $pdo->getAttribute(\PDO::ATTR_DRIVER_NAME);
$sql = match ($driver) {
    'mysql' => 'INSERT INTO pages (slug, html, data) VALUES (?, ?, ?)
                ON DUPLICATE KEY UPDATE html = VALUES(html), data = VALUES(data),
                                       updated_at = CURRENT_TIMESTAMP',
    default => 'INSERT INTO pages (slug, html, data) VALUES (?, ?, ?)
                ON CONFLICT(slug) DO UPDATE SET html = excluded.html,
                                                data = excluded.data,
                                                updated_at = CURRENT_TIMESTAMP',
};

$stmt = $pdo->prepare($sql);
$stmt->execute([
    (string) $payload['slug'],
    (string) $payload['html'],
    (string) $payload['data'],
]);

JsonResponse::success([
    'slug'    => (string) $payload['slug'],
    'message' => 'Saved to database.',
    'driver'  => $driver,
]);

schema.sql SQL 23 lines · 946 B

-- SQLite (zero-config dev default)
CREATE TABLE IF NOT EXISTS pages (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    slug        TEXT    NOT NULL UNIQUE,
    html        TEXT    NOT NULL,
    data        TEXT    NOT NULL,
    created_at  TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    updated_at  TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

CREATE INDEX IF NOT EXISTS pages_slug_idx ON pages(slug);

-- MySQL 8.0 / MariaDB 10.6+
CREATE TABLE pages (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    slug        VARCHAR(120)    NOT NULL UNIQUE,
    html        MEDIUMTEXT      NOT NULL,
    data        MEDIUMTEXT      NOT NULL,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
                                         ON UPDATE CURRENT_TIMESTAMP,
    INDEX pages_slug_idx (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Notes

Why an UPSERT? The Builder writes the same slug on every Save — so the second call would conflict on the unique index. SQLite 3.24+, MySQL 8.0+, and PostgreSQL 9.5+ all support a single-statement UPSERT; older versions can fall back to INSERT … ON DUPLICATE KEY UPDATE (MySQL) or a manual SELECT-then-UPDATE/INSERT wrapped in a transaction.

Reading saved pages back. The round-trip is symmetric: SELECT data FROM pages WHERE slug = ?, json_decode the result, hand it to builder.load() as the seed JSON. The shipped builder.php already accepts ?slug= as a query param if you wire the loader for it.

Production swap matrix.

MySQL / MariaDB. Set DB_DSN='mysql:host=…;dbname=…;charset=utf8mb4' + DB_USER + DB_PASS. The handler's match ($driver) dispatches MySQL syntax automatically.
PostgreSQL. Set DB_DSN='pgsql:host=…;dbname=…'. The default ON CONFLICT branch already speaks PG; the only extra step is running the commented-out PG block in schema.sql.
Cloudflare D1 / Turso / LibSQL. They all expose SQLite-compatible HTTP endpoints; replace new PDO(…) in Db::connect() with the appropriate HTTP client (curl + the vendor's auth header) and keep the SQL identical.
SQL Server. Swap the UPSERT for MERGE INTO pages … in a fourth match arm; the rest of the handler is driver-neutral.

Atomicity & concurrency. The single INSERT … ON CONFLICT … DO UPDATE is atomic per-row at the engine level. For multi-row saves (e.g. a "publish all pages" admin action) wrap the calls in $pdo->beginTransaction() + commit(). Don't share PDO instances across forked workers — the connection cache in Db::connect() is request-scoped on purpose.

What about NoSQL? If your store is MongoDB / DynamoDB / Firestore, skip the SQL-flavour swaps above and treat mysql-save.php as the contract reference: validate the same three fields, write to your store's primary key (slug), respond with the same JSON shape. The Validator + JsonResponse helpers are storage-agnostic.

Cross-tier neighbours that share APIs, events, or chrome family — pick the next hop that fits your goal.

Save to MySQL

Walkthrough

Bootstrap the schema

Validate every $_POST through the shared validator

Upsert via a single prepared statement

Wire the builder

Live demo

The whole snippet

Notes

Hello World — Save flow

Multi-page

Session auth

Save click — custom button

More backend examples