Querying the database

TypeScript

Prisma Postgres

Write your first query with Prisma Client

Now that you have generated Prisma Client, you can start writing queries to read and write data in your database. For the purpose of this guide, you'll use a plain TypeScript script to explore some basic features of Prisma Client.

Create a new file named queries.ts and add the following code to it:

queries.ts

// 1
import { PrismaClient } from '@prisma/client'
import { withAccelerate } from '@prisma/extension-accelerate'

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

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

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

Here's a quick overview of the different parts of the code snippet:

1. Import the PrismaClient constructor and the withAccelerate extension.
2. Instantiate PrismaClient and add the Accelerate extension.
3. Define an async function named main to send queries to the database.
4. Call the main function.
5. Close the database connections when the script terminates.

Inside the main function, add the following query to read all User records from the database and log the result:

queries.ts

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

Now run the code with this command:

npx tsx queries.ts

This should print an empty array because there are no User records in the database yet:

[]

Write data into the database

The findMany query you used in the previous section only reads data from the database (although it was still empty).

In this section, you'll learn how to write a query to write new records into the Post, User and Profile tables all at once.

Adjust the main function by removing the code from before and adding the following:

queries.ts

async function main() {
  await prisma.user.create({
    data: {
      name: 'Alice',
      email: 'alice@prisma.io',
      posts: {
        create: { title: 'Hello World' },
      },
      profile: {
        create: { bio: 'I like turtles' },
      },
    },
  })

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

This code creates a new User record together with new Post and Profile records using a nested write query.

The records are connected via the relation fields that you defined in your Prisma schema.

Notice that you're also passing the include option to findMany which tells Prisma Client to include the posts and profile relations on the returned User objects.

Run the code with this command:

npx tsx queries.ts

The output should look similar to this:

[
  {
    email: 'alice@prisma.io',
    id: 1,
    name: 'Alice',
    posts: [
      {
        content: null,
        createdAt: 2020-03-21T16:45:01.246Z,
        updatedAt: 2020-03-21T16:45:01.246Z,
        id: 1,
        published: false,
        title: 'Hello World',
        authorId: 1,
      }
    ],
    profile: {
      bio: 'I like turtles',
      id: 1,
      userId: 1,
    }
  }
]

Also note that the allUsers variable is statically typed thanks to Prisma Client's generated types. You can observe the type by hovering over the allUsers variable in your editor. It should be typed as follows:

const allUsers: ({
  posts: {
    id: number;
    createdAt: Date;
    updatedAt: Date;
    title: string;
    content: string | null;
    published: boolean;
    authorId: number;
  }[];
  profile: {
    id: number;
    bio: string | null;
    userId: number;
  } | null;
} & {
  ...;
})[]

Expand for a visual view of the records that have been created

The query added new records to the User, Post, and Profile tables:

User

id	email	name
1	"alice@prisma.io"	"Alice"

Post

id	createdAt	updatedAt	title	content	published	authorId
1	2020-03-21T16:45:01.246Z	2020-03-21T16:45:01.246Z	"Hello World"	null	false	1

Profile

id	bio	userId
1	"I like turtles"	1

The numbers in the authorId column on Post and userId column on Profile both reference the id column of the User table, meaning the id value 1 column therefore refers to the first (and only) User record in the database.

Before moving on to the next section, you'll "publish" the Post record you just created using an update query. Adjust the main function as follows:

queries.ts

async function main() {
  const post = await prisma.post.update({
    where: { id: 1 },
    data: { published: true },
  })
  console.log(post)
}

Now run the code using the same command as before:

npx tsx queries.ts

You will see the following output:

{
  id: 1,
  title: 'Hello World',
  content: null,
  published: true,
  authorId: 1
}

The Post record with an id of 1 now got updated in the database:

Post

id	title	content	published	authorId
1	"Hello World"	null	true	1

Congratulations! You've now learned how to query a Prisma Postgres database with Prisma Client in your application. If you got lost along the way, want to learn about more queries or explore the caching feature of Prisma Accelerate, check out the comprehensive Prisma starter template.