Migration in Remix with Prisma & MYSQL

 In the previous tutorial, you learnt to style remix app and setup navbar using Tailwind CSS and Daisyui. In this tutorial, you learn to use Prisma to work with MYSQL database. 

Prisma is a new generation Object Relational Mapping tool of Node for PostgreSQL, MYSQL, MongoDB, etc. It is easy to synchronize database schema with Prisma schema and vice versa. With Prisma client, you can perform crud operations against the database with ease. 

To install Prisma in to the remix-app, execute the following command.

npm install prisma

Run npx prisma generate to generate the Prisma Client. 

Update the remix-app/prisma/schema.prisma file to include users schema. Change the database credentials to connect to MYSQL database on your local machine.

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = "mysql://username:password@localhost:3306/db_name?schema=public"
}


model users {
  id        Int      @id @default(autoincrement())
  firstName String?  @db.VarChar(255)
  lastName  String?  @db.VarChar(255)
  password  String?  @db.VarChar(255)
  email     String?  @db.VarChar(255)
  createdAt DateTime @db.DateTime(0)
  updatedAt DateTime @db.DateTime(0)
  username  String?  @db.VarChar(255)
  photo     String?  @db.VarChar(255)
}

Save the project and execute the following command to push Prisma schema to the database. 

npx prisma db push

The users table should be now created in your database. If you need to add column to the user table, simple modify the model to add the new column and run the npm prisma db push again.

If you have to create or update Prisma schema from existing database schema execute the below command. The command will create a new Prisma schema if it does not exist or update existing Prisma scheme.

npx prisma db pull

Migrations

With push and pull commands you can easily synchronize Prisma and database schema. Prisma also allows you to sync the schema using migrations. In migration files, you are able write query to create tables, modify existing table to add or remove column, to insert data to the table. etc. Run the following command to create addisAdmin migration file under the prisma/migrations folder.

npx prisma migrate dev --name addisAdmin

Modify the addisAdmin migration file to add isAdmin column to the users table:

ALTER TABLE `users` ADD COLUMN `isAdmin` INT DEFAULT 0;

Then run npx prisma migrate dev to run the migration. 

The isAdmin column now is added to the users table in database. However, it is not added to the model in Prisma. Thus, simply execute the pull command  again to synchronize the database schema with the Prisma schema.