In this concise guide, I’ll walk you through setting up PostgreSQL and Prisma ORM with Next.js, giving you the foundation to build your next web application with ease. First, let me provide you with a quick introduction to each of these technologies.
Table of Contents
What is PostgreSQL?
PostgreSQL is an open-source relational database management system (RDBMS). PostgreSQL allows you to store and manage structured data using tables, rows, and columns, and it supports complex queries, transactions, and data integrity constraints.
What is Prisma?
Prisma is a modern Object-Relational Mapping (ORM) tool for Node.js and TypeScript that simplifies database access and management. An ORM allows you to interact with a database using code instead of SQL, mapping database tables to objects in your choice of programming language.
Prisma consists of three main components: Prisma Client (a type-safe query builder), Prisma Migrate (a migration tool for database schema changes), and Prisma Studio (a GUI to explore and edit database data).
What is Next.js?
Next.js is a popular React framework that enables developers to build fast, server-rendered, or statically-generated web applications.
It offers key features like file-based routing, API routes, and built-in support for SSR (Server-Side Rendering) and SSG (Static Site Generation), making it ideal for building optimized, production-ready web apps.
Alright, now that you have a basic understanding, let’s lay the foundation and get started.
Create a new Next.js application
Ok, the first thing to do is to create and set up a new Next.js app. Let’s see how to do that. Follow the 3 steps below.
- Run
npx create-next-appin the terminal. You will be asked a series of questions, Accept all the default settings. Notice, that the latest version of Next.js uses TypeSript as the default language. Make sure to an appropriate name for your project. I useddemo-student-info-system.
- Now, access the root of your project folder. So, you should be at
\YourProjectFolder( ex:\demo-student-info-system). Now, you should be inside your project folder in the terminal. - Run
npx prisma init --datasource-provider postgresqlin the terminal to set up the necessary Prisma files and configuration for working with a PostgreSQL database.
Note:
- npx prisma init: Initializes a new Prisma project, creating a prisma directory with a
schema.prismafile and a .env file. - –datasource-provider postgresql: Specifies PostgreSQL as the database provider.
Again make sure to run this command in the root of your project.
Add Prisma Data Model to the schema
After executing the command in step 3 above, you will notice that a new folder prisma has been created in your project. Expand this folder, and you will see schema.prisma, a file with some auto-generated code( generator client and datasource db parts ). You need to add Prisma Data models in this file. A Prisma data model defines the structure of your database tables using a simple, schema-like syntax. Each model maps to a database table, where fields represent columns and their data types.
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
//you define the data model
model Student {
id Int @id @default(autoincrement())
name String
city String
}Setting up PostgreSQL
PostgreSQL Installation and Database URL
Note that in the above code, the database URL is in a .env file and is accessed by env(“DATABASE_URL”), You will see that a .env has been created with a sample connection string to your data source.
The sample URL should be something like this: postgresql://johndoe:mypassword@localhost:5432/mydb?schema=public
- User: johndoe
- Password: mypassword ( this is the password for your database user ‘johndoe’ )
- Host: localhost
- Port: 5432
- Database name: mydb
- Schema name: public
We are not going to use this URL. we need to change this and replace our database URL with a PostgreSQL database.
Database URL with default PostgreSQL user
I assumed that you have already downloaded and installed the latest compatible PostgreSQL in your system. if not, you can download and install the newest version of PostgreSQL compatible with your operating system using the EDB installer.
Once you installed the PostgreSQL, add the following Database URL (the connection string/url ) to your .env file.
DATABASE_URL="postgresql://postgres:stdAdmin@localhost:5432/student"In this URL:
- User: postgres
- Password: stdAdmin
- Host: localhost
- Port: 5432
- Database name: student
Here, in my PostgreSQL, I use the default user postgres, which is the default superuser in PostgreSQL. This user has all the privileges, including the ability to create databases, and roles, and manage the entire PostgreSQL instance.
Note at this point even if you have not created the database student, Prisma can create the database automatically under certain conditions when using the prisma migrate dev command, which is the command that you will run next on the terminal. If the user and connection details are valid but the specific database doesn’t exist, Prisma will attempt to create the database as part of the migration process.
It is not necessary to rely on the default user, You can create a new user and use it in the database URL. You can check this post on creating a new PostgreSQL user and database.
Prisma Migrate
A migration tool for database schema changes is a feature that helps manage and apply changes to your database schema over time. It automates the process of synchronizing your database structure with changes defined in your Prisma schema file (schema.prisma).
IYou can use npx prisma migrare dev in the development settings to trigger migration. ( In production and testing environments, you should use npx prisma migrate deploy ).
npx prisma migrare dev will prompt you to enter a name for the migration, I entered “add_student_sys”. You can enter an appropriate name. The command will run and create a folder named migrations. You can expand this folder and view the sql files generated from your Prisma data model.
migrations/
└─ 20240904184224_add_student_sys/
└─ migration.sql
Let’s say you change your existing Prisma data model and run npx prisma migrate dev. What do you think will happen?
This will NOT automatically update the existing migrations. Prisma migrations are designed to be incremental, meaning each migration represents a specific set of changes that have been applied to your database schema over time.
Therefore, when you modify your Prisma schema, Prisma generates a new migration file that applies the changes to your database schema without altering previous migrations. consequently, Prisma migrate creates a history of .sql migration files. It is essential not to delete an old migration, as this can lead to issues, especially if the migration has already been applied to your database.
You can learn about database migration using Prisma migration in the official documentation.