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 prismawhich 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 .envwhich is also automatically created by Prisma on initialization.

Looking at the contents of the file .envthen 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 .gitignoreso 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 migrationsinside 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.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *