verifytests / verify.sqlserver

Extends Verify to allow verification of SqlServer bits.

Verify.SqlServer

Extends Verify to allow verification of SqlServer bits.

See Milestones for release notes.

NuGet package

https://nuget.org/packages/Verify.SqlServer/

Usage

[ModuleInitializer]
public static void Init() =>
    VerifySqlServer.Initialize();

^{snippet source | anchor}

SqlServer Schema

This test:

await Verify(connection);

^{snippet source | anchor}

Will result in the following verified file:

## Tables

### MyOtherTable

```sql
CREATE TABLE [dbo].[MyOtherTable](
	[Value] [int] NULL
) ON [PRIMARY]
```

### MyTable

```sql
CREATE TABLE [dbo].[MyTable](
	[Value] [int] NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable]
(
	[Value] ASC
) ON [PRIMARY]

CREATE TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);

ALTER TABLE [dbo].[MyTable] ENABLE TRIGGER [MyTrigger]
```

## Views

### MyView

```sql
CREATE VIEW MyView
AS
  SELECT Value
  FROM MyTable
  WHERE (Value > 10);
```

## StoredProcedures

### MyProcedure

```sql
CREATE PROCEDURE MyProcedure
AS
BEGIN
  SET NOCOUNT ON;
  SELECT Value
  FROM MyTable
  WHERE (Value > 10);
END;
```

## UserDefinedFunctions

### MyFunction

```sql
CREATE FUNCTION MyFunction(
  @quantity INT,
  @list_price DEC(10,2),
  @discount DEC(4,2)
)
RETURNS DEC(10,2)
AS
BEGIN
    RETURN @quantity * @list_price * (1 - @discount);
END;
```

## Synonyms

### synonym1

```sql
CREATE SYNONYM [dbo].[synonym1] FOR [MyTable]
```

Object types to include

await Verify(connection)
    // include only tables and views
    .SchemaIncludes(DbObjects.Tables | DbObjects.Views);

^{snippet source | anchor}

Available values:

namespace VerifyTests.SqlServer;

[Flags]
public enum DbObjects
{
    StoredProcedures = 1,
    Synonyms = 2,
    Tables = 4,
    UserDefinedFunctions = 8,
    Views = 16,
    All = StoredProcedures | Synonyms | Tables | UserDefinedFunctions | Views
}

^{snippet source | anchor}

Filtering

Objects can be dynamically filtered:

await Verify(connection)
    // include tables & views, or named MyTrigger
    .SchemaFilter(
        _ => _ is TableViewBase ||
             _.Name == "MyTrigger");

^{snippet source | anchor}

Recording

Recording allows all commands executed to be captured and then (optionally) verified.

Call SqlRecording.StartRecording():

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
Recording.Start();
await using var command = connection.CreateCommand();
command.CommandText = "select Value from MyTable";
var value = await command.ExecuteScalarAsync();
await Verify(value!);

^{snippet source | anchor}

Will result in the following verified file:

{
  target: 42,
  sql: {
    Text: select Value from MyTable,
    HasTransaction: false
  }
}

^{snippet source | anchor}

Sql entries can be explicitly read using SqlRecording.FinishRecording, optionally filtered, and passed to Verify:

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
Recording.Start();
await using var command = connection.CreateCommand();
command.CommandText = "select Value from MyTable";
var value = await command.ExecuteScalarAsync();

await using var errorCommand = connection.CreateCommand();
errorCommand.CommandText = "select Value from BadTable";
try
{
    await errorCommand.ExecuteScalarAsync();
}
catch
{
}

var entries = Recording
    .Stop()
    .Select(_ => _.Data);
//Optionally filter results
await Verify(
    new
    {
        value,
        sqlEntries = entries
    });

^{snippet source | anchor}

Interpreting recording results

Recording results can be interpreted in a a variety of ways:

var entries = Recording.Stop();

// all sql entries via key
var sqlEntries = entries
    .Where(_ => _.Name == "sql")
    .Select(_ => _.Data);

// successful Commands via Type
var sqlCommandsViaType = entries
    .Select(_ => _.Data)
    .OfType<SqlCommand>();

// failed Commands via Type
var sqlErrorsViaType = entries
    .Select(_ => _.Data)
    .OfType<ErrorEntry>();

^{snippet source | anchor}

Icon

Database designed by Creative Stall from The Noun Project.

verify.sqlserver's Issues

Microsoft.Data.SqlClient Version 3.0.0

Suggestion: SQL Server specific attributes

Is the feature request related to a problem

Not a problem, just an increment.

Describe the solution

Since this package is SQL Server specific, would be nice to see SQL Server specific fields such as SqlParameter.SqlDbType. Additionally, could be nice to also add other DbParameter attributes, such as Size, Precision and/or Scale. Personally, the SqlDbType would help me notice if a command is using NVarchar instead of Varchar. This is achievable through the DbType AnsiString/String`, but I think it still would be useful to have it, maybe by having fields being serialized by default instead of hand-picking them?

Describe alternatives considered

Looking at the source, looks like it can be achieved by implementing my own diagnostic listener and grabbing the fields I want. Also DbCommand.DbType kind of gives me this information.

Additional context

I decided to open an issue instead of a pull request just to hear your thoughts on it.

[Suggestion] Allowing filtering based on schema

Is the feature request related to a problem

Currently, we run our tests in 2 modes; with one of them being a shared database where the schemas are postfixed with a guid to avoid collision.

One of the tests we run is to verify the schema hasn't changed i.e. we run a Verify(sqlConnection). This, however, runs across the entire database. I was going to use SchemaSettings(includeItem: (x) but x is the table name :( which is the same for us (only schema is different) so we can't filter them out.

Describe the solution

I'm happy to create a PR to fix this but not sure what the best API approach is here, I'm thinking we change the https://github.com/VerifyTests/Verify.SqlServer/blob/main/src/Verify.SqlServer/SchemaValidation/SchemaSettings.cs file to something like this;

// New constructor
class SchemaSettings(
    bool storedProcedures,
    bool tables,
    bool views,
    bool userDefinedFunctions,
    bool synonyms,
    Func<NamedSmoObject, bool> includeItem)
{
    public bool StoredProcedures { get; } = storedProcedures;
    public bool Tables { get; } = tables;
    public bool Views { get; } = views;
    public bool Synonyms { get; } = synonyms;
    public bool UserDefinedFunctions { get; } = userDefinedFunctions;
    public Func<NamedSmoObject, bool> IncludeItem { get; } = includeItem;

    SchemaSettings(
      bool storedProcedures,
      bool tables,
      bool views,
      bool userDefinedFunctions,
      bool synonyms,
      Func<string, bool> includeItem) : this(storedProcedures, tables, views, userDefinedFunctions, synonyms, 
 // putting on new line to make clear, this just maps it to the "new" function
 ((item) => includeItem(item.Name))) {}
}

Then in my case I can just do a type-cast check on NamedSmoObject to either Table/View/others and extract schema that way.

This should be pretty transparent to the user and won't break any existing code.

Describe alternatives considered

I'm not too sure but open to alternatives.

Additional context

None I can think of.