Database Migrations for .NET and Entity Framework with Prisma
Tags: prisma, database, backend, entityframework
Published: 2021-11-04 12:51:58

---
title: Database Migrations for .NET and Entity Framework with Prisma
published: true
description: Learn how you can model your data and manage database migrations with Prisma, and auto-generate your database models in .NET using Entity Framework
tags: prisma, databases, backend, entityframework
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6wl2j0jwj74z9jljdskm.png
---

## Introduction

In this article, you will learn how to model your data using [Prisma](https://www.prisma.io/), run a migration with Prisma Migrate and then introspect your database with [.NET](https://dotnet.microsoft.com/) to generate your [Entity Framework](https://docs.microsoft.com/en-us/aspnet/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](https://www.djangoproject.com/).

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](https://www.prisma.io/migrate).
3. Use the Entity Framework to *[reverse engineer](https://docs.microsoft.com/en-us/ef/core/managing-schemas/scaffolding?tabs=dotnet-core-cli)/ 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](https://www.prisma.io/docs/concepts/components/prisma-client) – an intuitive and  type-safe query builder based off your schema.

At the core of Prisma is the [Prisma Schema](https://www.prisma.io/docs/concepts/components/prisma-schema) – aka *schema file*. The schema contains 3 parts: the [data sources](https://www.prisma.io/docs/concepts/components/prisma-schema/data-sources/), [generators](https://www.prisma.io/docs/concepts/components/prisma-schema/generators) and your [data model definition](https://www.prisma.io/docs/concepts/components/prisma-schema/data-model). 

## Prerequisites

To follow along, ensure you have the following:

- Familiarity with .NET and the [Entity framework](https://docs.microsoft.com/en-us/aspnet/entity-framework)
- Installed Node.js v 12.6.x or higher
- Installed .NET locally
- A SQL Server or Azure SQL database
- [Prisma Extension](https://marketplace.visualstudio.com/items?itemName=Prisma.prisma) for VS Code users

> **Note:** If you're not familiar with setting up SQL Server, check out [Setting up a local SQL Server database](https://www.prisma.io/dataguide/mssql/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.

```bash
dotnet new console --output *__app-name__*
```

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

```bash
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.

```bash
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](https://www.prisma.io/docs/concepts/components/prisma-cli) is the only dependency for this project. 

```bash
npm install --save-dev prisma
```

### Initialize Prisma

Once the install is done, run the following command.

```bash
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.

```groovy
/// 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](https://www.prisma.io/docs/concepts/database-connectors/sql-server#connection-details) for information on how to configure the connection string.

```bash
# .env
DATABASE_URL="sqlserver://HOST:PORT;database=DATABASE;user=USER;password=PASSWORD;"
```
{% details If you're using a remote database during development like Azure SQL, open this to learn more about how to configure the [shadow database](https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database). %} 
Create a new database which will be the [shadow database](https://www.prisma.io/docs/concepts/components/prisma-migrate/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 

```bash
# .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. 

```groovy
/// 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:

![https://www.prisma.io/docs/static/fa8149049da32d83d8014e3d14d644c0/a6d36/shadow-database.png](https://www.prisma.io/docs/static/fa8149049da32d83d8014e3d14d644c0/a6d36/shadow-database.png)
{% enddetails %}
    
  

### 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.

```groovy
/// 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](https://www.prisma.io/docs/reference/api-reference/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`.

```bash
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:

```bash
npx prisma studio
```

![Screenshot 2021-10-17 at 19.49.28.png](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/de9p51dk06cbmaviv5lt.png)

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

![Screenshot 2021-10-17 at 19.51.25.png](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tm5gi64m3xeb68utpt5l.png)

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.

![Screenshot 2021-10-17 at 19.53.24.png](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ph7m5cz8jho4m5p921zw.png)

## 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.

```bash
cd ../
```

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

```bash
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.

```bash
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.

```bash
*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.

```bash
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](https://www.prisma.io/docs/guides/deployment/deployment-guides/deploying-to-azure-functions).

If you would like to learn more about Prisma, check out the [documentation](https://www.prisma.io/docs) and feel free to create an [issue](https://github.com/prisma/prisma/issues) or start a [discussion](https://github.com/prisma/prisma/discussions) on GitHub.