🤓 Migrating from Knex to Prisma ORM

Prisma provides the best experience for your team to work and interact with databases.

Migrating from Knex.js to Prisma involves several steps as Prisma is a full-fledged ORM with its own schema and migration system, while Knex is a SQL query builder.

🎉 Migrating to Prisma removes a lot of boilerplate code from your projects, really simplifies your code and reduces the development time significantly! Prisma ORM makes developers a lot more productive! 🤓 Read more at why prisma.

Here's a high-level guide for a smooth migration:

1. Adding Prisma in your existing project

No need to worry about how Prisma gets installed or configured in your project, theGraphQL Rocket Generator will do everything for you and make you project "Prisma ready".

1. Prisma infrastructure overview

• First we have the prisma folder containing the schema.prisma file, where you will define your data models.

• Then we have the src/prisma folder where would find some custom implementation for pagination or multi-tenancy and the most important, the prisma() function, that returns the instance of PrismaClient, which we will use everywhere and allows us to basically write the queries.

1. Database Connection

• In schema.prisma, configure the database connection:

  prismaCopy codedatasource db {
    provider = "sqlserver" # or your database provider (mysql, sqlite, etc.)
    url      = env("DATABASE_URL")
  }

Make sure the DATABASE_URL is set correctly in your .env file with your existing Knex database credentials. We usually use this pattern for database connection string configuration:

DATABASE_URL="sqlserver://{server}:{port};database={database};user={user};password={password};trustServerCertificate=true"

1. Define Prisma Models

• In Prisma, you define models to represent your tables. Prisma's models are more declarative compared to Knex migrations. Start by replicating your existing Knex schema into Prisma models. Here is an example for a users table:

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

Prisma offers a rich set of features like relations, field constraints, and defaults. Carefully match these with your existing schema.

1. Introspect Existing Database (Optional)

• If your database already exists, you can use Prisma's introspection tool to generate the schema:

  npx prisma db pull

This command introspects your database and generates Prisma models based on your current schema.

1. Replace Knex Queries with Prisma Client

• After setting up the schema, replace Knex queries with Prisma's type-safe client. For example, a Knex query like:

  knex('users').select('*').where({ id: 1 })

Would become:

const user = await prisma.user.findUnique({ where: { id: 1 }, });

📍Prisma queries should directly be written inside the GraphQL resolvers. No need for the old data-sources extra files. 📍Prisma automatically handles the n+1 problem so the old dataloaders we had to write for knex will be removed! 🎉 Read more about it here.

You will notice that after migrating a feature to Prisma a few folders will be useless and must be removed. here is the full list of files and folders related to Knex that must be deleted after migrating to Prisma:

• /src/db/

• /middleware/db/

• /messaging/middleware/dbInstance.js

• /startup/dataLoaders.js

• /features/common/dbGenerators.js

• /features/<your-feature>/dataLoaders.js

• /features/<your-feature>/dataSources/<old DataSource files>

• /utils/sqlDataSource.js

1. Handling Data Migrations

• Prisma Migrate can be used for future data migrations. Create migration files, then run prisma migrate dev or prisma migrate deploy to apply them. You can also handle more complex migrations by writing custom SQL or scripts as needed.

1. Unsupported Features

• Prisma may not support certain SQL-level features you used with Knex (like raw SQL queries, partial indexes, or database-specific features). You can either replace or modify Prisma-generated SQL or use prisma.$queryRaw for raw SQL queries where necessary.

1. Testing and Deployment

• Test the migration in a staging environment to ensure everything works as expected, then apply it to production using:

  prisma migrate deploy

This migration process ensures that you move to Prisma's more modern ORM capabilities while retaining control over your schema and data. Full guides and examples can be found on the (Prisma)