Doctrine migrations and Postgis

Using Postgres with the Postgis extension to integrate GeoData / GIS functionality in your project is not natively supported by Doctrine and Doctrine migrations.

First you have to add the extension to Postgres, even if you use the Postgis docker image like postgis/postgis:14-3.3-alpine.

So add this SQL statement to the up method of your first migration:
$this->addSql('CREATE EXTENSION IF NOT EXISTS postgis;');

and the DROP statement for the extension to the down method:
$this->addSql('DROP EXTENSION postgis;');

Now, when using Doctrine with Postgres and Postgis extension, migrations still behave a bit odd and try to remove Sequences created by Postgis, because Doctrine migrations does not take Postgis extension’ s built-in Sequences into account.

So you will find this statements in your first migration, which is basically wrong because –of course– we want to keep the topology and tiger Sequences.

	public function up(Schema $schema): void
	{
		// this up() migration is auto-generated, please modify it to your needs
		$this->addSql('DROP SEQUENCE topology.topology_id_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.county_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.state_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.place_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.cousub_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.edges_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.addrfeat_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.faces_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.featnames_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.addr_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.zcta5_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.tract_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.tabblock_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.bg_gid_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.pagc_gaz_id_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.pagc_lex_id_seq CASCADE');
		$this->addSql('DROP SEQUENCE tiger.pagc_rules_id_seq CASCADE');
	}

	public function down(Schema $schema): void
	{
		// this down() migration is auto-generated, please modify it to your needs
		$this->addSql('CREATE SCHEMA topology');
		$this->addSql('CREATE SCHEMA tiger');
		$this->addSql('CREATE SCHEMA tiger_data');
		$this->addSql('CREATE SEQUENCE topology.topology_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.county_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.state_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.place_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.cousub_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.edges_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.addrfeat_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.faces_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.featnames_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.addr_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.zcta5_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.tract_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.tabblock_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.bg_gid_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.pagc_gaz_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.pagc_lex_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
		$this->addSql('CREATE SEQUENCE tiger.pagc_rules_id_seq INCREMENT BY 1 MINVALUE 1 START 1');
	}Code language: PHP (php)

So we have to teach Doctrine to respect these Postgis features.
Therefor extend the PostgreSqlPlatform class, add the Postgis features as exclude condition to the query:

src/DBAL/PostgisPostgreSqlPlatform.php:

<?php

namespace App\DBAL;

use Doctrine\DBAL\Platforms\PostgreSQLPlatform as PostgreSqlPlatformBase;

class PostgisPostgreSqlPlatform extends PostgreSqlPlatformBase
{
	public function getListNamespacesSQL()
	{
		# exclude postgis schemas
		return "SELECT schema_name AS nspname
				FROM   information_schema.schemata
				WHERE  schema_name NOT LIKE 'pg\_%'
				AND schema_name NOT LIKE 'topology'
				AND schema_name NOT LIKE 'tiger%'
				AND    schema_name != 'information_schema'";
	}

	public function getListSequencesSQL($database)
	{
		return 'SELECT sequence_name AS relname,
					   sequence_schema AS schemaname,
					   minimum_value AS min_value, 
					   increment AS increment_by
				FROM   information_schema.sequences
				WHERE  sequence_catalog = ' . $this->quoteStringLiteral($database) . "
				AND    sequence_schema NOT LIKE 'pg\_%' 
				AND sequence_schema NOT LIKE 'topology%'
				AND sequence_schema NOT LIKE 'tiger%'
				AND    sequence_schema != 'information_schema'";
	}
}Code language: PHP (php)

As Gist: https://gist.github.com/ivoba/74a143d8074110ef47f93a581bb0c3f6

Now tell doctrine to use this PostgreSqlPlatform class instead.
In symfony you can add this to config/packages/doctrine.yaml:

doctrine:
	dbal:
		url: '%env(resolve:DATABASE_URL)%'
		types:
			geometry: LongitudeOne\Spatial\DBAL\Types\GeometryType
			point: LongitudeOne\Spatial\DBAL\Types\Geometry\PointType
			polygon: LongitudeOne\Spatial\DBAL\Types\Geometry\PolygonType
			linestring: LongitudeOne\Spatial\DBAL\Types\Geometry\LineStringType
		platform_service: App\DBAL\PostgisPostgreSqlPlatformCode language: YAML (yaml)

This is inspired by this Gist, which does the same for other Postgres extensions: https://gist.github.com/dextervip/a2f384050748d6ee3ed7d573425e9d58

Now you can run your migrations diff bin/console do:mi:di and the Postgis sequences will be respected by the Doctrine.