Querying the database
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 Node.js script to explore some basic features of Prisma Client.
Create a new file named index.ts
and add the following code to 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)
})
Here's a quick overview of the different parts of the code snippet:
- Import the
PrismaClient
constructor from the@prisma/client
node module - Instantiate
PrismaClient
- Define an
async
function namedmain
to send queries to the database - Call the
main
function - 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 print the result:
async function main() {
// ... you will write your Prisma Client queries here
const allUsers = await prisma.user.findMany()
console.log(allUsers)
}
Now run the code with this command:
npx ts-node index.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
and User
tables.
Adjust the main
function to send a create
query to the database:
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 User
record is connected to the two other ones via the Post.author
↔ User.posts
and Profile.user
↔ User.profile
relation fields respectively.
Notice that you're 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 ts-node index.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 allUsers
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: (User & {
posts: Post[]
})[]
export type Post = {
id: number
title: string
content: string | null
published: boolean
authorId: number | null
}
The query added new records to the User
and the Post
tables:
User
id | 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 |
Note: The numbers in the
authorId
column onPost
anduserId
column onProfile
both reference theid
column of theUser
table, meaning theid
value1
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:
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 ts-node index.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 |
Fantastic, you just wrote new data into your database for the first time using Prisma Client 🚀