How to deploy a REST API to Azure Functions with Azure SQL

This guide explains how to set up and deploy a Prisma based Node.js REST API to Azure Functions with Azure SQL as the database.

Azure Functions is a serverless deployment platform. You do not need to maintain infrastructure to deploy your code. Azure SQL Database is a relational database service built for the cloud with automatic scaling.

In this guide, you create the necessary resources in Azure, create the database schema with Prisma Migrate. You then deploy a Node.js REST API with resource endpoints that use Prisma Client to handle database operations against the Azure SQL database.

This guide shows how you can use Prisma in the Azure cloud, focusing on Azure Functions and Azure SQL. The starting point is the Prisma Azure Functions example. This is a REST API for a simple blog with two models: User and Post (one-to-many). The example contains REST endpoints preconfigured as serverless functions. It uses Prisma Client to fetch, create, and delete records from a database.

With Azure Functions, the fundamental building block is the function app. A function app provides an execution context in Azure in which your functions run. It is comprised of one or more individual functions that Azure manages, deploys, and scales together. You can organize and collectively manage multiple functions as a single logical unit.

Prerequisites

An Azure account.
Git installed.
Azure CLI installed.
Node.js installed.

Prisma workflow

In this guide, you use Prisma Migrate to create the database schema. Prisma Migrate is based on the Prisma schema. It generates .sql migration files that are executed against the database.

Migrate comes with two primary workflows:

Create migrations and apply during local development with prisma migrate dev.
Apply the generated migrations to production with prisma migrate deploy.

For brevity, this guide does not cover how you create migrations with prisma migrate dev. It focuses on the production workflow with prisma migrate deploy and uses the Prisma schema and SQL migration that are included in the example code.

To learn more about how migrations are created with Prisma Migrate, check out the start from scratch guide.

Required Azure resources

In this guide, you set up the following resources in your Azure account:

Resource group
Azure SQL Database server
Database
Firewall rule
Storage account
Function App

1. Download the example and install dependencies

1.1. Download the example

Steps

Open your terminal and navigate to a location of your choice.

Create the directory for the application code and download the example code:


$mkdir prisma-azure
$cd prisma-azure
$curl https://codeload.github.com/prisma/prisma-examples/tar.gz/latest | tar -xz --strip=3 prisma-examples-latest/deployment-platforms/azure-functions/

Result

You directory contains the following files:

$CreatePost/
$CreateUser/
$DeletePost/
$FilterPosts/
$GetFeed/
$GetPost/
$PublishPost/
$host.json
$lib/
$package.json
$prisma/
$proxies.json

1.2. Install the dependencies

Run the following command:


$npm install


$az login

3. Create the Resource Group on Azure

In Azure, a resource group is a way to group together different cloud resources. Whenever you create a resource, such as an Azure Function, you must assign it a resource group. Because the REST API in this guide uses Azure Functions and an Azure SQL database, you must first create the resource group.

Steps

Use the following command:


$az group create --location germanywestcentral --name prisma-azure-example

Note: The command above creates the resource group in the germanywestcentral region. You should create the resource group in the region closest to your users. For the full list of Azure regions, run the az account list-locations command.

Result

The command outputs information about the created resource group similar to the following:

{
  "id": "/subscriptions/SUBSCRIPTION_ID/resourceGroups/prisma-azure-example",
  "location": "germanywestcentral",
  "managedBy": null,
  "name": "prisma-azure-example",
  "properties": {
    "provisioningState": "Succeeded"
  },
  "tags": null,
  "type": "Microsoft.Resources/resourceGroups"
}

4. Create the Azure SQL database server

Steps

To create the Azure SQL database server, run the following command. Before you run the command, replace UNIQUE_DB_SERVER_NAME with a unique name for the database and replace CHOOSE_A_PASSWORD with a password, and note it down.


$az sql server create -l germanywestcentral -g prisma-azure-example --name UNIQUE_DB_SERVER_NAME --admin-user prisma --admin-password CHOOSE_A_PASSWORD --enable-public-network true

The command does the following:

Creates the database server in the germanywestcentral region.
Associates it with the prisma-azure-example resource group created in the previous step.
Sets a unique name for the Azure SQL server with UNIQUE_DB_SERVER_NAME.
Sets the admin user to prisma.
Sets the admin password.
Enables public network access so you can create the database schema from your machine.

In the next step, you will create the database that Prisma will use in the REST API.

Result

The command outputs information similar to the following:

{
  "administratorLogin": "prisma",
  "administratorLoginPassword": null,
  "administrators": null,
  "federatedClientId": null,
  "fullyQualifiedDomainName": "UNIQUE_DB_SERVER_NAME.database.windows.net",
  "id": "/subscriptions/YOUR_SUBSCRIPTION_ID/resourceGroups/prisma-azure-example/providers/Microsoft.Sql/servers/UNIQUE_DB_SERVER_NAME",
  "identity": null,
  "keyId": null,
  "kind": "v12.0",
  "location": "westeurope",
  "minimalTlsVersion": null,
  "name": "UNIQUE_DB_SERVER_NAME",
  "primaryUserAssignedIdentityId": null,
  "privateEndpointConnections": [],
  "publicNetworkAccess": "Enabled",
  "resourceGroup": "prisma-azure-example",
  "restrictOutboundNetworkAccess": "Disabled",
  "state": "Ready",
  "tags": null,
  "type": "Microsoft.Sql/servers",
  "version": "12.0",
  "workspaceFeature": null
}

5. Create the database

In this step, you create a database on the server that you created in the previous step.

Steps

Run the following command in the terminal. Replace UNIQUE_DB_SERVER_NAME with the database name that you chose in the previous step:


$az sql db create --resource-group prisma-azure-example --server UNIQUE_DB_SERVER_NAME --name prisma-azure-example --service-objective Basic

Here's a breakdown of the command's parameters:

--resource-group adds the database to the resource group created in step 3
--server sets the Azure SQL database server to create it in
--name sets the name of the database
--service-objective sets the database's service tier that determines the cost.

6. Create a firewall rule to allow local access to the database

In this step, you add two firewall rules:

Rule 1: Allow remote access from your local computer's public IP to the Azure SQL database. This is necessary so that you can create the database schema and use the database to test locally.
Rule 2: Allow access to the Azure SQL database from Azure Functions.

6.1. Allow access from your local computer

Steps

Determine your public IP with the following command:


$curl ifconfig.me

Copy the IP from the output and run the following command. Replace YOUR_PUBLIC_IP with the IP address and UNIQUE_DB_SERVER_NAME with the name from step 4.


$az sql server firewall-rule create --resource-group prisma-azure-example --server UNIQUE_DB_SERVER_NAME --name allow-local-access --start-ip-address YOUR_PUBLIC_IP --end-ip-address YOUR_PUBLIC_IP

Result

The command outputs information similar to the following:

{
  "endIpAddress": "YOUR_PUBLIC_IP",
  "id": "/subscriptions/YOUR_SUBSCRIPTION_ID/resourceGroups/prisma-azure-example/providers/Microsoft.Sql/servers/prisma-db/firewallRules/allow-local-access",
  "kind": "v12.0",
  "location": "Germany West Central",
  "name": "allow-local-access",
  "resourceGroup": "prisma-azure-example",
  "startIpAddress": "YOUR_PUBLIC_IP",
  "type": "Microsoft.Sql/servers/firewallRules"
}

6.2 Allow access from Azure Functions

To allow applications hosted inside Azure to connect to your SQL server, you must enable Azure connections. To enable Azure connections, you set up a firewall rule with starting and ending IP addresses set to 0.0.0.0.

Steps

Create the rule with the following command:


$az sql server firewall-rule create --resource-group prisma-azure-example --server UNIQUE_DB_SERVER_NAME --name allow-function-access --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

Result

The command outputs information similar to the following:

{
  "endIpAddress": "0.0.0.0",
  "id": "/subscriptions/YOUR_SUBSCRIPTION_ID/resourceGroups/prisma-azure-example/providers/Microsoft.Sql/servers/UNIQUE_DB_SERVER_NAME/firewallRules/allow-function-access",
  "name": "allow-function-access",
  "resourceGroup": "prisma-azure-example",
  "startIpAddress": "0.0.0.0",
  "type": "Microsoft.Sql/servers/firewallRules"
}

7. Create a storage account

In this step, you create a storage account. This account stores information about your functions, including state information.

Steps

Run the following command to create the storage account. Replace UNIQUE_STORAGE_ACCOUNT_NAME with a name for the storage account:


$az storage account create --name UNIQUE_STORAGE_ACCOUNT_NAME --location germanywestcentral --resource-group prisma-azure-example --sku Standard_LRS

Result

The command outputs a large JSON object. Check that the status of provisioningState is Succeeded:


{
  "id": "/subscriptions/YOUR_SUBSCRIPTION_ID/resourceGroups/prisma-azure-example/providers/Microsoft.Storage/storageAccounts/UNIQUE_STORAGE_ACCOUNT_NAME",
  "provisioningState": "Succeeded",
  "resourceGroup": "prisma-azure-example",
  "type": "Microsoft.Storage/storageAccounts"
}

8. Create the function app

In this step, you create the function app, which provides the environment in which your function code executes. A function app maps to your local function project and lets you group functions as a logical unit for easier management, deployment, and resource sharing.

Steps

Copy the following command.

Replace FUNCTION_APP_NAME with a unique name for your function app.
Replace STORAGE_ACCOUNT_NAME with the name you chose in the previous step.


$az functionapp create --resource-group prisma-azure-example --consumption-plan-location germanywestcentral --runtime node --runtime-version 14 --functions-version 3 --name FUNCTION_APP_NAME --storage-account STORAGE_ACCOUNT_NAME --os-type Linux

Note: You must choose a globally unique name for your Function App, because the name determines the subdomain of the deployed function.

Result

The command outputs a large JSON object in the terminal. Verify that that the state key in the object is set to Running.

9. Set the DATABASE_URL environment variable locally

In this step, you define the DATABASE_URL environment variable locally to create the database schema and test the functions locally.

Steps

To construct the connection URL, copy the following connection URL template:


sqlserver://DB_SERVER_NAME.database.windows.net:1433;database=DB_NAME;user=DB_ADMIN_USER@DB_SERVER_NAME;password={DB_ADMIN_PASSWORD};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30

Replace the following parts:

DB_SERVER_NAME with the database server name defined in step 4
DB_NAME with the database name defined in step 5
DB_ADMIN_USER with the database admin user set in step 4 to prisma
DB_ADMIN_PASSWORD with the database admin password set in step 4

After you set all the values, run the command as follows:


$export DATABASE_URL="sqlserver://DB_SERVER_NAME.database.windows.net:1433;database=DB_NAME;user=DB_ADMIN_USER@DB_SERVER_NAME;password={DB_ADMIN_PASSWORD};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"

10. Create the Azure Functions local configuration

In this step, you create the local configuration file for Azure Functions. The file defines local configuration, such as environment variables, for the functions and the runtime – in this case Node.js.

Steps

Use the following command to create a file named local.settings.json in the root of the project:


touch local.settings.json

Add the following contents to the file:


{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "FUNCTIONS_WORKER_RUNTIME": "node"
  }
}

11. Create the database schema

You can now create the database schema with the prisma migrate deploy command.

Note: The prisma migrate deploy command runs migration files from the prisma/migrations folder. The initial migration is already included in the example. To learn more about how to create migrations, see the Prisma Migrate docs.

Steps

Run the following command to create the database schema:


$npx prisma migrate deploy

Result

The command outputs information similar to the following::


1 migration found in prisma/migrations

The following migration have been applied:

migrations/
  └─ 20210322111219_init/
    └─ migration.sql

All migrations have been successfully applied.

12. Expose the DATABASE_URL environment variable to the functions

In this step, you expose the DATABASE_URL environment variable to the functions so that Prisma can connect to the database. In Azure Functions, you must set environment variables with app settings.

Steps

Run the following command. Replace FUNCTION_APP_NAME_FROM_STEP_8 with the name of the function app that you created in step 8:


$az functionapp config appsettings set --name FUNCTION_APP_NAME_FROM_STEP_8 --resource-group prisma-azure-example --settings DATABASE_URL=$DATABASE_URL

The command sets the DATABASE_URL app setting with the locally defined DATABASE_URL environment variable that you set in step 9.

Note: By default, app settings are not encrypted. The DATABASE_URL contains sensitive information. You can store it more securely in Azure's Key Vault

You have created all the necessary resources and configuration, which means that you can now deploy your API.

13. Deploy the functions

In this step, you generate Prisma Client and deploy the functions.

Steps

In the project directory, run the following command:


$npx prisma generate

Result

The command generates Prisma Client in the node_modules folder.

Steps

To deploy the functions, run the following command. Replace FUNCTION_APP_NAME with the name of the function app that you created in step 8.


$npx func azure functionapp publish FUNCTION_APP_NAME

Result

You see output similar to the following:

Getting site publishing info...
Uploading package...
Uploading 67.24 MB [##############################################################################]
Upload completed successfully.
Deployment completed successfully.
Syncing triggers...
Functions in FUNCTION_APP_NAME:
    CreatePost - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/post
    CreateUser - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/user
    DeletePost - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/post/{postid}
    FilterPosts - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/filterposts
    GetFeed - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/feed
    GetPost - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/post/{postid}
    PublishPost - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/publish/{postid}

You successfully deployed a Prisma based REST API to Azure Functions, with Azure SQL as the database.

What's next

In the next step, you test the functions and take a closer look at how the functions are implemented.

14. Test the deployed functions

In this step, you test the API's different endpoints with the URLs from the previous step.

Steps

Use the following command to make a POST HTTP request to the CreateUser endpoint with curl. Replace FUNCTION_APP_NAME in the URL with the app name you chose in step 8.


$curl --request POST --data '{"email":"alice@prisma.io","name":"Alice"}' https://FUNCTION_APP_NAME.azurewebsites.net/api/user

Result

You see the created user object returned, similar to the following:

{
  "createdAt": "2021-03-02T14:48:15.746Z",
  "email": "alice@prisma.io",
  "id": 1,
  "name": "Alice"
}

The files associated with the function are as follows. They are in the CreateUser folder.

function.json: Function configuration, such as the HTTP method, path, and return value.
index.js: The function handler that uses Prisma Client to create the user in the Azure SQL database.

Steps

Use the following command to create a post associated with the user you created. Replace FUNCTION_APP_NAME in the URL with the app name that you chose in step 8.


$curl --request POST --data '{"title":"Prisma with Azure","content":"","authorEmail":"alice@prisma.io"}' https://FUNCTION_APP_NAME.azurewebsites.net/api/post

Result

You see the created post object returned, similar to the following:

{
  "id": 1,
  "createdAt": "2021-03-02T17:09:53.160Z",
  "updatedAt": "2021-03-02T17:09:53.161Z",
  "title": "Prisma with Azure",
  "content": "",
  "published": false,
  "authorId": 1
}

Steps

To update the published field of the post, make the following request. Replace FUNCTION_APP_NAME in the URL with the app name that you chose in step 8.


$curl --request PUT https://FUNCTION_APP_NAME.azurewebsites.net/api/publish/1

Result

You see the updated post object returned, similar to the following:

{
  "authorId": 1,
  "content": "",
  "createdAt": "2021-03-02T17:09:53.160Z",
  "id": 1,
  "published": true,
  "title": "Prisma with Azure",
  "updatedAt": "2021-03-03T10:07:11.047Z"
}

Steps

Finally, to test the feed endpoint, make the following request. Replace FUNCTION_APP_NAME in the URL with the app name that you chose in step 8.


$curl https://FUNCTION_APP_NAME.azurewebsites.net/api/feed

Result

You see the post you created and the related author, similar to the following:

[
  {
    "id": 1,
    "createdAt": "2023-01-24T12:14:04.340Z",
    "updatedAt": "2023-01-24T12:19:11.121Z",
    "title": "Prisma with Azure",
    "content": "",
    "published": true,
    "authorId": 1,
    "author": {
      "id": 1,
      "createdAt": "2023-01-24T12:10:04.206Z",
      "email": "alice@prisma.io",
      "name": "Alice"
    }
  }
]

Develop and debug the functions locally

When you implement Azure Functions, you can also start a local development environment with the Azure Functions Core tools' function runtime. This lets you test and debug the implementation of the functions locally.

To launch the functions runtime, run the following command:


$npx func start

The command starts a local server and allows you to call any of the functions in the project.

To inject environment variables into the functions, add them to the Values object in the local.settings.json file at the root of the project.

Set up a local database for development

When you develop locally, consider running a local Microsoft SQL Server instance. Microsoft SQL Server is not the same as Azure SQL, but the two have high compatibility with each other.

The quickest way to set up a local Microsoft SQL Server is with Docker. See the Microsoft SQL Server example for information on how to set it up.

Bootstrap a new function

When you want to create a new function, you can use the following command to bootstrap the new function:


npx func function new --language JavaScript --template "HTTP trigger" --name FUNCTION_NAME

The command creates a folder with the index.js and function.json files.

Summary

Congratulations! You have successfully deployed the REST API to Azure Functions and used Prisma Client to handle database queries to the Azure SQL database.

For more insight into Prisma Client's API, explore the function handlers and check out the Prisma Client API Reference

While this guide uses the Azure CLI to create all the resources, you can also create the resources with the Azure Portal UI or the VS Code extension, which supports deployments directly from VS Code.

As a next step, you might want to learn how to implement a continuous delivery pipeline with GitHub Actions to automate the deployment process from a GitHub repository.

Was this helpful?

Edit this page on GitHub

Prisma ORM

Prisma Data Platform

Latest from the blog

Deploy to Azure Functions

Prerequisites

Prisma workflow

Required Azure resources

1. Download the example and install dependencies

1.1. Download the example

Steps

Result

1.2. Install the dependencies

2. Sign in to Azure with the Azure CLI

3. Create the Resource Group on Azure

Steps

Result

4. Create the Azure SQL database server

Steps

Result

5. Create the database

Steps

6. Create a firewall rule to allow local access to the database

6.1. Allow access from your local computer

Steps

Result

6.2 Allow access from Azure Functions

Steps

Result

7. Create a storage account

Steps

Result

8. Create the function app

Steps

Result

9. Set the DATABASE_URL environment variable locally

Steps

10. Create the Azure Functions local configuration

Steps

11. Create the database schema

Steps

Result

12. Expose the DATABASE_URL environment variable to the functions

Steps

13. Deploy the functions

Steps

Result

Steps

Result

What's next

14. Test the deployed functions

Steps

Result

Steps

Result

Steps

Result

Steps

Result

Develop and debug the functions locally

Set up a local database for development

Bootstrap a new function

Summary

Was this helpful?

Product

Developers

Use Cases

Company

Newsletter