This article is all about declarative database development using Azure Data Studio for both beginners and professionals who are new to it.

Additionally, some professional life tips in the context of the topic are also shared. The importance of declarative database development over its counterparts can also be fairly understood by going through this article.

Declarative database development

Let us get a good understanding of declarative database development first.

What is declarative database development?

Declarative database development is a style or approach of developing databases in which the developers only maintain the final state of the database.

In other words, declarative database development is a way of creating and managing database objects where the developer is only concerned with the most recent definition of the object.

Is there any simple way to define declarative database development?

Yes, we can simply say that it is the style of database development in which you only design a database object and directly change it to meet new requirements without going through the laborious exercise of writing the change scripts since they will be handled by the declarative database development tool.

What is State-Based Development? Another name of declarative database development is state-based development because you are only concerned with the state of the object and not how to change it from one state to another.

What are tools that support declarative database development?

Certain tools or add-ons can help you to adopt declarative database development including the following:

1. SQL Server Data Tools (SSDT) in Visual Studio
2. SQL Database Projects Extension in Azure Data Tools

What are the benefits of declarative database development?

There are many benefits of declarative database development including the following:

1. The database is easy to build and manage
2. The actual form of the database as a single source of truth becomes available
3. You can quickly make changes to the database without worrying to write change scripts
4. The database can be comfortably and successfully centrally managed and modified
5. It is easy to keep track of database versions in the form of snapshots
6. Multiple target platforms can be easily supported and built

Declarative database development vs Imperative database development?

Imperative database development is the traditional way of managing a database in the form of scripts right from the start. However, a lot of effort is required to manage the scripts once the database grows with time and gets many modification hits due to dynamically changing business requirements.

Unlike declarative database development, there is no portable structure or template of the database for reference in imperative database development except what (shape of the database) you see on the target servers such as dev, test, or production.

So, imperative development is totally script-based where you not only define the objects but also write scripts to change the objects while in the state-based development you only keep the overall final version of the database objects and maintain it.

Declarative Database Development in Azure Data Studio

Let us get some hands-on experience of declarative database development in Azure Data Studio. Do remember that declarative database development is possible through SQL Database Projects.

Prerequisites

This article assumes the following:

1. You are familiar with relational database concepts
2. You have a local or remote SQL instance (SQL Server) ready for use
3. Azure Data Studio along with SQL Server Database Projects and SQL Server Schema Comparison extensions have been installed on your machine
4. You can comfortably write and run T-SQL scripts to build database objects

The following articles help fulfil some of the above requirements:

1. Starting your journey with Azure Data Studio (sqlshack.com)
2. Two ways to build SQL Database Projects in Azure Data Studio (sqlshack.com)

Professional Life Scenario

To understand how declarative database development works we are going to see it in action in the light of an assumed professional life scenario.

Let us suppose you are a database developer who has been hired by a car spare parts company that is struggling to manage their spare parts data and has asked you to instantly build a database for them.

Now, you have to keep in mind that new requirements may be on their way very soon, so it is going to be a slightly more active database development scenario.

Identifying business requirements and Propose Solution:

After gathering some more information you have been able to identify the following requirements:

1. A database for storing information about spare parts is required
2. Currently, one table that holds the information is fine
3. All the parts must be identifiable by a distinct code or id
4. All the parts names must be stored in the database

You come up with the following proposed solution:

1. CarPartsDemo database needs to be created
2. CarPart table in the database needs to be created with the following fields:
   1. CarPartId
   2. Name

You decide to use declarative database development to design the database solution.

Set up CarPartsDemo Database Project in Azure Data Studio

Open Azure Data Studio and switch to the Projects sidebar. Click Create new to create a fresh SQL database project.

Name the project as CarPartsDemo following choosing a location for the workspace and the project to be stored and finally click Create button:

Create CarPart table

In Projects, right-click on the CarParsDemo and click Add Table to add table name CarPart using the following T-SQL script:

Press the CTRL+S shortcut key to save the project.

This is illustrated as follows:

Build and deploy changes to create a target database

Right-click on the project and click Build to build the project:

Once you see a successful build please prepare to publish the project. We have decided to publish the project to a debug database on the local instance of SQL Server assuming this the target environment.

Right-click on the project CarPartsDemo and click Publish followed by configuring the publishing:

Check the created database

Now switch to the Connections and refresh the local server instance and locate the newly created database that appeared by project publishing:

Now we can see how quickly we have created a database from the database project using declarative database development in Azure Data Studio. Let us view the contents of the table by running the following query against the CarPartsDemo database:

The results are shown below:

You might also have noticed we have a single source of truth in the form of the project now.

Requirements to Add Detail and Stock Column

Now as soon as you have got the database ready you receive new requirements to add the Detail and Stock column in the database. Now we are going to make this change in the main design or template of the database we have kept in the form of a database project.

Add Detail and Stock Column to the Project

Now switch to the Projects and click the CarPart table under the Project to view its contents on the right side in the code window and just add the two required columns in the same table create script:

This is illustrated as follows:

Do save the table to ensure that your changes have been committed.

Build and Republish the Project

Build and publish the project to make sure that the new changes have been sent across to the target database.

View the database after publishing the project changes

Let us check the database after we have made some more changes to it by navigating to the database after switching to the Connections and expanding Databases in the local server node in Azure Data Studio.

Run the following query against the CarPartsDemo database:

The output is as follows:

Congratulations! You have successfully learned the basics of declarative database development in Azure Data Studio along with its implementation in a simple scenario.

A Word of Advice

Although declarative database development, in the beginning, does seem to be slightly steep to adopt due to its state-based nature once you get used to it, it gets very smooth and highly flexible from many other points of view including database changes.

Another bigger benefit of maintaining SQL Database Project through declarative database development is to be able to instantly build and manage Dev, Test, and QA database environments which is by far the most widely accepted database development practice.

Summary

In this article, we learned the basics of declarative database development followed by understanding its differences and advantages against imperative development, and finally implementing this approach using Azure Data Studio to get practical skills and knowledge.

Table of contents

• Author
• Recent Posts

Haroon Ashraf

Haroon’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.

He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.

He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.

His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).

He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.

View all posts by Haroon Ashraf

Latest posts by Haroon Ashraf (see all)

• SQL Machine Learning in simple words - May 15, 2023
• MySQL Cluster in simple words - February 23, 2023
• Common use cases of SQL SELECT Distinct - February 2, 2023