Skip to content

100-from-the-cli.mdx

Latest commit

 

History

History
472 lines (336 loc) · 11.3 KB

100-from-the-cli.mdx

File metadata and controls

472 lines (336 loc) · 11.3 KB
Raw
title From the CLI
metaTitle From the CLI
metaDescription Start building a Prisma application with a Prisma Postgres database from the CLI
staticLink true
tocDepth 3
toc true
community_section true

This page provides a step-by-step guide for Prisma Postgres after setting it up with prisma init --db:

  1. Set up a TypeScript app with Prisma ORM
  2. Migrate the schema of your database
  3. Query your database from TypeScript

Prerequisites

This guide assumes you set up Prisma Postgres instance with prisma init --db:

npx prisma@latest init --db

Success! Your Prisma Postgres database is ready ✅

We created an initial schema.prisma file and a .env file with your DATABASE_URL environment variable already set.

--- Next steps ---

Go to https://pris.ly/ppg-init for detailed instructions.

1. Define your database schema
Open the schema.prisma file and define your first models. Check the docs if you need inspiration: https://pris.ly/ppg-init 

2. Apply migrations
Run the following command to create and apply a migration:
npx prisma migrate dev --name init

3. Manage your data
View and edit your data locally by running this command: 
npx prisma studio

... or online in Console:
https://console.prisma.io/cliwxim5p005xqh0g3mvqpyak/cm6kw97t801ijzhvfwz4a0my3/cm6kw97ta01ikzhvf965vresv/studio

4. Send queries from your app
To access your database from a JavaScript/TypeScript app, you need to use Prisma ORM. Go here for step-by-step instructions: https://pris.ly/ppg-init

Once this command terminated:

  • You're logged into Prisma Data Platform.
  • A new Prisma Postgres instance was created.
  • The prisma/ folder was created with an empty schema.prisma file.
  • The DATABASE_URL env var was set in a .env file.

1. Organize your project directory

:::note

If you ran the prisma init --db command inside a folder where you want your project to live, you can skip this step and proceed to the next section.

:::

If you ran the command outside your intended project directory (e.g., in your home folder or another location), you need to move the generated prisma folder and the .env file into a dedicated project directory.

Create a new folder (e.g. hello-prisma) where you want your project to live and move the necessary files into it:

mkdir hello-prisma
mv .env ./hello-prisma/
mv prisma ./hello-prisma/

Navigate into your project folder:

cd ./hello-prisma

Now that your project is in the correct location, continue with the setup.

2. Set up your project

2.1. Set up TypeScript

Initialize a TypeScript project and add the Prisma CLI as a development dependency:

npm init --init-type=module -y
npm install typescript tsx @types/node --save-dev

This creates a package.json file with an initial setup for your TypeScript app.

Next, initialize TypeScript with a tsconfig.json file in the project:

npx tsc --init --types node

2.2. Set up Prisma ORM

Install the required dependencies to use Prisma Postgres:

npm install prisma --save-dev
npm install @prisma/extension-accelerate

2.3. Create a TypeScript script

Create an index.ts file in the root directory, this will be used to query your application with Prisma ORM:

touch index.ts

3. Migrate the database schema

Update your prisma/schema.prisma file to include a simple User model:

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
}

After adding the models, migrate your database using Prisma Migrate:

npx prisma migrate dev --name init

4. Send queries with Prisma ORM

Paste the following boilerplate into index.ts:

import { PrismaClient } from './generated/prisma'
import { withAccelerate } from '@prisma/extension-accelerate'

const prisma = new PrismaClient().$extends(withAccelerate())

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

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

This code contains a main function that's invoked at the end of the script. It also instantiates PrismaClient which you'll use to send queries to your database.

4.1. Create a new User record

Let's start with a small query to create a new User record in the database and log the resulting object to the console. Add the following code to your index.ts file:

import { PrismaClient } from './generated/prisma'
import { withAccelerate } from '@prisma/extension-accelerate'

const prisma = new PrismaClient().$extends(withAccelerate())

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

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

Next, execute the script with the following command:

npx tsx index.ts

{ id: 1, email: 'alice@prisma.io', name: 'Alice' }

Great job, you just created your first database record with Prisma Postgres! 🎉

4.2. Retrieve all User records

Prisma ORM 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 ORM query and add the new findMany query instead:

import { PrismaClient } from './generated/prisma'
import { withAccelerate } from '@prisma/extension-accelerate'

const prisma = new PrismaClient().$extends(withAccelerate())

async function main() {
  // add-start
  const users = await prisma.user.findMany()
  console.log(users)
  // add-end
}

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

Execute the script again:

npx tsx index.ts

[{ id: 1, email: 'alice@prisma.io', name: 'Alice' }]

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.

4.3. Explore relation queries

One of the main features of Prisma ORM 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:

import { PrismaClient } from './generated/prisma'
import { withAccelerate } from '@prisma/extension-accelerate'

const prisma = new PrismaClient().$extends(withAccelerate())

async function main() {
  // add-start
  const user = await prisma.user.create({
    data: {
      name: 'Bob',
      email: 'bob@prisma.io',
      posts: {
        create: [
          {
            title: 'Hello World',
            published: true
          },
          {
            title: 'My second post',
            content: 'This is still a draft'
          }
        ],
      },
    },
  })
  console.log(user)
  // add-end
}

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

Run the query by executing the script again:

npx tsx index.ts

{ id: 2, email: 'bob@prisma.io', name: 'Bob' }

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

import { PrismaClient } from './generated/prisma'
import { withAccelerate } from '@prisma/extension-accelerate'

const prisma = new PrismaClient().$extends(withAccelerate())

async function main() {
// add-start
  const usersWithPosts = await prisma.user.findMany({
    include: {
      posts: true,
    },
  })
  console.dir(usersWithPosts, { depth: null })
  // add-end
}

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

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

npx tsx index.ts

[
  { id: 1, email: 'alice@prisma.io', name: 'Alice', posts: [] },
  {
    id: 2,
    email: 'bob@prisma.io',
    name: 'Bob',
    posts: [
      {
        id: 1,
        title: 'Hello World',
        content: null,
        published: true,
        authorId: 2
      },
      {
        id: 2,
        title: 'My second post',
        content: 'This is still a draft',
        published: false,
        authorId: 2
      }
    ]
  }
]

This time, you're seeing two User objects being printed. Both of them have a posts field (which is empty for "Alice" and populated with two Post objects for "Bob") that represents the Post records associated with them.

Next steps

You just got your feet wet with a basic Prisma Postgres setup. If you want to explore more complex queries, such as adding caching functionality, check out the official Quickstart.

View and edit data in Prisma Studio

Prisma ORM comes with a built-in GUI to view and edit the data in your database. You can open it using the following command:

npx prisma studio

With Prisma Postgres, you can also directly use Prisma Studio inside the Console by selecting the Studio tab in your project.

Build a fullstack app with Next.js

Learn how to use Prisma Postgres in a fullstack app:

Explore ready-to-run examples

Check out the prisma-examples repository on GitHub to see how Prisma ORM can be used with your favorite library. The repo contains examples with Express, NestJS, GraphQL as well as fullstack examples with Next.js and Vue.js, and a lot more.

These examples use SQLite by default but you can follow the instructions in the project README to switch to Prisma Postgres in a few simple steps.