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 TypeScript script to explore some basic features of Prisma Client.
Create a new file named queries.ts
and add the following code to it:
// 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:
- Import the
PrismaClient
constructor and thewithAccelerate
extension. - Instantiate
PrismaClient
and add the Accelerate extension. - 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 log the result:
async function main() {
const allUsers = await prisma.user.findMany()
console.log(allUsers)
}
Now run the code with this command:
npx ts-node 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:
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 ts-node 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 | 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:
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 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 |
Fantastic, you just wrote new data into your database for the first time using Prisma Client 🚀
Receive real-time updates when data changes in the database
Because Prisma Postgres comes with Prisma Pulse by default, you can easily stream any changes that are happening in your database into your application by simply using your existing PrismaClient
instance. Let's see how it works!
First, install the Pulse Client extension in your project:
npm install @prisma/extension-pulse
Next, create a new file called realtime.ts
and add the following code to it:
// 1
import { PrismaClient } from '@prisma/client';
import { withPulse } from '@prisma/extension-pulse';
// 2
const apiKey: string = process.env.PULSE_API_KEY ?? '';
if (!apiKey || apiKey === "") {
console.log(`Please set the \`PULSE_API_KEY\` environment variable in the \`.env\` file.`);
process.exit(1);
}
// 3
const prisma = new PrismaClient().$extends(
withPulse({ apiKey: apiKey })
);
async function main() {
// 4
const stream = await prisma.user.stream();
process.on('exit', () => {
stream.stop();
});
// 5
console.log(`Waiting for an event on the \`User\` table ... `);
for await (const event of stream) {
console.log('Received an event:', event);
}
}
main()
.then(async () => {
await prisma.$disconnect();
})
.catch(async (e) => {
console.error(e);
await prisma.$disconnect();
process.exit(1);
});
There's a bunch of things happening here, let's take a closer look:
- Import the
PrismaClient
constructor and thewithPulse
extension. - Read the
PULSE_API_KEY
environmen variable (which is configured in the.env
file). - Instantiate
PrismaClient
and add the Pulse extension (with the Pulse API key). - Create a stream to capture any write-evens on the
User
table. - Start an async iterator that waits for new events and will log them to your terminal.
Start the script with the following command:
npx ts-node realtime.ts
You should see the following output:
Waiting for an event on the `User` table ...
In the same terminal, you'll now see new output being logged whenever:
- a new
User
record is created - an existing
User
record is updated - an existing
User
record is deleted
To trigger one of these events, you can use Prisma Studio. Go ahead and open it (in a new terminal tab or window) using this command:
npx prisma studio
If you create a new User
record in the visual editor, you should see output similar to this in previous terminal tab/window:
Received an event: {
action: 'create',
created: { id: @, email: 'bob@prisma.io', name: 'Bob' },
id: '01JAFNSZHQRDTW773BCAA9G7FJ',
modelName: 'User'
}
These change events will also be triggered when you write to the database using Prisma Client. For example, you could update your queries.ts
file with the following update
query to rename the user from "Alice"
to "Alicia"
:
async function main() {
await prisma.user.update({
where: {
id: 1
},
data: {
name: 'Alicia',
email: 'alicia@prisma.io',
}
});
}
Then you can run the script using the command from before (in a new terminal tab or window):
npx ts-node queries.ts
You should now see output similar to this in previous terminal tab/window:
{
action: 'update',
after: { id: 1, email: 'alicia@prisma.io', name: 'Alicia' },
before: null,
id: '0/2A5A248',
modelName: 'User'
}
Congratulations! You've now learned how to query a Prisma Postgres database with Prisma Client and how to receive real-time events 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.