SQL

The SQL client is structured by 2 concepts: immutable Query objects as input and Sequences of immutable Rows as output.

Usage

use Formal\AccessLayer\{
    Query\SQL,
    Row,
};
use Innmind\Url\Url;

$sql = $os
    ->remote()
    ->sql(Url::of('mysql://user:password@127.0.0.1:3306/database_name'));

$sql(SQL::of('SELECT * FROM users'))->foreach(
    static fn(Row $row) => var_dump($row->toArray()),
);

Prepared queries

If you need to inject data in your queries you should use parameters.

Do not use string concatenation as it can lead to SQL injection.

use Formal\AccessLayer\Query\Parameter;

$query = SQL::of('INSERT INTO users VALUES (:id, :username)')
    ->with(Parameter::named('id', 'some-id'))
    ->with(Parameter::named('username', 'some-username'));

$sql($query);

Here named parameters are used via the format :parameter_name with the named specified again in Paramater::named().

You can also bind parameters by indices via the format ? and then Parameter::of('value'). This way you don't duplicate strings, but the order you add the parameters via the with method matters.

Query builder

The SQL class allows you to specify the exact query you want to execute. But if you want to generate queries programmatically you should use the other classes from the Formal\AccessLayer\Query\ namespace, such as Select or Insert.

SelectInsertetc...

use Formal\AccessLayer\{
    Query\Select,
    Table\Name,
    Table\Column,
};

$select = Select::from(Name::of('users'))->columns( #(1)
    Column\Name::of('id'),
    Column\Name::of('username'),
);

$sql($select);

1. If you don't specify the columns it will retrieve them all by default.

use Formal\AccessLayer\{
    Query\Insert,
    Row,
};

$insert = Insert::into(
    Name::of('users'),
    Row::of([
        'id' => 'id-1',
        'username' => 'john',
    ]),
    Row::of([
        'id' => 'id-2',
        'username' => 'jane',
    ]),
    // etc...
);

$sql($insert);

Other query builders include:

• CreateTable
• DropTable
• Delete
• Update

Filtering

When selecting from a table you can restrict the rows by specifying them manually in the SQL class. But if you need to programmatically construct the where clause you can use the specification pattern via the Select class.

For example let's say you want to retrieve all users whose username starts with a. The first step is to create a specification:

use Innmind\Specification\{
    Comparator,
    Composable,
    Sign,
};

/** @psalm-immutable */
final class Username implements Comparator
{
    use Composable;

    private string $value;

    private function __construct(string $value)
    {
        $this->value = $value;
    }

    /** @psalm-pure */
    public static function startsWith(string $value): self
    {
        return new self($value);
    }

    public function property(): string
    {
        return 'username'; #(1)
    }

    public function sign(): Sign
    {
        return Sign::startsWith;
    }

    public function value(): string
    {
        return $this->value;
    }
}

1. This is the column name.

And then you use it like this:

$select = Select::from(Name::of('users'))
    ->where(Username::startsWith('a'));

$sql($select);

The big advantage of specifications is that you can easily compose them. For example if you want users starting with a or b you'd do Username::startsWith('a')->or(Username::startsWith('b')); and if you want all except these ones you can chain a ->not() to negate the whole condition.

Another advantage is that this composition forces you to think about precedence of your conditions to reduce the risk of implicit behaviours.

Laziness

All the queries you've seen so far return deferred Sequences meaning that the queries are executed immediately but the returned rows will be loaded (and kept) in memory when you use the returned sequence.

For most queries this is fine. But if you want to select a large amount of data that may not fit in memory you should use lazy queries.

To do so instead of using SQL::of()/Select::from() use SQL::onDemand()/Select::onDemand().

$select = Select::onDemand(Name::of('users'));

$sql($select)->foreach(
    static fn(Row $row) => doStuff($row),
);

With this even if the result contains a million rows there'll only be one at a time in memory.

Info

However this means that if you call foreach twice it will run the query twice. The returned rows may change between the 2 calls, if you need the results to be the same you can't use lazy queries!

Transactions

To run queries inside a transaction you need to run the corresponding sql queries like this:

use Formal\AccessLayer\Query\{
    StartTransaction,
    Commit,
    Rollback,
};

try {
    $sql(new StartTransaction);

    // run your queries here

    $sql(new Commit);
} catch (\Throwable $e) {
    $sql(new Rollback);

    throw $e;
}