arrow_back Back to the Blog
Resource

Automatic GraphQL and REST endpoint for Azure SQL with Directus

By Michelle Maddox on January 20, 2022

Davide Mauri, senior program manager of Azure SQL, and Rijk van Zanten, CTO and Co-Founder of Directus, share insight on how to get up and running with Directus, and explain how to host Directus itself on Azure App Service.

Recently Directus was featured in a Microsoft blog article, the full version can be found here: "Automatic GraphQL and REST endpoint for Azure SQL with Directus".

“Extremely performant and built entirely on Node.js, Directus is an open-source Data Platform that enables anyone in an organization — from developers to data scientists to business users — to access and manage the database content that powers all your applications and digital experiences. It installs as a layer on top of your existing SQL database, mirroring its content and schema, so there is no need to migrate or alter your data. Once installed, you instantly get a dynamic API (REST and GraphQL) and no-code app that democratizes data so you can extract maximum value from it across your organization.”

In this post, Rijk van Zanten details how to quickly and easily deploy and get up and running with Directus, using this sample implementation, and explains how to host Directus itself on Azure App Service, allowing your app and APIs to be accessible from anywhere. Let’s get started.

**Before we begin, you will need Node.js 12.20 or later, and an Azure SQL database server accessible from your local machine. **

To spin up a Directus instance running on Azure SQL and Azure App Service, begin by setting up a local project working directory and configure the connection to Azure SQL.

Next, create a Directus compatible Azure SQL database using CLI commands.

Creating the Database

Create a resource group to hold the resources for this project:

az group create \--location eastus \--name {GROUP\_NAME}

Create the database server:

az sql server create \-l eastus \-g {GROUP\_NAME} \--name {DB\_SERVER\_NAME} \--admin user {DB\_ADMIN\_USER} \--admin\-password {DB\_PASSWORD} \--enable\-public\-network true

Make sure to note the database name, username and password somewhere safe.

Create the database:

az sql db create \--resource\-group {GROUP\_NAME} \--server {DB\_SERVER\_NAME} \--name {DB\_NAME} \--service\-objective Basic

Allow your computer to access the database:

az sql server firewall\-rule create \--resource\-group {GROUP\_NAME} \--server {DB\_SERVER\_

Creating the Project & Installing Directus

Create a project directory, and install Directus by running:

npm init directus\-project ./directus\-example

In the prompts, enter:

  • Database Client: Microsoft SQL Server
  • Database host: {DB_SERVER_NAME}.database.windows.net
  • Port: 1433 (default)
  • Database Name: {DB_NAME}
  • Database User: {DB_ADMIN_USER}
  • Database Password: {DB_ADMIN_PASSWORD}
  • Encrypt Connection: Yes

Next, enter an email address and password combination for the first user.

Now, navigate into the project folder and run npx directus start to launch Directus.

Directus will run on port 8055 by default. To open the admin app, navigate to localhost:8055, and log in with the admin email and password combination you just created.

Creating the Data Model

For a more in-depth explanation on what Directus has to offer, please visit the Directus Documentation here.

Collections and fields correspond directly to tables and columns in the database. Every collection you build creates a table in the database, and, similarly, every field you create will be a column in the database.

Opening the admin panel app without having any existing collections will prompt you to create your first collection. For this example, we will name our collection articles.

Now that the collection is created, it's time to add some fields. Go to Settings > Data Model > Articles and select "Create Field." We’ll keep it easy here and stick to a regular input, but be sure to check out the other fields if you’re curious. Let’s give this field a key of title, then click Save.

Configuring Permissions

Now that you have a basic data model spun up, be sure only the correct people have access to it. Let’s talk about permissions and how to give public read/write access to the articles collection we’ve created.

Select Settings > Roles & Permissions > Public and use the checkbox icons in the table to Create (plus) and Read (eye) to “All Access.” This allows anyone outside the service to create and read items from your “articles” table.

Use Directus’s powerful rule-based permissions system to fine-tune access control for your exact needs. The example above is just the tip of the iceberg! For more information about permissions, please refer to the Directus Documentation.

Connecting Data

Directus ships with powerful RESTful and GraphQL APIs that beautifully wrap the raw power of Azure SQL. Now that you have a basic data model in place with permissions to save and retrieve data, you are ready to start using the API to easily interact with your data!

With your local server running, start by querying the API using curl or any other local request dev tool you prefer.

Use this command to create an article:

curl localhost:8055/items/articles
   -X POST
   -H "Content-Type: application/json"
   -d "{\"title\": \"Hello World!\"}"

Use this command to retrieve all articles:

curl localhost:8055/items/articles

Or, in GraphQL, you can use the GraphQL endpoint:

# localhost:8055/graphql
query {
articles {
title
}
}

You can use any desktop GraphQL client or the online interactive client: https://graphiql-online.com/graphiql

This is just a sample of what the APIs have to offer. To keep it short in this quick introduction, we are skipping over the powerful query parameters that allow you to search, filter, sort, limit and nest relational data. To learn more about the flexibility Directus APIs provide, please refer to the Directus Documentation API Reference.

Going Live

Directus runs on anything that can run Node, but for the easiest deployment, we recommend using Azure App Service running the official Directus Docker Image. This enables you to run a highly available installation of Directus, without having to deal with server maintenance or other manual intervention.

Take these steps to get started:

Allow Azure services to reach the database:

az sql server firewall-rule create --resource-group {GROUP_NAME} --server {DB_SERVER_NAME} --name allow-azure-services --start-ip-address 0.0.0.0 --end ip-address 0.0.0.0

Create an App Service plan:

az appservice plan create --name {APP_PLAN_NAME} --resource-group {GROUP_NAME} --is-linux

Create the web app:

az webapp create --resource-group {GROUP_NAME} --plan {APP_PLAN_NAME} --name {APP_NAME} --deployment-container-image-name directus/directus:latest

Configure Azure to use port 8055 for Directus:

az webapp config appsettings set --resource-group {GROUP_NAME} --name {APP_NAME} --settings WEBSITES_PORT=8055

Import the environment variables from your local project installation:

az webapp config appsettings set --resource-group {GROUP_NAME} --name {APP_NAME} --settings DB_CLIENT=mssql
DB_HOST={DB_SERVER_NAME}.database.windows.net DB_PORT=1433 

DB_DATABASE={DB_NAME} DB_USER={DB_ADMIN_USER} DB_PASSWORD={DB_ADMIN_PASSWORD} DB_OPTIONS__ENCRYPT=true KEY={KEY} SECRET={SECRET}

(See your local .env file for the correct values to use above.)

You now have your Directus installation running on the web! You can access it directly on {APP_NAME}.azurewebsites.net. Try executing some of the requests from earlier to this new address!

Summary

Congratulations! You have successfully created a production-ready installation of Directus on top of your own Azure SQL database. Continue exploring Directus by adding more collections, fields, roles and permissions, discovering important insights, modifying files, and much more.

If you need a one-click, ready-to-go deployment of everything we went over, check out this article and GitHub Repo to learn how to complete an end-to-end setup. This is a great sandbox environment for you to explore and discover the power of Directus. And once you’ve kicked the tires and are ready to graduate from a development environment into a production environment, Directus Cloud has price points available for any project. Learn more about Directus Cloud here.

Want to write for us?

We're always looking for new writers and ideas. If you have an idea for content we should cover, or are interested in submitting your work, give us a shout.

Press and Media
For any press or media inquiries, feel free to reach out via our contact form and we can discuss. Contact us
Brand Assets
Use the link below to download a zip file containing of our brand guidelines, vector logo, or other resources. Download brand assets

Take back control of your data.

No spam, just important updates regarding Directus.