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.
Supported engines
| Engine | PDO driver | Status | Notes |
|---|---|---|---|
| MySQL / MariaDB / TiDB | mysql | ✅ Native | The canonical dialect — no translation, zero overhead. |
| Virtual Database (SQLite) | sqlite | ✅ Native | The recommended default — a private DB created instantly inside the project, nothing to install. |
| PostgreSQL / CockroachDB | pgsql | ✅ Fully supported | Validated end-to-end (console, build, generated app, provisioning). |
| Microsoft SQL Server | sqlsrv / dblib | ✅ Fully supported | Uses the Microsoft driver when present, else FreeTDS (dblib). |
| Oracle | oci | ⚠️ Experimental | Browse/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:
- Virtual Database (recommended) — a zero-config SQLite database created for you. Perfect for most apps; nothing to install, no host or credentials.
- A server engine — MySQL, PostgreSQL, SQL Server, etc. Enter the host, database name and credentials; the wizard tests the connection before saving.
- Standalone — an app with no database (static/marketing pages).
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:
| Construct | MySQL (canonical) | PostgreSQL | SQL Server | SQLite |
|---|---|---|---|---|
| Identifier quoting | `col` | "col" | [col] | `col` |
| Pagination | LIMIT 10 OFFSET 20 | LIMIT 10 OFFSET 20 | OFFSET 20 ROWS FETCH NEXT 10… | LIMIT 10 OFFSET 20 |
| Row cap (no offset) | LIMIT 1 | LIMIT 1 | SELECT TOP (1) … | LIMIT 1 |
| Case-insensitive search | LIKE | ILIKE | LIKE (CI collation) | LIKE |
| Auto-increment key | INT AUTO_INCREMENT | SERIAL | INT IDENTITY(1,1) | INTEGER … AUTOINCREMENT |
| "Now" | NOW() | CURRENT_TIMESTAMP | GETDATE() | CURRENT_TIMESTAMP |
| Date maths | DATE_ADD(d, INTERVAL 7 DAY) | d + 7 * INTERVAL '1 day' | DATEADD(DAY, 7, d) | datetime(d, '+7 day') |
| Date parts | YEAR(d) | EXTRACT(YEAR FROM d) | YEAR(d) | strftime('%Y', d) |
| Upsert | ON DUPLICATE KEY UPDATE | ON CONFLICT … DO UPDATE | UPDATE-then-INSERT | ON 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)
- Open Build & Deploy and click Deployment Package.
- 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.
- Download the ZIP, upload and unzip it on the server, and open the site in a browser.
- 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.
• 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 project | On 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-numbers | The 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 rules | Recreated |
| Calculated columns | Recreated 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 date | Translated 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:
- Text matching ignores case and accents, as MySQL does:
name = 'cafe'finds “Café”, and sorting puts “Émile” with the E's. A trailing space counts ('abc 'is not'abc'). - Big whole numbers stay exact — 10-digit phone numbers, large IDs and amounts in paisa keep every digit,
and
phone LIKE '984%'finds them. - Money adds up exactly: decimal columns keep their decimals (
12.50, not12.5), and0.1 + 0.2is0.3. - Totals and subtotals:
GROUP BY … WITH ROLLUP,SQL_CALC_FOUND_ROWS+FOUND_ROWS(), window totals such asSUM(total) OVER ()and the JSON functions all work. - MySQL's rules apply: a query that shows a column next to a total must group by it
(
SELECT status, COUNT(*) … GROUP BY status) — MySQL refuses it otherwise, so the built-in database does too, and you find out while building, not after going live. - Errors name the field — a duplicate, a missing linked record or a rule that fails points at the field, on every engine, so the message users see is the same.
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
- Run Build & Deploy → Health Check on the new server: it lists which database drivers are installed.
- Open a few list, add and edit pages and a dashboard — every query is translated, but your own hand-written SQL may use a function the new engine lacks (guard it with
DB::CurrentDriver()). - Dividing two whole numbers: MySQL returns a decimal, PostgreSQL and SQL Server return a whole number — write
SUM(a) * 1.0 / COUNT(*)when you want decimals everywhere.
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
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:
- Use
DB::Insert(),DB::Update(),DB::Delete(),DB::Select()andDB::Upsert()— they build dialect-correct SQL for you. - Raw
DB::Query()/DB::FetchAll()strings written in the canonical (MySQL) style — backticks,LIMIT,NOW(),LIKE— are translated automatically. - Use the dialect-aware time helpers for windows:
DB::SqlNow(),DB::SqlNowMinusSeconds($n),DB::SqlSecondsUntil($col, $n).
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
- PostgreSQL —
LIKEis case-sensitive natively; the runtime rewrites it toILIKEso search behaves like MySQL.TEXTdefaults and sequences are handled for you. - SQL Server — columns default to
NOT NULL(the reverse of MySQL); the builder makes nullable columns explicit.TIMESTAMPmaps toDATETIME2(SQL Server'sTIMESTAMPis a rowversion, not a date). - SQLite / Virtual DB — the data ships as a file; enable it in New Project → Virtual Database. The console itself can run on SQLite too (the platform owner's Platform database guide covers it).
- Oracle — connect and browse today; full generated-app support lands once the
pdo_ocidriver is certified on the target host.