Setting up Prisma using PostgreSQL provider with Next.js

Note: In this guide, I will assume you've already read the previous one Setting up a new Next.js project with Tailwind CSS.

After we've created our project, and configured tailwindcss, Now it's time to install and configure Prisma with the seeds data script.

Why Prisma and PostgreSQL?

By now you should know that Prisma works really great with Next.js and TypeScript, by giving us an easy and reliable way to design our database schema based on models.

Prisma models are then used to create tables on the database, generate a typed ORM experience, as well as making types available to use per Prisma model.

Generally, I feel more comfortable working with relational databases than with NoSQL databases I think it may be because my first introduction to databases was with SQL Server.

When talking about relational databases two main database servers comes to mind MySQL (or MariaDB) and PostgreSQL.

I used MySQL in my previous projects with Laravel and still use it in work, But Postgres just feel good, it's open-source, and have a solid community, and supports all the features I need.

When using Prisma, interacting with your database will be the same if you change for example from Postgres to MySQL we only need to reconfigure Prisma's data-source provider.

Note: Just be aware that some features may be supported by one database server, and not by another.

Install and initialize Prisma

Prisma CLI is a dev dependency that helps us while developing our application, for example, it allows us to push changes from schema.prisma to the database schema, and so on.

Let's install it using npm, open your terminal, navigate to the project root, and run npm install -D prisma.

After it's installed we can now initialize our schema file using npx prisma init --datasource-provider postgresql.

Prisma uses postgresql as its default data source provider, But we've specified it anyway to make sure we use postgresql.

Create PostgreSQL database

After we run the Prisma init command, it created a .env file with the DATABASE_URL variable, which is referenced inside prisma/schema.prisma.

In order for our Prisma Client to connect with our database, we need to create it first, I will assume that you already have Postgres installed on your system.

To create a new database run createdb --username=postgres nextjs-learning-platform, if you got an error, try with your username instead of postgres.

Let's change DATABASE_URL inside .env by changing the username, password, and database.

DATABASE_URL="postgresql://postgres:[email protected]:5432/nextjs-learning-platform?schema=public"

For me, both username, and password are postgres which is the default, then the database name which is the one we use before with the createdb command.

Install and Generate Prisma Client

While Prisma CLI is used on development, Prisma Client is used on runtime. Prisma Client is what allows us to interact with our database server.

In order for us to generate Prisma Client we need first to have at least one model, for now, let's put the following model which is provided by Prisma CLI when it doesn't find any model.

Go ahead and open prisma/schema.prisma then put the following at the end of the file.

Tip: If you're using vscode make sure to install Prisma.prisma extension if it's not already installed.

...

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

Prisma has good documentation that you can check out and learn more about Prisma Client and Prisma Schema.

Now, we can run npx prisma generate at the first run it will install @prisma/client then it will generate the Prisma Client.

Finally, let's run npx prisma db push to update our Database schema to match what we have in the Prisma schema.

Set up prisma db seed

Database seeding is a good way to pre-populate our database with some initial data. it's specialty helpful when deploying our project to production or setting it up in a different environment.

For example, imagine we want to have some predefined categories, an admin account, and maybe some default settings, having database seeding in place is what we need.

Prisma provides a db seed command to seed our database, we just need to set it up! which is what we will do in this section.

The npx prisma db seed will run the command found inside package.json under the field prisma.seed which we need to add.

Without further ado, go ahead and open package.json then insert the prisma properly mentioned below.

{
	// ...
	"prisma": {
		"seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"
	},
	"scripts": {
		// ...
	},
	// ...
}

As explained in the prisma seeding documentation we're using ts-node to execute our seeding file which will create next.

In order to avoid errors like SyntaxError: Cannot use import statement outside a module we're setting the --compiler-options to use the CommonJS module.

Cause we're using ts-node we need to install it, go ahead and run npm install -D ts-node.

Now it's time to create a new file at prisma/seed.ts as we specified inside the package.json file, with the following setup as mentioned in prisma docs.

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
	const admin = await prisma.user.upsert({
		where: { email: '[email protected]' },
		update: {},
		create: {
			email: '[email protected]',
			name: 'Admin',
		},
	})

	console.log('Admin name:', admin.name)
}

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

First, we create an instance of Prisma Client, then use it to update or insert an admin user to test that our database seeding is working as expected.

The main function is just a simple async function for us to be able to use await, and also we use it to close our database connections in case of any unexpected exception inside the main function.

Finally, let's run npx prisma db seed to see if everything is working as expected, if you can see "Admin name: Admin" in the console that means that all is good.

We can also use Prisma Studio to manage our data by running npx prisma studio which will open a nice interface with our database model and their data.

Before saving changes to Git, I always like to have a copy of the .env file as .env.example to keep track of the project environment variables.

DATABASE_URL="postgresql://postgres:[email protected]:5432/nextjs-learning-platform?schema=public"

Note: Please make sure to not include any sensitive values like production passwords, secrets, or API keys.

Now, let's commit our changes to Git and move to the next guide: git add . to stage changed files. Then git commit -m "set up prisma with database seeding" to commit the changes.

Next, we'll work on Adding JWT Authentication to our Next.js app using NextAuth.js