Forem: Drew Skwiers-Koballa

Code analysis with SQL projects

Drew Skwiers-Koballa — Wed, 22 Jan 2025 00:09:08 +0000

⏸️ Before you get started

In a previous article, "SQL database projects intro", we created a SQL project with 2 tables and a stored procedure.

dbo.Product (table)
dbo.ProductOrder (table)
dbo.GetProductInfo (stored procedure)

SQL database projects are built on top of the .NET SDK - you don't need to know .NET development to use them - but you do need the .NET 8 or higher SDK installed.

Want to quickly check if you have the .NET SDK installed?

dotnet --list-sdks

▶️ With the .NET SDK installed and a sample project created like the one from the intro article, you're ready to go.

📝 Create a new view

Add a new view [dbo].[All Products] in a .sql file (dbo.AllProducts.sql) in a new folder Views under project folder.

CREATE VIEW [dbo].[All Products] AS
SELECT *
FROM dbo.Product

Build the SQL project, verifying the object references and our SQL syntax is correct.

dotnet build

🛠️ Check our code quality

While SELECT * seems like a nice shortcut to reference all potential columns, it comes with risks of schema drift, inadvertently clogging up I/O with extra data, and more. In general - your code is more fragile in ways that aren't easily observed.

This is where SQL code analysis comes in handy, because it will check the database model created from our SQL project for detectable issues. We can run code analysis on our SQL project as a 1-off analysis with an extra property on the project build /p:RunSqlCodeAnalysis=true.

dotnet build /p:RunSqlCodeAnalysis=true

Now instead of the build succeeding with no warnings, we have two warnings:

warning SR0011: Microsoft.Rules.Data : Object name(All Products) contains special characters.
warning SR0001 Microsoft.Rules.Data : The shape of the result set produced by a SELECT * statement will change if the underlying table or view structure changes.

Getting ongoing analysis of our SQL code quality provides a quick feedback loop even before we send the code into a git repository. We can enable code analysis to run by default on every project build by incorporating that property into the project file itself with <RunSqlCodeAnalysis>. A sample project file with the code analysis setting added would look like:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="1.0.0-rc1" />
  <PropertyGroup>
    <Name>ProductsTutorial</Name
<DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <RunSqlCodeAnalysis>true</RunSqlCodeAnalysis>
  </PropertyGroup>
</Project>

🚧 Choose errors and warnings

Some code issues are worse than others - they might have a high impact or be difficult to change in the future. For these kinds of issues we can elevate the warning to an error that will cause our build to fail.

In our current project we're getting a warning that the object name [dbo].[All Products] contains a special character (the space). Before I fix this issue I want to make sure that these kinds of mistakes don't get ignored in the future.

The project property <SqlCodeAnalysisRules> can be used to exclude a rule completely or elevate a rule from a warning to an error. When choosing errors and warnings, the project property is a semicolon separated list with three settings available for each rule:

elevate a rule from warning to error with +!
no entry for the rule results in the rule throwing a warning
demote a rule to no warning with - (disable)

The setting per rule is prefixed to the rule name. Let's turn the object name warning into an error message since names can be more difficult to change down the road. Right below the property <RunSqlCodeAnalysis> in our project file we add:

<SqlCodeAnalysisRules>+!Microsoft.Rules.Data.SR0011;</SqlCodeAnalysisRules>

If we build our project again using dotnet build we recieve one error and one warning:

error SR0011: Microsoft.Rules.Data : Object name(All Products) contains special characters.
warning SR0001 Microsoft.Rules.Data : The shape of the result set produced by a SELECT * statement will change if the underlying table or view structure changes.

⚠️ Note that when the build fails, the build artifact (.dacpac file) isn't updated. You need a successful build for your database model to be ready for deployment.

✏️ Fix the view name

Using the information in the error message, including the filename and line number, we'll fix the build error resulting from our modification to the code analysis warnings and errors. We should edit the view definition in dbo.AllProducts.sql to have a name without spaces, like [dbo].[AllProducts].

Once the view name is fixed, verify that your project now builds with a single warning message for SR0001 (SELECT *).

📍 Supress a specific code analysis finding

Code analysis rules provide a structure for evaluating your code, but at the end of the day its your project and you may have a reason for contradicting best practices. Instead of completely disabling a rule with the <SqlCodeAnalysisRules> project property, we can use the StaticCodeAnalysis.SuppressMessages.xml file to suppress a specific rule for a specific file.

Create a new file next to the SQL project file named StaticCodeAnalysis.SuppressMessages.xml. In this file we add an entry for the view file and the SELECT * rule.

<?xml version="1.0" encoding="utf-8" ?>
<StaticCodeAnalysis version="2" xmlns="urn:Microsoft.Data.Tools.Schema.StaticCodeAnalysis">
  <SuppressedFile FilePath="Views/dbo.AllProducts.sql">
    <SuppressedRule Category="Microsoft.Rules.Data" RuleId="SR0001" />
  </SuppressedFile>
</StaticCodeAnalysis>

If we use SELECT * in other files of our project the warning will appear for those instances, but we've specified that SELECT * is allowed in our view dbo.AllProducts.

Running dotnet build with this message suppression XML file in the project will result in no warnings being shown for our project now.

🧩 (optional) Incorporate additional code analysis rules

The small set of code analysis rules included with SQL projects are helpful, but there are quite a few community projects extending the code analysis rules. Incorporating these custom code analysis rulesets into your SQL project code analysis is quickly accomplished through package references.

A package reference is an entry in the SQL project file to a NuGet feed, like the community TSQLSmells on NuGet.org - https://www.nuget.org/packages/ErikEJ.DacFX.TSQLSmellSCA

We don't even need to manualy edit the .sqlproj because the .NET tooling has a terminal command to add packages:

dotnet add package ErikEJ.DacFX.TSQLSmellSCA --version 1.1.2

When we run dotnet build again, the build succeeds but we now have two new warnings!

warning SML005: Smells : Avoid use of 'Select *'
warning SML030: Smells : Include SET NOCOUNT ON inside stored procedures

While adding the custom rules to your SQL project takes just a few moments, the developers that work on the rulesets have invested countless hours of their time and share with the community. Remember to contribute to the projects that benefit your work as you are able.

🚀 (optional) Add a GitHub continuous integration (CI) pipeline

If we've pushed our project to a GitHub repository, we can utilize GitHub Actions to check our project for code analysis findings. This is a great way to automate watching for code smells in databases.

The pipeline definitions are stored in a .github/workflows folder. Create a new file code-analysis.yml in the .github/workflows folder. An example pipeline definition is shown below to get you started, but your branching strategy and code review process may vary.

# .github/workflows/code-analysis.yml
name: Run code analysis on SQL project
on:
  pull_request:
    branches:
    - main

jobs:
  build-with-analysis:
    runs-on: ubuntu-latest

    steps:
    - uses: actions/checkout@v4
    - name: Setup .NET
      uses: actions/setup-dotnet@v4
      with:
        dotnet-version: 8.0.x

    - name: Build with code analysis
      run: dotnet build -warnaserror ProductsTutorial.sqlproj /p:RunSqlCodeAnalysis=true

In this workflow we've added yet another flag to dotnet build so that all code analysis warnings are elevated to errors. This is useful when you are using code analysis as an optional check. If you're using code analysis as a required check (highly suggested) selecting specific rules to throw errors instead via the SqlCodeAnalysisRules project property is a better approach.

Guess what!? Your code analysis CI check is also doing the basic model validation for the database - ensuring you're not referencing objects that don't exist and the SQL syntax is valid. That's a win-win.

📚 Learn more

The tooling ecosystem around SQL projects includes VS Code, Visual Studio, and the SqlPackage CLI. Here are some additional resources to learn more about SQL projects for development and deployment:

DEV.TO posts

SQL database projects intro

SQL database projects intro

Drew Skwiers-Koballa — Fri, 13 Dec 2024 22:42:21 +0000

SQL projects are a development capability for SQL Server and Azure SQL where your database objects are stored as code with integrations for CI/CD capabilities like code quality checks and dynamically calculated deployments. This article walks/runs through creating a new SQL project and deploying it to a database from the command line and any text editor. The tooling ecosystem around SQL projects includes VS Code, Visual Studio, and the SqlPackage CLI. Learn more about all of this at the Microsoft.Build.Sql projects documentation.

⏸️ Before you get started

SQL database projects are built on top of the .NET SDK - you don't need to know .NET development to use them - but you do need the .NET 8 or higher SDK installed.

Want to quickly check if you have the .NET SDK installed?

dotnet --list-sdks

The SqlPackage CLI is an indespensible part of database deployments because it will automatically calculate the actual scripts necessary to modify a new or existing database to match the contents of your SQL project. SqlPackage is available for Windows, macOS, and Linux all as a .NET tool, so you can install it quickly in a single command:

dotnet tool install -g microsoft.sqlpackage

▶️ With the .NET SDK and the SqlPackage CLI both installed, you're ready to go.

📁 Create a SQL project

We'll use the Microsoft.Build.Sql.Templates NuGet package to get started with a new SQL project.

dotnet new install Microsoft.Build.Sql.Templates

Create a new SQL project using the sqlproj template:

dotnet new sqlproj -n ProductsTutorial

📝 Add a database object

Add a new table dbo.Product in a .sql file (dbo.Product.sql) alongside the project file.

CREATE TABLE [dbo].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ProductName] [nvarchar](200) NOT NULL
);

🛠️ Build the project

The SQL project (.sqlproj) is the source code for our database definition, and we would use a build artifact to deploy to a database instance (such as a local container or a SQL Server).
Build the project to create a .dacpac file.

dotnet build

✚ Add more database objects

Add a stored procedure

We're going to add a stored procedure to the project. Create a new file dbo.GetProductInfo.sql alongside the project file.

CREATE PROCEDURE [dbo].[GetProductInfo]
    @ProductID INT
AS
BEGIN
    SELECT P.ProductID
        , P.ProductName
        , SUM(PO.Quantity) AS TotalQuantity
    FROM dbo.Product P
    LEFT JOIN dbo.ProductOrder PO ON PO.ProductID = P.ProductID
    WHERE P.ProductID = @ProductID
    GROUP BY P.ProductID, P.ProductName
END

🚧 We've added a stored procedure that references the dbo.Product table we created earlier in addition to a table we haven't created yet (dbo.ProductOrder). Running dotnet build at this point returns build warnings about unresolved references. This is good - we want our development tooling to help us create valid SQL objects.

Adding the missing table

Create a new file dbo.ProductOrder.sql alongside the project file for the missing table dbo.ProductOrder.

CREATE TABLE [dbo].[ProductOrder](
    [ProductOrderID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ProductID] [int] NOT NULL FOREIGN KEY REFERENCES dbo.Product(ProductID),
    [Quantity] [int] NOT NULL
);

Organize the project

We've been creating all our files in the same folder with the .sqlproj file. This works, but it's not the best way to organize a project. A common approach is to group objects by schema and/or object type.

Create a new folder Tables and move the dbo.Product.sql and dbo.ProductOrder.sql files into it. Create a new folder StoredProcedures and move the dbo.GetProductInfo.sql file into it.

When you run dotnet build again, the build still succeeds. The build process automatically finds the .sql files in the project folder and subfolders.

💡 Use your existing databases

If you have an existing database, the command line tool SqlPackage can extract object definitions for the whole database.

sqlpackage /Action:Extract /TargetFile:newfolderforfiles /SourceConnectionString:"Data Source=yourservername;Initial Catalog=yourdatabasename;Authentication=Active Directory Interactive" /p:ExtractTarget=SchemaObjectType

🛳️ Publish a SQL project

Publish a SQL project to a database using the SqlPackage publish command. Learn more about the publish command in the SqlPackage documentation, where additional examples and details on the parameters are available.

# example publish to Azure SQL Database using SQL authentication and a connection string
SqlPackage /Action:Publish /SourceFile:"bin\Debug\ProductsTutorial.dacpac" \
    /TargetConnectionString:"Server=tcp:{yourserver}.database.windows.net,1433;Initial Catalog=ProductsTutorial;User ID=sqladmin;Password={your_password};Encrypt=True;TrustServerCertificate=False;"

When the publish command completes, you can connect to the database and view the objects that were created.

🧩 (optional) Update the project and publish again

We can update the project by adding a new column to the dbo.Product table. Open the existing dbo.Product.sql file and add column for ProductDescription.

The resulting file is:

CREATE TABLE [dbo].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [ProductName] [nvarchar](200) NOT NULL,
    [ProductDescription] [nvarchar](800) NULL
);

When we run dotnet build again, the build succeeds and the ProductsTutorial.dacpac file is updated.

🪄 We can publish the updated project to the database using the same publish command as before. Running the publish command again will update the existing database schema automatically, there's no need to remove the database first or track the changes manually.

(optional) Check the project into source control

One of the benefits of using SQL projects is that the database schema is stored in source control. This makes it easy to collaborate and to deploy changes using CI/CD practices.

When we build the project, the .dacpac file and other artifacts are created in the bin and obj folders. We can keep these folders out of source control by adding them to the .gitignore file or by using the default .gitignore for .NET.

The default .gitignore for .NET projects can be created by running the following command in the project folder:

dotnet new gitignore

Once you've committed the project to source control, you can push it to a GitHub/remote repository. You can do this from your IDE or from the command line using the following commands:

Commit the project to source control

git init
git add .
git commit -m "new project"

Push the project to a GitHub repository

git remote add origin https://github.com/{yourusername}/{yourrepository}.git
git branch -M main
git push -u origin main

🚀 (optional) Add a GitHub build/deploy pipeline

If we've pushed our project to a GitHub repository, we can utilize GitHub Actions to build and deploy our project to a database. This is a great way to automate the deployment of database changes.

The pipeline definitions are stored in a .github/workflows folder. Create a new file build-and-deploy.yml in the .github/workflows folder. We'll use sql-action to handle building the SQL project and publishing it to a database. It is recommended you check out the sql-action documentation for more information on how to use it, but an example pipeline definition is shown below to get you started.

The example pipeline definition below uses a GitHub secret to store the connection string as SQL_CONNECTION_STRING.

# .github/workflows/build-and-deploy.yml
name: Build and deploy SQL project
on: [push]

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v3
    - uses: azure/sql-action@v2.3
      with:        
        connection-string: ${{ secrets.SQL_CONNECTION_STRING }}
        path: './ProductsTutorial.sqlproj'
        action: 'publish'

📚 Learn more

Wow, you made it to the end! Here are some additional resources to learn more about SQL projects for development and deployment:

DEV.TO posts

Code analysis with SQL projects

Connecting to SQL Server on macOS from a Windows VM

Drew Skwiers-Koballa — Mon, 15 Jan 2024 20:00:00 +0000

There are a few instances where you want to run SQL Server on macOS and interact with it from a windows VM. As someone that works within the world of SQL Server/Azure SQL, the use of SQL Server Management Studio (SSMS) comes to mind immediately - for the tasks you can't complete in Azure Data Studio on macOS directly. Another adjacent (and generally unfortunate scenario) is when work with .NET Framework is required, and for that, you need Windows. This article will step through the network information needed to connect from a Windows VM on macOS to a SQL Server container.

A few basics

VM networking

Shared networking is the recommended (and default) network setting in Parallels. In this network layout, your macOS machine is automatically sharing its network connection with the VM and this is usually sufficient for VM use. The other insight we need to connect between macOS and the Windows VM is knowledge of the virtual subnet (network) between the macOS host and the VM.

The fastest way I've found to get my network info for the VM is within the VM itself, using ipconfig from Windows terminal. From its output, I note the VM's IP address and its gateway address. On my machine, the VM has the address 10.211.55.3 and the gateway is 10.211.55.1.

Conceptually, our macOS machine is playing 2 roles in the VM network - both the gateway and as a participant in the network. The SQL Server container running on our macOS host is available to the Windows VM at the IP address assigned to our host as a participant in the network.

Since the gateway address is x.x.x.1 and the VM is assigned x.x.x.3, I'm going to make an educated guess that my host machine is available at x.x.x.2. All that IP address information I gathered by running ipconfig from the Windows VM.

Test the connection

Using any SQL client application on the Windows VM will allow me to test the connectivity to the SQL container. This moment is a good one to fire up SSMS in the VM. I'll use the address 10.211.55.2 as my SQL server address.

If you encounter difficulties gaining connectivity, you may need to adjust firewall settings in macOS. If your firewall is enabled, use the Options button to allow specific connections.

Recap

While there's a lot of the development process that you can accomplish from just about any machine, there are still some specific instances where you need that Windows VM. With the default shared network setting, your macOS host has an address assigned to it that you can determine and use to connect from the Windows VM to a container running on macOS. In the setup on my machine, the Windows VM was assigned 10.211.55.3 and the macOS host (and SQL Server container) was available at 10.211.55.2.