Azure Database for PostgreSQL Blog

4 MIN READ

Expose PostgreSQL tables as REST APIs with Data API Builder

Microsoft

Jun 06, 2023

Are you in search of a convenient and straightforward solution to expose database objects through REST or GraphQL? Look no further than Microsoft's open-source Data API Builder (DAB) in Azure Database for PostgreSQL Flexible Server. In this blog post, I will walk you through a demo that showcases the capabilities of this powerful tool and how it can revolutionize your app development process.

Azure Database for PostgreSQL - The Ideal Cloud Data Platform

Azure Database for PostgreSQL Flexible Server is a fully managed database service provided by Microsoft Azure. It is built on the popular open-source PostgreSQL database engine and offers flexibility, scalability, and high availability for your applications. It provides features such as automated backups, automated patching, monitoring, and enterprise-grade security to ensure the reliability and security of your PostgreSQL databases. Developers love it for its ease-of-use, query performance, and seamless integration with other Azure services like Azure Kubernetes Service (AKS) and App Service, enabling simplified deployments on the Azure platform. For more details, refer Azure Database for PostgreSQL.

Introducing Data API Builder (DAB)

Microsoft recently introduced Data API Builder (DAB) for Azure Databases, an open-source project that simplifies the process of exposing database objects via REST or GraphQL endpoints. With DAB, you can access data using modern techniques on any platform, language, or device. It provides a seamless way to create APIs for your Azure Database for PostgreSQL applications with minimal coding. It's worth noting that Azure Static Web Apps utilizes DAB "under-the-hood" to provide database connections.

To share insights about leveraging the power of OSS database to transform applications in Azure cloud, the Azure team has started an open-source developer video series. I had the privilege of presenting the integration of Data API Builder (DAB) with Azure Database for PostgreSQL. In my session, I demonstrated how Data API Builder can be used to create REST endpoints on database objects. This blog post outlines the steps I performed in the demo so that you can recreate them and create your first set of REST endpoints on a PostgreSQL database. The best part is that this demo utilizes 100% free local components.

Demo: Use Data API builder (DAB) with Azure PostgreSQL

I. Prerequisites

Before starting the Data API Builder demo, make sure you have the following installed:

II. Use DAB-CLI to create the config file and add objects.

1. Install the .NET 6 SDK and DAB CLI on your local machine

2. Log in to Azure Portal and retrieve the PSQL connection string for your Azure PostgreSQL database.

3. Start a command prompt and create a project directory

mkdir data-api-builder

4. Change the directory to the newly created project directory

cd data-api-builder

5. Open VS Code in this directory

code .

6. Open a new terminal in VS Code

7. Create a DAB configuration file and database connection (to Azure PostgreSQL database)

dab init 
--database-type "postgresql" 
--connection-string "Server=servername.postgres.database.azure.com;Database={your_database}; Port=5432; User Id=pg1user; Password={your_password}; Ssl Mode=Require; TrustServerCertificate=true" 
--host-mode "Development

8. Add the sample tables (books) to the DAB config file by running the provided commands

dab add book 
--config "dab-config.json" 
--source books 
--permissions "anonymous:create,read,update,delete"

III. Start DAB engine.

Run DAB locally by running the provided command in the terminal

dab start 
--config "dab-config.json"

IV. REST Endpoints

1. The REST examples can be executed from any REST client such as Postman or Insomnia. All the examples will use the following endpoints.

Note: DAB supports HTTP verbs like GET, POST, PUT, and DELETE, allowing you to perform CRUD operations effortlessly.

https://localhost:5000/api/book
https://localhost:5000/api/author

2. Get by books by ID

https://localhost:5000/api/book/id/1000

3. Filter columns using select operation

https://localhost:5000/api/book?select=title

4. Perform operations such as 'equals' and 'not equals'

https://localhost:5001/api/book?$filter=title eq 'Foundation'
https://localhost:5001/api/book?$filter=title ne 'Foundation'

5. Sort results with order by

https://localhost:5001/api/book?$orderby=pages desc

6. Using POST to create a record

POST https://localhost:5001/api/book
{    
  "title": "Atomic Habits",    
  "year": 2019,    
  "pages": 250
}

7. Using PUT to update a record

PUT https://localhost:5001/api/book/id/1000043
{    
  "title": "One Day",    
  "pages": 200,    
  "year": 2023
}

8. Using DELETE to delete a record

DELETE 'http://localhost:5000/api/book/id/1020'

Resources to get started

To learn more about DAB and Azure Database for PostgreSQL, explore the following resources:

Conclusion

Leverage the native support of Data API Builder (DAB) for PostgreSQL data, enabling seamless pagination, filtering, and sorting. Experience a remarkable reduction in development time, transforming CRUD backend service creation from days to minutes. With built-in data access, authentication, and authorization features, DAB meets your data security needs. Combine DAB with Azure Database for PostgreSQL - Flexible Server to streamline your app development and unlock the efficiency of building modern applications. Embrace the power of DAB and harness Azure's robust PostgreSQL capabilities to their fullest.

Our goal is to enhance your developer experience and maximize productivity with a comprehensive set of database development tools for Azure Database for PostgreSQL. We highly value your feedback, so please don't hesitate to contact us via email at Ask Azure DB for PostgreSQL. We genuinely appreciate your input!

Updated Jun 07, 2023

Version 10.0

api

azure

Azure Database for PostgreSQL

Azure Database for PostgreSQL Flexible Server