Write type-safe SQL with PHP

With this post I like to share an improvement to our code base at Fusio. We are currently working on our next major 3.0 release and this idea is maybe also useful for other applications.

In the past we had often the problem, that we have renamed a table column and we have missed to rename such column name in our code or that we have also misspelled a column name. In our PHP code those column names are always simple strings so that there was also no tool support which could inform us about those problems.

With the new version we would like to avoid those problems by making PHP aware of the available table columns so that also tools like Psalm help us to directly identify problems in case a wrong column was used. The solution to this problem was the psx/sql component which allows us to automatically generate a table and row class for every existing table of our application.

The generator internally uses the doctrine/dbal library to inspect the table schema and it generates based on those information type safe classes. A generated table class looks then like:

The class simply contains all column names as public constants. This has the great advantage that we can now use these constants instead of writing a simple string if we want to reference such a column in a DQL or DML statement. Thus if we rename a column and then regenerate our table code we have an unused constant in our PHP code which Psalm will directly report.

To give a little insight how this looks. In Fusio we have a domain service class which handles the logic i.e. to create a route. The following code shows the usage of theses constants:

Before the change we had simple strings as array keys and now we have a complete type safe code which is always in sync with the current schema.

Another example is our view where we generate complex JSON responses. This was also a perfect place where we could use those constants.

This approach works best if you follow a database first approach. That means that you use tools like doctrine/migrations to create a fitting schema and then the schema is the source of truth. Traditional ORMs use a different approach, there you define your schema at entities using annotations and attributes, so that the code is the source of truth.

This database first approach is also not new but not so common in the PHP world. We are heavily inspired by the great jOOQ library which is written in Java. As conclusion we are really happy with the change which makes our code base more type safe and thus more robust and error free.

If you like to get more information about Fusio feel free to visit out website. Fusio is an open source API management system which helps to build and manage REST APIs.