DEV Community

loading...
Cover image for What is Prisma? (part 1 of 3)

What is Prisma? (part 1 of 3)

Dominic Hadfield
・6 min read

Prisma is a nodejs ORM written in Rust that is rapidly growing in popularity. Offering a suite of database tools and type safety for your database, it is a useful addition to the eco system for beginner or expert alike. There are already first party clients written in Typescript and Go by Prisma but there are other community driven clients written in other languages. Initial focus was on SQL database connections but work has begun on a MongoDB connector and in future other databases too. During this article, we will be connecting to a Postgres database so, if you don't have Postgres installed, please install it.

Everything begins with a schema. If you already have a database, you can introspect it to generate a mapping of your database entites and allow you to perform migrations on your database. For some, this is enough of a feature that they do not even need to use any other part of Prisma to get benefits from it. We will start with a brand new project and build out from there.

If you want to skip to the code, its here

Lets make a new directory:
mkdir what-is-prisma && cd what-is-prisma && npm init -y

Now let's add the package for the CLI:
npm i -D prisma
And for the Typescript client:
npm i --save @prisma/client

Finally, we can initialise the empty schema:
npx prisma init

We now have an .env file with our database url and a prisma directory holding our prisma.schema file.

Lets setup our database url.

postgresql://johndoe:randompassword@localhost:5432/mydb?schema=public

The first part is the protool to connect to a postgresql database. It will use the credentials of user: johndoe and password: randompassword. It will connect to the instance at localhost on port 5432 (postgres default) to the database mydb. Finally, it will use the public schema.

I want to change most of these to default values so my new connection string is:

postgresql://postgres:postgres@localhost:5432/what-is-prisma?schema=public

To read more about data sources, go here

Let's move on to the schema file. If you use VSCode, I would recommend installing the Prisma extension for syntax highlighting, formatting and auto completion.

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}
Enter fullscreen mode Exit fullscreen mode

It's pretty empty in here, all we have so far is the datasource connector, connecting to postgres using the URL specified in the env file.

There is also the generator for the client, this will generate the types and the orm for us to use in our code. The generator pattern allows for the writing of custom generators and there are already some advanced ones to generate documentation, db diagrams or bindings into the Prisma ORM to generate GraphQL types. This will surely extend in future.

To read more about generators, go here

Now, because I have no imagination, we are going to make a blog (😲). This is just to show how to use various common use patterns in Prisma without it being too contrived. I could do a TODO app but I'm sure that is worse.

So, lets create a User table and add some columns:

model User {
  id          Int     @id @default(autoincrement())
  displayName String
}
Enter fullscreen mode Exit fullscreen mode

Et voila! So, this is a very basic model, I will quickly run through what everything is. The model is called User, this will create a User table with two columns: id of type Int and displayName of type String. The id column has been marked with @id which marks it as the primary key for the table and when an entry is created, by default it will use an autoincrementing id. With some minor adjustments, we could use UUID instead, but I will continue to use autoincrementing.

model User {
  id          String     @id @default(uuid())
  displayName String
}
Enter fullscreen mode Exit fullscreen mode

Let's add a few more fields:

model User {
  id          Int     @id @default(autoincrement())
  displayName String
  firstName   String?
  lastName    String
}
Enter fullscreen mode Exit fullscreen mode

So, we have two more columns, firstName and lastName. firstName is optional, as shown with the question mark. This means that the field can be null on creation or update. lastName is not optional and therefore must be set when creating or updating a User.

Lets add some timestamp fields so we can see when a User has been updated or created:

model User {
  id          Int      @id @default(autoincrement())
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  displayName String
  firstName   String?
  lastName    String
}
Enter fullscreen mode Exit fullscreen mode

Well, that was easy! Using the @default decorator, we can specify that if no value is passed that it uses the current timestamp. updatedAt will automatically be updated anytime the row in the database is updated.

This is all well and good, but using camelCase names in sql is a nightmare! We want to use a consistent pattern of snake_case to allow for easier writing of SQL scripts in the future. In comes @map and @@map to the rescue.

model User {
  id          Int      @id @default(autoincrement())
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")
  displayName String   @map("display_name")
  firstName   String?  @map("first_name")
  lastName    String   @map("last_name")

  @@map("user")
}
Enter fullscreen mode Exit fullscreen mode

@@map is used to map table names and @map is used to map column names. Now we have snake_case in the database but will have camelCase on the server.

To read more about Prisma data modelling, go here

"Well", I hear you say, "This is all well and good but how can we have a blog with only users?" Lets create our Post entity now.

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  title     String
  content   String

  @@map("post")
}
Enter fullscreen mode Exit fullscreen mode

So now we have a posts table but we need to connect it to our users, this is actually quite simple:

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  title     String
  content   String
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int      @map("author_id")

  @@map("post")
}
Enter fullscreen mode Exit fullscreen mode

So now, we have added an authorId to the table and, in the data layer that Prisma provides, it will make the association to the author so we can request related data through a single query. So just to be clear, in the database there is no author column, only an authorId, the @relation decorator maps the authorId from the post onto an id for the user. We need to just add in the reference to the user model.

model User {
  id          Int      @id @default(autoincrement())
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")
  displayName String   @map("display_name")
  firstName   String?  @map("first_name")
  lastName    String   @map("last_name")
  post        Post[]

  @@map("user")
}
Enter fullscreen mode Exit fullscreen mode

Thats it, we will now be able to request a user and their posts in a single query or, we can request a post and get all of the user's information.

Let's now add some tags that users can add to their posts. This should look very familiar by now:

model Tag {
  id          Int      @id @default(autoincrement())
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")
  title       String
  description String
  users       User[]
  posts       Post[]

  @@map("tag")
}
Enter fullscreen mode Exit fullscreen mode

And the other two entities:

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  title     String
  content   String
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int      @map("author_id")
  tags      Tag[]

  @@map("post")
}

model User {
  id          Int      @id @default(autoincrement())
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")
  displayName String   @map("display_name")
  firstName   String?  @map("first_name")
  lastName    String   @map("last_name")
  post        Post[]
  tags        Tag[]

  @@map("user")
}
Enter fullscreen mode Exit fullscreen mode

And that is it. So, in the background, Prisma will make join tables that connect the tags to the posts and users. These tables only contain two columns, one for each id. If you wish to add more to them, you need to explicity declare a join table. This will give you greater control of names and columns but increases your maintenance overhead.

model User {
  id          Int         @id @default(autoincrement())
  createdAt   DateTime    @default(now()) @map("created_at")
  updatedAt   DateTime    @updatedAt @map("updated_at")
  displayName String      @map("display_name")
  firstName   String?     @map("first_name")
  lastName    String      @map("last_name")
  post        Post[]
  tags        TagToUser[]

  @@map("user")
}

model Tag {
  id          Int         @id @default(autoincrement())
  createdAt   DateTime    @default(now()) @map("created_at")
  updatedAt   DateTime    @updatedAt @map("updated_at")
  title       String
  description String
  users       TagToUser[]
  posts       Post[]

  @@map("tag")
}

model TagToUser {
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  tag       Tag      @relation(fields: [tagId], references: [id])
  tagId     Int
  user      User     @relation(fields: [userId], references: [id])
  userId    Int

  @@id([userId, tagId])
}
Enter fullscreen mode Exit fullscreen mode

To read more about relations, go here

So that is all that I want to cover in part 1, in part 2 I will be covering the database tools and common pitfalls when running migrations. If you want the code, its here

Discussion (0)