Migrate from TypeORM

2.1. Set up Prisma

Before you can introspect your database, you need to set up your Prisma schema and connect Prisma to your database. Run the following command in your terminal to create a basic Prisma schema file:

$npx prisma init

This command created a new directory called prisma with the following files for you:

• schema.prisma: Your Prisma schema file that specifies your database connection and models
• .env: A dotenv to configure your database connection URL as an environment variable

The Prisma schema file currently looks as follows:

prisma/schema.prisma
1// This is your Prisma schema file,
2// learn more about it in the docs: https://pris.ly/d/prisma-schema
3
4datasource db {
5  provider = "postgresql"
6  url      = env("DATABASE_URL")
7}
8
9generator client {
10  provider = "prisma-client-js"
11}

2.2. Connect your database

If you're not using PostgreSQL, you need to adjust the provider field on the datasource block to the database you currently use:

schema.prisma
1datasource db {
2  provider = "postgresql"
3  url      = env("DATABASE_URL")
4}

Once that's done, you can configure your database connection URL in the .env file. Here's how the database connection from TypeORM maps to the connection URL format used by Prisma:

ormconfig.json
1{
2  "type": "postgres",
3  "host": "localhost",
4  "port": 5432,
5  "username": "alice",
6  "password": "myPassword42",
7  "database": "blog-typeorm"
8}

.env
1DATABASE_URL="postgresql://alice:myPassword42@localhost:5432/blog-typeorm"

.env
1DATABASE_URL="postgresql://alice:myPassword42@localhost:5432/blog-typeorm?schema=myschema"

2.3. Introspect your database using Prisma

With your connection URL in place, you can introspect your database to generate your Prisma models:

$npx prisma db pull

This creates the following Prisma models:

prisma/schema.prisma
1model typeorm_migrations {
2  id        Int    @id @default(autoincrement())
3  timestamp Int
4  name      String
5
6  @@map("_typeorm_migrations")
7}
8
9model category {
10  id                       Int                        @id @default(autoincrement())
11  name                     String
12  post_categories_category post_categories_category[]
13}
14
15model post {
16  id                       Int                        @id @default(autoincrement())
17  title                    String
18  content                  String?
19  published                Boolean                    @default(false)
20  authorId                 Int?
21  user                     user?                      @relation(fields: [authorId], references: [id])
22  post_categories_category post_categories_category[]
23}
24
25model post_categories_category {
26  postId     Int
27  categoryId Int
28  category   category @relation(fields: [categoryId], references: [id])
29  post       post     @relation(fields: [postId], references: [id])
30
31  @@id([postId, categoryId])
32  @@index([postId], name: "IDX_93b566d522b73cb8bc46f7405b")
33  @@index([categoryId], name: "IDX_a5e63f80ca58e7296d5864bd2d")
34}
35
36model profile {
37  id     Int     @id @default(autoincrement())
38  bio    String?
39  userId Int?    @unique
40  user   user?   @relation(fields: [userId], references: [id])
41}
42
43model user {
44  id      Int      @id @default(autoincrement())
45  name    String?
46  email   String   @unique
47  post    post[]
48  profile profile?
49}

The generated Prisma models represent your database tables and are the foundation for your programmatic Prisma Client API which allows you to send queries to your database.

2.4. Create a baseline migration

To continue using Prisma Migrate to evolve your database schema, you will need to baseline your database.

First, create a migrations directory and add a directory inside with your preferred name for the migration. In this example, we will use 0_init as the migration name:

$mkdir -p prisma/migrations/0_init

Next, generate the migration file with prisma migrate diff. Use the following arguments:

• --from-empty: assumes the data model you're migrating from is empty
• --to-schema-datamodel: the current database state using the URL in the datasource block
• --script: output a SQL script

$npx prisma migrate diff --from-empty --to-schema-datamodel prisma/schema.prisma --script > prisma/migrations/0_init/migration.sql

Review the generated migration to ensure everything is correct.

Next, mark the migration as applied using prisma migrate resolve with the --applied argument.

$npx prisma migrate resolve --applied 0_init

The command will mark 0_init as applied by adding it to the _prisma_migrations table.

You now have a baseline for your current database schema. To make further changes to your database schema, you can update your Prisma schema and use prisma migrate dev to apply the changes to your database.

2.5. Adjust the Prisma schema (optional)

The models that were generated via introspection currently exactly map to your database tables. In this section, you'll learn how you can adjust the naming of the Prisma models to adhere to Prisma's naming conventions.

All of these adjustment are entirely optional and you are free to skip to the next step already if you don't want to adjust anything for now. You can go back and make the adjustments at any later point.

As opposed to the current snake_case notation of TypeORM models, Prisma's naming conventions are:

• PascalCase for model names
• camelCase for field names

You can adjust the naming by mapping the Prisma model and field names to the existing table and column names in the underlying database using @@map and @map.

Also note that you can rename relation fields to optimize the Prisma Client API that you'll use later to send queries to your database. For example, the post field on the user model is a list, so a better name for this field would be posts to indicate that it's plural.

You can further completely remove model that represents the TypeORM migrations table (called _typeorm_migrations here) from the Prisma schema.

Here's an adjusted version of the Prisma schema that addresses these points:

prisma/schema.prisma
1model Category {
2  id                Int                @id @default(autoincrement())
3  name              String
4  postsToCategories PostToCategories[]
5
6  @@map("category")
7}
8
9model Post {
10  id                Int                @id @default(autoincrement())
11  title             String
12  content           String?
13  published         Boolean            @default(false)
14  authorId          Int?
15  author            User?              @relation(fields: [authorId], references: [id])
16  postsToCategories PostToCategories[]
17
18  @@map("post")
19}
20
21model PostToCategories {
22  postId     Int
23  categoryId Int
24  category   Category @relation(fields: [categoryId], references: [id])
25  post       Post     @relation(fields: [postId], references: [id])
26
27
28  @@id([postId, categoryId])
29  @@index([postId], name: "IDX_93b566d522b73cb8bc46f7405b")
30  @@index([categoryId], name: "IDX_a5e63f80ca58e7296d5864bd2d")
31  @@map("post_categories_category")
32}
33
34model Profile {
35  id     Int     @id @default(autoincrement())
36  bio    String?
37  userId Int?    @unique
38  user   User?   @relation(fields: [userId], references: [id])
39
40  @@map("profile")
41}
42
43model User {
44  id      Int      @id @default(autoincrement())
45  name    String?
46  email   String   @unique
47  posts   Post[]
48  profile Profile?
49
50  @@map("user")
51}

4.1. Replacing queries in GET requests

The REST API has three routes that accept GET requests:

• /feed: Return all published posts
• /filterPosts?searchString=SEARCH_STRING: Filter returned posts by SEARCH_STRING
• /post/:postId: Returns a specific post

Let's dive into the route handlers that implement these requests.

src/controllers/FeedAction.ts
1import { getManager } from 'typeorm'
2import { Post } from '../entity/Post'
3
4export async function feedAction(req, res) {
5  const postRepository = getManager().getRepository(Post)
6
7  const publishedPosts = await postRepository.find({
8    where: { published: true },
9    relations: ['author'],
10  })
11
12  res.send(publishedPosts)
13}

src/controllers/FeedAction.ts
1import { prisma } from '../prisma'
2
3export async function feedAction(req, res) {
4  const publishedPosts = await prisma.post.findMany({
5    where: { published: true },
6    include: { author: true },
7  })
8
9  res.send(publishedPosts)
10}

src/controllers/FilterPostsActions.ts
1import { getManager, Like } from 'typeorm'
2import { Post } from '../entity/Post'
3
4export async function filterPostsAction(req, res) {
5  const { searchString } = req.query
6  const postRepository = getManager().getRepository(Post)
7
8  const filteredPosts = await postRepository.find({
9    where: [
10      { title: Like(`%${searchString}%`) },
11      { content: Like(`%${searchString}%`) },
12    ],
13  })
14
15  res.send(filteredPosts)
16}

src/controllers/FilterPostsActions.ts
1import { prisma } from '../prisma'
2
3export async function filterPostsAction(req, res) {
4  const { searchString } = req.query
5
6  const filteredPosts = prisma.post.findMany({
7    where: {
8      OR: [
9        {
10          title: { contains: searchString },
11        },
12        {
13          content: { contains: searchString },
14        },
15      ],
16    },
17  })
18
19  res.send(filteredPosts)
20}

src/controllers/GetPostByIdAction.ts
1import { getManager } from 'typeorm'
2import { Post } from '../entity/Post'
3
4export async function getPostByIdAction(req, res) {
5  const { postId } = req.params
6  const postRepository = getManager().getRepository(Post)
7
8  const post = await postRepository.findOne(postId)
9
10  res.send(post)
11}

src/controllers/GetPostByIdAction.ts
1import { prisma } from '../prisma'
2
3export async function getPostByIdAction(req, res) {
4  const { postId } = req.params
5
6  const post = await prisma.post.findUnique({
7    where: { id: postId },
8  })
9
10  res.send(post)
11}

4.2. Replacing queries in POST requests

The REST API has three routes that accept POST requests:

• /user: Creates a new User record
• /post: Creates a new Post record
• /user/:userId/profile: Creates a new Profile record for a User record with a given ID

src/controllers/CreateUserAction.ts
1import { getManager } from 'typeorm'
2import { User } from '../entity/User'
3
4export async function createUserAction(req, res) {
5  const { name, email } = req.body
6
7  const userRepository = getManager().getRepository(User)
8
9  const newUser = new User()
10  newUser.name = name
11  newUser.email = email
12  userRepository.save(newUser)
13
14  res.send(newUser)
15}

src/controllers/CreateUserAction.ts
1import { prisma } from '../prisma'
2
3export async function createUserAction(req, res) {
4  const { name, email } = req.body
5
6  const newUser = await prisma.user.create({
7    data: {
8      name,
9      email,
10    },
11  })
12
13  res.send(newUser)
14}

src/controllers/CreateDraftAction.ts
1import { getManager } from 'typeorm'
2import { Post } from '../entity/Post'
3import { User } from '../entity/User'
4
5export async function createDraftAction(req, res) {
6  const { title, content, authorEmail } = req.body
7
8  const userRepository = getManager().getRepository(User)
9  const user = await userRepository.findOne({ email: authorEmail })
10
11  const postRepository = getManager().getRepository(Post)
12
13  const newPost = new Post()
14  newPost.title = title
15  newPost.content = content
16  newPost.author = user
17  postRepository.save(newPost)
18
19  res.send(newPost)
20}

src/controllers/CreateDraftAction.ts
1import { prisma } from '../prisma'
2
3export async function createDraftAction(req, res) {
4  const { title, content, authorEmail } = req.body
5
6  const newPost = await prisma.post.create({
7    data: {
8      title,
9      content,
10      author: {
11        connect: { email: authorEmail },
12      },
13    },
14  })
15
16  res.send(newPost)
17}

src/controllers/SetBioForUserAction.ts.ts
1import { getManager } from 'typeorm'
2import { Profile } from '../entity/Profile'
3import { User } from '../entity/User'
4
5export async function setBioForUserAction(req, res) {
6  const { userId } = req.params
7  const { bio } = req.body
8
9  const userRepository = getManager().getRepository(User)
10  const user = await userRepository.findOne(userId, {
11    relations: ['profile'],
12  })
13
14  const profileRepository = getManager().getRepository(Profile)
15  user.profile.bio = bio
16
17  profileRepository.save(user.profile)
18
19  res.send(user)
20}

src/controllers/SetBioForUserAction.ts.ts
1import { prisma } from '../prisma'
2
3export async function setBioForUserAction(req, res) {
4  const { userId } = req.params
5  const { bio } = req.body
6
7  const user = await prisma.user.update({
8    where: { id: userId },
9    data: {
10      profile: {
11        update: {
12          bio,
13        },
14      },
15    },
16  })
17
18  res.send(user)
19}

4.3. Replacing queries in PUT requests

The REST API has one route that accept a PUT request:

• /addPostToCategory?postId=POST_ID&categoryId=CATEGORY_ID: Adds the post with POST_ID to the category with CATEGORY_ID

Let's dive into the route handlers that implement these requests.

src/controllers/AddPostToCategoryAction.ts
1import { getManager } from 'typeorm'
2import { Post } from '../entity/Post'
3import { Category } from '../entity/Category'
4
5export async function addPostToCategoryAction(req, res) {
6  const { postId, categoryId } = req.query
7
8  const postRepository = getManager().getRepository(Post)
9  const post = await postRepository.findOne(postId, {
10    relations: ['categories'],
11  })
12
13  const categoryRepository = getManager().getRepository(Category)
14  const category = await categoryRepository.findOne(categoryId)
15
16  post.categories.push(category)
17  postRepository.save(post)
18
19  res.send(post)
20}

src/controllers/AddPostToCategoryAction.ts
1import { prisma } from '../prisma'
2
3export async function addPostToCategoryAction(req, res) {
4  const { postId, categoryId } = req.query
5
6  const post = await prisma.post.update({
7    data: {
8      postsToCategories: {
9        create: {
10          category: {
11            connect: { id: categoryId },
12          },
13        },
14      },
15    },
16    where: {
17      id: postId,
18    },
19  })
20
21  res.send(post)
22}

src/controllers/AddPostToCategoryAction.ts
1const post = await prisma.post.update({
2  data: {
3    categories: {
4      connect: { id: categoryId },
5    },
6  },
7  where: { id: postId },
8})

Implicit many-to-many relations

Similar to the @manyToMany decorator in TypeORM, Prisma allows you to model many-to-many relations implicitly. That is, a many-to-many relation where you do not have to manage the relation table (also sometimes called JOIN table) explicitly in your schema. Here is an example with TypeORM:

import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  ManyToMany,
  JoinTable,
} from 'typeorm'
import { Category } from './Category'

@Entity()
export class Post {
  @PrimaryGeneratedColumn()
  id: number

  @ManyToMany((type) => Category, (category) => category.posts)
  @JoinTable()
  categories: Category[]
}

import { Entity, PrimaryGeneratedColumn, Column, ManyToMany } from 'typeorm'
import { Post } from './Post'

@Entity()
export class Category {
  @PrimaryGeneratedColumn()
  id: number

  @ManyToMany((type) => Post, (post) => post.categories)
  posts: Post[]
}

If you generate and run a migration with TypeORM based on these models, TypeORM will automatically create the following relation table for you:

-- Table Definition ----------------------------------------------
CREATE TABLE post_categories_category (
    "postId" integer REFERENCES post(id) ON DELETE CASCADE,
    "categoryId" integer REFERENCES category(id) ON DELETE CASCADE,
    CONSTRAINT "PK_91306c0021c4901c1825ef097ce" PRIMARY KEY ("postId", "categoryId")
);

-- Indices -------------------------------------------------------
CREATE UNIQUE INDEX "PK_91306c0021c4901c1825ef097ce" ON post_categories_category("postId" int4_ops,"categoryId" int4_ops);
CREATE INDEX "IDX_93b566d522b73cb8bc46f7405b" ON post_categories_category("postId" int4_ops);
CREATE INDEX "IDX_a5e63f80ca58e7296d5864bd2d" ON post_categories_category("categoryId" int4_ops);

If you introspect the database with Prisma, you'll get the following result in the Prisma schema (note that some relation field names have been adjusted to look friendlier compared to the raw version from introspection):

schema.prisma
1model Category {
2  id                Int                @id @default(autoincrement())
3  name              String
4  postsToCategories PostToCategories[]
5
6  @@map("category")
7}
8
9model Post {
10  id                Int                @id @default(autoincrement())
11  title             String
12  content           String?
13  published         Boolean            @default(false)
14  authorId          Int?
15  author            User?              @relation(fields: [authorId], references: [id])
16  postsToCategories PostToCategories[]
17
18  @@map("post")
19}
20
21model PostToCategories {
22  postId     Int
23  categoryId Int
24  category   Category @relation(fields: [categoryId], references: [id])
25  post       Post     @relation(fields: [postId], references: [id])
26
27  @@id([postId, categoryId])
28  @@index([postId], name: "IDX_93b566d522b73cb8bc46f7405b")
29  @@index([categoryId], name: "IDX_a5e63f80ca58e7296d5864bd2d")
30  @@map("post_categories_category")
31}

In this Prisma schema, the many-to-many relation is modeled explicitly via the relation table PostToCategories.

By adhering to the conventions for Prisma relation tables, the relation could look as follows:

schema.prisma
1model Category {
2  id    Int    @id @default(autoincrement())
3  name  String
4  posts Post[]
5
6  @@map("category")
7}
8
9model Post {
10  id         Int        @id @default(autoincrement())
11  title      String
12  content    String?
13  published  Boolean    @default(false)
14  authorId   Int?
15  author     User?      @relation(fields: [authorId], references: [id])
16  categories Category[]
17
18  @@map("post")
19}

This would also result in a more ergonomic and less verbose Prisma Client API to modify the records in this relation, because you have a direct path from Post to Category (and the other way around) instead of needing to traverse the PostToCategories model first.