Migrations and Schema Management

This page documents the migration and schema management system in the Npgsql Entity Framework Core provider, which handles bidirectional synchronization between EF Core models and PostgreSQL databases. This includes generating SQL DDL statements from model changes (forward engineering) and creating models from existing databases (reverse engineering).

For database creation and connection management, see Database Creation and Connection Management. For reverse engineering and scaffolding details, see Scaffolding and Reverse Engineering.

Overview

The migration system consists of three major subsystems:

1. Forward Engineering: Converting model changes into PostgreSQL DDL statements via NpgsqlMigrationsSqlGenerator src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs24-25
2. Annotation System: Storing and transmitting PostgreSQL-specific metadata using NpgsqlAnnotationNames src/EFCore.PG/Metadata/Internal/NpgsqlAnnotationNames.cs9-10
3. Reverse Engineering: Reading PostgreSQL schemas and generating EF Core models via NpgsqlDatabaseModelFactory src/EFCore.PG/Scaffolding/Internal/NpgsqlDatabaseModelFactory.cs18-19

Natural Language to Code Entity Space: Overview

Sources: src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs24-154 src/EFCore.PG/Scaffolding/Internal/NpgsqlDatabaseModelFactory.cs51-158 src/EFCore.PG/Design/Internal/NpgsqlAnnotationCodeGenerator.cs13-14

Migration SQL Generation

The NpgsqlMigrationsSqlGenerator class is the central component for converting migration operations into PostgreSQL SQL. It extends EF Core's base MigrationsSqlGenerator to handle PostgreSQL-specific features.

Core Architecture

The generator overrides the Generate method to process a list of operations src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs50-54 It specifically handles PostgreSQL-specific operations like database creation and dropping src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs141-152

Sources: src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs24-154

Table Creation

When creating tables, the generator handles:

1. Unlogged tables: Uses NpgsqlAnnotationNames.UnloggedTable src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs175-178
2. System columns: Automatically removes PostgreSQL system columns (like xmin, ctid) from the operation src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs171
3. Comments: Generates COMMENT ON TABLE and COMMENT ON COLUMN statements src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs214-238

Sources: src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs159-245 test/EFCore.PG.FunctionalTests/Migrations/MigrationsNpgsqlTest.cs83-92

Column Alterations and Value Generation

The generator manages complex transitions between value generation strategies, such as converting a SerialColumn to an IdentityByDefaultColumn src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs548-571 This often involves renaming sequences and using setval to synchronize values src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs563-570

Sources: src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs534-638 test/EFCore.PG.FunctionalTests/Migrations/MigrationsNpgsqlTest.cs141-161

Sequence Bumping for Data Seeding

When data is seeded into identity or serial columns, the provider automatically bumps the underlying sequence to the maximum seeded value plus one src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs71-74 This prevents "duplicate key" errors on the next manual insert.

Sources: src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs71-132

Annotation System

PostgreSQL-specific metadata is stored using EF Core's annotation system, with keys prefixed by Npgsql: src/EFCore.PG/Metadata/Internal/NpgsqlAnnotationNames.cs17

Annotation Categories

Annotation Category	Key Examples	Purpose
Model	PostgresExtension, PostgresEnum, PostgresRange	Define database-wide objects src/EFCore.PG/Metadata/Internal/NpgsqlAnnotationNames.cs110-120
Table	UnloggedTable, StorageParameter	Configure table-level storage src/EFCore.PG/Metadata/Internal/NpgsqlAnnotationNames.cs25-230
Column	ValueGenerationStrategy, IdentitySequenceOptions, Compression	Configure column behavior src/EFCore.PG/Metadata/Internal/NpgsqlAnnotationNames.cs25-240
Index	IndexMethod, IndexOperators, CreatedConcurrently	Configure indexing engine src/EFCore.PG/Metadata/Internal/NpgsqlAnnotationNames.cs33-81

Sources: src/EFCore.PG/Metadata/Internal/NpgsqlAnnotationNames.cs1-274

Annotation Code Generation

NpgsqlAnnotationCodeGenerator converts these annotations back into Fluent API calls during scaffolding src/EFCore.PG/Design/Internal/NpgsqlAnnotationCodeGenerator.cs13-14 It suppresses annotations that are considered "default" by convention to keep code clean src/EFCore.PG/Design/Internal/NpgsqlAnnotationCodeGenerator.cs139-201

Natural Language to Code Entity Space: Annotation Mapping

Sources: src/EFCore.PG/Design/Internal/NpgsqlAnnotationCodeGenerator.cs71-91 src/EFCore.PG/Metadata/Internal/NpgsqlAnnotationNames.cs65-240 src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs1475-1510

Version-Specific Logic

The provider targets specific PostgreSQL versions via INpgsqlSingletonOptions.PostgresVersion src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs44 This affects generated SQL, such as using PERFORM instead of SELECT in idempotent scripts for sequence bumping src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs109-111

Sources: src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs32-47 src/EFCore.PG/Migrations/NpgsqlMigrationsSqlGenerator.cs109-111