SQL Providers

Relevant source files

• Audit.NET.sln
• README.md
• src/Audit.NET.PostgreSql/ConfigurationApi/IPostgreSqlProviderConfigurator.cs
• src/Audit.NET.PostgreSql/ConfigurationApi/PostgreSqlProviderConfigurator.cs
• src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs
• src/Audit.NET.PostgreSql/README.md
• src/Audit.NET.PostgreSql/SqlScript.sql
• src/pack.cmd
• src/push.cmd
• test/Audit.PostgreSql.UnitTest/Audit.PostgreSql.UnitTest.csproj
• test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs
• test/Audit.PostgreSql.UnitTest/SqlScript.sql

Purpose and Scope

This page provides an overview of the SQL database data providers in Audit.NET. These providers persist audit events to SQL database tables in JSON format. Three SQL database providers are available:

• SQL Server - See SQL Server Provider for detailed configuration
• PostgreSQL - See PostgreSQL Provider for detailed configuration
• MySQL - See MySQL Provider for detailed configuration

For the Entity Framework-based provider that stores audit events using EF models, see EntityFramework Data Provider.

---

Architecture Overview

All SQL providers follow a common pattern: they store each audit event as a row in a database table, with the event serialized as JSON in a designated column. Additional columns can store extracted metadata for efficient querying.

SQL Provider Class Hierarchy

Sources: src/Audit.NET.SqlServer/README.md1-5 src/Audit.NET.PostgreSql/README.md1-5 src/Audit.NET.MySql/Providers/MySqlDataProvider.cs9-21

---

Provider Comparison

Feature	SQL Server	PostgreSQL	MySQL
Package	Audit.NET.SqlServer	Audit.NET.PostgreSql	Audit.NET.MySql
Primary Key Type	Any type convertible to NVARCHAR	Any type convertible to STRING	Numeric (auto-increment)
JSON Column Type	NVARCHAR(MAX)	JSON, JSONB, or TEXT	TEXT or JSON
Schema Support	Yes	Yes	No
Query Methods	GetEvent()	GetEvent(), EnumerateEvents()	GetEvent()
Custom Columns	Yes	Yes	Yes
Last Updated Column	Yes	Yes	No
Fluent Configuration	UseSqlServer()	UsePostgreSql()	UseMySql()

Sources: src/Audit.NET.SqlServer/README.md67-77 src/Audit.NET.PostgreSql/README.md56-70 src/Audit.NET.MySql/Providers/MySqlDataProvider.cs23-46

---

Common Configuration Pattern

All SQL providers share a similar configuration structure through fluent API or direct property assignment.

Fluent Configuration Example

Sources: src/Audit.NET.SqlServer/README.md42-53 src/Audit.NET.PostgreSql/README.md24-36 src/Audit.NET.MySql/Providers/MySqlDataProvider.cs52-64

Core Configuration Properties

Sources: src/Audit.NET.SqlServer/README.md67-77 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs24-65 src/Audit.NET.MySql/Providers/MySqlDataProvider.cs23-46

---

Table Schema Requirements

All SQL providers expect specific table constraints:

Required Structure

• Single ID Column: The table must have exactly one primary key or unique key column
• ID Type Convertibility: The ID column type must be convertible to string/varchar
• JSON Column: A text-based column to store the serialized audit event (optional for custom scenarios)
• Table Pre-existence: The table must be created before use; providers do not create tables

Example Table Schemas

SQL Server:

PostgreSQL:

MySQL:

Sources: src/Audit.NET.SqlServer/README.md94-105 src/Audit.NET.PostgreSql/SqlScript.sql1-16 test/Audit.PostgreSql.UnitTest/SqlScript.sql1-16

---

Custom Columns

Custom columns allow extracting specific audit event fields into dedicated table columns for efficient querying without parsing JSON.

CustomColumn Class Structure

Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs65 src/Audit.NET.MySql/Providers/MySqlDataProvider.cs44-46

Example Configuration

Sources: src/Audit.NET.PostgreSql/README.md34-35 test/Audit.PostgreSql.UnitTest/PostgreSqlTests.cs248-270

---

JSON Storage Options

PostgreSQL Data Types

PostgreSQL supports three data type options for JSON storage via the DataType enumeration:

• JSON: Standard JSON text format
• JSONB: Binary JSON with indexing support (recommended for querying)
• String/TEXT: Plain text storage without JSON parsing

Sources: src/Audit.NET.PostgreSql/README.md68 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs59-60

JSON Column Casting

The PostgreSQL provider applies appropriate casting when inserting/updating events:

Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs391-396

---

Data Retrieval Operations

Core Methods

All SQL providers implement standard retrieval methods from AuditDataProvider:

Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs89-165 src/Audit.NET.MySql/Providers/MySqlDataProvider.cs66-140

PostgreSQL Extended Querying

The PostgreSQL provider includes additional query capabilities through EnumerateEvents:

Sources: src/Audit.NET.PostgreSql/README.md106-120 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs167-227

---

Insert and Replace Operations

SQL Command Generation

Each provider constructs database-specific INSERT and UPDATE commands:

PostgreSQL INSERT:

Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs259-266

PostgreSQL UPDATE:

Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs277-283

MySQL INSERT:

Sources: src/Audit.NET.MySql/Providers/MySqlDataProvider.cs142-153

---

Database Connection Management

Each provider manages database connections through vendor-specific connection classes:

PostgreSQL Connection Method

Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs120-127

Connection Usage Pattern

All providers follow a consistent using-statement pattern for connection lifecycle:

Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs89-103 src/Audit.NET.MySql/Providers/MySqlDataProvider.cs66-84

---

Configuration Builder Pattern

All SQL providers support both static configuration and dynamic per-event configuration through the Setting<T> class:

Dynamic Configuration Example

Sources: src/Audit.NET.SqlServer/README.md56-63

Setting Resolution

The Setting<T> class allows configuration values to be either static or dynamically computed:

Sources: src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs29-37 src/Audit.NET.PostgreSql/Providers/PostgreSqlDataProvider.cs398-401

---

Summary

The SQL providers in Audit.NET offer a straightforward approach to persisting audit events in relational databases:

• Consistent Pattern: All three providers follow the same architectural design with JSON event storage
• Flexible Configuration: Support both static and dynamic configuration per audit event
• Custom Columns: Extract metadata into dedicated columns for efficient querying
• Type-Specific Features: PostgreSQL offers JSONB support and advanced querying; MySQL optimized for auto-increment IDs
• Standard Compliance: All implement the core AuditDataProvider interface methods

For detailed configuration and usage of each provider, see:

• SQL Server Provider
• PostgreSQL Provider
• MySQL Provider