Nautilus.DataProvider.PostgreSql 1.2.0-alpha

Nautilus.DataProvider.PostgreSql

A PostgreSQL data provider implementation for the Nautilus ecosystem. This library provides a concrete implementation of DataProviderBase from Nautilus.Base, enabling seamless database operations with PostgreSQL through a consistent, database-agnostic API.

Features

• PostgreSQL-Specific Implementation: Full support for PostgreSQL including schemas, UUIDs, and native data types
• Automatic Migrations: PostgreSqlMigrator for creating tables with foreign key dependency resolution
• Type Mapping: Automatic .NET to PostgreSQL type mapping via PostgreSqlDataTypeMapper
• CRUD Operations: Create, Read, Update, and Delete with PostgreSQL-aware SQL generation
• Async/Await: All operations have async variants
• Schema Support: Configurable schema (defaults to "public"). A schema on [NautilusTable] wins over the provider default.
• Parameterized Queries: Values are parameterized. Identifiers are validated and quoted.
• Stored Functions: PostgreSQL functions with multiple result sets
• Dapper Integration: Leverages Dapper for efficient micro-ORM operations

Installation

Install the package via NuGet:

dotnet add package Nautilus.DataProvider.PostgreSql

Or via the Package Manager Console:

Install-Package Nautilus.DataProvider.PostgreSql

Quick Start

Define an Entity with Attributes

using Nautilus.Base.Data.ModelBase;
using Nautilus.Base.Data.Attributes;

// Basic entity with table and column mapping
[NautilusTableAttribute("users")]
public class User : EntityBase<int>
{
    [NautilusPrimaryKeyAttribute]
    [NautilusAutoIncrementAttribute]
    public int Id { get; set; }

    [NautilusColumnAttribute("first_name")]
    public string FirstName { get; set; }

    [NautilusColumnAttribute("last_name")]
    public string LastName { get; set; }

    public string Email { get; set; }
}

// Entity with schema and foreign key
[NautilusTableAttribute("orders", Schema = "sales")]
public class Order : EntityBase<int>
{
    [NautilusPrimaryKeyAttribute]
    [NautilusAutoIncrementAttribute]
    public int Id { get; set; }

    public string OrderNumber { get; set; }

    public decimal TotalAmount { get; set; }

    [NautilusForeignKeyAttribute]
    [ConstraintReferenceForeignKey(typeof(User), "Id", true, true)]
    public int UserId { get; set; }
}

Initialize the PostgreSQL Database

using Nautilus.DataProvider.PostgreSql;
using Nautilus.Base.Configuration;

// Load configuration from appsettings.json
IConfigurationRoot config = AppSettingJsonConfiguration.GetConfiguration("appsettings.json");
var appSettings = new AppSettings();
config.Bind(appSettings);

var providerSetting = appSettings.DatabaseProviders["DefaultConnection"];

// Create the data provider
var dataProvider = new PostgreSqlDatabase(providerSetting);

Basic CRUD Operations

// Insert a new user
var newUser = new User
{
    FirstName = "John",
    LastName = "Doe",
    Email = "john.doe@example.com"
};
object insertedId = dataProvider.Save(newUser);

// Get a single entity by primary key
User user = dataProvider.Get<User>(123);

// Get all entities
IEnumerable<User> allUsers = dataProvider.GetAll<User>();

// Get with where clause
var whereParams = new Dictionary<string, object>
{
    ["Email"] = "user@example.com"
};
User user = dataProvider.Get<User>(whereParams);

// Update existing entity
user.FirstName = "Jane";
int affected = dataProvider.Update(user);

// Delete by primary key
int deleted = dataProvider.Delete<User>(123);

Async Operations

// Async insert
object insertedId = await dataProvider.SaveAsync(newUser);

// Async get by primary key
User user = await dataProvider.GetAsync<User>(123);

// Async get all
IEnumerable<User> users = await dataProvider.GetAllAsync<User>();

// Async update
int affected = await dataProvider.UpdateAsync(user);

// Async delete
int deleted = await dataProvider.DeleteAsync<User>(123);

Bulk Operations

// Bulk insert/update multiple entities efficiently
var users = new List<User>
{
    new User { FirstName = "John", LastName = "Doe", Email = "john@example.com" },
    new User { FirstName = "Jane", LastName = "Smith", Email = "jane@example.com" },
    new User { FirstName = "Bob", LastName = "Johnson", Email = "bob@example.com" }
};
IEnumerable<object> insertedIds = dataProvider.SaveBulk(users);

// Async bulk operations
IEnumerable<object> insertedIds = await dataProvider.SaveBulkAsync(users);

Database Management

// Check if database exists
bool exists = dataProvider.DatabaseExists();

// Create database (if not exists)
dataProvider.CreateDatabase();

// Check database connection
bool isConnected = dataProvider.CheckConnection();

Database Migration

using Nautilus.DataProvider.PostgreSql;

// Create a migrator
var migrator = new PostgreSqlMigrator(
    providerSetting,
    () => dataProvider.CreateConnection(),
    () => dataProvider.CreateDatabase()
);

// Add tables for migration
migrator.AddTable<User>()
        .AddTable<Order>()
        .AddTable<Product>();

// Run the migration (creates tables with FK dependency resolution)
migrator.Run();

API Reference

PostgreSqlDatabase

Concrete implementation of DataProviderBase for PostgreSQL.

Constructors

Properties

Key Methods (inherited from DataProviderBase)

Async Methods

Database Management Methods

Database Introspection Methods

Query Methods

Stored Functions

These methods call PostgreSQL functions with SELECT * FROM name(...). A PostgreSQL procedure needs CALL and cannot return a result set in this form. Parameters are passed in dictionary enumeration order, so add them in the function's parameter order.

Utility Methods

PostgreSqlMigrator

Database migration tool for creating PostgreSQL tables.

Constructor

Methods

The migrator automatically:

• Resolves foreign key dependencies before creating tables
• Creates tables with the correct PostgreSQL data types
• Handles primary keys, auto-increment, and constraints
• Supports ON DELETE CASCADE and ON UPDATE CASCADE

PostgreSqlDataTypeMapper

Maps .NET types to PostgreSQL data types.

Type Mappings

Simple Type Mappings Reference

Usage Patterns

Entity Mapping with Attributes

// Simple mapping - uses class name for table
public class Category : EntityBase<int>
{
    [NautilusPrimaryKeyAttribute]
    public int Id { get; set; }
    public string Name { get; set; }
}
// Maps to table: Category

// Custom table name with schema
[NautilusTableAttribute("categories", Schema = "inventory")]
public class Category : EntityBase<int>
{
    [NautilusPrimaryKeyAttribute]
    public int Id { get; set; }
    public string Name { get; set; }
}
// Maps to: inventory.categories

// Column name overrides with data type attributes
[NautilusTableAttribute("users")]
public class User : EntityBase<int>
{
    [NautilusPrimaryKeyAttribute]
    [NautilusAutoIncrementAttribute]
    [NautilusColumnAttribute("user_id")]
    public int Id { get; set; }

    [NautilusVarcharAttribute(100)]
    [NautilusColumnAttribute("full_name")]
    public string FullName { get; set; }

    [NautilusNotNullAttribute]
    public string Email { get; set; }
}

Migration with Foreign Keys

// Define related entities
[NautilusTableAttribute("authors")]
public class Author : EntityBase<int>
{
    [NautilusPrimaryKeyAttribute]
    [NautilusAutoIncrementAttribute]
    public int Id { get; set; }

    [NautilusVarcharAttribute(200)]
    public string Name { get; set; }
}

[NautilusTableAttribute("books")]
public class Book : EntityBase<int>
{
    [NautilusPrimaryKeyAttribute]
    [NautilusAutoIncrementAttribute]
    public int Id { get; set; }

    [NautilusVarcharAttribute(200)]
    public string Title { get; set; }

    // Foreign key with cascade actions
    [NautilusForeignKeyAttribute]
    [ConstraintReferenceForeignKey(typeof(Author), "Id", OnDeleteCascade: true, OnUpdateCascade: true)]
    public int AuthorId { get; set; }
}

// Run migration - tables are created in dependency order
var migrator = new PostgreSqlMigrator(providerSetting, () => connection, () => database.CreateDatabase());
migrator.AddTable<Author>()
        .AddTable<Book>()
        .Run();

Schema Configuration

// Using appsettings.json
{
  "DatabaseProviders": {
    "DefaultConnection": {
      "Key": "DefaultConnection",
      "ProviderType": "PostgreSql",
      "ConnectionString": "Host=localhost;Port=5432;Database=mydb;Username=user;Password=pass",
      "Schema": "myschema",
      "Active": true,
      "UseExistingDatabase": false
    }
  }
}

// The provider will use the configured schema
var provider = new PostgreSqlDatabase(providerSetting);
// provider.Schema == "myschema"

// Or set schema directly
provider.Schema = "public";

Schema precedence and identifier rules:

1. A Schema value on [NautilusTable] wins over the provider Schema.
2. The migrator creates a missing custom schema with CREATE SCHEMA IF NOT EXISTS.
3. Table, schema, and column names are lowercased and quoted. Use lowercase names in custom SQL.
4. [NautilusColumn("name")] applies to migration and to every CRUD method.
5. DateTime maps to timestamp without time zone.

Raw SQL Execution

// Execute raw SQL with parameters
string sql = "SELECT * FROM users WHERE created_at > @cutoffDate";
var parameters = new Dictionary<string, object>
{
    ["cutoffDate"] = new DateTime(2024, 1, 1)
};
var results = dataProvider.ExecuteQuery(sql, parameters);

// Execute non-query SQL
string updateSql = "UPDATE products SET price = price * 1.1 WHERE category = @category";
var parameters = new Dictionary<string, object> { ["category"] = "Electronics" };
int affected = dataProvider.ExecuteNonQuery(updateSql, parameters);

Stored Procedure Execution

// Execute stored procedure returning single result
var parameters = new Dictionary<string, object>
{
    ["UserId"] = 123
};
User user = dataProvider.ExecuteStoredProcedure<User>("get_user_by_id", parameters);

// Execute stored procedure returning list
IEnumerable<Order> orders = dataProvider.ExecuteStoredProcedureList<Order>(
    "get_user_orders",
    parameters
);

// Execute stored procedure returning multiple result sets
var (ordersList, summary) = dataProvider.ExecuteStoredProcedureList<Order, OrderSummary>(
    "get_user_order_summary",
    parameters
);

Working with UUID Primary Keys

[NautilusTableAttribute("products")]
public class Product : EntityBase<Guid>
{
    [NautilusPrimaryKeyAttribute(AutoGenerateGuid = true)]
    public Guid Id { get; set; }

    [NautilusVarcharAttribute(200)]
    public string Name { get; set; }

    public decimal Price { get; set; }
}

// On insert, PostgreSQL's gen_random_uuid() is used
var product = new Product
{
    Name = "New Product",
    Price = 29.99m
};
dataProvider.Save(product);
// product.Id is now set to the generated UUID

Database Introspection

// Get all tables in the current schema
IEnumerable<TableInfo> tables = dataProvider.GetTables();
foreach (var table in tables)
{
    Console.WriteLine($"Schema: {table.SchemaName}, Table: {table.TableName}, Owner: {table.TableOwner}");
}

// Async variant
IEnumerable<TableInfo> tables = await dataProvider.GetTablesAsync();

// Get all database names
IList<string> databases = await dataProvider.GetAllDatabaseNameAsync();
foreach (var db in databases)
{
    Console.WriteLine($"Database: {db}");
}

Architecture

Nautilus.DataProvider.PostgreSql follows the Provider Pattern established by Nautilus.Base, providing a concrete PostgreSQL implementation while maintaining a consistent API.

                         Application Layer
           (Uses DataProviderBase - database-agnostic code)

                                    inherits

                             DataProviderBase
      (Abstract base class with CRUD, Query, Pagination from Nautilus.Base)

                              implements

                     PostgreSqlDatabase (This Library)
        - CreateConnection() -> NpgsqlConnection
        - CreateDatabase() -> PostgreSQL-specific database creation
        - Save/SaveAsync() -> PostgreSQL INSERT with RETURNING clause
        - UpdateAsync() -> PostgreSQL UPDATE with lowercase column names
        - CreateDbParameter() -> NpgsqlParameter

Key Design Principles

1. PostgreSQL-Specific: Leverages Npgsql and PostgreSQL features (UUIDs, schemas, RETURNING clause)
2. Dependency Resolution: Migrator automatically resolves foreign key dependencies
3. Type Mapping: Automatic .NET to PostgreSQL type mapping
4. SQL Injection Protection: All values are parameterized
5. Async-First: All operations have async variants

Project Structure

src/Nautilus.DataProvider.PostgreSql/
├── PostgreSqlDatabase.cs           # Main data provider implementation
├── PostgreSqlMigrator.cs           # Database migration tool
├── PostgreSqlDataTypeMapper.cs     # .NET to PostgreSQL type mapper
└── _globalusings.cs                # Global usings

Security

Nautilus.DataProvider.PostgreSql includes built-in protection against SQL injection attacks:

SQL Injection Prevention

Values are passed as parameters, never concatenated into SQL. Table, schema, column, and function names are validated and quoted before they enter SQL. Lowercase names are used to match PostgreSQL's folding of unquoted names:

// Safe - values are parameterized
var whereParams = new Dictionary<string, object>
{
    ["Email"] = "user'; DROP TABLE users; --"
};
var user = dataProvider.Get<User>(whereParams);
// The malicious input is treated as a literal string, not executable SQL

Dictionary keys in a where clause are column names. They are validated, lowercased, and quoted. With [NautilusColumn("first_name")], use "first_name" as the key.

The library uses NpgsqlParameter for all parameter values. Identifier validation rejects quotes, semicolons, comment markers, and invalid characters. An identifier longer than 63 characters is truncated to match PostgreSQL behavior.

Related Repositories

Nautilus.DataProvider.PostgreSql is part of the Nautilus ecosystem:

Other data provider implementations:

• Nautilus.DataProvider.MSSQL - Microsoft SQL Server support
• Nautilus.DataProvider.MongoDB - MongoDB support

License

This project is licensed under the Business Source License 1.1.

The license allows free commercial use while protecting the source code. The code will automatically convert to the MIT License on December 31, 2028, the Change Date in the LICENSE file.

Contributing

Contributions are welcome! Please see CONTRIBUTING.md for guidelines.

Support

• Issues: GitHub Issues
• Discussions: GitHub Discussions

Acknowledgments

Nautilus.DataProvider.PostgreSql is part of the Nautilus ecosystem - a collection of .NET libraries designed to simplify common development tasks.

Related Nautilus packages:

• Nautilus.Base - Foundation for data access abstraction
• Nautilus.FluentResult - Type-safe result pattern
• Nautilus.HttpClient - Fluent HTTP client
• Nautilus.HandlerPattern - Request/response pattern with pipelines

Built with:

• Npgsql - PostgreSQL .NET data provider
• Dapper - Micro-ORM for efficient data access

Note: This library follows semantic versioning from 1.0.0. Breaking changes require a major version.