Prisma – how to connect in a Nest project
Prisma – installation in a project
Support Installation Prisma in NestJS– the project is carried out very simply – with the help of two commands.
First – installs the client part of Prisma:
$ npm i @prisma/client
… second command – installs the cli part of Prisma:
$ npm i -D prisma
After that – you need to run the command to initialize Prisma in the current project:
$ npx prisma init
As a result, a folder will be created in the root of the project prisma
which will contain the file schema.prisma
to set up a Prisma connection to the database (and not only).
File contents schema.prisma
very simple:
// 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")
}
The most significant two things here are the driver postgresql
to connect to a PostgreSQL database (the default connection is to PostgreSQL). If the database is different – MySQL, etc. – then change to the desired driver.
The second is the line url
, which is used as a configuration file for connecting to the database. This line (as a variable DATABASE_URL
) is stored in a file .env
which is also automatically created by Prisma on initialization.
Looking at the contents of the file .env
then it will be like this:
# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#accessing-environment-variables-from-the-schema
# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server, MongoDB (Preview) and CockroachDB (Preview).
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings
DATABASE_URL="postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public"
That is – in one line – the complete configuration of the connection to the existing database is performed. In my (educational) case it looks like this:
DATABASE_URL="postgresql://postgres:123@localhost:5432/udemy_medium_clone?schema=public"
I.e – postgres
– database user name; 123
– database user password; udemy_medium_clone
– database name.
Where did I get / learned this data? I just created a sample database in PostgreSQL in advance, locally (in my case):
postgres=# CREATE DATABASE udemy_medium_clone;
CREATE DATABASE
postgres=# c udemy_medium_clone
You are now connected to database "udemy_medium_clone" as user "postgres".
udemy_medium_clone=# conninfo
You are connected to database "udemy_medium_clone" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
localhost:5432
– I left it by default, because – I also have PostgreSQL listening on this port by default.
Don’t forget to add the file .env
in .gitignore
so that the data does not light up on the remote repository – and that’s it, the basic setup of Prisma is completed.
Prisma – database migration
In my case – database udemy_medium_clone
– clean, there are no tables in it yet. It can be fixed two ways.
First – create a table in the database using SQL itself and then convert the table into a data model (Prisma schema) in the project – with the command:
$ prisma db pull
Second – on the contrary, create a Prisma data model in the project and then migrate it to PostgreSQL, which will automatically lead to the creation of such a table in the database.
Let’s act on second option. Create in file schema.prisma
user data model of the future application:
// 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")
}
model Users {
id Int @id @default(autoincrement())
email String @unique @db.VarChar(255)
bio String?
image String? @db.VarChar(500)
password String @unique @db.VarChar(255)
username String @unique @db.VarChar(255)
@@map("users")
}
By the way – under Visual Studio Code and WebStorm – there are special plugins for syntax highlighting and formatting files with the extension *.prisma
:
And that’s all – you can migrate to the database with the command:
$ npx prisma migrate dev --name users
… where --name users
is the name of the specific migration so that you can see and track the operation locally. Upon completion, Prisma will print a success report to the console:
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "udemy_medium_clone", schema "public" at "localhost:5432"
Applying migration `20220223160632_users`
The following migration(s) have been created and applied from new schema changes:
migrations/
└─ 20220223160632_users/
└─ migration.sql
Your database is now in sync with your schema.
✔ Generated Prisma Client (3.10.0 | library) to ./node_modules/@prisma/client in 75ms
As you can see from the report, Prisma has created a subfolder migrations
inside which there will be – folders with detailed information – for each specific migration.
In my case it will be a folder 20220223160632_users
(remember the key --name users
) and inside this folder is a curious file migration.sql
.
If you open this file, we will see nothing more than a set of ordinary SQL commands for creating a database table:
-- CreateTable
CREATE TABLE "users" (
"id" SERIAL NOT NULL,
"email" VARCHAR(255) NOT NULL,
"bio" TEXT,
"https://habr.com/ru/post/653399/image" VARCHAR(500),
"password" VARCHAR(255) NOT NULL,
"username" VARCHAR(255) NOT NULL,
CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);
-- CreateIndex
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");
-- CreateIndex
CREATE UNIQUE INDEX "users_password_key" ON "users"("password");
-- CreateIndex
CREATE UNIQUE INDEX "users_username_key" ON "users"("username");
That is, in fact – first, an operation was performed to convert the Prisma model into such an SQL query, and only then – this query was executed and a table was created in the database.
I think you should pay attention to the moment that the created migration file – you need check always, for possible jambs during its generation. Curve migration can destroy a terabyte of data, and the backup at the same time – did not have time to create. What you should pay attention to is the correctness of the creation indices And foreign keys. And since PostgreSQL is used in this modest manual, it will not be superfluous to provide a link to of documentation for this database.
And so, let’s check if this is really the case – that the table was successfully created in the database? Easily!
udemy_medium_clone=# d
List of relations
Schema | Name | Type | Owner
--------+--------------------+----------+----------
public | _prisma_migrations | table | postgres
public | users | table | postgres
public | users_id_seq | sequence | postgres
(3 rows)
… true – table users
was created. Let’s see what this table is users
:
udemy_medium_clone=# d users
Table "public.users"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
email | character varying(255) | | not null |
bio | text | | "https://habr.com/ru/post/653399/image" character varying(500) | | |
password | character varying(255) | | not null |
username | character varying(255) | | not null |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_email_key" UNIQUE, btree (email)
"users_password_key" UNIQUE, btree (password)
"users_username_key" UNIQUE, btree (username)
… hmm, very likely to be true – this is exactly the kind of table that I designed as a model in Prisma.
## Conclusion
Well, basically, that’s all – Prisma installed in the project, its connection to a specific database has been successfully configured; created a data model and successfully migrated it to a table in the database.
Of course, there is another tool for working with the database in NestJS – this TypeORM. But its setup and connection for me is so complicated, confusing and time-consuming that I’m not ashamed to admit that I don’t know TypeORM and I don’t have much desire to know – yet.