Akshay Kadam Akshay is a creator, computer artist and micropreneur from Mumbai | https://twitter.com/deadcoder0904

An introduction to Prisma 2

11 min read 3330

Prisma2

Prisma 2 provides an abstraction that allows us to write database queries using JavaScript methods and objects.

This makes it easier for us since we don’t have to write queries in the database language itself.

Prisma 2 maps the queries written in our chosen language into the database of our choice. Currently, it only supports mySQL, SQLite, and PostgreSQL.

Prisma 2 Preview was released on June 18, 2019. As of this writing, Prisma 2 is available for testing purposes for early adopters. It is almost ready for production usage.

Prisma 2
Is Prisma2 ready?

The latest status of Prisma 2 General Availability can be checked at isprisma2ready.com.

What is Prisma 2?

Prisma 2 is a database framework which consists of 3 major tools:

  1. Prisma Client JS: Type-safe and auto-generated database client (“ORM replacement”)
  2. Prisma Migrate: Declarative migration system with custom workflows
  3. Prisma Studio: An Admin UI to support various database workflows

1. Prisma Client JS

Prisma Client JS is a type-safe database client that replaces traditional ORMs like Sequelize, Bookshelf, and Mongoose.

It allows us to access the database through plain JavaScript methods and objects without having to write the query in the database language itself.

In short, it acts as an abstraction in front of the database, so it’s easier to write CRUD (create, read, update, and delete) applications.

2. Prisma Migrate

Prisma Migrate is a powerful database schema migration tool. It uses a declarative data modeling syntax to describe our database schema.

We made a custom demo for .
No really. Click here to check it out.

Prisma Migrate also stores our entire migration history and easily lets us revert and replay migrations.

It also allows us to run before-and-after hooks to execute scripts while migrating so we can populate the database with required values during a migration.

Currently, it is in an experimental state.

3. Prisma Studio

Prisma Studio allows us to visualize data via an Admin UI.

Here, we can perform CRUD (create, read, update, and delete) operations on our data. This is the easiest way to visualize data from our database as well as manipulate it.

Prerequisites

For this tutorial, you need a basic knowledge of how Node.js works, how to install packages from npm, and how to run npm scripts.

To make sure we’re on the same page, these are the versions used in this tutorial:

  • Node v13.7.0
  • npm v6.13.6
  • npx v6.13.6
  • prisma2 v2.0.0-preview020.3

Getting started

First, we need to install prisma2 globally. To do so, type the following command into the terminal:

$ npm install --global prisma2

The above command will install prisma2 globally, so now we can access prisma2 in our terminal from anywhere.

Go ahead and type the following in the terminal to bootstrap a new prisma2 project:

$ prisma2 init hello-world

It should now print the following in your terminal:

✔ Your Prisma schema was created at hello-world/prisma/schema.prisma. You can now open it in your favorite editor.

Next steps
1. Run cd hello-world to go to the newly created folder.
2. Set your DB connection string as the `url` of the `datasource` block.
3. Run prisma2 introspect to test the connection and obtain your data model.
4. Run prisma2 generate to generate Prisma Client.

You can then start using Prisma Client in your application:

```
import { PrismaClient } from '@prisma/client'
// or const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()
```

More information in our documentation:
https://pris.ly/getting-started

By typing prisma2 init, we bootstrapped a new Prisma 2 project and called it hello-world. Now we can follow the instructions from the output of the prisma2 init command.

First, go into the hello-world folder by typing the following in the terminal:

$ cd hello-world

Open up the folder in your favorite Text Editor or IDE. For me, its Visual Studio Code (VSCode).

I can open up hello-world inside a new VSCode window by typing the following in the terminal:

$ code .

If you use other Text Editors like Sublime or Brackets, it will have similar commands.

Prisma schema file

Now you should see a folder named prisma/ inside of it, which contains a file called schema.prisma.

Go ahead and open it. It should have the following contents:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// The `datasource` block is used to specify the connection to your DB.
// Set the `provider` field to match your DB type: "postgresql", "mysql" or "sqlite".
// The `url` field must contain the connection string to your DB.
// Learn more about connection strings for your DB: https://pris.ly/connection-strings
datasource db {
  provider = "postgresql" // other options are: "mysql" and "sqlite"
  url      = "postgresql://johndoe:johndoe@localhost:5432/mydb?schema=public"
}
// Other examples for connection strings are:
// SQLite: url = "sqlite:./dev.db"
// MySQL:  url = "mysql://johndoe:johndoe@localhost:3306/mydb"
// You can also use environment variables to specify the connection string: https://pris.ly/prisma-schema#using-environment-variables

// By adding the `generator` block, you specify that you want to generate Prisma's DB client.
// The client is generated by runnning the `prisma generate` command and will be located in `node_modules/@prisma` and can be imported in your code as:
// import { Prisma Client } from '@prisma/client'
generator client {
  provider = "prisma-client-js"
}

// Next steps:
// 1. Add your DB connection string as the `url` of the `datasource` block
// 2. Run `prisma2 introspect` to get your data model into the schema (this will override this file and delete all comments!)
// 3. Run `prisma2 generate` to generate Prisma Client JS
// 4. Start using Prisma Client JS in your application

Now go ahead and remove all the comments and change the contents of the schema.prisma file to the following:

// 1
datasource db {
  provider = "sqlite"
  url      = "sqlite:./dev.db"
}

// 2
generator client {
  provider = "prisma-client-js"
}

// 3
model User {
  id    String @default(cuid()) @id
  name  String
  todos Todo[]
}

// 4
model Todo {
  id        String  @default(cuid()) @id
  text      String
  completed Boolean @default(false)
}

The schema.prisma file contains the data model as well as the configuration options. Let’s break it down.

  1. The datasource block is used to specify the connection to the database. We set the provider field to sqlite.

SQLite is an embedded database software which can be installed anywhere from low-powered devices to devices with lower memory.

It allows us to create a local database without having to install anything.

The url field contains the connection string to our database. Here, whatever we type after sqlite: is the place where the database gets created.

In our case, the database will be created in the prisma/ folder with the name dev.db.

  1. By adding the generator block, we specify that we want to generate Prisma’s database client.

The client is generated by running the prisma generate command and will be located in node_modules/@prisma. It can be imported in our code as import { PrismaClient } from '@prisma/client'.

  1. Then, we define the User data model using the model block. Models represent the entities of our application domain.

On a technical level, a model maps to the underlying structures of the data source.

For example, in relational databases like SQL, SQLite, and PostgreSQL, a model maps to a table, whereas in non-relational databases like MongoDB, it would map to a collection. We have 3 attributes inside our User model, namely, id, name, and todos.

The id field is a primary key of type String with a default value of cuid(). To determine which field of a model is the ID field, we can annotate it with the @id attribute.

In relational databases like SQL, SQLite, and PostgreSQL, a unique ID corresponds to a column with a primary key constraint.

The name field is of type String. The todos field is of type Todo, which we’ll define later. It contains a type modifier [] that makes the field a list. So we can store a list of todos in our User data model.

  1. Finally, we define the Todo data model. Todo data model contains 3 fields: namely, id, text, and completed. Again, the id field is a primary key of type String with a default value of cuid().

The text field is of type String. Finally, the completed field is of type Boolean with a default value of false.

Generate empty package.json

Let’s generate a package.json file.

Type the following in the terminal:

$ npm init -y

The above command generates a package.json file by telling the generator to use the defaults without going through the interactive process of asking the questions.

Install Prisma Client JS

Now add Prisma Client to the project by installing @prisma/client using npm as follows:

$ npm install @prisma/client

Go ahead and generate Prisma Client by typing the following command:

$ prisma2 generate

The generated client will be located in node_modules/@prisma, thus allowing us to import Prisma Client into our code as import { PrismaClient } from '@prisma/client'.

Migrate database using Prisma Migrate

Now let’s migrate our database to create empty tables.

Migrating the database is a two-step process:

  1. Save a new migration (migrations are represented as directories on the file system)
  2. Run the migration (to migrate the schema of the underlying database)

In CLI commands, these steps can be performed as follows (the CLI steps are in the process of being updated to match):

$ prisma2 migrate save --name 'Init' --experimental
$ prisma2 migrate up --experimental

If you get a prompt like the following, select Yes:

You are trying to apply a migration for Sqlite database /dev.db.
A database with that name doesn't exist at sqlite:./dev.db.
Do you want to create the database?

┌─ Database options ────────────────────────────────────────────────────────────────────┐
│                                                                                       │
│ ❯ Yes            Create new Sqlite database /dev.db                                   │
│   No             Don't create the database                                            │
│                                                

Since Prisma Migrate is still in the experimental phase, we need to append the --experimental flag. The above commands will create a dev.db file with empty tables.

Seed database with initial values

Go ahead and create a file named seed.js inside of a prisma/ folder:

$ touch prisma/seed.js

Now, open up a seed.js file and start by importing Prisma Client:

const { PrismaClient } = require("@prisma/client")

const prisma = new PrismaClient()

const main = async () => {
  
}

main()
  .catch(e => console.error(e))
  .finally(async () => {
    await prisma.disconnect()
  })

First, we’ve imported PrismaClient from the @prisma/client package, which was generated from our schema file schema.prisma when we ran prisma2 generate. Next, we create a constant called prisma, which is an instance of PrismaClient.

Later, we have an async function named main — which is currently empty — but we will fill it later.

Finally, we call a main() function. If it has any errors, then we will catch() them and display them using console.error. And whether we catch any errors or not, we will run the finally() function.

The finally() function itself contains an async callback which disconnects from the Prisma database so as to not keep it running as we are just seeding the database.

Now open up main() function and paste the following:

const sasha = await prisma.users.create({
  data: {
    name: "Sasha"
  }
})

console.log(sasha)

If you type the above code, you will find auto-completion support thanks to TypeScript. The above code will create a user with a name Sasha.

We can access each function via the respective model property on our generated PrismaClient instance, e.g. users for the User model:

Note: The name users is auto-generated using the pluralize package. It is therefore recommended to name our models singular i.e. User and not Users.

This is the simplest way to create a user by just giving it a name field.

Go ahead and run the seed.js file by typing the following in the terminal:

$ node prisma/seed

The console should output this:

{ id: 'ck60v6k5c000044v5a0eqc0yg', name: 'Sasha' }

Now, below that, let’s create another user, johnny, while simultaneously setting the todos:

const johnny = await prisma.users.create({
  data: {
    name: "Johnny",
    todos: {
      create: [
        {
          text: "Do dishes"
        },
        {
          text: "Walk the dog"
        }
      ]
    }
  }
})

console.log(johnny)

Here, we create a user with the name Johnny (same as above). The difference is that we also create a list of todos.

The todos field is an object which takes in create. create is an array of objects.

The objects are the actual todo items containing the fields id, text, and completed from the Todo data model.

Note: Prisma has great auto-completion so that we don’t need to remember the data model or the create object. It will be provided in the auto-complete suggestion list so we can write code fast while simultaneously achieving great developer experience (DX).

This is an example of creating a user with todos. Again, run the seed.js file and you should see the console output the following:

{ id: 'ck60v6k5o000144v5jgn4t583', name: 'Johnny' }

Note: It doesn’t return todos but it definitely adds them to the database. We will see it when we query our database.

Let’s create a todo without a user by typing the following below that:

const run = await prisma.todos.create({
  data: {
    text: "Run a full marathon"
  }
})

console.log(run)

The above code will simply created a todo without assigning any user to it. Run the seed.js file to see the output as follows:

{
  id: 'ck60v6k5t000444v5gc1vv3cs',
  text: 'Run a full marathon',
  completed: false
}

Let’s create a todo with a user by typing the following below that:

const grocery = await prisma.todos.create({
  data: {
    text: "Buy groceries for the week",
    user: {
      create: {
        name: "Amelia"
      }
    }
  }
})

console.log(grocery)

The above code will create a todo while assigning it to the user named Amelia. Run the seed.js file to see the console output the following:

{
  id: 'ck60v6k5x000544v5y5oig1qq',
  text: 'Buy groceries for the week',
  completed: false
}

Note: It doesn’t return the user , but it definitely adds them to the database. We will see it when we query our database.

The entire seed.js file should now look like this:

const { PrismaClient } = require("@prisma/client")

const prisma = new PrismaClient()

const main = async () => {
  const sasha = await prisma.users.create({
    data: {
      name: "Sasha"
    }
  })

  console.log(sasha)

  const johnny = await prisma.users.create({
    data: {
      name: "Johnny",
      todos: {
        create: [
          {
            text: "Do dishes"
          },
          {
            text: "Walk the dog"
          }
        ]
      }
    }
  })

  console.log(johnny)

  const run = await prisma.todos.create({
    data: {
      text: "Run a full marathon"
    }
  })

  console.log(run)

  const grocery = await prisma.todos.create({
    data: {
      text: "Buy groceries for the week",
      user: {
        create: {
          name: "Amelia"
        }
      }
    }
  })

  console.log(grocery)
}

main()
  .catch(e => console.error(e))
  .finally(async () => {
    await prisma.disconnect()
  })

Go ahead and create an index.js file in the root folder using the following command:

$ touch index.js

Let’s start by importing Prisma Client:

const { PrismaClient } = require("@prisma/client")

const prisma = new PrismaClient()

const main = async () => {
  
}

main()
  .catch(e => console.error(e))
  .finally(async () => {
    await prisma.disconnect()
  })

Note: This is a demo project, which is why we are closing the connection to the database. In real world applications, the server keeps running, so technically we’d only call main() function without the finally() block.

Let’s start by adding the following code inside the main() function:

const users = await prisma.users.findMany()

console.log(users)

The above code will find all users as no condition is specified inside findMany(). To run the index.js file, type the following in the terminal:

$ node index

The output should be as follows:

[
  { id: 'ck60v6k5c000044v5a0eqc0yg', name: 'Sasha' },
  { id: 'ck60v6k5o000144v5jgn4t583', name: 'Johnny' },
  { id: 'ck60v6k5x000644v5f4opbbv5', name: 'Amelia' }
]

Below that, add the following block of code:

const usersWithTodos = await prisma.users.findMany({
    include: {
      todos: true
    }
  })

console.log(JSON.stringify(usersWithTodos, null, 2))

The above code returns all the users, but it also includes todos the user has created.

The JSON.stringify() method specified above allows us to pretty-print JSON with the spacing level of 2. Go ahead and run the index.js file to see the output as follows:

[
  {
    "id": "ck60v6k5c000044v5a0eqc0yg",
    "name": "Sasha",
    "todos": []
  },
  {
    "id": "ck60v6k5o000144v5jgn4t583",
    "name": "Johnny",
    "todos": [
      {
        "id": "ck60v6k5o000244v5kzryzqgx",
        "text": "Do dishes",
        "completed": false
      },
      {
        "id": "ck60v6k5o000344v5ngbt91qd",
        "text": "Walk the dog",
        "completed": false
      }
    ]
  },
  {
    "id": "ck60v6k5x000644v5f4opbbv5",
    "name": "Amelia",
    "todos": [
      {
        "id": "ck60v6k5x000544v5y5oig1qq",
        "text": "Buy groceries for the week",
        "completed": false
      }
    ]
  }
]

Now add the following chunk of code below that:

const todos = await prisma.todos.findMany()

console.log(todos)

Similar to the first users query, it finds all todos in the database. Run the index.js file to see the output:

[
  {
    id: 'ck60v6k5o000244v5kzryzqgx',
    text: 'Do dishes',
    completed: false
  },
  {
    id: 'ck60v6k5o000344v5ngbt91qd',
    text: 'Walk the dog',
    completed: false
  },
  {
    id: 'ck60v6k5t000444v5gc1vv3cs',
    text: 'Run a full marathon',
    completed: false
  },
  {
    id: 'ck60v6k5x000544v5y5oig1qq',
    text: 'Buy groceries for the week',
    completed: false
  }
]

Below that, add the following code:

const todosWithUsers = await prisma.todos.findMany({
  include: {
    user: true
  }
})

console.log(JSON.stringify(todosWithUsers, null, 2))

Similar to the second users query, it will find all todos with the user related to that todo.

Again, run the index.js file to see the output that will be pretty-printed with the following contents:

[
  {
    "id": "ck60v6k5o000244v5kzryzqgx",
    "text": "Do dishes",
    "completed": false,
    "user": {
      "id": "ck60v6k5o000144v5jgn4t583",
      "name": "Johnny"
    }
  },
  {
    "id": "ck60v6k5o000344v5ngbt91qd",
    "text": "Walk the dog",
    "completed": false,
    "user": {
      "id": "ck60v6k5o000144v5jgn4t583",
      "name": "Johnny"
    }
  },
  {
    "id": "ck60v6k5t000444v5gc1vv3cs",
    "text": "Run a full marathon",
    "completed": false,
    "user": null
  },
  {
    "id": "ck60v6k5x000544v5y5oig1qq",
    "text": "Buy groceries for the week",
    "completed": false,
    "user": {
      "id": "ck60v6k5x000644v5f4opbbv5",
      "name": "Amelia"
    }
  }
]

Notice, when we created the todo Run a full marathon , we didn’t specify a user, which is the reason it is null.

The entire index.js file should now look like this:

const { PrismaClient } = require("@prisma/client")

const prisma = new PrismaClient()

async function main() {
  const users = await prisma.users.findMany()

  console.log(users)

  const usersWithTodos = await prisma.users.findMany({
    include: {
      todos: true
    }
  })

  console.log(JSON.stringify(usersWithTodos, null, 2))

  const todos = await prisma.todos.findMany()

  console.log(todos)

  const todosWithUsers = await prisma.todos.findMany({
    include: {
      user: true
    }
  })

  console.log(JSON.stringify(todosWithUsers, null, 2))
}

main()
  .catch(e => console.error(e))
  .finally(async () => {
    await prisma.disconnect()
  })

Prisma Studio – visualize data using Admin UI

Prisma Studio allows us to visualize data using a beautiful Admin UI. It also allows us to perform CRUD (create, read, update, delete) operations on our data.

To open up Prisma Studio, type the following in the terminal:

$ prisma2 studio --experimental

Since Prisma Studio is still in the experimental phase, we need to add the --experimental flag. Now open up http://localhost:5555 to see the Admin UI.

Using Admin UI, we can quickly sort the data, filter it and even perform queries without having to write them in our script file.

The following are some screenshots of the Admin UI:

1. Users table

Prisma studio
Users table – Prisma Studio

2. Todos table

Prisma studio
Todos table – Prisma Studio

3. Run query

Prisma studio query
Run query – Prisma Studio

You can find the entire source code used in this tutorial on Github.

Conclusion

To sum it up, we learned the basics of Prisma 2. It has 3 major tools, namely: Prisma Client, Prisma Migrate, and Prisma Studio.

We used Prisma Client to seed values into our database as well as query data from our database.

Later, we used Prisma Migrate to create the initial migrations. Finally, we used Prisma Studio to visualize our data using a beautiful Admin UI.

Prisma 2 is still in its early phases. It has a lot of potential. The team at Prisma has nailed the developer experience.

It isn’t completely ready yet, but you can be an early adopter.

Give it a shot. You won’t be disappointed.

Plug: , a DVR for web apps

LogRocket is a frontend application monitoring solution that lets you replay problems as if they happened in your own browser. Instead of guessing why errors happen, or asking users for screenshots and log dumps, LogRocket lets you replay the session to quickly understand what went wrong. It works perfectly with any app, regardless of framework, and has plugins to log additional context from Redux, Vuex, and @ngrx/store.

In addition to logging Redux actions and state, LogRocket records console logs, JavaScript errors, stacktraces, network requests/responses with headers + bodies, browser metadata, and custom logs. It also instruments the DOM to record the HTML and CSS on the page, recreating pixel-perfect videos of even the most complex single-page apps.

.
Akshay Kadam Akshay is a creator, computer artist and micropreneur from Mumbai | https://twitter.com/deadcoder0904

2 Replies to “An introduction to Prisma 2”

  1. Hi! Does Prisma 2 work fine with MongoDB? Because there is nothing about it in the official docs, neither examples.

Leave a Reply