Getting Started with Power Apps and Microsoft SQL Database

Introduction

Just as our prehistoric ancestors evolved by adopting tools like fire and stone axes, your journey as a Power Apps developer must also evolve. As your experience grows, you'll need to reach further and build more complex applications. One of the most significant milestones in this evolution is moving beyond SharePoint lists and connecting your apps to enterprise-level storage solutions like Microsoft SQL Server.

In this guide, I'll walk you through the process of:

• Setting up an Azure SQL database
• Creating tables and relationships
• Connecting Power Apps to SQL using stored procedures and Power Automate
• Building a simple inventory and order management application

Let's take our first steps into this bigger world of enterprise-level data management!

Prefer to Watch the Video?

Head over to my YouTube channel Chino Does Stuff to watch the video tutorial, there you will see a variety of videos with loads of tricks and tips on the entire Power Platform suite.

Setting Up Your Azure SQL Database

The first step is to set up an Azure SQL database. If you're new to Azure, you can sign up for a free trial which gives you plenty of credits to explore the platform.

Creating a SQL Server and Database

In the Azure Portal:

1. Search for "SQL databases" and click "Add"
2. Select or create a resource group
3. Give your database a name (e.g., "ChinoDB")
4. Create a new SQL server (e.g., "ChinoDBSRV")
5. Set up server admin credentials
6. Take the default options for other settings and click "Create"

Configuring Server Firewall Rules

Once your server and database are created, you need to configure the firewall:

1. Navigate to your SQL server in the Azure Portal
2. Click on "Networking" under "Security"
3. Set "Allow Azure services and resources to access this server" to "Yes"
4. Add your client IP address so you can connect from your desktop

This firewall configuration is crucial as it allows Power Apps and Power Automate to connect to your database, while also letting you manage the database from your local computer.

Creating Your Database Structure

For this tutorial, we'll create a simple inventory management system with two tables: a Stock table for inventory items and an Orders table for tracking restock orders.

Connecting to Your Database

To manage your database, you'll need SQL Server Management Studio (SSMS):

1. Download and install SQL Server Management Studio
2. Connect to your database using the server name, admin username, and password

Creating Tables

We'll create two tables with a simple relationship:

Let's break down what we've done:

• Stock Table: Stores inventory items with a name, description, and current stock level
• Orders Table: Tracks order history with references to stock items
• Relationships: The StockID in Orders refers to the ID in Stock, creating a one-to-many relationship

Populating Sample Data

To make testing easier, let's add some sample data to our tables:

Creating a Stored Procedure

Here's where things get interesting. Power Apps can't directly query multiple related SQL tables efficiently, so we'll create a stored procedure that joins the data for us:

This stored procedure joins the Orders and Stock tables, giving us a flattened view that's perfect for displaying in Power Apps.

Connecting Power Apps to SQL Using Power Automate

Currently, Power Apps can't directly call SQL stored procedures. Instead, we'll use Power Automate as a bridge between our app and database.

Creating a Power Automate Flow

Here's how to set up the flow:

1. Create a new instant flow with the PowerApps trigger
2. Add an "Execute stored procedure" action from the SQL Server connector
3. Configure it to connect to your database and call the "GetOrders" procedure
4. Add a "Parse JSON" action to format the response
5. Add a "Respond to PowerApps" action to return the data

The key here is the "Parse JSON" step. When you execute the flow for the first time, you can copy the JSON output from the stored procedure execution and use it to generate the schema automatically.

Building Your Power App

With the database and flow set up, we can now build our Power App:

Create a New Canvas App

1. Create a blank canvas app
2. Add a gallery control
3. Add a button to refresh data from SQL

Setting Up Data Retrieval

Configure the button to call your flow and store the results:

Displaying the Data

In your gallery, add labels to display the data:

1. Add a label for the stock name
2. Add a label for the order description
3. Add a label for the order amount
4. Add a date picker (in view mode) for the order date

Beyond the Basics: Creating a Complete Application

While we've covered the fundamentals of connecting Power Apps to SQL, a complete application would include:

• Create/Edit Forms: Screens to add and update stock items and orders
• Data Validation: Ensuring data integrity before saving to SQL
• Automatic Reordering: Triggering orders when stock levels fall below thresholds
• User Authentication: Ensuring only authorised users can access certain functions

Key Takeaways for Working with SQL in Power Apps

As you continue to develop with SQL and Power Apps, remember these important points:

1. Use Stored Procedures: They're the key to working effectively with SQL, especially with related tables
2. Power Automate is Your Bridge: Use it to connect Power Apps with SQL stored procedures
3. Secure Your Database: Create specific database users for your apps rather than using admin credentials
4. Plan Your Data Model: A well-designed database schema makes app development much easier
5. Learn SQL Basics: Understanding SQL queries and joins will dramatically expand what you can do with Power Apps

Conclusion

Moving from SharePoint lists to SQL databases is a significant evolution in your Power Apps journey. While there's certainly a learning curve, the benefits are substantial:

• More sophisticated data relationships
• Better performance with large datasets
• Advanced data validation and business logic at the database level
• Enterprise-grade security and scalability

This tutorial covered the core functionality of using SQL with Power Apps, but there's much more to explore. In future tutorials, we'll build on this foundation to create more complete applications with full CRUD (Create, Read, Update, Delete) capabilities.

If you'd like to see more Power Apps development tutorials using SQL, please head over to the YouTube channel and leave a comment there. I actively monitor and respond to all YouTube comments!

About the Author

Marcello is a lead developer at Powerplatform Experts, an expert in, Computer Science, Power Platform integration and founder of the YouTube channel ChinoDoesStuff. If you have any questions, please feel free to get in contact at via the form below.