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:
- Model your data
- Run a migration using Prisma Migrate.
- 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 aschema.prisma
file. - Specifies the database provider as
sqlserver
inschema.prisma
file and provides a dummysqlserver
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;"
Update your 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: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;"
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")
}
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 populatingschema.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.
Top comments (0)