What is SQL Abstraction?

SQL Abstraction is a programming approach that allows developers to build SQL queries using PHP objects, instead of writing raw SQL strings manually.

Laminas provides the Laminas\Db\Sql component to abstract SQL queries in a safe, readable, and database-independent way.

Why Use SQL Abstraction?

• ✅ Clean and structured code
• ✅ Protects against SQL injection
• ✅ Easier to build dynamic and complex queries
• ✅ Enables reuse and readability
• ✅ Simplifies switching database systems (MySQL, PostgreSQL, etc.)

Basic SELECT Query with WHERE

Raw SQL:

SELECT * FROM users WHERE id = 1;

Laminas Db:

$sql = new Sql($this->dbAdapter);
$select = $sql->select();
$select->from('users')
       ->where(['id' => 1]);
$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

WHERE, AND, OR, NOT

Raw SQL:

SELECT * FROM users WHERE age > 18 AND (status = 'active' OR NOT verified);

Laminas Db:

$sql = new Sql($this->dbAdapter);
$select = $sql->select();
$select->from('users');
$select->where
       ->greaterThan('age', 18)
       ->AND
       ->nest()
           ->equalTo('status', 'active')
           ->or->literal('NOT verified')
       ->unnest();
$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

INSERT

Raw SQL:

INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

Laminas Db:

$sql = new Sql($this->dbAdapter);
$insert = $sql->insert('users');
$insert->values([
    'name'  => 'John',
    'email' => 'john@example.com',
]);

$statement = $sql->prepareStatementForSqlObject($insert);
$result    = $statement->execute();

UPDATE

Raw SQL:

UPDATE users SET name = 'Johnny' WHERE id = 5;

Laminas Db:

$sql = new Sql($this->dbAdapter);
$update = $sql->update('users');
$update->set(['name' => 'Johnny'])
       ->where(['id' => 5]);

$statement = $sql->prepareStatementForSqlObject($update);
$result    = $statement->execute();

DELETE

Raw SQL:

DELETE FROM users WHERE id = 5;

Laminas Db:

$sql = new Sql($this->dbAdapter);
$delete = $sql->delete('users');
$delete->where(['id' => 5]);

$statement = $sql->prepareStatementForSqlObject($delete);
$result    = $statement->execute();

INNER JOIN

Raw SQL:

SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;

Laminas Db:

$sql = new Sql($this->dbAdapter);
$select = $sql->select();
$select->from(['u' => 'users'])
       ->columns(['name'])
       ->join(['o' => 'orders'], 'u.id = o.user_id', ['total'], $select::JOIN_INNER);
$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

LEFT JOIN

Raw SQL:

SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id;

Laminas Db:

$sql = new Sql($this->dbAdapter);
$select = $sql->select();
$select->from(['u' => 'users'])
       ->columns(['name'])
       ->join(['o' => 'orders'], 'u.id = o.user_id', ['total'], $select::JOIN_LEFT);
$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

RIght JOIN

Raw SQL:

SELECT u.name, o.total FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

Laminas Db:

$sql = new Sql($this->dbAdapter);
$select = $sql->select();
$select->from(['u' => 'users'])
       ->columns(['name'])
       ->join(['o' => 'orders'], 'u.id = o.user_id', ['total'], $select::JOIN_RIGHT);
$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

GROUP BY / HAVING

Raw SQL:

SELECT status, COUNT(*) as total FROM users GROUP BY status HAVING total > 5;

Laminas Db:

$sql = new Sql($this->dbAdapter);
$select = $sql->select();
$select->from('users')
       ->columns(['status', 'total' => new Expression('COUNT(*)')])
       ->group('status')
       ->having(['total > ?' => 5]);
$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

ORDER BY / LIMIT / OFFSET

Raw SQL:

SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;

Laminas Db:

$sql = new Sql($this->dbAdapter);
$select = $sql->select();
$select->from('users')
       ->order('created_at DESC')
       ->limit(10)
       ->offset(20);
$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

 Expression / Functions (COUNT, AVG, SUM, NOW, etc)

Raw SQL:

SELECT COUNT(*) AS total FROM users;

Laminas Db:

use Laminas\Db\Sql\Expression;

$sql = new Sql($this->dbAdapter);
$select = $sql->select();
$select->from('users');
$select->columns([
    'total' => new Expression('COUNT(*)')
]);
$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

Custom SQL

$statement = $adapter->createStatement('SELECT * FROM users WHERE email LIKE ?', ['%gmail.com']);
$result  = $statement->execute();