Database Migrations for .NET and Entity Framework with Prisma

Introduction

In this article, you will learn how to model your data using Prisma, run a migration with Prisma Migrate and then introspect your database with .NET to generate your Entity Framework models. This article will use Azure SQL/ SQL Server.

This article only offers an alternative data modelling workflow that is succinct and offer a better development experience. Besides .NET and the Entity framework, you can also use Prisma with your tool/ frameworks of choice such as Django.

Prisma auto-generates migration files in SQL that you can modify before applying them on your database. The workflow following this approach would be:

1. Model your data
2. Run a migration using Prisma Migrate.
3. Use the Entity Framework to reverse engineer/ scaffold/ introspect your database and generate models and your database context.

What is Prisma?

Prisma is an object-relational mapper (ORM) that provides a declarative way to define your database models that are easy to read and comprehend. Prisma also provides Prisma Client – an intuitive and type-safe query builder based off your schema.

At the core of Prisma is the Prisma Schema – aka schema file. The schema contains 3 parts: the data sources, generators and your data model definition.

Prerequisites

To follow along, ensure you have the following:

• Familiarity with .NET and the Entity framework
• Installed Node.js v 12.6.x or higher
• Installed .NET locally
• A SQL Server or Azure SQL database
• Prisma Extension for VS Code users

Note: If you're not familiar with setting up SQL Server, check out Setting up a local SQL Server database.

Step 1: Create a new console application

The first step is to create a new .NET console app in your working directory. This guide uses a console application to keep it simple. However, feel free to create any other type of application.

dotnet new console --output *__app-name__*

Once the command is done, navigate to the project and confirm that your project is running.

cd *__app-name__*
dotnet run

If the output on the terminal is Hello World!, everything seems to be running just fine! 🎉

Step 2: Add Prisma to your project

On a separate terminal window but still within the same project run the following commands.

mkdir prisma-migrate
cd prisma-migrate
npm init -y

The commands create a new directory, navigate to the folder and initializes it as a Node.js project. The Prisma CLI is the only dependency for this project.

npm install --save-dev prisma

Initialize Prisma

Once the install is done, run the following command.

npx prisma init --database-provider sqlserver

The command:

• Creates a .env file at the root of the project for storing environment variables
• Creates prisma folder containing a schema.prisma file.
• Specifies the database provider as sqlserver in schema.prisma file and provides a dummy sqlserver connection string in .env.

The schema.prisma file defines the database connection and the Prisma Client generator. This is where you will also define your database models.

/// prisma/schema.prisma
datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

For this guide, you can delete generator client as it is relevant in a TypeScript/ JavaScript project. The generator client is used to generate the TypeScript definitions for the Prisma Client.

Update .env with your database connection string. Refer to connection details for information on how to configure the connection string.

# .env
DATABASE_URL="sqlserver://HOST:PORT;database=DATABASE;user=USER;password=PASSWORD;"

If you're using a remote database during development like Azure SQL, open this to learn more about how to configure the shadow database.

Create a new database which will be the shadow database on Azure. Update your .env file to include the SHADOW_DATABASE_URL as well. The shadow database will be used in the data modelling step

# .env
DATABASE_URL="sqlserver://HOST:PORT;database=DATABASE;user=USER;password=PASSWORD;"
SHADOW_DATABASE_URL="sqlserver://HOST:PORT;database=DATABASE;user=USER;password=PASSWORD;"

Update your schema.prisma file with the connection string for the shadow database.

/// prisma/schema.prisma
datasource db {
  provider          = "sqlserver"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

A shadow database is a second, temporary database for use in the local development workflow. When running a migration, the secondary database is created and deleted automatically unless you're using a cloud-hosted database.

Here's an illustration of how the shadow database works:

Data modelling

This example will define 2 models: Post and User.The relationship between User and Post is a one-to-many relationship.

The models defined in schema.prisma will resemble a GraphQL syntax . The models also map to a table in your database.

/// prisma/schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  content   String?
  title     String
  createdAt DateTime @default(now())
  author    User?    @relation(fields: [authorId], references: [id])
  authorId  Int?
}

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

Refer to the Prisma Schema Reference for further information on database field types, attributes, conventions and functions.

Create a database migration

After modelling your data, it's time to apply the changes to your database. On your terminal window, run npx prisma migrate dev.

npx prisma migrate dev --name init

If no database exists, a new database with the database name specified in your .env file will be created. The command also creates a new database migration that is located in ./prisma/migrations directory. The migration is in SQL and is applied against your database. init refers to the migration name which you can change to something more semantic.

If you wish to only create a migration but not apply it to your database, pass the --create-only option to Prisma Migrate. This will generate the SQL and allow you to customize it before applying it against your database.

You can confirm that the changes have been applied, run:

npx prisma studio

Select the Model you would like to view/ edit data.

Click Save 1 change to apply the changes.

Navigate to the User model, create a new user, link the post created in the previous step and save the change.

Step 3: Introspect your database aka Reverse Engineering

Entity framework allows you to introspect your database schema and scaffold entity type classes and the database context based off an existing database.

Navigate out of prisma-migrate directory to the root of your project.

cd ../

Install Entity Framework tools you will require to work with SQL Server in your project.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer 
dotnet add package Microsoft.EntityFrameworkCore.Tools 
dotnet add package Microsoft.EntityFrameworkCore.Design

Scaffold entity type classes based on your database schema to your project.

dotnet ef dbcontext scaffold "Server=**HOST**;Database=**DATABASE_NAME**;User Id=**USER**;Password=**PASSWORD**" Microsoft.EntityFrameworkCore.SqlServer --context-dir DbContext --context BlogExampleContext --output-dir Models

• Microsoft.EntityFrameworkCore.SqlServer refers to the package to be used to introspect the database.
• --context-dir and --output-dir refer to the location of the context and the entity type classes after introspection
• --context refers to the name of your database context

On a successful run 2 folders with 4 files will be created in your project.

*app-name*/
┣ DbContext/
┃ ┗ BlogExampleContext.cs
┣ Models/
┃ ┣ Post.cs
┃ ┗ PrismaMigration.cs
┃ ┗ User.cs
┣ ...

As you will notice, PrismaMigration.cs was not defined in schema.prisma. This is a table created by Prisma to track the migrations that have been created and applied against your database.

To exclude the PrismaMigrations table, and re-run the previous command but specify the tables you would like using the --table and —force options to overwrite the existing models.

dotnet ef dbcontext scaffold "Server=**HOST**;Database=**DATABASE_NAME**;User Id=**USER**;Password=**PASSWORD**" Microsoft.EntityFrameworkCore.SqlServer --context-dir DbContext --context BlogExampleContext --output-dir Models --force --table User --table Post

This time, 3 files are generated and the PrismaMigrations.cs file is excluded when scaffolding.

If you're interested in migrating to Prisma with an existing database, the Prisma CLI provides db pull for introspecting the database and populating schema.prisma with your database models.

Conclusion

Congratulations! You have successfully modelled your entities, run a migration with Prisma and scaffolded your models and context with Entity Framework.

Learn how you can set up and deploy a Prisma based Node.js REST API to Azure Functions together with Azure SQL in Deploying to Azure Functions.

If you would like to learn more about Prisma, check out the documentation and feel free to create an issue or start a discussion on GitHub.