Dialect Support

PDOdb provides unified API across MySQL, MariaDB, PostgreSQL, SQLite, Microsoft SQL Server (MSSQL), and Oracle while handling dialect-specific differences automatically.

Supported Databases

Database	Minimum Version	Native Features
MySQL	5.7+	JSON support, replication
MariaDB	10.3+	JSON support, compatibility mode
PostgreSQL	9.4+	JSONB support, advanced types
SQLite	3.38+	In-memory, file-based
Microsoft SQL Server	2019+	JSON support, MERGE statements, CROSS APPLY
Oracle	12c+	JSON support, MERGE statements, LATERAL JOINs

Automatic Dialect Handling

PDOdb automatically adapts to your database:

use tommyknocker\pdodb\helpers\Db;

// This works identically on all databases
$users = $db->find()
    ->from('users')
    ->where(Db::jsonContains('tags', 'php'))
    ->get();

Generated SQL

The library generates appropriate SQL for each database:

MySQL:

SELECT * FROM users WHERE JSON_CONTAINS(tags, '"php"')

PostgreSQL:

SELECT * FROM users WHERE tags @> '"php"'

SQLite:

SELECT * FROM users WHERE EXISTS (
    SELECT 1 FROM json_each(tags) WHERE value = '"php"'
)

Configuration Differences

Connection Strings

Each database requires different connection parameters:

// MySQL
$db = new PdoDb('mysql', [
    'host' => 'localhost',
    'username' => 'user',
    'password' => 'pass',
    'dbname' => 'mydb',
    'port' => 3306
]);

// PostgreSQL
$db = new PdoDb('pgsql', [
    'host' => 'localhost',
    'username' => 'user',
    'password' => 'pass',
    'dbname' => 'mydb',
    'port' => 5432
]);

// SQLite
$db = new PdoDb('sqlite', [
    'path' => '/path/to/database.sqlite'
]);

// Microsoft SQL Server
$db = new PdoDb('sqlsrv', [
    'host' => 'localhost',
    'username' => 'user',
    'password' => 'pass',
    'dbname' => 'mydb',
    'port' => 1433
]);

// Oracle
$db = new PdoDb('oci', [
    'host' => 'localhost',
    'port' => 1521,
    'username' => 'user',
    'password' => 'pass',
    'dbname' => 'XE', // Service name
    'charset' => 'UTF8'
]);

Data Type Differences

Auto-Increment IDs

// MySQL
$id = $db->find()->table('users')->insert(['name' => 'Alice']);
// Returns: auto-incrementing integer

// PostgreSQL
$id = $db->find()->table('users')->insert(['name' => 'Alice']);
// Returns: SERIAL integer

// SQLite
$id = $db->find()->table('users')->insert(['name' => 'Alice']);
// Returns: INTEGER PRIMARY KEY

// Microsoft SQL Server
$id = $db->find()->table('users')->insert(['name' => 'Alice']);
// Returns: IDENTITY(1,1) integer

// Oracle
$id = $db->find()->table('users')->insert(['name' => 'Alice']);
// Returns: Sequence-generated integer

Timestamps

use tommyknocker\pdodb\helpers\Db;

// NOW() works on all databases
$db->find()->table('users')->update([
    'updated_at' => Db::now()
]);

Generated SQL:

MySQL: NOW()
MariaDB: NOW()
PostgreSQL: CURRENT_TIMESTAMP
SQLite: CURRENT_TIMESTAMP
MSSQL: GETDATE()
Oracle: SYSTIMESTAMP

Boolean Values

$db->find()->table('users')->insert([
    'active' => true  // Automatically converts to 1/0 or TRUE/FALSE
]);

JSON Support

Creating JSON

use tommyknocker\pdodb\helpers\Db;

// Works identically across all databases
$db->find()->table('users')->insert([
    'name' => 'Alice',
    'meta' => Db::jsonObject(['city' => 'NYC', 'age' => 30]),
    'tags' => Db::jsonArray('php', 'mysql', 'docker')
]);

Storage:

MySQL: JSON column (5.7+)
MariaDB: JSON column (10.3+)
PostgreSQL: JSONB column
SQLite: TEXT column with JSON functions
MSSQL: NVARCHAR(MAX) or JSON column (2016+)
Oracle: JSON column (12c+)

Querying JSON

// Find users older than 25 (from JSON)
$users = $db->find()
    ->from('users')
    ->where(Db::jsonPath('meta', ['age'], '>', 25))
    ->get();

SQL Dialects

Identifier Quoting

Automatically handled:

// Table names
$db->find()->from('users');  // No need to quote

// Column names
$db->find()->from('users')->select(['id', 'name']);  // No need to quote

Generated SQL:

MySQL/MariaDB: SELECT `id`, `name` FROM `users`
PostgreSQL: SELECT "id", "name" FROM "users"
SQLite: SELECT "id", "name" FROM "users"
MSSQL: SELECT [id], [name] FROM [users]
Oracle: SELECT "id", "name" FROM "users"

String Concatenation

use tommyknocker\pdodb\helpers\Db;

$users = $db->find()
    ->from('users')
    ->select(['full_name' => Db::concat('first_name', ' ', 'last_name')])
    ->get();

Generated SQL:

MySQL/MariaDB: CONCAT(first_name, ' ', last_name)
PostgreSQL: first_name || ' ' || last_name
SQLite: first_name || ' ' || last_name
MSSQL: first_name + ' ' + last_name
Oracle: first_name || ' ' || last_name

LIMIT and OFFSET

$users = $db->find()
    ->from('users')
    ->limit(10)
    ->offset(20)
    ->get();

Generated SQL:

MySQL/MariaDB: ... LIMIT 10 OFFSET 20
PostgreSQL: ... LIMIT 10 OFFSET 20
SQLite: ... LIMIT 10 OFFSET 20
MSSQL: ... ORDER BY (SELECT NULL) OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Oracle: ... OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Note: SQLite requires LIMIT when using OFFSET. MSSQL requires ORDER BY when using OFFSET/FETCH. Oracle 12c+ uses OFFSET/FETCH syntax.

UPSERT Operations

PDOdb provides unified UPSERT API:

use tommyknocker\pdodb\helpers\Db;

$db->find()->table('users')->onDuplicate([
    'age' => Db::inc(),
    'updated_at' => Db::now()
])->insert([
    'email' => 'alice@example.com',
    'name' => 'Alice',
    'age' => 30
]);

Generated SQL:

MySQL:

INSERT INTO users (email, name, age) 
VALUES (:email, :name, :age)
ON DUPLICATE KEY UPDATE 
    age = age + 1, 
    updated_at = NOW()

PostgreSQL/SQLite:

INSERT INTO users (email, name, age) 
VALUES (:email, :name, :age)
ON CONFLICT (email) DO UPDATE SET 
    age = users.age + 1, 
    updated_at = CURRENT_TIMESTAMP

MSSQL:

MERGE users AS target
USING (VALUES (:email, :name, :age)) AS source (email, name, age)
ON target.email = source.email
WHEN MATCHED THEN UPDATE SET 
    age = target.age + 1,
    updated_at = GETDATE()
WHEN NOT MATCHED THEN INSERT (email, name, age) 
    VALUES (source.email, source.name, source.age);

Oracle:

MERGE INTO users target
USING (SELECT :email AS email, :name AS name, :age AS age FROM DUAL) source
ON (target.email = source.email)
WHEN MATCHED THEN UPDATE SET 
    age = target.age + 1,
    updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN INSERT (email, name, age) 
    VALUES (source.email, source.name, source.age)

REPLACE Operations

$db->find()->table('users')->replace([
    'id' => 1,
    'name' => 'Alice Updated'
]);

Generated SQL:

MySQL:

REPLACE INTO users (id, name) VALUES (:id, :name)

PostgreSQL/SQLite:

INSERT INTO users (id, name) VALUES (:id, :name)
ON CONFLICT (id) DO UPDATE SET name = :name

MSSQL:

MERGE users AS target
USING (VALUES (:id, :name)) AS source (id, name)
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name);

Oracle:

MERGE INTO users target
USING (SELECT :id AS id, :name AS name FROM DUAL) source
ON (target.id = source.id)
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name)

TRUNCATE Operations

$db->find()->table('users')->truncate();

Generated SQL:

MySQL/PostgreSQL:

TRUNCATE TABLE users

SQLite:

DELETE FROM users;
DELETE FROM sqlite_sequence WHERE name = 'users';

MSSQL:

TRUNCATE TABLE users

Oracle:

TRUNCATE TABLE users

Table Locking

$db->lock(['users', 'orders'])->setLockMethod('WRITE');
// Perform exclusive operations
$db->unlock();

Generated SQL:

MySQL:

LOCK TABLES users WRITE, orders WRITE
UNLOCK TABLES

PostgreSQL:

LOCK TABLE users, orders IN EXCLUSIVE MODE

SQLite:

BEGIN IMMEDIATE
COMMIT

MSSQL:

BEGIN TRANSACTION
-- Operations here
COMMIT TRANSACTION

Oracle:

LOCK TABLE users, orders IN EXCLUSIVE MODE
-- Locks released on COMMIT/ROLLBACK

Bulk Loading

CSV Loader

$db->find()->table('users')->loadCsv('/path/to/file.csv');

Implementation:

MySQL/MariaDB: LOAD DATA LOCAL INFILE
PostgreSQL: COPY FROM
SQLite: Row-by-row inserts in transaction
MSSQL: BULK INSERT or row-by-row inserts
Oracle: SQL*Loader or row-by-row inserts

XML Loader

$db->find()->table('users')->loadXml('/path/to/file.xml');

NULL Handling

use tommyknocker\pdodb\helpers\Db;

// Check for NULL
$users = $db->find()
    ->from('users')
    ->where(Db::isNull('deleted_at'))
    ->get();

// Handle NULL
$result = $db->find()
    ->from('users')
    ->select(['name' => Db::ifNull('username', 'Anonymous')])
    ->get();

Performance Considerations

Indexes

MySQL:

CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_meta_age ON users((CAST(meta->>'age' AS UNSIGNED)));

PostgreSQL:

CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_meta_age ON users((meta->>'age'));

SQLite:

CREATE INDEX idx_email ON users(email);

EXPLAIN

// Get query execution plan
$plan = $db->find()
    ->from('users')
    ->where('age', 25, '>')
    ->explain();

Returns dialect-specific execution plans.

Feature Compatibility Matrix

Feature	MySQL	MariaDB	PostgreSQL	SQLite	MSSQL	Oracle
Core Features
Prepared statements	✅	✅	✅	✅	✅	✅
Transactions	✅	✅	✅	✅	✅	✅
Savepoints	✅	✅	✅	✅	❌	✅
Data Types
JSON support	✅	✅	✅ (JSONB)	✅ (if JSON1)	✅	✅ (12c+)
Boolean type	TINYINT(1)	TINYINT(1)	BOOLEAN	INTEGER	BIT	NUMBER(1)
Auto-increment	AUTO_INCREMENT	AUTO_INCREMENT	SERIAL	AUTOINCREMENT	IDENTITY(1,1)	SEQUENCE
Operations
UPSERT	✅	✅	✅	✅	✅ (MERGE)	✅ (MERGE)
Bulk loading	✅	✅	✅	✅ (emulated)	✅	✅
Table locking	✅	✅	✅	✅ (BEGIN IMMEDIATE)	✅	✅
Advanced Features
Schema support	❌	❌	✅	❌	✅	✅
Table prefixes	✅	✅	✅	✅	✅	✅
REPEAT/REVERSE/LPAD/RPAD	✅	✅	✅ (emulated)	✅ (emulated)	✅	✅
MERGE statements	❌ (emulated)	❌ (emulated)	✅	❌ (emulated)	✅	✅
LATERAL JOINs	✅	✅	✅	❌	✅ (CROSS APPLY)	✅ (12c+)
Window functions	✅ (8.0+)	✅ (10.2+)	✅	✅ (3.25+)	✅	✅
Recursive CTEs	✅	✅	✅	✅	✅	✅
Full-text search	✅	✅	✅	✅ (FTS5)	✅	✅ (Oracle Text)
String Functions
LENGTH()	✅	✅	✅	✅	LEN()	✅
SUBSTRING()	✅	✅	✅	SUBSTR()	✅	SUBSTR()
CONCAT()	✅	✅	✅ (		)	✅ (
Date Functions
NOW()	✅	✅	CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	GETDATE()	SYSTIMESTAMP
DATE()	✅	✅	✅	✅	CAST()	TRUNC()
LIMIT/OFFSET
LIMIT/OFFSET	✅	✅	✅	✅	OFFSET/FETCH	OFFSET/FETCH (12c+)

Migration Between Databases

Schema Differences

MySQL:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,           -- SERIAL in PostgreSQL, INTEGER AUTOINCREMENT in SQLite
    name VARCHAR(255),                           -- TEXT in SQLite
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- DATETIME in SQLite
);

PostgreSQL:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,                       -- INT AUTO_INCREMENT in MySQL, INTEGER AUTOINCREMENT in SQLite
    name VARCHAR(255),                           -- TEXT in SQLite
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- DATETIME in SQLite
);

SQLite:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,        -- INT AUTO_INCREMENT in MySQL, SERIAL in PostgreSQL
    name TEXT,                                   -- VARCHAR(255) in MySQL/PostgreSQL
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP  -- TIMESTAMP in MySQL/PostgreSQL
);

Data Types Comparison

Type	MySQL	MariaDB	PostgreSQL	SQLite	MSSQL
String	VARCHAR(255)	VARCHAR(255)	VARCHAR(255)	TEXT	NVARCHAR(255)
Integer	INT	INT	INTEGER	INTEGER	INT
Big Integer	BIGINT	BIGINT	BIGINT	INTEGER	BIGINT
Decimal	DECIMAL(10,2)	DECIMAL(10,2)	NUMERIC(10,2)	REAL	DECIMAL(10,2)
Date/Time	DATETIME	DATETIME	TIMESTAMP	DATETIME	DATETIME
Timestamp	TIMESTAMP	TIMESTAMP	TIMESTAMP	DATETIME	DATETIME
JSON	JSON	JSON	JSONB	TEXT	NVARCHAR(MAX) or JSON
Boolean	TINYINT(1)	TINYINT(1)	BOOLEAN	INTEGER	BIT
Text	TEXT	TEXT	TEXT	TEXT	NTEXT or NVARCHAR(MAX)
Binary	BLOB	BLOB	BYTEA	BLOB	VARBINARY(MAX)

Next Steps

Connection Management - Learn about connections
Query Builder Basics - Fluent API overview
SELECT Operations - SELECT queries

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Dialect Support

Supported Databases

Automatic Dialect Handling

Generated SQL

Configuration Differences

Connection Strings

Data Type Differences

Auto-Increment IDs

Timestamps

Boolean Values

JSON Support

Creating JSON

Querying JSON

SQL Dialects

Identifier Quoting

String Concatenation

LIMIT and OFFSET

UPSERT Operations

REPLACE Operations

TRUNCATE Operations

Table Locking

Bulk Loading

CSV Loader

XML Loader

NULL Handling

Performance Considerations

Indexes

EXPLAIN

Feature Compatibility Matrix

Migration Between Databases

Schema Differences

Data Types Comparison

Next Steps

FilesExpand file tree

05-dialect-support.md

Latest commit

History

05-dialect-support.md

File metadata and controls

Dialect Support

Supported Databases

Automatic Dialect Handling

Generated SQL

Configuration Differences

Connection Strings

Data Type Differences

Auto-Increment IDs

Timestamps

Boolean Values

JSON Support

Creating JSON

Querying JSON

SQL Dialects

Identifier Quoting

String Concatenation

LIMIT and OFFSET

UPSERT Operations

REPLACE Operations

TRUNCATE Operations

Table Locking

Bulk Loading

CSV Loader

XML Loader

NULL Handling

Performance Considerations

Indexes

EXPLAIN

Feature Compatibility Matrix

Migration Between Databases

Schema Differences

Data Types Comparison

Next Steps