PPPHP

Databases — build once, run on any engine

A PPPHP project — and the app it generates — can run on MySQL, MariaDB, TiDB, PostgreSQL, CockroachDB, SQL Server, or the built-in Virtual Database (SQLite). You design your tables, pages and logic once; the platform speaks each engine's own SQL for you. This guide shows what's supported, how it works, how to choose an engine, how to deploy to each — and how to move a project to another database.

One design, every engine. The generated app emits one canonical (MySQL-flavoured) SQL, and a thin runtime layer translates it to whatever database it is connected to — identifier quoting, pagination, case-insensitive search, date functions, upserts and table creation. On MySQL/MariaDB nothing is translated at all, so existing MySQL apps are byte-for-byte unchanged.

Supported engines

EnginePDO driverStatusNotes
MySQL / MariaDB / TiDBmysql✅ NativeThe canonical dialect — no translation, zero overhead.
Virtual Database (SQLite)sqlite✅ NativeThe recommended default — a private DB created instantly inside the project, nothing to install.
PostgreSQL / CockroachDBpgsql✅ Fully supportedValidated end-to-end (console, build, generated app, provisioning).
Microsoft SQL Serversqlsrv / dblib✅ Fully supportedUses the Microsoft driver when present, else FreeTDS (dblib).
Oracleoci⚠️ ExperimentalBrowse/query in the console; generated-app support is code-complete but not yet certified. Needs pdo_oci.

Choosing an engine for a new project

On Projects → New you pick where the project's data lives:

Whatever you choose, the Designer, Fields, Pages, Query, Security, Events and every other SPA page work the same — the console introspects and queries your engine through its own dialect adapter, and the build emits an app that connects to that same database.

How the translation works

Every query in a generated app flows through two choke points — DB::Query() and DB::Exec() (the database helper every build ships). When the active driver is not MySQL, they translate the statement first:

ConstructMySQL (canonical)PostgreSQLSQL ServerSQLite
Identifier quoting`col`"col"[col]`col`
PaginationLIMIT 10 OFFSET 20LIMIT 10 OFFSET 20OFFSET 20 ROWS FETCH NEXT 10…LIMIT 10 OFFSET 20
Row cap (no offset)LIMIT 1LIMIT 1SELECT TOP (1) …LIMIT 1
Case-insensitive searchLIKEILIKELIKE (CI collation)LIKE
Auto-increment keyINT AUTO_INCREMENTSERIALINT IDENTITY(1,1)INTEGER … AUTOINCREMENT
"Now"NOW()CURRENT_TIMESTAMPGETDATE()CURRENT_TIMESTAMP
Date mathsDATE_ADD(d, INTERVAL 7 DAY)d + 7 * INTERVAL '1 day'DATEADD(DAY, 7, d)datetime(d, '+7 day')
Date partsYEAR(d)EXTRACT(YEAR FROM d)YEAR(d)strftime('%Y', d)
UpsertON DUPLICATE KEY UPDATEON CONFLICT … DO UPDATEUPDATE-then-INSERTON CONFLICT … DO UPDATE

Table creation is converted the same way (types, indexes, identities), and provisioning of a fresh database (schema + seed data) is handled per engine — including PostgreSQL sequence realignment and SQL Server IDENTITY_INSERT for seeded rows.

Move a project to another database — host it anywhere

You can design on one engine and host on another. The most common case: you built the app on the built-in Virtual Database (SQLite), and your client's hosting gives you MySQL (or a VPS with PostgreSQL). Your tables, data, keys and auto-numbers are converted for you — there is nothing to rewrite.

Option A — Deployment Package (recommended)

  1. Open Build & Deploy and click Deployment Package.
  2. In Database on the host, pick where the app will run: MySQL / MariaDB, PostgreSQL, SQL Server or SQLite (built-in file). The hint under the list explains what happens for each choice.
  3. Download the ZIP, upload and unzip it on the server, and open the site in a browser.
  4. The setup wizard asks for the database connection (for SQLite it needs nothing), creates every table, loads your data, then lets you create the administrator account. If any table or row cannot be created, the wizard stops and shows exactly which one — it never reports success over a half-built database.
Examples.
• Built on SQLite, hosting on cPanel → choose MySQL / MariaDB; create an empty database in cPanel and type its name, user and password into the wizard.
• Built on MySQL, hosting somewhere without a database server → choose SQLite; the package carries its own copy of your data and the wizard simply confirms it.
• Built on SQLite, hosting on a VPS with PostgreSQL → choose PostgreSQL; create an empty database (createdb yourapp) and connect the wizard to it.

Set the usual choice once in Output → Advanced → Deployment package database; the Deployment Package dialog then starts on it (you can still change it per package).

Option B — Generate SQL Script

Build & Deploy → Generate SQL Script downloads one .sql file. Choose Write it for (the target engine) and tick Include the data to add every row. Import it with the engine's usual tool:

mysql -u USER -p yourdb < yourapp_database_mysql.sql          # MySQL / MariaDB (or phpMyAdmin → Import)
psql -h HOST -U USER -d yourdb -f yourapp_database_pgsql.sql    # PostgreSQL
sqlite3 app.sqlite < yourapp_database_sqlite.sql                # SQLite

A converted script lists your views for you to recreate by hand (view SQL is engine-specific); the Deployment Package recreates them for you.

What is converted for you

In your projectOn the destination
Column types (text, numbers, dates, yes/no, JSON)The nearest native type of that engine
Choice lists (ENUM)A text column that only accepts the same choices
Auto-numbersThe engine's own auto-number, continuing after your existing rows
Links between tables (foreign keys)Added after the data is loaded, so load order never matters
Indexes and unique rulesRecreated
Calculated columnsRecreated as calculated columns
MySQL-style SQL in your pages and events — IFNULL(), IF(), GROUP_CONCAT(), SUM(status = 'Done'), FIND_IN_SET(), LIKE on a number or dateTranslated at run time

Keeping data valid everywhere

SQLite accepts almost anything; MySQL, PostgreSQL and SQL Server are strict. So the generated forms enforce the rules for you on every engine — a text field cannot exceed its column size, a choice field only offers its allowed values, and a blank value on a required column with a default simply takes the default. Data entered on a SQLite project therefore moves to a stricter engine without surprises.

The built-in database answers like MySQL

Write your queries the MySQL way and the Virtual Database (SQLite) gives the same answers — so an app built on it behaves the same after it moves to MySQL. In particular:

The one limit: SQLite has no number larger than 9,223,372,036,854,775,807, so a BIGINT UNSIGNED value above that is stored approximately. Use a text column for such values (for example, hash codes).

After moving — a short checklist

Deploying a generated app to each engine

MySQL / MariaDB

The classic path. Create the database on your host (cPanel/DirectAdmin), then import the generated files:

mysql -u USER -p YOUR_DB < schema.sql
mysql -u USER -p YOUR_DB < seed.sql

…and enter its connection details in the app's database settings — the standalone installer (install.php) does this for you.

PostgreSQL

Create the database in your panel, then import the PostgreSQL-dialect schema.sql and seed.sql the build produced for a Postgres project:

createdb yourdb                       # or create it in cPanel/DirectAdmin
psql -h HOST -U USER -d yourdb -f schema.sql
psql -h HOST -U USER -d yourdb -f seed.sql
The build emits Postgres-native DDL (SERIAL, "quoted" identifiers) and appends setval(…) statements after the seed so auto-increment sequences continue past your seeded rows — no manual reset needed.

Microsoft SQL Server

Create the database, then run the generated SQL Server script (it uses [bracketed] identifiers, IDENTITY keys, DATETIME2, and wraps seeded inserts in SET IDENTITY_INSERT):

sqlcmd -S HOST -U sa -P '••••' -Q "CREATE DATABASE yourdb"
sqlcmd -S HOST -U sa -P '••••' -d yourdb -i schema.sql
sqlcmd -S HOST -U sa -P '••••' -d yourdb -i seed.sql

Virtual Database (SQLite)

Nothing to import — the build ships the SQLite file itself (a snapshot of your project's data), and the app opens it directly. Ideal for shared hosting with no database server.

Connecting an existing database

If the target database already has your tables (for example you designed them in the console against that engine), you don't need schema.sql at all — just enter the connection in the app's database settings and deploy. The runtime translates every query to that engine automatically.

// The app's database settings (PostgreSQL example) — install.php writes these for you
return [
    'type' => 'pgsql', 'host' => 'db.example.com', 'port' => '5432',
    'dbname' => 'app', 'user' => 'app', 'pass' => '••••', 'schema' => 'public',
];

Writing your own SQL (events, buttons, Query)

When you write SQL by hand — in an Event, a custom button, or the Query module — prefer the portable helpers so your code runs on every engine:

Escape hatch caveat. A truly raw fragment you pass to a builder's whereRaw() — or SQL you run straight through DB::Pdo() — is not function-translated (identifiers and LIMIT are, if it goes through DB::Query). If you must hand-write engine-specific functions, guard them with DB::CurrentDriver().

Per-engine notes

Your MySQL apps are safe. The entire translation layer is a strict no-op when the driver is MySQL, so upgrading the platform changes nothing for apps already running on MySQL/MariaDB.