Nautilus.DataProvider.SqlServer 1.0.2-alpha

Nautilus.DataProvider.SqlServer

A SQL Server data provider for the Nautilus ecosystem. It implements DataProviderBase from Nautilus.Base and adds SQL Server specific migrations, type mapping, CRUD operations, bulk copy, and stored procedure calls.

Features

• Schema support: The provider schema defaults to dbo. A schema on [NautilusTable] wins over the provider default.
• Safe identifiers: Every table, column, schema, database, and stored procedure name is validated and bracket-quoted. Reserved words are safe.
• Parameterized values: Values are sent as SQL parameters. Database existence checks and stored procedure names do not use string interpolation.
• CRUD: Save, Update, Get, GetAll, Delete, and async variants.
• Bulk copy: SaveBulk and SaveBulkAsync skip identity columns and use typed DataColumn objects.
• Automatic migrations: SqlServerMigrator creates schemas, tables, primary keys, defaults, and foreign keys. It detects circular foreign key dependencies.
• Type mapping: SqlServerDataTypeMapper maps .NET types to SQL Server types and rejects unsupported types with a clear error.
• Stored procedures: Single-result, list, two-result, and three-result calls.
• Tested: Unit tests and integration tests against SQL Server 2022 in a container. The merged line coverage gate is 95.5%.

Installation

dotnet add package Nautilus.DataProvider.SqlServer

The package targets net10.0 and depends on Nautilus.Base 0.0.11-preview and Microsoft.Data.SqlClient 7.1.1. It also pins System.Security.Cryptography.Pkcs 10.0.13. The pin overrides a vulnerable transitive version from Microsoft.Data.SqlClient.

Quick start

using Nautilus.Base.Configuration.Models;
using Nautilus.Base.Data;
using Nautilus.Base.Data.Attributes;
using Nautilus.DataProvider.SqlServer;

[NautilusTable("widgets")]
public class Widget
{
    [PrimaryKey]
    [AutoIncrement]
    public int Id { get; set; }

    [NautilusColumn("display_name")]
    [NVarchar(100)]
    public string Name { get; set; } = string.Empty;

    public decimal Price { get; set; }
}

var setting = new DatabaseProviderSetting
{
    Key = "main",
    ProviderType = DbProviderType.SqlServer,
    ConnectionString =
        "Server=localhost;Database=app;User Id=sa;Password=Your_password123;TrustServerCertificate=True",
    Schema = "dbo",
    DefaultSqlDatabaseName = "master",
    UseExistingDatabase = false
};

var database = new SqlServerDatabase(setting);
var migrator = new SqlServerMigrator(setting, database.CreateConnection, database.CreateDatabase);
migrator.AddTable<Widget>().Run();

var widget = new Widget { Name = "Hammer", Price = 12.50m };
var generatedId = database.Save(widget); // Save inserts. It does not update.
widget.Price = 13.00m;
database.Update(widget);

var loaded = database.Get<Widget>(widget.Id);
var all = database.GetAll<Widget>().ToList();
database.Delete<Widget>(widget.Id);

Configuration

The simple constructor uses the key and connection string only:

var database = new SqlServerDatabase("main", connectionString);

CreateDatabase requires the DatabaseProviderSetting constructor. It throws InvalidOperationException with the simple constructor. Call IsDatabaseExists first, or use the settings constructor.

API reference

Provider

Stored procedure names accept one or two parts, for example "GetWidget" or "dbo.GetWidget". Use a schema-qualified name when the procedure is not in the caller's default schema.

Migrator

var migrator = new SqlServerMigrator(
    setting,
    database.CreateConnection,
    database.CreateDatabase);

migrator
    .AddTable<Author>()
    .AddTable<Book>()
    .Run();

The migrator:

• Creates missing schemas. It does not create dbo.
• Creates tables in dependency order. It detects circular foreign key dependencies.
• Uses the [NautilusTable] name and schema for existence checks.
• Includes inherited properties and honors [Ignore] and [NautilusColumn].
• Adds primary keys, identity columns, GUID defaults, column defaults, default constraints, and foreign keys.
• Executes one statement per command.

The old constructor remains for compatibility:

var migrator = new SqlServerMigrator(
    DbProviderType.SqlServer,
    database.CreateConnection);

Type mapper

var mapper = new SqlServerDataTypeMapper();
var sqlType = mapper.MapPropertyDataTypeToSqlType(propertyInfo);

SQL Server has no char(max). The mapper throws NautilusException for [Char] without a length. Use [Char(n)] or [Varchar].

Unsupported types throw NautilusException. The mapper does not return an empty type.

Nullability follows the Nautilus rule: a column gets NOT NULL only with [NotNull]. Primary key columns always get NOT NULL because SQL Server requires it.

Attributes

[NautilusTable("orders", Schema = "sales")]
public class Order
{
    [PrimaryKey]
    [AutoIncrement]
    public int Id { get; set; }

    [NautilusColumn("customer_id")]
    public int CustomerId { get; set; }

    [NotNull]
    [NVarchar(100)]
    public string Number { get; set; } = string.Empty;

    [Default("0")]
    public decimal Discount { get; set; }

    [Ignore]
    public string CacheKey { get; set; } = string.Empty;

    [ConstraintReferenceForeignKey(typeof(Customer), "Id", true, false)]
    public int CustomerKey { get; set; }
}

• [PrimaryKey] marks a primary key column. Use it on multiple properties for a composite key.
• [PrimaryKey(AutoGenerateGuid = true)] generates a GUID before insert when the value is empty.
• [AutoIncrement] emits IDENTITY(1,1).
• [NautilusColumn("name")] maps a property to a different column name.
• [Ignore] keeps a property out of SQL operations.
• [NotNull] adds NOT NULL.
• [Default("0")] adds an inline column default.
• [ConstraintDefault("0", "Column")] adds a named default constraint.
• [ConstraintReferenceForeignKey(typeof(Parent), "Id", onDeleteCascade, onUpdateCascade)] adds a foreign key.

Security notes

• All identifiers pass SqlServerIdentifier. The limit is 128 characters. Allowed characters are letters, digits, _, @, #, and $. Quotes, brackets, comment markers, and semicolons are rejected.
• Table and column names are wrapped in brackets. Escaping is applied.
• Values are always parameters. IsDatabaseExists uses DB_ID(@database).
• Stored procedure names are validated and quoted.
• CreateDatabase and Shutdown validate and quote the database name. Shutdown refuses to drop the maintenance database.
• Use a least-privilege SQL Server login. The provider does not grant permissions.

Testing

dotnet restore src/nautilus-dataprovider-sqlserver.sln
dotnet build src/nautilus-dataprovider-sqlserver.sln -c Release
dotnet test src/nautilus-dataprovider-sqlserver.sln -c Release

dotnet test src/nautilus-dataprovider-sqlserver.sln -c Release \
  --collect:"XPlat Code Coverage" --settings coverlet.runsettings

The integration tests use Testcontainers.MsSql with mcr.microsoft.com/mssql/server:2022-latest. Docker is required. On arm64 machines, Docker must support amd64 emulation. CI runs on an amd64 runner.

Coverage is merged from the unit and integration runs. The line coverage gate is 95.5%:

python3 .github/scripts/merge-coverage.py ./coverage.cobertura.xml $(find . -name coverage.cobertura.xml)
python3 .github/scripts/check-coverage.py ./coverage.cobertura.xml 95.5

See docs/coverage-plan.md for the coverage targets and work plan. See docs/test-matrix.md for the test map. See docs/coverage-exclusions.md for the exclusion register.

Release

The release workflow runs on tags such as v1/1.0.0 or v1/1.0.0-beta. It:

1. Validates the tag and checks that the commit is on master.
2. Builds, tests, merges coverage, and enforces the 95.5% gate.
3. Packs the package and symbol package.
4. Publishes with NuGet trusted publishing (OIDC). It does not use a long-lived API key.
5. Creates a GitHub release and verifies the package on NuGet.org.

The repository needs a NUGET_USER secret. NuGet.org needs a trusted publisher policy for this repository and the release.yml workflow. See docs/release.md.

Contributing

See CONTRIBUTING.md. Use develop as the integration branch and master for releases.

License

Business Source License 1.1. See LICENSE.