Using Nowdoc instead of single or double quotes for Postgres queries in PHP

When using aliases in a Postgres SQL query, you need to use double quotes in your query.

$sql = '
  SELECT facility.facility_id AS "facilityId"
  FROM facility
';

When using strings (for example to build JSON objects), you need to use single quotes in your query.

$sql = "
  SELECT json_build_object(
    'timeOptionInterval', facility_reservation_configuration.time_option_interval,
    'bookingInAdvance', facility_reservation_configuration.booking_in_advance
  )
  FROM facility_reservation_configuration
";

Often times you need both and then it's a back and forth between what you're using to define the string (single quotes or double quotes) and which you're using to escape.

$sql = '
    SELECT
        facility.facility_id AS "facilityId",
        facility.name,
        facility.spaces,
        facility.opening_hours AS "openingHours",
        (
            SELECT json_build_object(
                \'timeOptionInterval\', facility_reservation_configuration.time_option_interval,
                \'bookingInAdvance\', facility_reservation_configuration.booking_in_advance
            )
            FROM facility_reservation_configuration
            WHERE facility_reservation_configuration.facility_id = facility.facility_id
        ) AS "facilityReservationConfiguration",
    ...
';

The solution: Use Nowdoc or Heredoc

Nowdoc and Heredoc are the two other ways to define strings in PHP and are perfectly suited for SQL. Nowdoc is the same as Heredoc, just that it doesn't interpolate variables and doesn't interpret escape characters (the same way single quotes don't and double quotes do).

$sql = <<<'SQL'
    SELECT
        facility.facility_id AS "facilityId",
        facility.name,
        facility.spaces,
        facility.opening_hours AS "openingHours",
        (
            SELECT json_build_object(
                'timeOptionInterval', facility_reservation_configuration.time_option_interval,
                'bookingInAdvance', facility_reservation_configuration.booking_in_advance
            )
            FROM facility_reservation_configuration
            WHERE facility_reservation_configuration.facility_id = facility.facility_id
        ) AS "facilityReservationConfiguration",
    ...
    SQL;

Advantages of Newdoc:

• You can use single and double quotes in your query without the need to escape ether.
• With the end statement ("SQL" in the example), you define the indentation and therefore have a perfectly aligned query for debugging (the first line not being without indentation and the rest with a large one).
• More complex queries (for example when using nested ones with the "WITH" keyword) are still interpreted by IDEs like PHPStorm (which they aren't when using single or double quotes). Although this seems to be connected to the keyword "SQL".

Disadvantages:

• It's a new concept for less experienced members of the team that needs to be explained.

It's a simple concept and makes the query more readable. Therefore, it's a clear win for me and I'll be using it for native queries exclusively in the future. I'll even go so far as to refactor all existing ones right now.