Database Notes

Database Notes

Sumire supports SQLite, MySQL, and PostgreSQL through PDO.

SQLite

Use pdo_sqlite.

$database = Database::connect(new PDO('sqlite:' . __DIR__ . '/database.sqlite'));

Generated IDs are read with PDO::lastInsertId().

Example table:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    active INTEGER NOT NULL
);

SQLite does not have a strict boolean storage type. Store booleans as 0 or 1.

MySQL

Use pdo_mysql.

$database = Database::connect(new PDO(
    'mysql:host=127.0.0.1;port=3306;dbname=sumire;charset=utf8mb4',
    'root',
    'secret',
));

Generated IDs are read with PDO::lastInsertId().

Example table:

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    active BOOLEAN NOT NULL
);

MySQL identifiers are quoted with backticks.

PostgreSQL

Use pdo_pgsql.

$database = Database::connect(new PDO(
    'pgsql:host=127.0.0.1;port=5432;dbname=sumire',
    'postgres',
    'secret',
));

Generated IDs are read with INSERT ... RETURNING.

Example table:

CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    active BOOLEAN NOT NULL
);

PostgreSQL identifiers are quoted with double quotes.

Boolean Hydration

Different drivers return booleans differently. Sumire casts common boolean values when hydrating typed bool properties.

Values treated as true:

• 1
• true
• t
• on
• yes

Values treated as false:

• 0
• false
• f
• off
• no
• empty string

Identifier Quoting

Sumire quotes mapped table and column names before building SQL.

Driver	Quote style
MySQL	`identifier`
SQL Server	[identifier]
SQLite/PostgreSQL/default	"identifier"

Generated ID Behavior

Generated IDs are assigned back to the entity after insert.

$user = new User('Ada Lovelace', 'ada@example.com');

$database->persist($user);

echo $user->id();

For this to work, the ID property should allow null before insert.