Use Prisma with Neon

Prisma is an open source next-generation ORM that consists of the following parts:

• Prisma Client: An auto-generated and type-safe query builder for Node.js & TypeScript
• Prisma Migrate: A schema migration system
• Prisma Studio: A GUI for viewing and editing data in your database

This tutorial steps you through how to connect from Prisma to Neon, how to use Prisma Migrate to create and evolve a schema, how to add data using the Neon SQL Editor or Prisma Studio, how to send queries using Prisma Client, and finally, how to introspect an existing database using the Prisma CLI.

Step 1: Create a Neon project and copy the connection string

1. In the Neon Console, click Create a project to open the Project Creation dialog.
2. Specify a name, a PostgreSQL version, a region, and click Create Project.

The project is created and you are presented with a dialog that provides connection details. Copy the connection string, which looks similar to the following:

postgres://sally:************@ep-white-thunder-826300.us-east-2.aws.neon.tech/neondb

Step 2: Create a shadow database for Prisma Migrate

Prisma Migrate requires a "shadow" database to detect schema drift and generate new migrations. For more information about the purpose of the shadow database, refer to About the shadow database, in the Prisma documentation.

For cloud-hosted databases like Neon, you must create the shadow database manually. To create the shadow database:

1. In the Neon Console, select Settings > Databases.
2. Click New Database.
3. Select the branch where you want to create the database, enter a database name, and select a database owner. For simplicity, name the shadow database shadow, and select the same branch where the neondb database resides.

The connection string for this database should be the same as the connection string for your neondb database except for the database name:

postgres://sally:************@ep-white-thunder-826300.us-east-2.aws.neon.tech/shadow

Step 3: Set up your Prisma project

Deploy a sample Typescript project and set up Prisma.

To complete these steps, you require Node.js v14.17.0 or higher. For more information about Prisma's system requirements, see System requirements.

1. Create a project directory and navigate to it.

   mkdir hello-neon-prisma
   cd hello-neon-prisma

2. Initialize a TypeScript project using npm. This creates a package.json file with the initial setup for your TypeScript project.

   npm init -y
   npm install typescript ts-node @types/node --save-dev

3. Initialize TypeScript:

   npx tsc --init

4. Install the Prisma CLI, which is a Prisma project dependency:

   npm install prisma --save-dev

5. Set up Prisma with the Prisma CLI init command. This creates a prisma directory with a Prisma schema file and configures PostgreSQL as your database.

   npx prisma init --datasource-provider postgresql

Step 4: Connect your Prisma project to Neon

In this step, you will update your project's .env file with the connection strings for your neondb and shadow databases.

1. Open the .env file located in your prisma directory.
2. Update the value of the DATABASE_URL variable to the connection string you copied in Step 2.
3. Add a SHADOW_DATABASE_URL variable and set the value to the connection string for the shadow database you created in Step 3.

When you are finished, your .env file should have entries similar to the following:

DATABASE_URL=postgres://sally:************@ep-white-thunder-826300.us-east-2.aws.neon.tech/neondb?connect_timeout=10
SHADOW_DATABASE_URL=postgres://sally:************@ep-white-thunder-826300.us-east-2.aws.neon.tech/shadow?connect_timeout=10

Step 5: Add a model to your schema.prisma file

In this step, you will update the datasource db entry in your schema.prisma file and add a model for the Elements table. A model represents the table in your underlying database and serves as the foundation for the generated Client API. For more information about data modeling, see Data modeling, in the Prisma documentation.

1. Update the datasource db entry. Ensure that the provider is set to postgresql, and add a shadowDatabaseUrl entry.

2. Add the model for the Elements table.

   Your schema.prisma file should now appear as follows:

   // This is your Prisma schema file,
   // learn more about it in the docs: https://pris.ly/d/prisma-schema
   
   generator client {
     provider = "prisma-client-js"
   }
   
   datasource db {
     provider = "postgresql"
     url      = env("DATABASE_URL")
     shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
   }
   
   model Elements {
     AtomicNumber Int @id
     Element String?
     Symbol  String?  
   }

Step 6: Run a migration to create the table in Neon

At this point, you do not have a table in your neondb database. In this step, you will run a migration with Prisma Migrate, which creates the table. The table is created based on the Elements table model you defined in the schema.prisma file in the previous step.

During the migration, Prisma Migrate performs the following actions:

• Creates an SQL migration file in your prisma/migrate directory.
• Runs the SQL migration file on your database

To run Prisma Migrate, issue the following command from your hello-neon-prisma project directory:

$> npx prisma migrate dev --name init

The output of this command appears similar to the following:

Environment variables loaded from ../.env
Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "neondb", schema "public" at "ep-white-thunder-826300.us-east-2.aws.neon.tech:5432"

Applying migration `20230105222046_init`

The following migration(s) have been created and applied from new schema changes:

migrations/

  └─ 20230105222046_init/

    └─ migration.sql

Your database is now in sync with your schema.

Running generate... (Use --skip-generate to skip the generators)

added 2 packages, and audited 24 packages in 3s

found 0 vulnerabilities

✔ Generated Prisma Client (4.8.1 | library) to ./../node_modules/@prisma/client in 73ms

Step 7: View your table in the Neon Console

To view the Elements table that was created in your neondb database by the migration performed in the previous step:

1. Navigate to the Neon console.
2. Select your project.
3. Select Tables.
4. Select the neondb database and default public schema. The Elements table should be visible in the sidebar. The table has no data at this point. Data will be added later in this tutorial.

Step 8: Evolve your schema with Prisma Migrate

In this step, you will evolve your Prisma schema by performing another migration with prisma migrate dev.

Assume that you want to add an AtomicMass field to your Elements model. The modified schema model should now appear as follows:

model Elements {
  AtomicNumber Int @id
  Element String?
  Symbol  String?
  AtomicMass Decimal
}

3. Apply the schema change to your database using the prisma migrate dev command. In this example, the name given to the migration is add-field.

   $> npx prisma migrate dev --name add-field

   This command creates a new SQL migration file for the migration, applies the generated SQL migration to your database, and regenerates the Prisma Client. The output resembles the following:

   Environment variables loaded from .env
   Prisma schema loaded from prisma/schema.prisma
   Datasource "db": PostgreSQL database "neondb", schema "public" at "ep-white-thunder-826300.us-east-2.aws.neon.tech:5432"
   
   Applying migration `20230113120852_add_field`
   
   The following migration(s) have been created and applied from new schema changes:
   
   migrations/
     └─ 20230113120852_add_field/
       └─ migration.sql
   
   Your database is now in sync with your schema.
   
   ✔ Generated Prisma Client (4.8.1 | library) to ./node_modules/@prisma/client in 
   91ms

   You can view the migration in your prisma/migrations folder.

Step 9: Add data to your table

You have a couple of options for adding data to the Elements table. You can add data using the Neon SQL Editor or with Prisma Studio. Both methods are described below.

Option A: Add data using the Neon SQL Editor

1. Navigate to the Neon console.
2. Select your project.
3. Select the SQL Editor.
4. Select the main branch of your project and select the neondb database.
5. To add data, enter the following statement into the editor and click Run.

INSERT INTO "Elements" VALUES  (10, 'Neon', 'Ne', 20.1797);

To verify that data was added, run:

SELECT * FROM "Elements";

Option B: Add data using Prisma Studio

To add data from Prisma Studio:

Open your terminal and run the npx prisma studio command from your prisma directory:

$> npx prisma studio
Environment variables loaded from ../.env
Prisma schema loaded from schema.prisma
Prisma Studio is up on http://localhost:5555

Prisma Studio opens locally in your browser.

Click Add record and enter some values as follows:

• AtomicNumber: 10
• Element: Neon
• Symbol: Ne
• AtomicMass: 20.1797

To add the record, click the Save 1 change button.

Step 10: Send queries to your Neon database with Prisma Client

Follow the steps below to create a TypeScript file for executing queries with Prisma Client. Two examples are provided, one for creating a new record, and one for retrieving all records.

Create a TypeScript file to execute Prisma Client queries

In your hello-neon-prisma directory, create a new file called query.ts:

touch query.js

Add the following code to the query.ts file:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // ... write Prisma Client queries here
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

This code contains a main() function that's invoked at the end of the script. It also instantiates Prisma Client, which acts as the query interface to your database.

Create a new record

Add a query to the main() function in your query.ts file that creates a new record in the Elements table and logs the result to the console. With the query added, your query.ts file will look like this:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  const elements = await prisma.elements.create({
    data: {
      AtomicNumber: 8,
      Element: 'Oxygen',
      Symbol: 'O',
      AtomicMass: 15.999,
    },
  })
  console.log(elements)
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

Next, execute the query.ts script with the following command:

$ npx ts-node query.ts
{ AtomicNumber: 8, Element: 'Oxygen', Symbol: 'O', AtomicMass: 15.999 }

Congratulations! You have created your first record with Prisma Client.

Retrieve all records

Prisma Client offers various queries to read data from your database. In this section, you will use the findMany query to retrieve all records in the database for the specified model.

Delete the previous query from your query.ts file and replace it with the findMany query. Your query.ts file should now appear as follows:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  const elements = await prisma.elements.findMany()
  console.log(elements)
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

Execute the query.ts script again to retrieve the records from the Elements table.

$ npx ts-node query.ts
[
  {
    AtomicNumber: 10,
    Element: 'Neon',
    Symbol: 'Ne',
    AtomicMass: 20.1797
  },
  {
    AtomicNumber: 8,
    Element: 'Oxygen',
    Symbol: 'O',
    AtomicMass: 15.999
  }
]

Step 11: Introspect a database using Prisma CLI

Instead of creating data models in your Prisma schema and using Prisma Migrate to create the tables in your database, as you did for the Elements table in the previous steps, you can use Prisma's Introspection capability to generate data models from an existing database.

Introspection is often used to generate an initial version of the data model when adding Prisma to an existing project, and may be more convenient than developing your data model manually, especially if you have numerous tables or tables with many columns.

Another use case for Introspection is when using plain SQL for schema changes or a tool other than Prisma Migrate to perform schema migrations. In these cases, you might introspect your database after each schema change to re-generate your Prisma Client to reflect the changes in your Prisma Client API.

Create a schema in Neon

Let's assume your database has an extended version of the Elements table that was used in the previous steps. This table is called Elements_ext. Let's create that table in the Neon SQL Editor:

1. Navigate to the Neon console.
2. Select your project.
3. Select the SQL Editor.
4. Select the main branch of your project and select the neondb database.
5. Enter the following statement into the editor and click Run.

CREATE TABLE "Elements_ext" (
  AtomicNumber INTEGER PRIMARY KEY,
  "Element" TEXT,
  Symbol TEXT,
  AtomicMass DECIMAL,
  NumberOfNeutrons INTEGER,
  NumberOfProtons INTEGER,
  NumberOfElectrons INTEGER,
  "Period" INTEGER,
  "Group" INTEGER,
  Phase TEXT,
  Radioactive BOOLEAN,
  "Natural" BOOLEAN,
  Metal BOOLEAN,
  Nonmetal BOOLEAN,
  Metalloid BOOLEAN,
  "Type" TEXT,
  AtomicRadius DECIMAL,
  Electronegativity DECIMAL,
  FirstIonization DECIMAL,
  Density DECIMAL,
  MeltingPoint DECIMAL,
  BoilingPoint DECIMAL,
  NumberOfIsotopes INTEGER,
  Discoverer TEXT,
  "Year" INTEGER,
  SpecificHeat DECIMAL,
  NumberOfShells INTEGER,
  NumberOfValence INTEGER
);

Run prisma db pull

To introspect the Elements_ext table to generate the data model, run the prisma db pull command:

$ npx prisma db pull
Prisma schema loaded from prisma/schema.prisma
Environment variables loaded from .env
Datasource "db": PostgreSQL database "neondb", schema "public" at "ep-white-thunder-826300.us-east-2.aws.neon.tech:5432"

✔ Introspected 2 models and wrote them into prisma/schema.prisma in 1.78s

Two models were introspected because of the Elements table that existed in the neondb database previously. Prisma does not yet support introspecting a subset of a database schema, so you cannot introspect an individual table.

View the introspected model

To view the model generated for the new Elements_ext table, open your schema.prisma file. You will find the following model is now defined:

model Elements_ext {
  atomicnumber      Int      @id
  Element           String?
  symbol            String?
  atomicmass        Decimal? @db.Decimal
  numberofneutrons  Int?
  numberofprotons   Int?
  numberofelectrons Int?
  Period            Int?
  Group             Int?
  phase             String?
  radioactive       Boolean?
  Natural           Boolean?
  metal             Boolean?
  nonmetal          Boolean?
  metalloid         Boolean?
  Type              String?
  atomicradius      Decimal? @db.Decimal
  electronegativity Decimal? @db.Decimal
  firstionization   Decimal? @db.Decimal
  density           Decimal? @db.Decimal
  meltingpoint      Decimal? @db.Decimal
  boilingpoint      Decimal? @db.Decimal
  numberofisotopes  Int?
  discoverer        String?
  Year              Int?
  specificheat      Decimal? @db.Decimal
  numberofshells    Int?
  numberofvalence   Int?
}

The typical workflow for a project that does not use Prisma Migrate is:

1. Change the database schema (using plain SQL, for example)
2. Run prisma db pull to update the Prisma schema
3. Run prisma generate to update Prisma Client
4. Use the updated Prisma Client in your application

You can read more about this workflow in the Prisma documentation. See Introspection workflow.

Conclusion

Congratulations! You have completed the Use Prisma with Neon tutorial. To recap, you have learned how to connect from Prisma to Neon, how to use Prisma Migrate to evolve a schema, how to add data using the Neon SQL Editor and Prisma Studio, how to send queries using Prisma Client, and finally, how to introspect an existing database.

Need help?

Send a request to support@neon.tech, or join the Neon community forum.