postgres-frameworks

PostgreSQL Framework Patterns

EF Core (Npgsql Provider)

PostgreSQL-Specific Features

• Map PostgreSQL enums: modelBuilder.HasPostgresEnum<OrderStatus>()
• Map JSONB: builder.Property(o => o.Metadata).HasColumnType("jsonb")
• Use UseIdentityAlwaysColumn() instead of UseIdentityColumn()
• Use HasDefaultSchema("public") for PostgreSQL convention
• Configure GIN indexes for JSONB: builder.HasIndex(o => o.Metadata).HasMethod("gin")

Migrations

• Generate idempotent scripts: dotnet ef migrations script --idempotent
• PostgreSQL supports transactional DDL — migrations run atomically
• Use NpgsqlModelBuilderExtensions for PostgreSQL-specific features

SQLAlchemy + Alembic

Model Best Practices

• Use DeclarativeBase (SQLAlchemy 2.0+)
• Use JSONB dialect type, not generic JSON
• Use server_default for database-level defaults
• Use __table_args__ for schema and constraints

Alembic Patterns

• Use --autogenerate for migration creation
• Always review autogenerated migrations before applying
• Use op.execute() for raw SQL when needed (e.g., creating enums)
• Wrap enum creation in alembic: op.execute("CREATE TYPE order_status AS ENUM ('pending', 'completed')")

Flyway

PostgreSQL-Specific

• Use transactional migrations (default in Flyway for PostgreSQL)
• snake_case in migration names: V1__create_orders_table.sql
• Use IF NOT EXISTS for idempotent DDL

Dapper (Npgsql)

PostgreSQL-Specific

• Use NpgsqlConnection directly
• PostgreSQL uses $1, $2 parameters internally, but Dapper's @Name syntax works via Npgsql
• Map enums with NpgsqlConnection.GlobalTypeMapper.MapEnum<T>()
• Use NpgsqlDbType.Jsonb for JSONB parameters