Getting Start On Prisma

Prisma is an open-source database toolkit and Object-Relational Mapping (ORM) tool.

What’s Prisma

Prisma provides a set of tools and libraries that simplify database access and management for developers. Prisma supports various databases, including PostgreSQL, MySQL, SQLite, and SQL Server.

Here are some key features and concepts of Prisma:

1. Database Modeling: Prisma allows you to define your database schema using its own declarative language called Prisma Schema. You can define entities, relationships, fields, and other aspects of your database structure.
2. Type-Safe Database Access: Prisma generates a type-safe and auto-completed database client based on your Prisma Schema. This client provides a set of methods for querying, creating, updating, and deleting data in your database. It leverages TypeScript or JavaScript to provide compile-time type checking and autocompletion, reducing the chances of runtime errors.
3. Database Migrations: Prisma includes a migration system that helps you manage changes to your database schema over time. It tracks and applies migrations to keep your database schema in sync with your Prisma Schema.
4. Query Language: Prisma provides a powerful and expressive query language called Prisma Client Query API. It allows you to write complex database queries in a concise and readable manner, supporting filtering, sorting, pagination, and other query operations.
5. Real-Time Data Sync: Prisma supports real-time data synchronization through its integration with GraphQL subscriptions. You can subscribe to changes in your database and receive real-time updates in your application.
6. Prisma Client: Prisma generates a client library specific to your database schema. This client library acts as an interface between your application and the database, providing a convenient and type-safe API to interact with the database.

Prisma is often used in modern web development stacks, alongside frameworks like Next.js, Express, or GraphQL. It simplifies database access, improves developer productivity, and helps maintain a clean and scalable data layer in your application.

Quick start on Prisma

Create project and setup

mkdir hello-prisma 
cd hello-prisma
npm init -y 
npm install typescript ts-node @types/node --save-dev

As a first step, create a project directory and navigate into it. Then the code executed in the terminal using npm, the package manager for Node.js. Let’s break down each command:

1. npm init -y: This command initializes a new npm project in the current directory. The -y flag automatically accepts the default options for the project initialization, such as the package name, version, entry point, and license. It generates a package.json file that holds metadata about the project and its dependencies.
2. npm install typescript ts-node @types/node --save-dev: This command installs several packages as development dependencies in the project. Here’s what each package does:
   • typescript: This package installs the TypeScript compiler, which allows you to write and transpile TypeScript code into JavaScript.
   • ts-node: This package provides a TypeScript execution environment for Node.js. It allows you to directly run TypeScript files without explicitly compiling them to JavaScript first.
   • @types/node: This package provides TypeScript type definitions for Node.js. It enables TypeScript to understand and provide type checking for Node.js-specific modules and APIs.
   • The --save-dev flag indicates that these packages should be saved as development dependencies in the package.json file. Development dependencies are packages required during the development process but not necessary for the production deployment of the application.

By running these commands, you set up a new npm project, install TypeScript and related tools, and configure your project to use TypeScript for development.

npx tsc --init
npm install prisma --save-dev
npx prisma init --datasource-provider sqlite

1. npx tsc --init: This command initializes a TypeScript project in the current directory. It generates a tsconfig.json file that contains configuration options for the TypeScript compiler (tsc). The --init flag tells tsc to create a default tsconfig.json file with basic settings. You can further customize this file to suit your project’s needs.
2. npm install prisma --save-dev: This command installs the Prisma package as a development dependency in the project. The --save-dev flag indicates that the package should be saved in the devDependencies section of the package.json file. Prisma is a toolkit for working with databases, and installing it as a development dependency means it’s not required for the production deployment of the application.
3. npx prisma init --datasource-provider sqlite: This command initializes a Prisma project in the current directory. It sets up the necessary files and configurations for using Prisma in your project. The --datasource-provider sqlite flag specifies that you want to use SQLite as the data source for your Prisma project. Prisma will generate the required files and configurations to connect to a SQLite database.

By running these commands, you set up a TypeScript project, install Prisma as a development dependency, and initialize a Prisma project with SQLite as the data source. This allows you to leverage Prisma’s features and tools for working with databases, such as defining your database schema, generating a type-safe database client, and managing database migrations.

Model data in Prisma

The Prisma schema provides an intuitive way to model data. Add the following models to your schema.prisma file:

model User { 
    id Int @id @default(autoincrement()) 
	email String @unique 
	name String? 
	posts Post[] 
} 

model Post { 
    id Int @id @default(autoincrement()) 
    title String 
    content String? 
    published Boolean @default(false) 
    author User @relation(fields: [authorId], references: [id]) 
    authorId Int 
}

This code is written in Prisma Schema Language, which is used to define the database schema and models for a Prisma project. Let’s explain User first:

• model User: This declares a model named “User” representing a user entity in the database.
• id Int @id @default(autoincrement()): This defines an id field of type Int as the primary key for the “User” model. The @id attribute specifies that this field is the primary identifier. The @default(autoincrement()) attribute indicates that the field should automatically increment its value for each new record.
• email String @unique: This defines an email field of type String in the “User” model. The @unique attribute ensures that each email value in the database is unique.
• name String?: This defines a nullable name field of type String in the “User” model. The ? denotes that the field can be optional and may contain a null value.
• posts Post[]: This establishes a one-to-many relationship between the “User” and “Post” models. It indicates that a user can have multiple posts. The Post[] syntax represents an array of Post objects associated with the user.

The the Post:

• model Post: This declares a model named “Post” representing a post entity in the database.
• id Int @id @default(autoincrement()): This defines an id field of type Int as the primary key for the “Post” model, similar to the “User” model.
• title String: This defines a title field of type String in the “Post” model, representing the title of the post.
• content String?: This defines a nullable content field of type String in the “Post” model, representing the content of the post.
• published Boolean @default(false): This defines a published field of type Boolean in the “Post” model. The @default(false) attribute sets the default value of the field to false.
• author User @relation(fields: [authorId], references: [id]): This establishes a many-to-one relationship between the “Post” and “User” models. It indicates that a post belongs to a single user. The @relation attribute specifies the relationship, and fields: [authorId] and references: [id] define the fields used for establishing the relationship.
• authorId Int: This defines an authorId field of type Int in the “Post” model. It represents the foreign key that references the id field of the associated user in the “User” model.

  Run migration to create database tables

At this point, you have a Prisma schema but no database yet. Run the following command in your terminal to create the SQLite database and the User and Post tables represented by your models:

npx prisma migrate dev --name init

Let’s explain it:

• npx: This is a utility that allows you to run a package without installing it globally. It executes the following command using the locally installed version of the package.
• prisma: This is the command for invoking the Prisma CLI.
• migrate dev: This command is used to apply pending database migrations. It ensures that the database schema is up to date with the latest changes defined in your Prisma schema file.
• --name init: This flag specifies the name of the migration. In this case, it is set to “init”. The name is used to identify the migration and can be helpful for tracking and managing migrations in your project.

When you run the provided code, the Prisma CLI will perform the following steps:

1. It will check for any pending migrations that have not been applied to the database.
2. If there are pending migrations, it will generate the necessary SQL statements to apply those migrations to the database. These migrations typically involve creating or modifying database tables, columns, or other schema changes defined in your Prisma schema file.
3. It will execute the generated SQL statements to apply the migrations to the database, ensuring that the database schema is synchronized with your Prisma schema.
4. Once the migrations are applied, the Prisma CLI will update the migration history, marking the applied migrations as completed.

Send queries to your database with Prisma Client

To send queries to the database, you will need a TypeScript file to execute your Prisma Client queries. Create a new file called script.ts for this purpose:

touch script.ts

Paste the following boilerplate into it:

import { PrismaClient } from '@prisma/client'; 

const prisma = new PrismaClient(); 

async function main() { 
    // ... you will write your Prisma Client queries here 
} 

main() 
    .then(async () => { 
        await prisma.$disconnect() 
    }) 
    .catch(async (e) => { 
        console.error(e) 
        await prisma.$disconnect() 
        process.exit(1) 
    })

The code:

• Imports the PrismaClient class from the @prisma/client package. The PrismaClient is the main entry point for interacting with your database using Prisma.
• Creates an instance of the PrismaClient class and assigns it to the prisma variable. This instance represents a connection to your database and provides methods for executing queries and mutations.
• Defines an async function named main(). This function serves as the entry point for executing Prisma Client queries and mutations. You can write your Prisma Client code inside this function.
• Then
  • Calls the main() function and handles the execution flow. It uses a combination of then() and catch() to handle successful execution and error scenarios.
  • If the main() function resolves successfully, the then() block is executed. Inside the then() block, await prisma.$disconnect() is called to close the database connection and release any resources held by Prisma Client.
  • If an error occurs during the execution of main(), the catch() block is executed. The error is logged to the console using console.error(e). Then, await prisma.$disconnect() is called to close the database connection, and process.exit(1) is used to exit the Node.js process with a non-zero status code (indicating an error).

Create new User record

Add the following code to your script.ts file:

async function main() {
    const user = await prisma.user.create({
        data: {
          name: 'Alice',
          email: 'alice@prisma.io',
        },
    })
    console.log(user)
}

Next, execute the script with the following command:

npx ts-node script.ts

You just created your first database record with Prisma Client!

Retrieve all User records

Prisma Client offers various queries to read data from your database. In this section, you’ll use the findMany query that returns all the records in the database for a given model.

Delete the previous Prisma Client query and add the new findMany query instead:

async function main() {
  const users = await prisma.user.findMany()
  console.log(users)
}

Then:

npx ts-node script.ts

Notice how the single User object is now enclosed with square brackets in the console. That’s because the findMany returned an array with a single object inside.

Explore relation queries with Prisma

One of the main features of Prisma Client is the ease of working with relations. In this section, you’ll learn how to create a User and a Post record in a nested write query. Afterwards, you’ll see how you can retrieve the relation from the database using the include option.

First, adjust your script to include the nested query:

sync function main() {
  const user = await prisma.user.create({
    data: {
      name: 'Bob',
      email: 'bob@prisma.io',
      posts: {
        create: {
          title: 'Hello World',
        },
      },
    },
  })
  console.log(user);
}

Run the query by executing the script again:

npx ts-node script.ts

By default, Prisma only returns scalar fields in the result objects of a query. That’s why, even though you also created a new Post record for the new User record, the console only printed an object with three scalar fields: id, email and name.

In order to also retrieve the Post records that belong to a User, you can use the include option via the posts relation field:

...
  const usersWithPosts = await prisma.user.findMany({
    include: {
      posts: true,
    },
  })
  console.dir(usersWithPosts, { depth: null })
...

Run the script again to see the results of the nested read query.