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:
Top comments (0)