How do you design and deploy your Postgres code? Some of you probably define the data model directly in your application code. Deploying a new build of your app then creates and updates the objects in your Postgres database. This approach can be pretty quick and convenient, especially when you’re testing. But it has its drawbacks in production, one of which is making coordination with your DBA and other app teams more difficult.
Alternatively, you could create database-specific deployment scripts that are separate from your application code. This approach provides more flexibility and control over what happens to your Postgres database and when. For example, separating app and database code can help make your version upgrades cleaner. You can even have instances of your app run against different database versions as needed. If you like this approach to organizing code, Azure Data Studio wants to give you a helping hand.
Did you know there is a Postgres extension for Azure Data Studio?
Last year we rolled out the Postgres extension to Azure Data Studio. The integration of Postgres with Azure Data Studio is super useful if Postgres is your jam and if you prefer a graphical editor over command line options like psql. (Psql as you probably know is the most popular interactive terminal for Postgres; in fact, some users refer to psql in Postgres as their best friend).
More and more of our Postgres developers (well, the ones who aren’t in the cli-only camp) now use Azure Data Studio in their day to day work and in their demos. And just last month, we also rolled out an update to the Postgres extension in Azure Data Studio that allows seamless sign in using Active Directory for those of you with Azure Database for PostgreSQL - Single Server databases. Sweet.
Big welcome to Postgres Projects in Azure Data Studio
But back to today’s topic.
If you’re interested in the second approach I describe above, where you keep your application and database deployment scripts separate—what I’m calling database code isolation—we’re introducing a feature today to help make your life just a little easier: Introducing Projects for PostgreSQL in Azure Data Studio.
You’re probably already familiar with the idea of creating a ‘project’ or ‘solution’ to organize your app code.
Well this is like that but for your Postgres database. In the context of Azure Data Studio, a project is a collection of SQL script files, which can be compiled into one single deployment script you can apply to your Postgres database. Save your project to a shared Git repository and use Azure Data Studio’s source control features to collaborate across your team.
Getting started tour with Postgres Projects in Azure Data Studio
To get started with Postgres Projects, you need to install the latest versions of
Once you have installed Azure Data Studio along with the Postgres extension:
- Start Azure Data Studio.
- Select the File menu, then select Open Folder.
- Navigate to the folder in which you want to create your project file, right-click the folder area, and then select New PostgreSQL Project.
- In the Project Name text box, specify a name for the project, and then press Enter to create. This creates your project. The project folder will now have a file with file type .pgproj, which represents the Postgres project.
- Add your .sql script files to the project folder. For example we will define two tables, ‘MyTable’ and ‘MySecondTable,’ in two different sql scripts.
- To build your project, right-click your .pgproj file, and then select Build PostgreSQL project. Building your PostgreSQL project will validate the syntax of all the scripts in your project folder and generate an output script which can be deployed to your Postgres database server.
- An output script is generated by the ‘Build PostgreSQL project’ command:
- Now right-click the .pgproj file, and then Deploy. It will start building your project.
- If there is no build error, a connection dialog box appears so that you can enter the target database connection. Provide your database details and then select Connect.
- If the connection is successful, the deploy operation now begins. The output pane shows the progress and notifies you of any errors.
Projects - not just for app code
The 'project' or 'solution' structure is well used in app development for a reason. A little organization goes a long way towards building scalable, collaborative code. Your Postgres code should get to benefit from that too.
Important: The Project feature for Postgres in Azure Data Studio only supports table objects right now. Your feedback will help guide what's next for this feature. Let us know what you think at our GitHub repo for the Postgres extension to Azure Data Studio—or on Twitter via @AzureDBPostgres.