README

Generates the table and record classes for your PostgreSQL tables, for use with version 4.1 of the programster/pgsql-objects package.

Point it at your database and you get a {Name}Table.php and a {Name}Record.php file for each table, already filled in from the database schema: the ID column, how IDs are generated, which columns allow NULL or have defaults, and a getter and setter for every column.

Requirements

PHP 8.2 or later, with the pgsql extension
A PostgreSQL database

Installation

Your project needs programster/pgsql-objects 4.1 to use the generated classes, and the generator is only needed while developing, so install them with:

composer require programster/pgsql-objects:4.1.*
composer require --dev programster/orm-generator

The generator requires pgsql-objects 4.1 itself, so composer will make sure the two versions match. Require pgsql-objects in your project as well though, as otherwise it would only be installed in development, and the generated classes wouldn't work in production.

Usage

With the package installed in your project, call the generator from a script in your project's scripts, tools or commands directory, such as scripts/generate-orm.php:

<?php

use App\ConnectionHandler;
use Programster\OrmGenerator\PgSqlGenerator;

require_once(__DIR__ . '/../vendor/autoload.php');

$generator = new PgSqlGenerator(
    connectionFetcher: ConnectionHandler::getDb(...),
    outputDirectory: __DIR__ . '/../models/orm',
    namespace: 'App\Models',
);

$report = $generator->run();
print $report->getSummary();
exit($report->hasWarnings() ? 1 : 0);

connectionFetcher is the method your application already uses to get its database connection, passed with PHP's first-class callable syntax (the (...) on the end). The generator calls it to connect to the database, and the generated table classes call it in their getDb() methods, so you only need to tell the generator how your application connects once. It must be a public static method that takes no arguments and declares a return type of Programster\PgsqlLib\PgSqlConnection, such as:

namespace App;

use Programster\PgsqlLib\PgSqlConnection;

class ConnectionHandler
{
    public static function getDb() : PgSqlConnection
    {
        static $connection = null;
        $connection ??= PgSqlConnection::create(DB_HOST, DB_NAME, DB_USER, DB_PASSWORD, DB_PORT);
        return $connection;
    }
}

Be sure to give the generator the path to the directory your ORM classes live in, such as models/orm, so the generated files land straight in your project. Then run the script whenever you add or change tables:

php scripts/generate-orm.php

The report

The generator doesn't print anything, or raise any PHP warnings or notices, so it behaves the same wherever you run it from, including inside frameworks that turn warnings into exceptions. Instead, run() returns a GenerationReport saying what happened, for your script to print or act on:

getSummary() returns a human-readable summary, such as:

2 files: 0 created, 1 updated, 0 unchanged, 0 kept as edited. 1 table skipped.
Warning: Skipped the audit_log table, as it doesn't have a single-column primary key.
Warning: Left models/orm/TicketRecord.php as it is, as it has been edited, but it is out of date with the database. Update it by hand, or delete it to have it generated again.

hasWarnings() and getWarnings() cover anything that needs looking at: skipped tables, and existing files that may not match the database. Exiting with a non-zero code when there are warnings, as in the example above, lets you fail a CI pipeline on them.
getFileResults() and getFileResultsWithOutcome() return a FileResult for each file, with its path and its FileOutcomeEnum outcome.
getSkippedTables() returns a SkippedTable for each skipped table, with its name and the reason.

Options

Parameter	Default	Description
`connectionFetcher`	(required)	The public static method that returns your application's `PgSqlConnection`, e.g. `ConnectionHandler::getDb(...)`.
`outputDirectory`	(required)	An existing directory to write the generated files to.
`namespace`	`''`	The namespace to put the generated classes in. Leave it empty for the global namespace.

What gets generated

Table classes

Each table class is filled in from the table's primary key and columns:

Primary key column	Base class	Where new IDs come from
`uuid`	`AbstractUuidTable`	Generated in code (v7 UUIDs). If the column also has a default, such as `gen_random_uuid()`, `canDatabaseGenerateId()` returns true as well.
`SERIAL`, `IDENTITY`, or any other column with a default	`AbstractTable`	Generated by the database when the row is saved.
Any other column, e.g. a `char(2)` country code	`AbstractTable`	Must be provided when creating the row.

On top of that:

If the primary key column isn't called id, a getIdColumnName() override is added.
getFieldsThatAllowNull() lists the nullable columns.
getFieldsThatHaveDefaults() lists the columns the database fills in if no value is given, but that still accept one: those with defaults (including SERIAL columns), and GENERATED BY DEFAULT AS IDENTITY columns.
getVirtualFields() lists the columns whose values PostgreSQL always computes itself, and won't accept a value for: generated columns (GENERATED ALWAYS AS (...), both STORED and VIRTUAL), and GENERATED ALWAYS AS IDENTITY columns that aren't the primary key. pgsql-objects never sends these in inserts or updates, and reads their values back from the database after create(), save() and update(). This method is always generated, even when it returns [], as pgsql-objects 5.0 will require every table to implement it.
numeric columns are listed in getFieldsToKeepAsString(), so their values stay exact strings rather than being converted to floats, which could lose precision. Remove them from that list if you would rather have floats.

For example, this table:

CREATE TABLE ticket (
    id SERIAL PRIMARY KEY,
    title text NOT NULL,
    status text NOT NULL DEFAULT 'open',
    price numeric(20,10),
    is_open boolean NOT NULL
);

generates this table class (with the namespace option set to App\Models):

<?php

/*
 * This file was generated by programster/orm-generator. If you edit it, the generator will stop updating
 * it, so that your changes aren't lost.
 * @generated-hash 8006d6472af9a157c07a99e9d251c8732bba26f1c5ea4242fd0da1790385f41c
 */

declare(strict_types = 1);

namespace App\Models;

use App\ConnectionHandler;
use Programster\PgsqlLib\PgSqlConnection;
use Programster\PgsqlObjects\AbstractTable;
use Programster\PgsqlObjects\Exceptions\ExceptionIdGeneratedInDatabase;


class TicketTable extends AbstractTable
{
    public function getDb() : PgSqlConnection
    {
        return ConnectionHandler::getDb();
    }


    public function getObjectClassName() : string
    {
        return TicketRecord::class;
    }


    public function getTableName() : string
    {
        return 'ticket';
    }


    public function getFieldsThatAllowNull() : array
    {
        return ['price'];
    }


    public function getFieldsThatHaveDefaults() : array
    {
        return ['status'];
    }


    public function getVirtualFields() : array
    {
        return [];
    }


    public function getFieldsToKeepAsString() : array
    {
        return ['price'];
    }


    public function generateId() : mixed
    {
        throw new ExceptionIdGeneratedInDatabase("TicketTable IDs are generated by the database.");
    }


    public function canCodeGenerateId() : bool
    {
        return false;
    }


    public function canDatabaseGenerateId() : bool
    {
        return true;
    }
}

Record classes

Each record class has a property, a getter and a setter for every column except the ID column, which the AbstractTableRowObject base class manages and exposes through getId(). Column names are converted to camelCase, so the is_open column above gets getIsOpen() and setIsOpen().

Virtual fields (see getVirtualFields() above) get a getter but no setter, as the database always computes their values, so anything set would be ignored.

Naming

Table names are converted to PascalCase for the class names, and column names to camelCase for the properties, getters and setters. Names don't have to be valid PHP, so the generator adjusts them where needed:

Database name	PHP name
`user_log` table	`UserLogTable` and `UserLogRecord`
`first_name`, or `"first name"`, `"first-name"`, etc. column	`$m_firstName`, `getFirstName()`, `setFirstName()`
`"price.gbp"` column	`$m_priceGbp`, `getPriceGbp()`, `setPriceGbp()`
A column that clashes with the `AbstractTableRowObject` base class, e.g. `id` (when it isn't the primary key) or `table_handler`	`Column` is added on the end, e.g. `$m_idColumn`, `getIdColumn()`, `setIdColumn()`

Characters that PHP doesn't allow in names, such as spaces, hyphens and dots, are treated as word breaks. The records still use the real column names when talking to the database, e.g. in getArrayForm() and in queries.

A table is skipped with a warning explaining why, rather than generating a file that would crash, if:

its class name would start with a digit (e.g. 2fa_codes), or clash with a class that the generated files import (e.g. abstract would make AbstractTable).
it would have the same class name as another table (e.g. user_log and "UserLog"), in which case both are skipped.
two of its columns would end up with the same name (e.g. first_name and "firstName", or first_name and firstname, as PHP method names aren't case-sensitive).

To generate a skipped table, rename the table or columns in the database, or write its classes by hand.

Once the generated files are in your project, you use them as you would any other pgsql-objects classes:

use App\Models\TicketTable;

$ticket = TicketTable::getInstance()->create(['title' => 'Printer is on fire', 'is_open' => true]);
$ticket->getId();     // e.g. 1, as generated by the database
$ticket->getStatus(); // 'open', the column's default

$ticket->setTitle('Printer was on fire');
$ticket->save();

Running the generator again

Run the generator again whenever you add or change tables. It updates the files it generated before, but it never overwrites your edits.

Each generated file starts with a header comment containing a hash of the code that was generated:

/*
 * This file was generated by programster/orm-generator. If you edit it, the generator will stop updating
 * it, so that your changes aren't lost.
 * @generated-hash 8006d6472af9a157c07a99e9d251c8732bba26f1c5ea4242fd0da1790385f41c
 */

When the generator comes across a file that already exists, it checks that hash. The outcome for each file is reported as a FileOutcomeEnum case (see The report):

Existing file	What happens	Outcome	Warning?
Unedited, and up to date with the database	Left as it is.	`Unchanged`	No
Unedited, but out of date with the database	Updated to match the database.	`Updated`	No
Edited, but its table hasn't changed	Left as it is.	`KeptEdited`	No
Edited, and its table has changed since it was generated	Left as it is, so you'll need to update it by hand.	`KeptEditedOutOfDate`	Yes
Not created by the generator (no hash in its header)	Left as it is.	`KeptNotGenerated`	Yes

New files are reported as Created.

To have an edited file generated again, delete it and rerun the generator. Changes to line endings alone, such as git converting them on checkout, don't count as edits.

Limitations

Views are ignored, and tables that don't have a single-column primary key (such as many-to-many join tables) are skipped with a warning, as pgsql-objects needs a single ID column.
Only tables in the public schema are generated, and tables in other schemas are ignored. This is because pgsql-objects loads column types with PHP's pg_meta_data(), which only looks in the public schema for table names that aren't qualified with a schema, so classes for tables in other schemas couldn't load their rows, even if the connection's search_path included them.
batchSave() can't read the values of virtual fields back from the database, so they will be out of date on the saved records until they are reloaded with useCache set to false. This is a limitation of pgsql-objects.
Edits to generated files aren't merged with changes to the database. An edited file is left alone, so you have to update it by hand, or delete it and have it generated again (see Running the generator again).

Testing

The tests need an empty PostgreSQL 18 or newer database (for VIRTUAL generated columns) just for them, as each test creates its own tables, and drops every table and view in the public schema beforehand. As a safety net, they refuse to run if the public schema isn't empty to begin with. You can run a throwaway database for them through docker with:

docker run -d --rm \
  --name orm-generator-test \
  -e POSTGRES_USER=test \
  -e POSTGRES_PASSWORD=test \
  -e POSTGRES_DB=orm_generator_test \
  -p 127.0.0.1:5433:5432 \
  postgres:18

Then:

Copy testing/Settings.php.tmpl to testing/Settings.php. It's already filled in for the container above.
Install the dependencies with composer install.
Run the tests:

php testing/main.php

To run only some of the tests, name them, e.g. php testing/main.php TestSerialTable TestUuidTable. The runner exits with a non-zero code if any test fails. To keep the settings somewhere else, such as in CI, set the ORM_GENERATOR_TEST_SETTINGS environment variable to the path of the settings file.

The tests are in testing/Tests, and each one is a class with a run() method that throws if the test fails.

License

MIT. See LICENSE.

programster / orm-generator

Package info

Security

Statistics