Today’s web applications demand the ability to efficiently handle user data, store it in a database and wrangle it to provide user feedback.

Relational databases like Azure SQL have come a long way in ensuring that your application data is safely stored even as your application scales. However, adopting rapid development where you frequently evolve the database schema and application code to introduce new features brings many challenges that reduce developer productivity.

Some of these challenges include:

• How do you ensure that the application code interacting with the database aligns with the database schema?
• How do you verify the correctness of database queries in your application?
• How do you approach data modeling and schema migrations?

For example, imagine adding a new required column to one of your database tables that your application code writes to. If you’re using raw SQL to insert data from the application, there’s no way for you to verify that the query is valid other than trying to run the query. If you’re using an ORM, you have the burden of keeping your application models in sync with the database schema. Adding a new required column to a database table typically requires you to add the new required column to the application models by hand.

The Node.js ecosystem – while becoming increasingly popular to build database-backed applications – does not provide modern tools for application developers to deal with these challenges.

In this blog post, you will learn about Prisma and how it addresses these challenges by creating a Node.js app with Prisma, TypeScript, and Azure SQL.

Prisma

Prisma is an open-source next-generation ORM for Node.js and TypeScript that eliminates many of these problems and makes you more productive and confident when building data-intensive applications. You can use it as an alternative to traditional ORMs and SQL query builders to read and write data to your database.

It consists of the following tools:

• Prisma Client: Auto-generated and type-safe database client
• Prisma Migrate: Declarative data modeling and auto-generated SQL migrations
• Prisma Studio: Modern UI to view and edit data

There are three core concepts central to development with Prisma:

• The Prisma schema: A declarative single source of truth where you define your data model and other Prisma-related configuration, which can be written by hand or populated by introspecting an existing database. It’s worth noting that the Prisma schema is a human-readable DSL (Domain Specific Language).
• Type safety: A way to ensure that all application code interacting with the database can only do so safely. Attempting to query a non-existent column immediately raises a type error. Prisma gives you type safety without the burden of manually defining types based on your database schema using TypeScript and code generation.
• Code generation: You should only need to write things once. Prisma saves you time by auto-generating two artifacts that you would otherwise have to write by hand:
  • Fully typed TypeScript database client
  • SQL migrations based on changes in the Prisma schema.

Prisma can be used in any Node.js project that uses a database. For example, you can build REST and GraphQL APIs with it.

Quickstart with Prisma and Azure SQL

In this part, you will create a new Node.js project with Prisma, TypeScript, and Azure SQL.

You will begin by creating the project and configuring the connection string to Azure SQL. Next, you will define the data model using the Prisma schema, create the database schema with Prisma Migrate, query the database with Prisma Client, and explore the data with Prisma Studio.

Prerequisites

• Node.js installed (minimum v12.6.x)
• Azure SQL database server with two databases accessible from your IP
• Visual Studio Code with the Prisma extension installed.

Creating the Azure SQL database

You can use the following Azure CLI commands to create a resource group, an Azure SQL database server, the two databases, and the firewall rule.

Create the resource group:

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

Create the Azure SQL database server:

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

Create the main database:

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

Create the shadow database:

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

Create the firewall rule to allow access from your machine:

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

Note: For development with Prisma and Azure SQL you will need a shadow database.

Creating the project

As a first step, create a project directory and navigate into it:

mkdir prisma-azure-sql
cd prisma-azure-sql

Next, initialize the project and install the Prisma CLI as a development dependency to it:

npm init -y
npm install prisma typescript ts-node @types/node --save-dev

This creates a package.json with an initial setup for your TypeScript app.

Next, create a tsconfig.json file and add the following configuration to it:

{
  "compilerOptions": {
    "sourceMap": true,
    "outDir": "dist",
    "strict": true,
    "lib": ["esnext"],
    "esModuleInterop": true
  }
}

Initializing Prisma

Next, set up your Prisma project by creating your Prisma schema file with the following command:

npx prisma init --datasource-provider sqlserver

This command will create two files:

• prisma/schema.prisma: contains the Prisma schema where you define your data model and the configuration necessary for Prisma to connect to Azure SQL.
• .env: contains environment variables for your database connection.

In the next step, you will connect Prisma to Azure SQL.

Connecting to Azure SQL

In this step, you will set up the database connection strings in the .env file and configure the shadow database for creating migrations.

Open the prisma/schema.prisma and update the datasource block as follows:

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

Note: The shadow database is created and deleted automatically* each time you run a development-focused command, e.g. prisma migrate dev and is primarily used to detect problems such a schema drift.

Next, open the .env file and define the following environment 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"

Replace DB_SERVER_NAME, DB_NAME, DB_USER, PASSWORD with the correct information and be sure to use a different DB_NAME for the SHADOW_DATABASE_URL.

Defining the data model

In this step, you will define a data model for a simple blog with two tables: User and Post with a 1-to-many relation between the two so that each user can have multiple related blog posts.

Open the Prisma schema file (prisma/schema.prisma) and add the following:

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  email     String   @unique
  name      String?
  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
}

You’ve just defined two models with some fields and a relation between them. In the Prisma schema, each model maps to a database table and each field to a database column. The posts and author relation fields won’t be created in the database; their purpose is for accessing relations in Prisma Client.

Now that you have your data model defined, you will use Prisma Migrate to create a migration and run it to create the database schema.

Creating the database schema

Run the following Prisma Migrate command to create and run a migration:

npx prisma migrate dev

When prompted to enter a name for the migration, enter init, and press Enter to continue.

You should see the following output:

The following migration(s) have been created and applied from new schema changes:

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

Your database is now in sync with your schema.

Running generate... (Use --skip-generate to skip the generators)

✔ Generated Prisma Client (3.2.0) to ./node_modules/@prisma/client in 157ms

Congratulations, you just created the database schema. Let’s unpack what the prisma migrate dev command just did:

• Created the init SQL migration
• Executed the sql migration to create the database schema
• Added the @prisma/client dependency
• Generated Prisma Client

If you remember from the three core concepts explained above, Prisma relies heavily on code generation to make you more productive. In this step Prisma inspected your Prisma schema, compared it against the database (which was empty), created a migration accordingly, and executed it. Finally, after Prisma created the database schema, Prisma Client was generated.

In the next step, you will see how Prisma Client enables type-safe access to the database.

Interacting with the database with Prisma Client

In this step, you will use Prisma Client to create a user and related posts and then query it.

Creating the script.ts file in the root of your project and add the following to it:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {

}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

Currently, the script instantiates Prisma Client and exits. Next, you write your first Prisma Client query to create a user and two related posts.

In the main function add the following query:

const user = await prisma.user.create({
  data: {
    email: 'hana@prisma.io',
    name: 'Hana',
    posts: {
      create: [
        {
          title: 'First blog post about Prisma and Azure SQL',
          published: true,
        },
        {
          title: 'Azure Functions and the serverless paradigm',
          published: false,
        },
      ],
    },
  },
})

Prisma will create the user and two related posts in a single transaction.

Now, run the query with the following command:

ts-node script.ts

You should see the following output:

{
  id: 1,
  createdAt: 2021-10-06T07:22:18.870Z,
  email: 'hana@prisma.io',
  name: 'Hana'
}

Congratulations, you successfully inserted data into the database.

With Prisma, all queries are fully typed. This means that you get two benefits:

1. You get rich autocompletion as you write the query.
2. The return value of every Prisma Client query is inferred.

Here’s what autocompletion looks like: 

Here’s how the return type is inferred when querying for a user and related posts: 

Now that you’ve successfully run a Prisma Client query feel free to explore the Prisma Client API using the autocompletion in VSCode and write a query to fetch users ordered by the number of related posts in descending order.

Hint: The findMany method allows querying for multiple rows and accepts an orderBy object.

Next, you will explore the data with Prisma Studio.

Exploring data visually with Prisma Studio

Prisma Studio is a modern UI to view and edit data.

To open it, run the following command in your terminal:

npx prisma studio

Note: Running the command will start Prisma Studio on http://localhost:5555

As demonstrated in the following gif, with Prisma Studio, you can view and create rows in your database:

Prisma and GraphQL and REST

• Azure Static WebApp: to bind everything together in one easy package, natively integrated with GitHub CI/CD pipeline
• Vue.Js as front-end client
• Azure Function for providing serverless back-end infrastructure
• NodeJS for the back-end logic
• TypeScript for the back-end logic
• Prisma to interact with the Azure SQL database
• Azure SQL as database to store ToDo data
• GitHub Actions to Deploy the full-stack website (thanks to Azure Static Web Apps)

Here: https://github.com/azure-samples/azure-sql-db-prisma

Summary

Congratulations! You have successfully created a new Node.js project with Prisma, TypeScript, and Azure SQL. You learned about data modeling with the Prisma schema. You created the database schema with Prisma Migrate, wrote a script to write to the Azure SQL database with Prisma Client, and explored the data with Prisma Studio.

You learned about the three core concepts of Prisma and how they work in practice:

• The Prisma schema
• Code generation
• Type safety

For more insight into Prisma Client’s API, check out the Prisma Client CRUD docs.

If you’re interested in how to build a full-stack application with Prisma, TypeScript, Vue.js, Azure SQL, and Azure Static Web Apps, check out this talk by Davide Mauri in which he shows an implementation of ToDoMVC using both REST and GraphQL and Github Actions to automate deployment. Here’s the GitHub Repo with the end-to-end working code:

https://github.com/Azure-Samples/azure-sql-db-prisma

If you’re looking to deploy to Azure Functions, check out the Prisma Azure Functions deployment guide.

As a next step, check out the Prisma Data Platform, which gives you a hosted collaborative data browser (similar to Prisma Studio), a query console to test Prisma Client queries directly from the browser. It allows you to create a new Prisma project in a minute.

Photo by Dobromir Hristov from Pexels