Learn - A Full Stack Application with Azure SQL & Prisma: Full Course

A real study case application how to apply Node.Js with:

• Azure SQL
• Prisma
• Vue.Js
• Azure Functions
• Azure Static Web Apps
• Azure Dev CLI

🚀 Resources Used

• Visual Studio Code
• Node.js 16.x
• Prisma
• Azure Functions Core Tools 4.x
• Azure SQL
• Azure Free Account
• Azure Static Web Apps
• Some Visual Studio Code Extensions:
  • Azure Tools Extension
  • Azure Functions Vs Code Extension
  • REST Client Extension
  • Remote Containers Extension
  • Prisma Vs Code Extension

FrontEnd Starter Project

Before starting to watch the video series we recommend that you download the project (FrontEnd side). Because we will use it to make the communication with the BackEnd created during this course!

FrontEnd Starter Project - HERE

💻 Video Series

Below you can see all the recorded videos about the application's development.

Videos	Description
Video 01 - Course Overview	In this video we will see about the course and the application that will be developed.
Video 02 - Learning Objectives	In this video we will see the learning objectives of the Prisma Fundamentals module
Video 03 - What is Prisma?	In this video we will learn what is Prisma?
Vídeo 04 - Why we Should use Prisma?	In this video we will learn why we should use Prisma, which has been considered a powerful ORM for JavaScript Developers.
Video 05 - Key Concepts about Prisma	In this video we will learn the main pillars around the Prisma!
Vídeo 06 - Important Resources & Recap	Recap on what we learned and how you can dig deeper into Prisma.
Vídeo 07 - Module 02: Overview	In this video you will see what will learn during the Module 02 about Azure SQL.
Vídeo 08 - What is Azure SQL?	In this video you will understand what is Azure SQL and the different options like: Single Database and Elastic Pool.
Vídeo 09 - Why should we use Azure SQL?	In this video you will understand why is so important to use Azure SQL in your projects!
Vídeo 10 - Azure SQL Support in Prisma	In this video we will show you that Azure SQL has a perfect fit with Prisma!
Vídeo 11 - Important Resources & Recap	Recap on what we learned and how you can dig deeper into Azure SQL.
Vídeo 12 - Module 03: Overview	In this video you will see what will learn during the Module 03 about Azure Static Web Apps.
Vídeo 13 - What is Azure Static Web Apps?	In this video we will learn what is Azure Static Web Apps.
Vídeo 14 - Understanding the Azure Static Web Apps Workflow	In this video we will understand under the hood how the Azure Static Web Apps workflow works.
Vídeo 15 - Why should we use Azure Static Web Apps?	In this video we will understand the benefits to use Azure Static Web Apps!
Vídeo 16 - Which libs/frameworks can I use with Azure Static Web Apps?	In this video we will see that can use Azure Static Web Apps with different libs and the most popular frameworks!
Vídeo 17 - Important Resources & Recap	Recap on what we learned and how you can dig deeper into Azure Static Web Apps.
Vídeo 18 - Dev Tools Overview & Installation	In this video we are going to install all the necessary dev tools that we will go into need to develop our application in the BackEnd side.
Vídeo 19 - Configuring the .devContainer folder	In this video we will understand the importance around to create a .devContainer folder in any project and a little overview about it!
Vídeo 20 - Important Resources & Recap	Recap what we saw during the module 04 and preparing for what is coming!
Vídeo 21 - Application Overview	In this video we will take a first look around the BackEnd application that we are going to build!
Vídeo 22 - Forking the Front-End project	In this video we are going to forking/cloning the Front-End starter project!
Vídeo 23 - Structuring the Back-End project	In this video we are going to structuring the BackEnd project using Azure Functions.
Vídeo 24 - Creating Database Server	In this video we are going to create our Database Server using the Azure Portal.
Vídeo 25 - Creating Database	Now we already created our Database server, it's time to create our Employee Database using Azure Portal.
Vídeo 26 - Creating Shadow Database	In this video we are going to create shadow database using Azure Portal.
Vídeo 27 - Installing Packages	In this video we are going to install all the necessary packages for our project!
Vídeo 28 - Modeling Database with Prisma	In this video we are going to learn how to initialize and modeling our database using Prisma.
Vídeo 29 - Using Prisma Migrations	In this video we are going to understand how Prisma migrations works!
Vídeo 30 - Using Prisma Studio	In this video we are going to take a look how Prisma Studio works and use it to manipulate our database.
Vídeo 31 - Developing 'CreateEmployee'	In this video we're going to create our first function: Create Employee using Prisma & Azure Functions
Vídeo 32 - Developing 'GetEmployees'	In this video we are going to develop the functionality to list all the employees using Azure Functions.
Vídeo 33 - Developing 'GetEmployee'	In this video we are going to develop the functionality to return an employee by Id using Azure Functions.
Vídeo 34 - Developing 'UpdateEmployee'	In this video we are going to develop the functionality to update an employee by Id using Azure Functions.
Vídeo 35 - Developing 'DeleteEmployee'	In this video we are going to develop the functionality to delete an employee by Id using Azure Functions.
Vídeo 36 - Deploying the App with ASWA	In this video we are going to deploy our application using Azure Static Web Apps with GitHub Actions Integration!
Vídeo 37 - Next Steps	In this video we are going to see the next steps that you should see after this course!

🔥 How to run the application locally?

This repo uses Development Containers. Make sure to have Docker installed and running on your machine before moving forward. If you want to know more about DevContainers and why they are so important, read here: Your open source project needs a DevContainer - Here's Why.

Project: client

To execute locally this project you will need to follow the steps bellow:

1. First go to the folder: client and run the command:

npm install

2. After to install all the Node.Js packages, now you can execute the command:

npm run serve

3. Now, open your browser on http://localhost:8080/. Thanks to the configuration defined in vue.config.js all requests will be automatically proxied to the local Azure Functions host. You can learn more about the integrated proxy in vue client here: VUE CLI Configuration Reference

Project: db

Depending on what you prefer, you can use a local SQL Server to Azure SQL DB hosted in Azure to run your local application.

Using local SQL Server

Thanks to DevContainers, SQL Server 2019 is already running in your enviroment. All you have to do is to create an .env file in the api folder and add the connection string to connect to the aformentioned SQL Server 2019:

# Database connection string
DATABASE_URL="sqlserver://db:1433;database=EmployeeDB;user=sa;password=P@ssw0rd;encrypt=true;trustServerCertificate=true;"

# Shadow database connection string for development
SHADOW_DATABASE_URL="sqlserver://db:1433;database=EmployeeDB;user=sa;password=P@ssw0rd;encrypt=true;trustServerCertificate=true;"

Unless you have changed the database name, login and password in the Dockerfile that comes into the .devcontainer folder, the above connection string is already correct. Of course make sure you use this connection string only for testing, as it is using the sa account to log into SQL Server, which is the most powerful administrative account and should not be used to connect your application to the database.

Using Azure SQL database

If you want to test Azure SQL database, to make sure everything will work perfectly even when deployed on Azure, you need to create an Azure SQL database before.

Using the Azure Dev CLI

The following prerequisites are required to use this application. Please ensure that you have them all installed locally.

• Azure Developer CLI
  • Windows:

    powershell -ex AllSigned -c "Invoke-RestMethod 'https://aka.ms/install-azd.ps1' | Invoke-Expression"

  • Linux/MacOS:

    curl -fsSL https://aka.ms/install-azd.sh | bash
    

• Azure CLI (2.38.0+)

To create the necessary infrastructure on Azure:

1. Open a terminal, change directory to the root of this project.
2. Run the following command to initialize the project, provision Azure resources.

azd provision

For more details, read the Azure Dev CLI documentation.

Once the infrastructure has been created, create a .env file in the api folder and copy/paste content from .azure/azure-sql-prisma-vue/.env. We only care about the DATABASE_URL and SHADOW_DATABASE_URL variables:

# Database connection string
DATABASE_URL="sqlserver://DB_SERVER_NAME.database.windows.net:1433;database=DB_NAME;user=DB_USER@DB_SERVER_NAME;password={PASSWORD};encrypt=true"

# Shadow database connection string for development
SHADOW_DATABASE_URL="sqlserver://DB_SERVER_NAME.database.windows.net:1433;database=DB_NAME;user=DB_USER@DB_SERVER_NAME;password={PASSWORD};encrypt=true"

Using the Azure Portal

The are different ways you can create an Azure SQL Database. Using the Portal, using AZ CLI or Powershell. The easiest is using the Portal, and you can have see complete walk-through in this recording: Demo: Deploy Azure SQL Database | Azure SQL for beginners (Ep. 14). If you prefer reading instead of watching, here a complete quickstart guide available, with detailed steps for each option: Quickstart: Create an Azure SQL Database single database. If you want something more concise and direct, you can to follow the simple 6-steps guide available here: Create and connect to an Azure SQL DB.

Make sure you create two Azure SQL databases:

• azuresql-prisma-database
• azuresql-prisma-database-shadow

And you also create two users, one for each database. If you are not sure how to do it, don't worry, the links mentioned above will help you in going throught the simple process.

Once the database have beem created, include the Azure SQL Server connection string creating an .env file in the api folder:

# Database connection string
DATABASE_URL="sqlserver://DB_SERVER_NAME.database.windows.net:1433;database=DB_NAME;user=DB_USER@DB_SERVER_NAME;password={PASSWORD};encrypt=true"

# Shadow database connection string for development
SHADOW_DATABASE_URL="sqlserver://DB_SERVER_NAME.database.windows.net:1433;database=DB_NAME;user=DB_USER@DB_SERVER_NAME;password={PASSWORD};encrypt=true"

Project: api

1. First go to the folder: api and run the command:

> npm install

2. Make sure you have an .env file configured as explained in the Project: db section above.

3. Now create a file called: local.settings.json (root of the project) and include this information below:

{
  "IsEncrypted": false,
  "Values": {
    "FUNCTIONS_WORKER_RUNTIME": "node",
    "AzureWebJobsStorage": ""
  },
  "Host": {
    "LocalHttpPort": 7071,
    "CORS": "*"
  }
}

4. Now we need to execute the prisma migrate command. Run the command below

> npx prisma migrate dev

5. Now you can execute the command:

> npm run dev

You will see the message saying the application is running in the port: http://localhost:7071/api/

Objective	HTTP Verb	Route
CreateEmployee	POST	http://localhost:7071/api/employees
GetEmployees	GET	http://localhost:7071/api/employees
GetEmployee	GET	http://localhost:7071/api/{id}
UpdateEmployee	PUT	http://localhost:7071/api/{id}
DeleteEmployee	DELETE	http://localhost:7071/api/{id}

⭐️ Important Resources

• ✅ Build serverless, full stack applications in Azure - Free Course
• ✅ Azure SQL Documentation
• ✅ Microsoft Learn Free Course - Azure Static Web Apps
• ✅ Microsoft Learn Free Course - Build Serverless APIs with Azure Functions
• ✅ Microsoft SQL Server Support in Prisma is Production-Ready
• ✅ Prisma Client CRUD Docs
• ✅ Deploying to Azure Functions Documentation

❓ Questions? Comments?

If you have any questions about the code developed, feel free to open an ISSUE HERE. We'll get back to you soon!