Prisma Meetup Recording

This sample has been presented and discussed in the Prisma Meetup 2021 #8. The recording is available here:

https://www.youtube.com/watch?v=-u2CwW40X0k

Folder Structure

• /api: the NodeJs Azure Function code used to provide the backend API, called by the Vue.Js client
• /api/src/functions/rest.ts: contains the Azure Function that provides REST endpoint support
• /api/src/functions/graphql.ts: contains the Azure Function that provides GraphQL endpoint support
• /api/prisma: contains the Prisma model
• /client: the Vue.Js client. Original source code has been taken from official Vue.js sample and adapted to call a REST or GraphQL client instead of using local storage to save and retrieve todos

Install the dependencies

Make sure you have Node installed. Since the free tier of Azure Static Web Apps only supports Node 12, it is recommended to use Node 12 for development too.

Also install the Azure Function Core Tools v4:

npm i -g azure-functions-core-tools@4

Make sure this dependency is not included in the package.json as otherwise the generated package will be too big for Azure Static Web Apps free tier (deployed .zip can max 10MB size).

Also install the Azure Static Web Apps CLI:

npm install -g @azure/static-web-apps-cli`

Now you can install the dependencies. Enter the ./api/ folder and install the dependencies:

npm install

then generate the Prisma client:

npx prisma generate

and then build the solution (always from in the ./api folder):

npm run build

Create the database

You have two options. If you only want to experiment locally, you can use SQL Server. If you prefer to use the cloud, you can use Azure SQL.

Option 1 - Start the SQL Server with Docker

If you want to develop locally without any dependency on Azure, you can run SQL Server locally using the included docker-compose.yaml file with the following command:

docker compose up -d

Now use the /api/.env.template file to create an .env file and add the correct information needed to access your SQL Server.

Create a .env file by copying .env.template inside the ./api folder, and then add the connection string to connect to the local SQL Server, for example:

DATABASE_URL=sqlserver://localhost:1433;database=prisma-demo;user=DB_USER;password=DB_PASSWORD;trustServerCertificate=true;encrypt=true

Option 2 - Create the Azure SQL database

Beginners: If you are completely new to Azure SQL, no worries! Here's a full playlist that will help you: Azure SQL for beginners.

Another option is to use an Azure SQL database also as a development database. If you need to create an Azure SQL database from scratch, an Azure SQL S0 database would be more than fine to run the tests.

Make sure you have an existing Azure SQL server, or create a new one (there is no cost associated with an Azure SQL server). Remember that you can use the Azure Free offering if needed:

az sql server create -g <resource-group> -n <server-name>  -u <admin-user> -p <admin-password> -l <location>

make sure you can access the server from your client machine by configuring the firewall:

az sql server firewall-rule create --server <server-name> -g <resource-group> --start-ip-address <your-ip> --end-ip-address <your-ip> --name MyClient

then create the Azure SQL database:

az sql db create -g <resource-group> -s <server-name> -n todo_prisma --service-objective S0

Note: Remember that if you don't have a Linux environment where you can run AZ CLI you can always use the Cloud Shell. If you prefer to do everything via the portal, here's a tutorial: Create an Azure SQL Database single database.

Prisma will connect to the database using the DATABASE_URL environment variable which can be defined in the ./api/.env file.

Create a .env file by copying .env.template inside the ./api folder and then define the database URL using the following format:

DATABASE_URL="sqlserver://DB_SERVER_NAME.database.windows.net:1433;database=DB_NAME;user=DB_USER;password=DB_PASSWORD;encrypt=true;trustServerCertificate=false;loginTimeout=30"

Create the database schema

Now that you have a database, you can create the database schema by using Prisma Migrate:

npx prisma migrate dev

Note: If you run the prisma migrate dev command with an Azure SQL database, you will need to also set the connection string for the shadow database which is necessary for development purposes. To avoid this you can run instead the prisma migrate deploy command which will execute existing migrations without the need for a shadow database.

Start the local development server

Start the Azure Static Web App server (in the root folder):

swa start ./client --api-location ./api

Enjoy the Static Web Site running locally!

Running on Azure

This is the amazing part of using Azure Static Web Apps. Deploying to Azure is completely automated via GitHub actions. There are some manual steps because the Static Web Apps CLI is still in Preview at the moment of writing and because Prisma and the Azure Static Web App GitHub Action need some help to get along.

1. Fork this repository
2. Get a GitHub Token
3. Run ./azure-deploy.sh. Please note that if this is the first time you run it, it will create an .env file in the root folder. Fill the .env file. Run the ./azure-deploy.sh again.
4. Once the deployment is done go to the Azure portal, and open the Azure Static Web App resource just created.
5. Open the "Configuration" pane and add a new environment variable named DATABASE_URL and assign the value of the database connection string mentioned before in the local development section.
6. Done! Well, not really, read on.

Fixing generated workflow file

The generated workflow file will not work. Even if the CI/CD pipeline will complete successfully, the Azure Static Web App will not work. This is due to how Oryx, the tool that automates the building and deployment for Static Web Apps, doesn't now how to properly deal with the nuances of Prisma. Fixing this issue is quite easy, just add the following enviroment variable to the workflow. The workflow file you have to change is in ./github/workflow. Note the name returned by the deployment script. For example if the deployment script reported that:

Static Web App created at: gentle-mud-01cd9ba1e.azurestaticapps.net

your workflow file will be ./github/workflow/azure-static-web-apps-gentle-mud-01cd9ba1e.yml.

You can make the requested small change right from your GitHub repository, if you don't want to clone the forked repo locally. Just after the line:

###### End of Repository/Build Configurations ######

in the "Build and Deploy" step, add these environment variables:

    env: # Add environment variables here
      PRE_BUILD_COMMAND: "npm install -g prisma@6.17.1"
      CUSTOM_BUILD_COMMAND: "npm install @prisma/client@6.17.1 && npm run build"
      POST_BUILD_COMMAND: "npm install @prisma/client@6.17.1"

Make sure you indent the lines correctly, as requested by YAML syntax, and than commit the change. (If you are using the GitHub online editor, and you don't see any red squiggly lines you should be good to go.)

Take a look at the sample workflow in the ./github/workflow folder to see what your workflow file should look like.

Running on Azure (yep!)

After you have commited the changes to the workflow file, the CI/CD pipeline will run again automatically (You can verify it from the "Action" section of your GitHub repo). Once the pipeline has run, you should have a working website. Go to http://[your-swa-name].azurestaticapps.net, and enjoy!

REST Endpoint

You can create, update and delete ToDos, that are then in turn stored in Azure SQL, completely via REST using the /api/todo endpoint. It supports GET, POST, PATCH and DELETE methods. For example using cUrl:

Get all available todos

curl -s -X GET https://[your-swa-name].azurestaticapps.net/api/todo

Get a specific todo

curl -s -X GET https://[your-swa-name].azurestaticapps.net/api/todo/123

Create a todo

curl -s -H "Content-Type: application/json" -X POST https://[your-swa-name].azurestaticapps.net/api/todo/ -d '{"title":"Hello world"}'

Update a todo

curl -s -H "Content-Type: application/json" -X PUT https://[your-swa-name].azurestaticapps.net/api/todo/123 -d '{"title":"World, hello!", "completed":true}'

Delete a todo

curl -s -X DELETE https://[your-swa-name].azurestaticapps.net/api/todo/123

A sample of REST endpoint usage in a web page is av