Working Across MySQL/MariaDB, PostgreSQL, and SQLite
Understand the practical differences between MySQL/MariaDB, PostgreSQL, and SQLite from a PHP developer's point of view — connecting to each, a few database-specific capabilities, and how to write SQL that stays portable when it matters.
PDO lets the same PHP code talk to several different database engines, but the engines themselves are not identical: each has its own connection details, its own SQL extensions, and its own sweet spot of use case. This lesson looks at the three most common relational databases you'll meet in PHP work — MySQL/MariaDB, PostgreSQL, and SQLite — what distinguishes them day to day, a couple of database-specific features worth knowing, and how to think about portability when your code might need to run against more than one of them.
MySQL and MariaDB
MySQL is the database most PHP developers meet first — it's the M in the classic LAMP stack, ships with virtually every shared host, and has powered WordPress, Laravel's default setup, and countless other PHP projects for decades. MariaDB is a community-driven fork of MySQL, created after Oracle's acquisition of MySQL, that stays largely drop-in compatible with it at the SQL and wire-protocol level — the same mysql: PDO DSN and driver work against either one, and most everyday SQL runs unchanged on both.
MySQL/MariaDB use mysql:host=...;dbname=...;charset=utf8mb4 as their DSN, and both offer the InnoDB storage engine by default in modern versions, which supports transactions and foreign keys (the older MyISAM engine does not support either, and you'll still see it referenced in older tutorials — avoid it for new tables). Auto-incrementing primary keys use AUTO_INCREMENT, and both support LIMIT n OFFSET m for pagination.
• DSN: mysql:host=...;dbname=...;charset=utf8mb4 — same driver/DSN shape for MySQL and MariaDB. • MariaDB: community fork of MySQL, largely SQL/wire-compatible. • Use the InnoDB storage engine (transactions + foreign keys) — avoid legacy MyISAM for new tables. • AUTO_INCREMENT for surrogate primary keys; LIMIT n OFFSET m for pagination.
PostgreSQL
PostgreSQL (often just "Postgres") is a more strictly standards-compliant, feature-rich relational database, popular for applications that need advanced SQL features: native array and JSON/JSONB column types with rich querying support, powerful window functions, full-text search, and strong support for custom types and extensions (like PostGIS for geographic data). It tends to be stricter about implicit type conversions than MySQL, which can surface bugs that MySQL would have silently tolerated.
Its DSN uses the pgsql: driver prefix: pgsql:host=localhost;port=5432;dbname=shop (username/password are still passed as the constructor's separate arguments). PostgreSQL uses SERIAL (or, in modern versions, the SQL-standard GENERATED ALWAYS AS IDENTITY) for auto-incrementing primary keys instead of MySQL's AUTO_INCREMENT. It also supports LIMIT/OFFSET for pagination, the same as MySQL.
• DSN: pgsql:host=...;port=5432;dbname=... • Stronger native support for arrays, JSON/JSONB, window functions, full-text search. • Stricter type checking than MySQL — fewer silent implicit conversions. • SERIAL / GENERATED ALWAYS AS IDENTITY for auto-incrementing keys, instead of AUTO_INCREMENT.
SQLite
SQLite is fundamentally different from the other two: it's not a client-server database at all. There's no separate server process to start or connect to — the entire database is a single file on disk, and the SQLite library reads and writes it directly as part of your PHP process. This makes it ideal for local development, automated tests (an in-memory SQLite database is extremely fast and disappears the moment the process ends — no cleanup needed), small embedded applications, and command-line tools that need structured storage without any setup.
Its DSN is just sqlite:/path/to/file.sqlite, or sqlite::memory: for a database that lives entirely in RAM and vanishes when the connection closes — perfect for a test suite that wants a fresh, isolated database on every run. SQLite is intentionally simpler than MySQL/PostgreSQL: it's dynamically typed per value rather than strictly typed per column, has no built-in user/permission system (file permissions on disk are the only access control), and isn't built for many concurrent writers hitting the same database file at once — it's a poor fit for a busy multi-user production web application, but an excellent one for anything single-process or read-heavy.
• DSN: sqlite:/path/to/file.sqlite, or sqlite::memory: for an ephemeral in-RAM database. • No server process — the database is a plain file, read/written directly by the process. • Ideal for local dev, automated tests, embedded/CLI tools; weak for high-concurrency multi-user writes. • Looser, per-value typing rather than MySQL/PostgreSQL's strict per-column typing.
Database-Specific Capabilities
Each engine has SQL extensions the others don't share. A common example is 'upsert' — insert a row, but update it instead if a conflicting unique/primary key already exists. MySQL/MariaDB spell this INSERT ... ON DUPLICATE KEY UPDATE .... PostgreSQL and modern SQLite instead use the SQL-standard-inspired INSERT ... ON CONFLICT (column) DO UPDATE SET .... Both accomplish the same goal, but the syntax is not interchangeable — code written for one will simply error on the other.
Other examples: PostgreSQL's RETURNING clause lets an INSERT/UPDATE/DELETE hand back the affected row's data in the same statement (INSERT INTO users (...) VALUES (...) RETURNING id), which MySQL doesn't support at all (you'd call lastInsertId() afterward instead). PostgreSQL's JSONB type supports rich indexed queries into JSON documents that MySQL's JSON type only partially matches. MySQL has historically been more forgiving about things like inserting a slightly-too-long string (silently truncating in older default modes) where PostgreSQL would reject it outright — another reason Postgres's strictness tends to catch bugs earlier.
• Upsert: MySQL uses INSERT ... ON DUPLICATE KEY UPDATE; PostgreSQL/SQLite use INSERT ... ON CONFLICT (col) DO UPDATE SET. • PostgreSQL's RETURNING clause returns affected row data from INSERT/UPDATE/DELETE directly; MySQL has no equivalent (use lastInsertId()). • PostgreSQL's JSONB supports richer indexed JSON querying than MySQL's JSON type. • MySQL has historically been more lenient about invalid/oversized data than PostgreSQL's stricter defaults.
Portability Considerations
If your application genuinely needs to support more than one database engine (a self-hosted open-source product that lets users pick MySQL or PostgreSQL, for instance), the practical approach is to write plain, standard SQL wherever possible — basic SELECT/INSERT/UPDATE/DELETE, JOIN, GROUP BY, and standard functions work identically across engines — and isolate anything database-specific (upserts, JSON querying, full-text search) behind a repository or data-access layer with one implementation per supported engine. That way the rest of the application calls $repository->upsertUser(...) without caring which SQL dialect is underneath.
For the much more common case — an application that will only ever run against one database in production — don't over-engineer for portability you'll never use. It's completely reasonable to lean on MySQL-specific or PostgreSQL-specific features when they genuinely make the code simpler or faster; premature abstraction to support a database you'll never actually switch to just adds complexity for no benefit. The pragmatic rule: know which engine you're targeting, use its features confidently, and only build an abstraction layer when there's a real, current requirement to support more than one.
Whichever engine (or engines) you target, PDO's own API — prepare(), execute(), fetch(), transactions — stays the same across all of them; the differences that need porting live entirely in the SQL text itself and in a handful of connection-setup details, not in how you call PDO.
• Standard SQL (basic SELECT/INSERT/UPDATE/DELETE/JOIN/GROUP BY) is portable across MySQL/PostgreSQL/SQLite. • Isolate vendor-specific SQL (upserts, JSON querying) behind a repository/data-access layer if true multi-database support is required. • Don't build portability abstractions for databases you'll never actually switch to. • PDO's own API is uniform across engines; only the SQL dialect and connection details vary.
Code Examples
Only the DSN (and, for SQLite, the absence of real credentials) changes between the three connections — every other line of PDO code, and every prepare()/execute()/fetch() call made afterward, looks identical regardless of which engine is behind it.
new PDO('sqlite::memory:', options: $options);
Both statements do the same thing — insert a fresh view-count row, or increment the existing one if page_id already has a row — but the syntax is engine-specific and not interchangeable; code written for one will raise a syntax error on the other.
Common mistake: Copy-pasting an ON DUPLICATE KEY UPDATE query into a PostgreSQL codebase (or vice versa) fails outright — these two upsert syntaxes are not portable and must be written per engine.
The vendor-specific SQL is confined to one method on one class per engine, behind a shared interface. Anything calling trackPageView() only depends on the PageViewRepository interface, so swapping the database engine means swapping which implementation is wired in, not rewriting application logic.
interface PageViewRepository { public function recordView(int $pageId): void; }
<?php
declare(strict_types=1);
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
];
// MySQL / MariaDB
$mysql = new PDO(
'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4',
'shop_user',
'secret',
$options
);
// PostgreSQL
$postgres = new PDO(
'pgsql:host=127.0.0.1;port=5432;dbname=shop',
'shop_user',
'secret',
$options
);
// SQLite — file-based, no server, no username/password needed
$sqlite = new PDO('sqlite:/var/data/shop.sqlite', options: $options);
// An in-memory SQLite database — perfect for a fast, isolated test suite
$testDb = new PDO('sqlite::memory:', options: $options);-- MySQL / MariaDB
INSERT INTO page_views (page_id, views)
VALUES (42, 1)
ON DUPLICATE KEY UPDATE views = views + 1;
-- PostgreSQL / modern SQLite
INSERT INTO page_views (page_id, views)
VALUES (42, 1)
ON CONFLICT (page_id) DO UPDATE SET views = page_views.views + 1;<?php
declare(strict_types=1);
interface PageViewRepository
{
public function recordView(int $pageId): void;
}
final class MySqlPageViewRepository implements PageViewRepository
{
public function __construct(private PDO $pdo) {}
public function recordView(int $pageId): void
{
$this->pdo->prepare(
'INSERT INTO page_views (page_id, views) VALUES (:id, 1)
ON DUPLICATE KEY UPDATE views = views + 1'
)->execute(['id' => $pageId]);
}
}
final class PostgresPageViewRepository implements PageViewRepository
{
public function __construct(private PDO $pdo) {}
public function recordView(int $pageId): void
{
$this->pdo->prepare(
'INSERT INTO page_views (page_id, views) VALUES (:id, 1)
ON CONFLICT (page_id) DO UPDATE SET views = page_views.views + 1'
)->execute(['id' => $pageId]);
}
}
// Calling code never needs to know which engine is underneath:
function trackPageView(PageViewRepository $repo, int $pageId): void
{
$repo->recordView($pageId);
}DSN Shapes Side by Side
The connection string and a handful of SQL extensions vary by engine; the PDO calling code around them does not.
MySQL/MariaDB mysql:host=localhost;port=3306;dbname=shop;charset=utf8mb4
PostgreSQL pgsql:host=localhost;port=5432;dbname=shop
SQLite (file) sqlite:/var/data/shop.sqlite
SQLite (memory) sqlite::memory:
new PDO($dsn, $user, $pass, $options)
|
+-------------------+
| Same PDO API: |
| prepare/execute |
| fetch/fetchAll |
| beginTransaction |
+-------------------+
Only the DSN + SQL dialect differ per engine.Important Rules
MySQL/MariaDB share the same PDO driver (mysql:) and are largely SQL-compatible with each other.
PostgreSQL is stricter about types and offers richer native JSON/array/window-function support.
SQLite is a serverless, single-file database — great for tests/local dev/embedded use, weak for high-concurrency production writes.
Plain, standard SQL (basic CRUD, JOIN, GROUP BY) is portable across all three engines.
Isolate any vendor-specific SQL (upserts, JSON querying, RETURNING) behind a repository layer only when real multi-database support is required.
Where This Shows Up
A typical PHP project runs SQLite (often in-memory) for its automated test suite because it's fast and needs zero setup, while running MySQL or PostgreSQL in staging and production for real concurrent traffic — Laravel's default testing config does exactly this. Meanwhile, an open-source project distributed to many different users (a CMS, a forum package) often abstracts its SQL behind a query builder or repository layer specifically so it can support whichever database its users already have running.
Writing INSERT ... ON DUPLICATE KEY UPDATE ... and assuming it will also work if the app is later deployed against PostgreSQL.
Either commit to MySQL's syntax knowingly, or isolate the upsert behind a repository interface with a PostgreSQL-specific implementation using ON CONFLICT ... DO UPDATE.
Upsert syntax is not portable between MySQL and PostgreSQL; code that works perfectly on one throws a syntax error on the other, and this is exactly the kind of vendor-specific SQL that belongs behind an abstraction if multi-engine support is a real requirement.
Using a single shared SQLite file as the database for a busy production web app with many simultaneous users writing to it.
Use SQLite for local development, tests, or low-concurrency/embedded use; use MySQL/MariaDB or PostgreSQL for a production app with meaningful concurrent write load.
SQLite serializes writes at the file level and isn't designed for many concurrent writers; under real production write concurrency it becomes a bottleneck (or a source of 'database is locked' errors) in a way a client-server database like MySQL or PostgreSQL is built to handle.
What makes SQLite fundamentally different from MySQL and PostgreSQL? A. SQLite doesn't support SQL syntax at all B. SQLite has no server process — the database is a single file read/written directly by the process, rather than a client-server system C. SQLite cannot be used with PDO D. SQLite only works on Windows
B. SQLite has no server process — the database is a single file read/written directly by the process, rather than a client-server system — MySQL and PostgreSQL are client-server databases with a separate server process you connect to over a network; SQLite is serverless — the whole database lives in one file that your process reads and writes directly.
Which pair correctly matches an engine to its upsert syntax? A. MySQL uses ON CONFLICT DO UPDATE; PostgreSQL uses ON DUPLICATE KEY UPDATE B. MySQL uses ON DUPLICATE KEY UPDATE; PostgreSQL uses ON CONFLICT ... DO UPDATE C. Both use identical ON DUPLICATE KEY UPDATE syntax D. Neither supports upsert; it must be done as a SELECT then INSERT or UPDATE
B. MySQL uses ON DUPLICATE KEY UPDATE; PostgreSQL uses ON CONFLICT ... DO UPDATE — MySQL/MariaDB use INSERT ... ON DUPLICATE KEY UPDATE ...; PostgreSQL (and modern SQLite) use INSERT ... ON CONFLICT (column) DO UPDATE SET ... — the two are not interchangeable.
What is the recommended approach to portability when an application genuinely must support multiple database engines? A. Never use any database-specific feature, ever, on any project B. Write plain standard SQL where possible and isolate vendor-specific SQL behind a repository/data-access layer with one implementation per engine C. Always use SQLite in production since it works everywhere D. Rewrite the entire application separately for each database engine
B. Write plain standard SQL where possible and isolate vendor-specific SQL behind a repository/data-access layer with one implementation per engine — The practical approach is to keep the bulk of the application on portable, standard SQL, and confine anything vendor-specific (upserts, JSON queries, RETURNING) behind an interface with per-engine implementations, so the rest of the app doesn't need to know which database it's talking to.
You're building a small internal tool that only your team will ever use, backed by MySQL in production, with a fast local test suite. Decide: which database do you use for tests, and do you need a repository abstraction layer for portability? Justify both answers.
Use SQLite (in-memory) for the test suite for speed and zero setup, while production stays on MySQL — this is fine because PDO's API is identical either way and the SQL used is simple/standard. No repository abstraction is needed: the app only ever targets one production engine (MySQL), so building a portability layer for databases it will never run against would be unnecessary complexity.
Consider how many database engines this application will ever actually run against in production.