This example shows how to use Prisma Client in a simple TypeScript script to read and write data in a SQLite database. You can find the database file with some dummy data at ./prisma/dev.db
.
Download this example:
npx try-prisma@latest --template typescript/script
Install npm dependencies:
cd script
npm install
Clone this repository:
git clone [email protected]:prisma/prisma-examples.git --depth=1
Install npm dependencies:
cd prisma-examples/typescript/script
npm install
Run the following command to create your SQLite database file. This also creates the User
and Post
tables that are defined in prisma/schema.prisma
:
npx prisma migrate dev --name init
Execute the script with this command:
npm run dev
Evolving the application typically requires two steps:
For the following example scenario, assume you want to add a "profile" feature to the app where users can create a profile and write a short bio about themselves.
The first step is to add a new table, e.g. called Profile
, to the database. You can do this by adding a new model to your Prisma schema file file and then running a migration afterwards:
// schema.prisma
model Post {
id Int @default(autoincrement()) @id
title String
content String?
published Boolean @default(false)
author User? @relation(fields: [authorId], references: [id])
authorId Int
}
model User {
id Int @default(autoincrement()) @id
name String?
email String @unique
posts Post[]
+ profile Profile?
}
+model Profile {
+ id Int @default(autoincrement()) @id
+ bio String?
+ userId Int @unique
+ user User @relation(fields: [userId], references: [id])
+}
Once you've updated your data model, you can execute the changes against your database with the following command:
npx prisma migrate dev
You can now use your PrismaClient
instance to perform operations against the new Profile
table. Here are some examples:
const profile = await prisma.profile.create({
data: {
bio: "Hello World",
user: {
connect: { email: "[email protected]" },
},
},
});
const user = await prisma.user.create({
data: {
email: "[email protected]",
name: "John",
profile: {
create: {
bio: "Hello World",
},
},
},
});
const userWithUpdatedProfile = await prisma.user.update({
where: { email: "[email protected]" },
data: {
profile: {
update: {
bio: "Hello Friends",
},
},
},
});
If you want to try this example with another database than SQLite, you can adjust the the database connection in prisma/schema.prisma
by reconfiguring the datasource
block.
Learn more about the different connection configurations in the docs.
For PostgreSQL, the connection URL has the following structure:
datasource db {
provider = "postgresql"
url = "postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA"
}
Here is an example connection string with a local PostgreSQL database:
datasource db {
provider = "postgresql"
url = "postgresql://janedoe:mypassword@localhost:5432/notesapi?schema=public"
}
For MySQL, the connection URL has the following structure:
datasource db {
provider = "mysql"
url = "mysql://USER:PASSWORD@HOST:PORT/DATABASE"
}
Here is an example connection string with a local MySQL database:
datasource db {
provider = "mysql"
url = "mysql://janedoe:mypassword@localhost:3306/notesapi"
}
Here is an example connection string with a local Microsoft SQL Server database:
datasource db {
provider = "sqlserver"
url = "sqlserver://localhost:1433;initial catalog=sample;user=sa;password=mypassword;"
}
Here is an example connection string with a local MongoDB database:
datasource db {
provider = "mongodb"
url = "mongodb://USERNAME:PASSWORD@HOST/DATABASE?authSource=admin&retryWrites=true&w=majority"
}
#product-wishlist
channel on the Prisma Slack