Migrate from Sequelize

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:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

src/models/index.js
1const sequelize = new Sequelize('blog-sequelize', 'alice', 'myPassword42', {
2  host: 'localhost',
3  dialect: 'postgres',
4})

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

.env
1DATABASE_URL="postgresql://alice:myPassword42@localhost:5432/blog-sequelize?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 Categories {
2  id             Int              @id @default(autoincrement())
3  name           String
4  createdAt      DateTime
5  updatedAt      DateTime
6  PostCategories PostCategories[]
7}
8
9model PostCategories {
10  createdAt  DateTime
11  updatedAt  DateTime
12  CategoryId Int
13  PostId     Int
14  Categories Categories @relation(fields: [CategoryId], references: [id])
15  Posts      Posts      @relation(fields: [PostId], references: [id])
16
17  @@id([CategoryId, PostId])
18}
19
20model Posts {
21  id             Int              @id @default(autoincrement())
22  title          String
23  content        String?
24  published      Boolean?         @default(false)
25  createdAt      DateTime
26  updatedAt      DateTime
27  authorId       Int?
28  Users          Users?           @relation(fields: [authorId], references: [id])
29  PostCategories PostCategories[]
30}
31
32model Profiles {
33  id        Int      @id @default(autoincrement())
34  bio       String
35  createdAt DateTime
36  updatedAt DateTime
37  userId    Int?     @unique
38  Users     Users?   @relation(fields: [userId], references: [id])
39}
40
41model SequelizeMeta {
42  name String @id
43}
44
45model Users {
46  id        Int       @id @default(autoincrement())
47  name      String?
48  email     String    @unique
49  createdAt DateTime
50  updatedAt DateTime
51  Posts     Posts[]
52  Profiles  Profiles?
53}

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 createdAt and updatedAt fields

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. You'll adjust the createdAt and updatedAt fields in our models. Sequelize doesn't add the DEFAULT constraint to createdAt when creating the tables in the database. Therefore, you'll add @default(now()) and @updatedAt attributes to the createdAt and updatedAt columns respectively. To learn more how Prisma does this, you can read more @default(now()) and @updatedAt here. Our updated schema will be as follows:

prisma/schema.prisma
1model Categories {
2  id             Int              @id @default(autoincrement())
3  name           String
4  createdAt      DateTime         @default(now())
5  updatedAt      DateTime         @updatedAt
6  PostCategories PostCategories[]
7}
8
9model PostCategories {
10  createdAt  DateTime   @default(now())
11  updatedAt  DateTime   @updatedAt
12  CategoryId Int
13  PostId     Int
14  Categories Categories @relation(fields: [CategoryId], references: [id])
15  Posts      Posts      @relation(fields: [PostId], references: [id])
16
17  @@id([CategoryId, PostId])
18}
19
20model Posts {
21  id             Int              @id @default(autoincrement())
22  title          String
23  content        String?
24  published      Boolean?         @default(false)
25  createdAt      DateTime         @default(now())
26  updatedAt      DateTime         @updatedAt
27  authorId       Int?
28  Users          Users?           @relation(fields: [authorId], references: [id])
29  PostCategories PostCategories[]
30}
31
32model Profiles {
33  id        Int      @id @default(autoincrement())
34  bio       String
35  createdAt DateTime @default(now())
36  updatedAt DateTime @updatedAt
37  userId    Int?     @unique
38  Users     Users?   @relation(fields: [userId], references: [id])
39}
40
41model SequelizeMeta {
42  name String @id
43}
44
45model Users {
46  id        Int       @id @default(autoincrement())
47  name      String?
48  email     String    @unique
49  createdAt DateTime  @default(now())
50  updatedAt DateTime  @updatedAt
51  Posts     Posts[]
52  Profiles  Profiles?
53}

2.6. 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 Prisma 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.

Sequelize generates a SequelizeMeta model that is used internally by the library that is not needed. Therefore, you'll manually delete it from the schema.

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

prisma/schema.prisma
1generator client {
2  provider = "prisma-client-js"
3}
4
5datasource db {
6  provider = "postgresql"
7  url      = env("DATABASE_URL")
8}
9
10model Category {
11  id             Int                @id @default(autoincrement())
12  name           String
13  createdAt      DateTime           @default(now())
14  updatedAt      DateTime           @updatedAt
15  postCategories PostToCategories[]
16
17  @@map("Categories")
18}
19
20model PostToCategories {
21  createdAt  DateTime @default(now())
22  updatedAt  DateTime @updatedAt
23  categoryId Int
24  postId     Int
25  category   Category @relation(fields: [categoryId], references: [id])
26  post       Post     @relation(fields: [postId], references: [id])
27
28  @@id([categoryId, postId])
29  @@map("PostCategories")
30}
31
32model Post {
33  id               Int                @id @default(autoincrement())
34  title            String
35  content          String?
36  published        Boolean?           @default(false)
37  createdAt        DateTime           @default(now())
38  updatedAt        DateTime           @updatedAt
39  authorId         Int?
40  author           User?              @relation(fields: [authorId], references: [id])
41  postToCategories PostToCategories[]
42
43  @@map("Posts")
44}
45
46model Profile {
47  id        Int      @id @default(autoincrement())
48  bio       String
49  createdAt DateTime @default(now())
50  updatedAt DateTime @updatedAt
51  userId    Int?     @unique
52  user      User?    @relation(fields: [userId], references: [id])
53
54  @@map("Profiles")
55}
56
57model User {
58  id        Int      @id @default(autoincrement())
59  name      String?
60  email     String   @unique
61  createdAt DateTime @default(now())
62  updatedAt DateTime @updatedAt
63  posts     Post[]
64  profile   Profile?
65
66  @@map("Users")
67}

4.1. Replacing queries in GET requests

The REST API has four 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
• /authors: Returns a list of authors

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

src/controllers/post.js
1const feed = async (req, res) => {
2  try {
3    const feed = await Post.findAll({
4      where: { published: true },
5      include: ['author', 'categories'],
6    })
7    return res.json(feed)
8  } catch (error) {
9    return res.status(500).json(error)
10  }
11}

src/controllers/post.js
1const feed = async (req, res) => {
2  try {
3    const feed = await prisma.post.findMany({
4      where: { published: true },
5      include: { author: true, postToCategories: true },
6    })
7    return res.json(feed)
8  } catch (error) {
9    return res.status(500).json(error)
10  }
11}

src/controllers/post.js
1const filterPosts = async (req, res) => {
2  const { searchString } = req.query
3
4  try {
5    const filteredPosts = await Post.findAll({
6      where: {
7        [Op.or]: [
8          {
9            title: {
10              [Op.like]: `%${searchString}%`,
11            },
12          },
13          {
14            content: {
15              [Op.like]: `%${searchString}%`,
16            },
17          },
18        ],
19      },
20      include: 'author',
21    })
22
23    res.json(filteredPosts)
24  } catch (error) {
25    return res.status(500).json(error)
26  }
27}

src/controllers/post.js
1const filterPosts = async (req, res) => {
2  const { searchString } = req.query
3
4  try {
5    const filteredPosts = prisma.post.findMany({
6      where: {
7        OR: [
8          {
9            title: { contains: searchString },
10          },
11          {
12            content: { contains: searchString },
13          },
14        ],
15      },
16    })
17
18    res.json(filteredPosts)
19  } catch (error) {
20    return res.status(500).json(error)
21  }
22}

src/controllers/post.js
1const getPostById = async (req, res) => {
2  const { postId } = req.params
3
4  try {
5    const post = await Post.findOne({
6      where: { id: postId },
7      include: 'author',
8    })
9
10    return res.json(post)
11  } catch (error) {
12    return res.status(500).json(error)
13  }
14}

src/controllers/post.js
1const getPostById = async (req, res) => {
2  const { postId } = req.params
3
4  try {
5    const post = await prisma.post.findUnique({
6      where: { id: Number(postId) },
7      include: { author: true },
8    })
9
10    return res.json(post)
11  } catch (error) {
12    return res.status(500).json(error)
13  }
14}

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 User record
• /user/:userId/profile: Creates a new Profile record for a User record with a given ID

src/controllers/user.js
1const createUser = async (req, res) => {
2  const { name, email } = req.body
3
4  try {
5    const user = await User.create({
6      name,
7      email,
8    })
9
10    return res.json(user)
11  } catch (error) {
12    return res.status(500).json(error)
13  }
14}

src/controllers/user.js
1const createUser = async (req, res) => {
2  const { name, email } = req.body
3
4  try {
5    const user = await prisma.user.create({
6      data: {
7        name,
8        email,
9      },
10    })
11
12    return res.json(user)
13  } catch (error) {
14    return res.status(500).json(error)
15  }
16}

src/controllers/post.js
1const createDraft = async (req, res) => {
2  const { title, content, authorEmail } = req.body
3
4  try {
5    const user = await User.findOne({ email: authorEmail })
6
7    const draft = await Post.create({
8      title,
9      content,
10      authorId: user.id,
11    })
12
13    res.json(draft)
14  } catch (error) {
15    return res.status(500).json(error)
16  }
17}

src/controllers/post.js
1const createDraft = async (req, res) => {
2  const { title, content, authorEmail } = req.body
3
4  try {
5    const draft = await prisma.post.create({
6      data: {
7        title,
8        content,
9        author: {
10          connect: { email: authorEmail },
11        },
12      },
13    })
14
15    res.json(draft)
16  } catch (error) {
17    return res.status(500).json(error)
18  }
19}

src/controllers/user.js
1const setUserBio = async (req, res) => {
2  const { userId } = req.params
3  const { bio } = req.body
4
5  try {
6    const user = await User.findOne({
7      where: {
8        id: Number(userId),
9      },
10    })
11
12    const updatedUser = await user.createProfile({ bio })
13
14    return res.json(updatedUser)
15  } catch (error) {
16    return res.status(500).json(error)
17  }
18}

src/controllers/user.js
1const setUserBio = async (req, res) => {
2  const { userId } = req.params
3  const { bio } = req.body
4
5  try {
6    const user = await prisma.user.update({
7      where: { id: Number(userId) },
8      data: {
9        profile: {
10          create: { bio },
11        },
12      },
13    })
14
15    return res.json(user)
16  } catch (error) {
17    return res.status(500).json(error)
18  }
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/post.js
1const addPostToCategory = async (req, res) => {
2  const { postId, categoryId } = req.query
3
4  try {
5    const post = await Post.findOne({
6      where: { id: postId },
7    })
8
9    const category = await Category.findOne({
10      where: { id: categoryId },
11    })
12
13    const updatedPost = await post.addCategory(category)
14
15    return res.json(updatedPost)
16  } catch (error) {
17    return res.status(500).json(error)
18  }
19}

src/controllers/post.js
1const addPostToCategory = async (req, res) => {
2  const { postId, categoryId } = req.query
3
4  try {
5    const post = await prisma.post.update({
6      data: {
7        postToCategories: {
8          create: {
9            categories: {
10              connect: { id: Number(categoryId) },
11            },
12          },
13        },
14      },
15      where: {
16        id: Number(postId),
17      },
18    })
19
20    return res.json(post)
21  } catch (error) {
22    return res.status(500).json(error)
23  }
24}

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

Primary key column

By default, Sequelize defines a primaryKey and used id with the autoby default if not defined. This is optional. If you would like to set your own primary key, you can use the primaryKey: true and define your preferred data type in your field of choice:

// changing the primary key column
module.exports = (sequelize, DataTypes) => {
  const Post = sequelize.define('Post', {
    postId: {
      type: DataTypes.INTEGER,
      primaryKey: true,
    },
  })
  return Post
}

// changing the id DataType
module.exports = (sequelize, DataTypes) => {
  const Post = sequelize.define('Post', {
    id: {
      type: DataTypes.UUID, // alternative: DataTypes.STRING
      primaryKey: true,
    },
  })
  return Post
}

Table name inference

Sequelize infers table names from the model name. When the name of a table isn't provided Sequelize automatically pluralizes the model name and uses that as the table name using a library called inflection. Prisma on the other hand maps the model name to the table name in your database modelling your data. If you wish to change this default behaviour in Sequelize, you can either enforce the table name to be equal to the model name or provide the table name directly:

// enforcing table name to be equal to model name
module.exports = (sequelize, DataTypes) => {
  const Post = sequelize.define(
    'Post',
    {
      // ... attributes
    },
    {
      freezeTableName: true,
    }
  )
  return Post
}

// providing the table name directly
module.exports = (sequelize, DataTypes) => {
  const Post = sequelize.define(
    'Post',
    {
      // ... attributes
    },
    {
      tableName: 'Post',
    }
  )
  return Post
}

Timestamps

Sequelize automatically adds the fields createdAt and updatedAt to every model using the data type DataTypes.DATE, by default. You can disable this for a model with the timestamps: false option:

sequelize.define(
  'User',
  {
    // ... (attributes)
  },
  {
    timestamps: false,
  }
)

Prisma offers you the flexibility to define these fields in your model. You add the createdAt and updatedAt fields by defining them explicitly in your model. To set the createdAt field in your model, add the default(now()) attribute to the column. In order to set the updatedAt column, update your model by adding the @updatedAt attribute to the column.

model User {
  id        Int      @id @default(autoincrement())
  name      String?
  email     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Implicit many-to-many relations

Similar to the belongsToMany() association method in Sequelize, 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 Sequelize:

module.exports = (sequelize, DataTypes) => {
  const Post = sequelize.define('Post', {
    title: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    content: {
      type: DataTypes.STRING,
    },
    published: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
  })
  Post.associate = (models) => {
    Post.belongsTo(models.User, {
      foreignKey: 'authorId',
      as: 'author',
    })
    Post.belongsToMany(models.Category, {
      through: 'PostCategories',
      as: 'categories',
    })
  }
  return Post
}

module.exports = (sequelize, DataTypes) => {
  const Category = sequelize.define('Category', {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
  })
  Category.associate = (models) => {
    Category.belongsToMany(models.Post, {
      through: 'PostCategories',
      as: 'posts',
    })
  }
  return Category
}

When you start your application, Sequelize will create the the tables for you - based on these models:

Executing (default): CREATE TABLE IF NOT EXISTS "PostCategories"
("createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
"CategoryId" INTEGER  REFERENCES "Categories" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
"PostId" INTEGER  REFERENCES "Posts" ("id") ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY ("CategoryId","PostId"));

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):

model Categories {
  id             Int              @id @default(autoincrement())
  name           String
  createdAt      DateTime
  updatedAt      DateTime
  PostCategories PostCategories[]

  @@map("category")
}

model PostCategories {
  createdAt  DateTime
  updatedAt  DateTime
  CategoryId Int
  PostId     Int
  Categories Categories @relation(fields: [CategoryId], references: [id])
  Posts      Posts      @relation(fields: [PostId], references: [id])

  @@id([CategoryId, PostId])
  @@map("PostCategories")
}

model Posts {
  id             Int              @id @default(autoincrement())
  title          String
  content        String?
  published      Boolean?         @default(false)
  createdAt      DateTime
  updatedAt      DateTime
  authorId       Int?
  Users          Users?           @relation(fields: [authorId], references: [id])
  PostCategories PostCategories[]

  @@map("post")
}

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

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

model Categories {
  id    Int     @id @default(autoincrement())
  name  String
  posts Posts[]

  @@map("category")
}

model Posts {
  id         Int          @id @default(autoincrement())
  title      String
  content    String?
  published  Boolean      @default(false)
  authorId   Int?
  author     User?        @relation(fields: [authorId], references: [id])
  categories Categories[]

  @@map("post")
}

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 PostCategories model first.