Zachtbeer Sql Schema Hasher

https://github.com/zachtbeer-labs/sqlschemahasher
Admin
Zachtbeer Sql Schema Hasher is a .NET library that computes deterministic SHA256 hashes of SQL...
Tokens:3,896
Snippets:51
Trust Score:4.3
License:MIT
Update:3 months ago
Show doc for...
Tokens:
Raw
### Install SqlSchemaHasher Package

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Add the SqlSchemaHasher NuGet package to your .NET project.

```bash
dotnet add package zachtbeer.SqlSchemaHasher
```

--------------------------------

### Build, Test, and Pack SqlSchemaHasher

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/CONTRIBUTING.md

Commands to build the solution, run tests, and pack the project for release. Ensure prerequisites like .NET 9 SDK and Docker are installed.

```bash
dotnet build SqlSchemaHasher.sln
```

```bash
dotnet test SqlSchemaHasher.sln
```

```bash
dotnet pack src/zachtbeer.SqlSchemaHasher.csproj -c Release
```

--------------------------------

### Run Project Tests with .NET CLI

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Execute this command to run all project tests using the .NET CLI. This requires the .NET SDK to be installed. Integration tests depend on Docker.

```bash
dotnet test SqlSchemaHasher.sln
```

--------------------------------

### Verify Build Provenance with GitHub CLI

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Use this command to verify the SLSA build provenance attestation for a release package. Ensure you have the GitHub CLI installed and are authenticated.

```bash
gh attestation verify zachtbeer.SqlSchemaHasher.<version>.nupkg \
  --repo zachtbeer-labs/sqlschemahasher \
  --bundle provenance.intoto.jsonl
```

--------------------------------

### Get Schema Hash Asynchronously

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Use the SqlSchemaHash.GetHashAsync method to compute the schema hash for a given database connection string. This is a simple one-liner for quick integration.

```csharp
using zachtbeer.SqlSchemaHasher;

// Simple one-liner to get a schema hash
var hash = await SqlSchemaHash.GetHashAsync("Server=localhost;Database=MyDb;Trusted_Connection=true");
// Returns: "dGhpcyBpcyBhIGJhc2U2NCBoYXNo..."
```

--------------------------------

### Get Database Schema Hash

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Computes a SHA256 hash of the database schema using a connection string. This is the primary method for generating a schema fingerprint.

```csharp
var hash = await SqlSchemaHash.GetHashAsync(connectionString);
```

--------------------------------

### Get Schema Hash with Custom Options

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Computes a schema hash with customizable normalization options. Use this to control how specific schema elements are treated during hashing, such as index names or clustering types.

```csharp
var options = new SchemaHashOptions
{
    NormalizeAutoGeneratedIndexNames = true,  // Strip GUID suffixes from index names
    NormalizeClusteringType = true            // Treat clustered/nonclustered as equivalent
};

var hash = await SqlSchemaHash.GetHashAsync(connectionString, options);
```

--------------------------------

### Verify Environment Consistency

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Compare schema hashes between different environments (e.g., staging and production) to ensure they are identical. This prevents deployment issues caused by schema drift.

```csharp
var prodHash = await SqlSchemaHash.GetHashAsync(prodConnectionString);
var stagingHash = await SqlSchemaHash.GetHashAsync(stagingConnectionString);

if (prodHash != stagingHash)
{
    throw new Exception("Staging schema does not match production!");
}
```

--------------------------------

### Group Databases by Schema Hash

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Iterate through multiple databases, calculate their schema hashes, and group databases that have identical schemas. This can help identify redundant or identically configured databases.

```csharp
var databases = new[] { "Db1", "Db2", "Db3", "Db4" };
var groups = new Dictionary<string, List<string>> ();

foreach (var db in databases)
{
    var connStr = $"Server=localhost;Database={db};Trusted_Connection=true";
    var hash = await SqlSchemaHash.GetHashAsync(connStr);

    if (!groups.ContainsKey(hash))
        groups[hash] = new List<string>();
    groups[hash].Add(db);
}

// Databases with identical schemas are grouped together
```

--------------------------------

### Detect Schema Changes

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Compare schema hashes before and after applying migrations to detect any schema modifications. This helps ensure that migrations are applied correctly.

```csharp
var beforeHash = await SqlSchemaHash.GetHashAsync(connectionString);
// ... apply migration ...
var afterHash = await SqlSchemaHash.GetHashAsync(connectionString);

if (beforeHash != afterHash)
{
    Console.WriteLine("Schema changed!");
}
```

--------------------------------

### SqlSchemaHash.GetHashAsync(connectionString, options)

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Computes and returns a base64-encoded SHA256 hash of the database schema using specified normalization options. This allows for customized schema comparison.

```APIDOC
## SqlSchemaHash.GetHashAsync(connectionString, options)

### Description
Returns a hash with custom normalization options.

### Method
`GetHashAsync`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
#### SchemaHashOptions
- **NormalizeAutoGeneratedIndexNames** (bool) - Optional - If true, strips GUID suffixes from index names.
- **NormalizeClusteringType** (bool) - Optional - If true, treats clustered and nonclustered index types as equivalent.

### Request Example
```csharp
var options = new SchemaHashOptions
{
    NormalizeAutoGeneratedIndexNames = true,  // Strip GUID suffixes from index names
    NormalizeClusteringType = true            // Treat clustered/nonclustered as equivalent
};

var hash = await SqlSchemaHash.GetHashAsync(connectionString, options);
```

### Response
#### Success Response (200)
- **hash** (string) - Base64-encoded SHA256 hash of the database schema with applied normalization.

#### Response Example
```json
{
  "hash": "aBcDeFgHiJkLmNoPqRsTuVwXyZ0123456789abcd"
}
```
```

--------------------------------

### SqlSchemaHash.GetHashAsync(connectionString)

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Computes and returns a base64-encoded SHA256 hash of the entire database schema. This method is read-only and does not modify the target database.

```APIDOC
## SqlSchemaHash.GetHashAsync(connectionString)

### Description
Returns a base64-encoded SHA256 hash of the database schema.

### Method
`GetHashAsync`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```csharp
var hash = await SqlSchemaHash.GetHashAsync(connectionString);
```

### Response
#### Success Response (200)
- **hash** (string) - Base64-encoded SHA256 hash of the database schema.

#### Response Example
```json
{
  "hash": "aBcDeFgHiJkLmNoPqRsTuVwXyZ0123456789abcd"
}
```
```

--------------------------------

### SqlSchemaHash.ExtractSchemaAsync(connectionString)

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Extracts detailed schema metadata from the database. This is useful for inspecting the schema structure without computing a hash.

```APIDOC
## SqlSchemaHash.ExtractSchemaAsync(connectionString)

### Description
Returns detailed schema metadata if you need to inspect the schema structure.

### Method
`ExtractSchemaAsync`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```csharp
var schema = await SqlSchemaHash.ExtractSchemaAsync(connectionString);

Console.WriteLine($"Tables: {schema.Tables.Count}");
Console.WriteLine($"Stored Procedures: {schema.StoredProcedures.Count}");
Console.WriteLine($"User-Defined Types: {schema.UserDefinedTableTypes.Count}");
```

### Response
#### Success Response (200)
- **schema** (object) - An object containing detailed schema metadata.
  - **Tables** (list) - A list of table metadata.
  - **StoredProcedures** (list) - A list of stored procedure metadata.
  - **UserDefinedTableTypes** (list) - A list of user-defined table type metadata.

#### Response Example
```json
{
  "tables": [
    {
      "name": "ExampleTable",
      "columns": [
        {
          "name": "Id",
          "type": "int"
        }
      ]
    }
  ],
  "storedProcedures": [],
  "userDefinedTableTypes": []
}
```
```

--------------------------------

### Compute Hash from Extracted Schema

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Computes a schema hash directly from pre-extracted schema metadata. This is beneficial when you need to compare the same schema against different normalization options without re-extracting the schema.

```csharp
var schema = await SqlSchemaHash.ExtractSchemaAsync(connectionString);

var strictHash = SqlSchemaHash.ComputeHash(schema);
var normalizedHash = SqlSchemaHash.ComputeHash(schema, new SchemaHashOptions
{
    NormalizeAutoGeneratedIndexNames = true
});
```

--------------------------------

### SqlSchemaHash.ComputeHash(schema, options)

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Computes a schema hash directly from pre-extracted schema metadata, optionally applying normalization options. This is useful for comparing schemas programmatically.

```APIDOC
## SqlSchemaHash.ComputeHash(schema, options)

### Description
Computes a hash from pre-extracted schema metadata. Useful when comparing the same schema with different normalization options.

### Method
`ComputeHash`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
#### Schema Object
- **schema** (object) - Required - The pre-extracted schema metadata object.

#### SchemaHashOptions
- **NormalizeAutoGeneratedIndexNames** (bool) - Optional - If true, strips GUID suffixes from index names.
- **NormalizeClusteringType** (bool) - Optional - If true, treats clustered and nonclustered index types as equivalent.

### Request Example
```csharp
var schema = await SqlSchemaHash.ExtractSchemaAsync(connectionString);

var strictHash = SqlSchemaHash.ComputeHash(schema);
var normalizedHash = SqlSchemaHash.ComputeHash(schema, new SchemaHashOptions
{
    NormalizeAutoGeneratedIndexNames = true
});
```

### Response
#### Success Response (200)
- **hash** (string) - Base64-encoded SHA256 hash of the provided schema metadata.

#### Response Example
```json
{
  "hash": "aBcDeFgHiJkLmNoPqRsTuVwXyZ0123456789abcd"
}
```
```

--------------------------------

### Hash Only Specific Schema

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Use SchemaFilter to limit hashing to a specific schema, ignoring others. This is useful when you only want to track changes within a particular schema.

```csharp
var options = new SchemaHashOptions { SchemaFilter = "dbo" };
var hash = await SqlSchemaHash.GetHashAsync(connectionString, options);
```

--------------------------------

### Extract Detailed Schema Metadata

Source: https://github.com/zachtbeer-labs/sqlschemahasher/blob/main/README.md

Extracts detailed schema metadata from a database. This is useful for inspecting the schema structure programmatically, such as counting tables, stored procedures, or user-defined types.

```csharp
var schema = await SqlSchemaHash.ExtractSchemaAsync(connectionString);

Console.WriteLine($"Tables: {schema.Tables.Count}");
Console.WriteLine($"Stored Procedures: {schema.StoredProcedures.Count}");
Console.WriteLine($"User-Defined Types: {schema.UserDefinedTableTypes.Count}");
```
Tokens:
Raw