Database Creation and Connection Management

This page explains how the Npgsql Entity Framework Core provider manages database creation and establishes connections to PostgreSQL databases. It covers the database lifecycle management and connection handling mechanisms within the provider.

For information about migrations and schema management, see Migrations and Schema Management.

Architecture Overview

The Npgsql EF Core provider includes a specialized subsystem for handling database creation and connection management. This subsystem is designed to work with PostgreSQL's specific requirements for administrative operations like database creation and deletion.

Component Interaction Diagram

Sources: src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs13-17 src/EFCore.PG/Storage/Internal/NpgsqlRelationalConnection.cs15-47 src/EFCore.PG/Storage/Internal/INpgsqlRelationalConnection.cs11-36

Connection Management

The Npgsql EF Core provider supports two approaches for managing connections to PostgreSQL:

1. DataSource-based connections: Using the newer NpgsqlDataSource API that offers better performance and resource management.
2. Traditional connection-based approach: Using connection strings and direct NpgsqlConnection creation.

NpgsqlRelationalConnection

The NpgsqlRelationalConnection class is the central component for managing connections to PostgreSQL databases. It implements the INpgsqlRelationalConnection interface and handles the logic for choosing between a DbDataSource and a standard connection string.

Connection Entity Mapping

Sources: src/EFCore.PG/Storage/Internal/NpgsqlRelationalConnection.cs15-130 src/EFCore.PG/Storage/Internal/INpgsqlRelationalConnection.cs11-36

DataSource Management

The provider includes a NpgsqlDataSourceManager that creates and manages NpgsqlDataSource instances. This manager is registered as a singleton and ensures that data sources are reused when appropriate, particularly when features like Enum mappings or plugins are used.

Sources: src/EFCore.PG/Storage/Internal/NpgsqlDataSourceManager.cs29-88 src/EFCore.PG/Storage/Internal/NpgsqlRelationalConnection.cs44-54 test/EFCore.PG.Tests/NpgsqlRelationalConnectionTest.cs28-56

Database Creation Process

Creating a PostgreSQL database through the Npgsql EF Core provider follows a specific workflow to handle PostgreSQL's requirement that a database cannot be created when you're connected to it.

NpgsqlDatabaseCreator

The NpgsqlDatabaseCreator class implements database creation, deletion, and existence checking. It overrides RelationalDatabaseCreator to provide PostgreSQL-specific logic, such as using an admin connection for CREATE DATABASE commands.

Sources: src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs41-60 src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs68-89 src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs154-168

Admin Connections

The provider uses admin connections to perform operations that can't be done on a regular connection, such as creating or dropping the target database.

The CreateAdminConnection() method in NpgsqlRelationalConnection creates a special connection, typically to the postgres database, with pooling and multiplexing disabled to ensure the administrative command executes successfully.

Sources: src/EFCore.PG/Storage/Internal/NpgsqlRelationalConnection.cs191-215 src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs43-44 src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs274-290

Database Existence and Table Checking

NpgsqlDatabaseCreator implements logic to check if a database exists and if it contains any tables.

Table Detection SQL

The HasTables() method queries pg_class and pg_namespace to identify user-defined tables, excluding those belonging to internal schemas or extensions.

Sources: src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs97-108 src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs130-142

PostgreSQL-Specific Database Options

When creating a database, the provider supports PostgreSQL-specific options through NpgsqlCreateDatabaseOperation, which are then rendered into SQL by the NpgsqlMigrationsSqlGenerator.

Option	Description	SQL Clause
Template	Use an existing database as a template	TEMPLATE "name"
Collation	Set the default collation (LC_COLLATE)	LC_COLLATE "name"
Tablespace	Set the target tablespace	TABLESPACE "name"

Sources: test/EFCore.PG.FunctionalTests/Migrations/NpgsqlMigrationsSqlGeneratorTest.cs20-71 src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs154-168

Connection Pooling and Lifecycle

The Npgsql provider has special handling for connection pooling to ensure proper resource management during DDL operations.

• Pool Clearing: When a database is created or deleted, ClearPool() is called to ensure that the ADO.NET provider doesn't attempt to reuse connections that might be invalid due to the database state change.
• Reloading Types: The NpgsqlMigrator forces a ReloadTypes() call on the underlying NpgsqlConnection if a migration adds extensions, enums, or range types, ensuring the type mapping system is aware of the new database-side types.

Sources: src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs56 src/EFCore.PG/Storage/Internal/NpgsqlDatabaseCreator.cs85 src/EFCore.PG/Migrations/Internal/NpgsqlMigrator.cs74-92

Specialized Connection Features

SSL/TLS and Callbacks

NpgsqlRelationalConnection handles advanced security configurations provided via NpgsqlOptionsExtension, including:

• ProvideClientCertificatesCallback
• RemoteCertificateValidationCallback
• ProvidePasswordCallback

These are applied to the NpgsqlConnection instance during creation if a DbDataSource is not being used.

Sources: src/EFCore.PG/Storage/Internal/NpgsqlRelationalConnection.cs108-129 src/EFCore.PG/Storage/Internal/NpgsqlRelationalConnection.cs17-21

Value Generation Integration

Connection management also intersects with value generation. The NpgsqlValueGeneratorSelector uses the INpgsqlRelationalConnection to access sequence states when generating HiLo values.

Sources: src/EFCore.PG/ValueGeneration/Internal/NpgsqlValueGeneratorSelector.cs52-68 src/EFCore.PG/ValueGeneration/Internal/NpgsqlValueGeneratorSelector.cs15-37