Rebasing and Connecting the Prisma to an existing Database:

Suppose you already have a database, and you already have some data in it.
How to connect that database with your Prisma without losing all of your precious data?

• Run the following commands to set up the Prisma :

npm i prisma 
npx prisma init

this will create the Prisma files.

• write the connection string inside the env files
• Then we need to pull the schema from the existing db. To do this simply run :

npx prisma db pull

• Now your prisma.schema file will have the schema of your existing database
  
  Now inside the Prisma folder (which already consists of a schema.prisma file)

Create the following folder and files ->

• Next run the following command:

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

This will populate the migration.sql file.

• Now we need to change the encoding of this migration.sql file.

To do this go to your VS code command Palette ( by pressing control + Shift + P ). Search Encoding

And now save the encoding to windows 1252.

Save the file again

• Now run the following command :

npx prisma migrate resolve --applied 0_init

• That’s it, now just run :

And now you can start using Prisma with your existing database!

Common Problems with Prisma and their Solution:

1) Connecting with the db.

In the .env file it is important to write the string in quotes..
for example in the .env file :

DATABASE_URL="postgresql://postgres:Password@hostIP:PORT_NUMBER/DB_Name"

2) While running the npx prisma generate command a common error which looks like this occurs quite often :

To resolve this simply stop your server and run again.

3) Error handling in Prisma

Prisma throws generic errors like so :

If I console log the error, it will show something like this:

 To fix this, first import the following:

const { PrismaClientKnownRequestError, PrismaClientValidationError } =require("@prisma/client/runtime/library");

              Then modify your catch block using instanceof like so:

catch (e) {
    console.log(e, "error");
    if (e instanceof PrismaClientKnownRequestError || e instanceof PrismaClientValidationError) {
        return res.status(200).json({
          error: e.message.split("\n").at(-1)
        });
      }
    else
    
    res.send(e);
  }

Now go ahead and try creating another user with the same email, your newUser API will now send sensible errors!

4) Database and schema related problems.

1. This list can go on and on. So here in this section, we’ll just learn how to approach Database and schema related problems.
   
   DB and schema-related challenges can be quite intricate, as any misstep carries the potential of inadvertently erasing your entire database’s invaluable data.
   
   Let’s learn with an example scenario.

Use your newly created post API ( newUser ) and insert some 4-5 new users with some really long names.

Consider an example database like so:

Now in the Prisma schema let’s say we reduced the VarChar value for firstName to 20 Like so:

firstName             String           @db.VarChar(20)

Now let’s see what happens when we migrate this change to db like so:

npx prisma migrate dev --name varchar_modified

we receive a warning like so:

After pressing y you shall receive an error like so:

Let’s see another example:

Add a new column in the Prisma schema like so

subscription          String           @db.Text

Now migrate the changes:

npx prisma migrate dev --name added_subscription

If you press y here, ALL YOUR DATA WILL BE ERASED. So clearly that’s now the way to solve this.

The underlying issue here is that we are attempting to introduce a new column that cannot contain null values. Presently, in the database, there are entries that would inevitably result in null values when inserting this column with a ‘not null’ constraint. This situation contradicts logical expectations.

The solution here involves either providing a default value for this column in the Prisma schema, as follows:

  subscription          String           @db.Text @default("Yearly")

Alternatively, you have the option to mark this field as non-mandatory, as illustrated below

subscription          String?           @db.Text

5) Drift between the prisma schema and the database Schema

Database schema drift occurs when your database schema is out of sync with your migration history – the database schema has ‘drifted away’ from the source of truth.

Here let’s cover three scenarios:
This issue can arise when someone modifies the schema directly within the database, possibly by running SQL queries or employing the npx prisma db push command. Another potential cause is the absence of necessary Prisma migrations in your project.
In both these  case a simple npx prisma db pull is enough.

Let’s consider a more complex scenario where changes have occurred in both your Prisma.schema file and the database schema, and you want to keep both sets of changes. Here’s a straightforward approach:

• Copy the contents of your Prisma schema file and save it in a temporary file (let’s call it ‘copy_Prisma.schema’).
• Run npx prisma db pull to update your original Prisma schema file.
• Execute npx prisma migrate dev to apply these updates and ensure your schemas are synchronized.
• Now, review the modifications made to the copied version of your Prisma schema file.
• Copy and paste the relevant lines from ‘copy_Prisma.schema’ into your main Prisma schema file, and migrate the changes once more.
• This process will help you keep both your Prisma schema and database schema changes in sync.

To read more in detail about this you can refer to the Prisma docs
Dealing with drifts in migrations