Using a Database

Databases are critical infrastructure components that are used to compile information about tasks, individuals, services, products, or any other entity that needs to be tracked. Common database-driven applications include Customer Relationship Management (CRM) platforms, product catalogs, Enterprise Resource Planning (ERP) applications, accounting packages, storefronts, and inventory management tools.

In the world of Power Automate, you might use a database to store information about responses to surveys or forms, or to manage scheduled jobs. You may even use one as a logging repository for actions taken by other applications and users. In this chapter, we’re going to look at some of the basics of connecting to a SQL database and using a flow to add data entries. Specifically, we’ll learn about the following topics:

• Understanding database connectors, triggers, and actions
• Connecting to a database
• Creating a connection to a...

Understanding database connectors, triggers, and actions

If you’re not familiar with databases, that’s ok! While designing scalable database systems can be quite complex, the general concepts of databases are relatively simple.

Databases are typically made up of tables, columns, and rows. You can think of a simple database as a collection of spreadsheets, where the spreadsheet itself is the table, and then the rows and columns in the spreadsheet represent rows and columns in the database.

When reading or writing to a database, you need to tell the application some key details:

• The server name or address
• The database name
• Authentication credentials

This information is stored in a connection object or connection string and tells the application how to locate the database. Later, when performing actions such as queries, inserts, or updates against a database, you will specify additional details such as table names and filters to...

Connecting to a database

In this section, we’ll look at creating a simple button flow that adds a row of data to a database table. To do this, you’ll need a database and a table. In the following example, we’ll create a SQL instance and a database that we’ll use for our Power Automate configuration.

Creating a server

While you can use any supported database technology with Power Automate, we’re going to focus on SQL Server. If you already have an existing SQL server available, you can skip this step. Otherwise, you can provision the necessary components in Azure to start working immediately.

If you do not have access to server infrastructure, you can sign up for a free Azure trial at https://azure.microsoft.com/en-us/offers/ms-azr-0044p/ or by navigating to https://portal.azure.com, signing in with your global admin credential for your trial Microsoft 365 tenant, and then completing the Azure trial sign-up process.

To...

Creating a connection to a database

As you learned earlier in the chapter, when working with databases, you’ll need to tell an application how to connect to a database. That data is stored in a configuration object generally called a connection string. In Power Automate, the connection string data is referred to as a connection.

Connection details will depend on the type of database you’re connecting to, but the most common fields or properties that you’ll need to populate include a server name or IP address, credentials, port numbers, and a database name.

To connect to a database in Power Automate, follow these steps:

1. Navigate to the Power Automate web portal (https://flow.microsoft.com). Expand Data and click Connections.
2. Select New connection:

Figure 12.10: Creating a new connection

3. Select SQL Server from the list of connection types:

Figure 12.11: Selecting the SQL Server connection type

...

Adding content to a database

Now that you have configured a server, a database, and a table with columns, you get to see all the pieces working together. In this section, you’ll create a flow, execute it, and then verify that it wrote the data successfully.

First, let’s create a flow so that we can add content.

Creating the flow

To verify that you’ve configured database connectivity, we’ll walk through creating a sample button or instant flow so that we can post data to the database. Follow these steps to create the flow:

1. From the Power Automate web portal (https://flow.microsoft.com), select Create.
2. In the Start from blank section, select Instant cloud flow.
3. Enter a name for the flow, and then select Manually trigger a flow as the trigger type.
4. Click Create.
5. Click New step.
6. In the search box, enter SQL insert and then select the Insert row (V2) action:

Figure 12.13: Selecting the Insert...

Summary

In this chapter, you learned about some of the database interaction capabilities of Power Automate. Following the steps in this chapter, you were able to successfully create an Azure SQL Server instance, a database, and a database table to store content. You were able to connect to the database, create a flow, and insert a new row of data. Finally, you examined the run history of the flow and learned how to query the database directly.

The next chapter will build on this knowledge by showing you how to take Microsoft Forms data and insert it into a database using Power Automate.

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

https://packt.link/lcncdserver