Streamline your SQL development with database projects and Git

Managing SQL databases efficiently is essential for ensuring smooth operations and seamless deployments. However, the traditional methods of database development often lack the agility and robustness required to keep up with evolving project requirements.

Have you ever found yourself grappling with the challenge of tracking changes in your SQL schema effectively? Hello Visual Studio database projects – a game-changer for developers seeking a streamlined approach to SQL database development.

First things first

Let’s get you familiar with some concepts. What exactly is a Visual Studio Database Project about? A database project consists of a set of SQL objects resulting in the schema for a single database, such as tables, stored procedures, views or functions. This schema can then easily be tracked using version control like Git. This gives your SQL projects the opportunity to track changes, branch out for different features and many more functionalities that come with a Git integration. This approach is directed to people or teams that prefer minimal change to the current development process.

There are multiple options for maintaining Database Projects such as Azure Data Studio and Visual Studio. In this blog, we use Azure Data Studio as this is a more lightweight tool to get started. Azure Data Studio is a brother of Visual Studio Code and is focused on working with data, database development and management. The database we use is a SQL Server Database on Azure. For our Git repository, we’ll rely on Git in Azure Repos (DevOps). Before starting this tutorial, make sure the following tasks have been completed:

• A project and repository in Azure DevOps exist.

• A SQL-server instance on Azure with or without an existing database.

  • Make sure the field Allow Azure services and resources to access this server is checked. This can be found on azure for the SQL server under Security -> Networking -> Exceptions or be checked when creating the server.

• The SQL Database Projects extension is installed in Azure Data Studio.

• The right Azure account is logged in in Data Studio.

• Download the project files on the Plainsight Pro GitHub

Connect to Azure SQL database

To kick off, we’ll set up a connection to the database in Azure Data Studio. In the connections tab, located on the bottom of your screen, there is a list with all the Azure SQL servers. Select the right server and click on the connect icon. Verify that the connection details are correct and click connect. The database is now added to the connections.

Create a database project

Go to the database projects tab and click create new. Choose Azure SQL database and give it a name. Now the database project has been created and can be viewed in the database projects tab.

Import database objects & data to Azure SQL database

If you already have data or want to use other data, this step can be skipped. Before continuing, make sure that the SQL Server Dacpac extension is installed, this will allow us to create the demo database with a simple popup. The data we will use concerns Formula 1 races. This data is publicly available on https://ergast.com/mrd/db/. In a later blog we will see how we can use Data Factory to add new tables to our database. For now, we are just going to import the tables and data into our database. Go back to Data Studio and right click your database in the connections tab. Click on Data-tier Application Wizard.

Here we have two main options to deploy/import: a dacpac file or a bacpac file. A dacpac file contains the database model (tables, views, ...) but no data. The bacpac file also includes the data. We will choose the bacpac file because we also want to import the data. Select Import Bacpac and click next. Enter the file location of the .bacpac file in the project files you unzipped and give the database a name. Click next, verify the details and click import. When the process is finished, a database with four tables should be created.

💡 if you intend to keep this database running for a while, change the compute tier to serverless in the Azure portal. This can save quite a bit of money

Update database project

Now that we have a database that is filled with data, it is time to load the schema into the database project. Go back to the database projects tab, right click on the project and choose update project from database.

Select the right server and database, verify the details and click update. The schema compare window should appear. Here you can see the changes made by updating the project from the database. Verify these changes and click apply.

The changes will now be loaded into the project, and this will be built. The build gives information on warnings and errors and creates files that can be used to update other databases (later, we will use CI/CD to deploy to other databases so we will not use these files). If we now look at our database project, we can see that folders have appeared containing the database schemas. These folders contain the SQL files for the tables in the database.

Add database project to source control

Now that our project contains the database schema, we will connect these changes with our Git repository on azure repos. Go to the Source Control tab, click Initialize a repository and choose the right project folder. Now the local Git repository has been created.

Add the .gitignore file to the project folder that you downloaded from GitHub. You can do this quickly by going to the explorer tab, right clicking on the folder and choosing Create File. Name the file .gitignore. And enter the following the following text in the file and save it:

bin/

obj/

The explorer should now look like this:

Adding the gitignore file makes sure local files generated by the build are not uploaded to Git as these are not needed in the repository. Now go back to the Version Control tab, enter a commit message click Commit all changes.

Next, go to your Azure Repo on the files tab and copy the HTTPS link.

Go back to Data Studio in the Version Control tab. Click on the three dots next to source control, go to remote and click add remote.

Give the remote a name and paste the HTTPS link. Adding a remote connects your local instance of Git to Azure Repos. You may need to log in with your Microsoft account. To push these changes to the remote, click on publish branch. The database project should now be uploaded to Azure Repos.

Our SQL database has now been transformed to a database project that is connected using Git to our Azure Repo.

Conclusion

In wrapping up, we've went on a transformative journey in SQL development, showcasing the potent combination of database projects and version control. We've explored the seamless setup process using Azure Data Studio, the creation and management of database projects, efficient data importation and synchronization of schema changes via Git.

By embracing these practices, you've not only learned how to elevate your SQL development endeavors but also how to fortify team collaboration and ensure the reliability of database deployments.

As we journey forward, we invite you to stay tuned for upcoming posts where we delve into practical use cases and advanced CI/CD integration strategies.

Anxious to know what Plainsight could mean for you?

• Read more “About us”

• Consider “your career at Plainsight 🚀”