https://github.com/LaunchAcademy/prisma-boilerplate-immersive
Resources Quickstart (follow along by reading, but dont worry about going through) https://www.prisma.io/docs/getting-started/quickstart CRUD queries https://www.prisma.io/docs/concepts/components/prisma-client/crud Relation queries https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries Prisma CLI commands https://www.prisma.io/docs/reference/api-reference/command-reference#migrate-reset
Run from server folder Existing CLI exists to use with npx. Will likely replace with yarn commands later.
npx prisma init
This command does two things:
creates a new directory called prisma that contains a file called schema.prisma, which contains the Prisma schema with your database connection variable and schema models creates the .env file in the root directory of the project, which is used for defining environment variables (such as your database connection)
npx prisma migrate dev --name <name of migration>
Creates a migration
Flag is to name the migration
Also runs the migration
Order of operations
Update the prisma.schema file to have the models you wish
Run the above command
Columns are required/ not nullable by default
Add ? to type for optional column
image String? @db.VarChar(255)
Associations in prisma.schema file can be renamed/ changed/ added at any time without needing a new migration
`npx prisma migrate reset https://www.prisma.io/docs/reference/api-reference/command-reference#migrate-dev
In this assignment, we will focus on a new library, Prisma, that is a database toolkit. Prisma will be our primary tool for defining and editing the tables in our databases through JavaScript migration files. While its interface is extensive for interacting with SQL databases, we will cover the essentials in this lesson.
- Use SQL to directly create tables and columns
- Create our first migration and table with Prisma
- Add and remove individual columns on a table
- Understand an overview of Prisma config
et get introduction-to-prisma
cd introduction-to-prisma
yarn install
Before we can insert rows into a relational database, we need to define the table structure to store this information. SQL includes a data definition language for creating and updating our schema using statements such as CREATE TABLE and ALTER TABLE.
Let's create a database to store song information. From the terminal, run the following commands to create a database and open a connection to it:
createdb music_development
psql music_development
You should see the following in your console (the version number may vary):
psql (15.1)
Type "help" for help.
music_development=#
Running \d (describe) at the music_development=# prompt should yield the message No relations found., indicating that there are currently no tables (i.e., relations) in the songs database.
In the terminal, define a table to store individual songs along with the album they appear on and the artist:
CREATE TABLE songs (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
album VARCHAR(255) NOT NULL,
artist VARCHAR(255) NOT NULL
);The CREATE TABLE statement will update our database schema so that we have a place to store songs. We can check out our new songs table by running \d songs at the prompt, which should yield
Table "public.songs"
Column | Type | Modifiers
--------+------------------------+----------------------------------------------------
id | integer | not null default nextval('songs_id_seq'::regclass)
name | character varying(255) | not null
album | character varying(255) | not null
artist | character varying(255) | not null
Indexes:
"songs_pkey" PRIMARY KEY, btree (id)
It can be difficult to predict everything we might need for an application when first building it. In the example for our database, we can assume we will also need to store the genre. Our updated statement might look something like this:
CREATE TABLE songs (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
album VARCHAR(255) NOT NULL,
artist VARCHAR(255) NOT NULL,
genre VARCHAR(255) NOT NULL
);The problem is that if we run this new statement after we have already created the table, we'll encounter the following error:
ERROR: relation "songs" already exists
PostgreSQL doesn't allow us to redefine tables in this way. Instead, we need to specify what has changed from one version to the next. In this case, we're adding a column to a table that already exists in the database, meaning we can use the ALTER TABLE statement instead:
ALTER TABLE songs ADD COLUMN genre VARCHAR(255) NOT NULL;Running this command will update the schema accordingly:
music_development=# \d songs
Table "public.songs"
Column | Type | Modifiers
--------+------------------------+----------------------------------------------------
id | integer | not null default nextval('songs_id_seq'::regclass)
name | character varying(255) | not null
album | character varying(255) | not null
artist | character varying(255) | not null
genre | character varying(255) | not null
Indexes:
"songs_pkey" PRIMARY KEY, btree (id)
This process of updating our database via a psql session is tedious. It's also error-prone, and we don't have a way of tracking how we want to update the database over time. For instance, what if you wanted your friend Anjali to run the same update to their songs database? From what we have seen so far with SQL, we could track this in a .sql file, and Prisma uses a similar system to help us with this!
Like we saw with the example above, our database schema will likely change over time. As applications mature and requirements change, we have to modify our database schema to handle new information or find better ways to represent what we already have.
Applications tend to accumulate many incremental changes that move the database from one state to the next. It's crucial to maintain an ordering of these changes so we can re-build the schema from scratch and determine what new changes are required. For example, if we add a column to a table in our development database, we need to record that change somewhere so we can also apply it to our production database. If our team has multiple developers, it is important that they apply the same changes to their development databases so that everyone's databases stay in sync.
Prisma is a library that helps us interface with our database using files. Prisma manages incremental changes by defining migrations, which are sets of instructions to create or update the database from one state to the next. Prisma will also act as our ORM, or Object-Relational Mapping library, by giving us helpful methods to use when we want to query data from tables in the database (instead of writing raw SQL statements).
Whenever we want to make changes to our database with Prisma, we will first add the changes to the schema.prisma file that lives in the server/src/prisma folder. Let's take a look at this file, then see how it will be used to generate our migrations.
The schema.prisma contains the configuration instructions to connect our application with the correct database. There are three main sections that are required in this file. At the top we will see the setup for the first two, generator and datasource:
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "postgresql"
url = "postgres://postgres:postgres@localhost:5432/music_development"
}We specified the JavaScript Prisma Client as the generator provider, which will be used later to help us query data stored in this database. The datasource points to which database this schema should connect to. The datasource provider tells Prisma that we will be using a postgresql database. Lastly, the url is required for setting up a connection to the correct database based on its name, music_development (the rest of the url path is where the database will receive queries from on our machine). Each app will have a slightly different development and test database name based on the topic for the app.
With the schema configured, we can now define our tables for this database.
The schema.prisma file is written in Prisma Schema Language, Prisma's own language, that we will use to create the necessary tables in our database. Using Prisma as our ORM, there is a distinction between the tables in our database and what we will create as their model representation. We have created and worked with database tables, which are the physical locations for data in our database. With Prisma, we will define models within the schema that Prisma will use to build our database tables. Add this Song model to the schema.prisma after the generator and datasource:
model Song {
id Int @id @default(autoincrement())
name String @db.VarChar(255)
album String? @db.VarChar(255)
artist String @db.VarChar(255)
songNumber Int @db.Integer
}Starting with the model keyword, we declare the name of the table we want to create in the database. The role of model within the schema.prisma is to define what the structure of the table will look like in the database, include necessary validations, and setup associations between tables.
The models we define in this file should be familiar to you, as they resemble the object oriented classes we have made to store and manage data in previous assignments. Prisma first uses this model to create a table in the database. When we are ready to add to the database in the form of new records, this model will allow us to use javaScript syntax in order to execute SQL queries like INSERT.
Let's break down the different pieces inside the Song model, where we specify the columns we want the Song table to have in the database.
id Int @id @default(autoincrement())Prisma requires all models to have a unique identifier so that we can differentiate the records of data stored in the table. We set up a column id to be the primary key for the table. It is declared as a data type of Int for how values will be stored in the database for this field. Lastly, this column is auto-incrementing, meaning we will never need to edit or set the value for this column directly (Prisma will take care of this field for us!). Every table will need this id column.
name String @db.VarChar(255)
album String? @db.VarChar(255)
artist String @db.VarChar(255)
songNumber Int @db.IntegerThe first line in the above block tells Prisma we want to create a column called name on our table, that has a String data type for Prisma to use on a model level. @db.VarChar(255) tells the postgres database what type of column it should create for this table - in this case, VARCHAR(255).
By default, Prisma applies NOT NULL to all columns. If we want a column to be optional, we add the ? modifier to the Prisma data type:
album String? @db.VarChar(255)Int and String are going to be the most frequent data types used to designate columns for a table, but other options include:
columnName Float @db.DoublePrecision // numbers requiring a decimal
columnName String @db.text // strings larger than 255 characters
columnName Boolean @db.boolean // true/false
columnName DateTime @default(now()) @db.Timestamp(6) // recording when we last updated a recordFor more column data types we can designate, check out the documentation for Prisma model field types.
Before we continue to Prisma migrations, make sure to reset your database from the first section with the following commands:
dropdb music_development
createdb music_development
How do migrations play a part in building our database? Migrations are files that hold a set of instructions to create or update a database from one state to the next. Migrations usually consist of changing the schema in some way: creating a new table, adding or removing columns, inserting indexes, etc.
In the previous section we took the first step in creating our first migration. We declared a change for the music_development database by modifying the schema.prisma file to add the model Song in order to build the Song table.
With a blank database and the model declared in the schema.prisma, let's add a new migration to create the Song table.
From the server folder in the terminal:
npx prisma migrate dev --name create-songs
We're using the library npx to help us run and execute commands with Prisma. The Prisma script npx prisma migrate dev can take a flag command, --name, to name the migration folder that will be generated. We generally use a brief description of the change we want to make to the database. create-songs is conventional to indicate to us and other developers that the change in this migration is creating a new table for songs. This provided name will be automatically appended to the end of the name for the migration. The migration will also be prepended with a timestamp indicating when we created this migration file, which is important to ensure that our migrations are run in the proper order to build the database consistently every time.
If we do not provide the flag for --name, the terminal will ask you to specify a name before it creates the migration file. If the name will have multiple words, create songs, we need to add a character between the words to bind them together, such as a hyphen, create-songs, or underscore, create_songs.
Let's examine this file at server/src/prisma/migrations/<TIMESTAMP>_create_songs/migration.sql
-- CreateTable
CREATE TABLE "Song" (
"id" SERIAL NOT NULL,
"name" VARCHAR(255) NOT NULL,
"album" VARCHAR(255),
"artist" VARCHAR(255) NOT NULL,
"songNumber" INTEGER NOT NULL,
CONSTRAINT "Song_pkey" PRIMARY KEY ("id")
);We can see that this migration.sql file lives in a new migration folder named by the timestamp when the migration was executed, and has the name we provided at the end of the folder name. Every time we run a migration, a new migration folder will be generated and have a migration.sql file that specifies the database changes for that migration.
By declaring the Song model in the schema.prisma, Prisma was able to create the necessary SQL statements to make those changes from the migrate command. This is a very powerful tool! Using Prisma we can build our database without having to write the raw SQL ourselves.
We can verify the table exists by connecting directly with psql and inspecting the schema:
psql music_development
music_development=# \d "Song"
Note: if you navigate to psql without specifying the database you can connect to a database by running \c music_development
You'll notice that how we specified the database table to be described, \d "Song", is different than the beginning of the article. We are now calling on the singular, capitalized Song, and wrapping it in quotes. When Prisma created the table in our database through the migration it used the model name, Song, as the table name. Including quotations makes the statement case-sensitive, so that postgres will search for a table called Song. Otherwise postgres will default to be case-insensitive and convert the provided table name to lowercase.
Now let's see how we can add the genre column to our table. To make a change to a table in our database we will directly modify the schema.prisma file by adding the column to the declared model:
model Song {
id Int @id @default(autoincrement())
name String @db.VarChar(255)
album String? @db.VarChar(255)
artist String @db.VarChar(255)
songNumber Int @db.Integer
genre String @db.VarChar(255) // this line is new!
}After adding the column to the schema.prisma we need create a migration to record this database change and implement it against the database:
npx prisma migrate dev --name add-genre-to-songs
Note the name we gave this migration: add-genre-to-songs. This name is concise, accurately describes the action we wish to perform, and which table is affected.
When we run this migration it will add the column to the table, and create a new migration file:
Applying migration `20231002185637_add_genre_to_songs`
The following migration(s) have been created and applied from new schema changes:
migrations/
└─ 20231002185637_add_genre_to_songs/
└─ migration.sql
Your database is now in sync with your schema.
✔ Generated Prisma Client (v5.2.0) to ./../node_modules/@prisma/client in 198ms
Every change we want to make to the database schema should exist in a new migration. Migrations represent the incremental changes we want to make to our database and exist as a list of files in the prisma/migrations directory. Let's open the newly generated migration file in our editor to see the raw SQL statements it created for us:
-- AlterTable
ALTER TABLE "Song" ADD COLUMN "genre" VARCHAR(255) NOT NULL;Note: When creating columns, remember that numbers like zip codes should be strings, rather than integers, because the leading zero is meaningful and will otherwise be ignored by PostgreSQL (e.g., if it evaluated 03038 as a number, it would become 3038 in our database). Moreover, some column names are considered reserved keywords by PostgreSQL, such as type, as they serve other purposes.
Requirements for a database table might change overtime in which we need to remove or change a column or table that already exists in the database. As always, we need to create a new migration to track this new change. For example, what if we wanted to remove the column songNumber?
The process is very simple to change a table, and follows the same migration steps as we did previously.
First we will make the change in the schema.prisma file:
model Song {
id Int @id @default(autoincrement())
name String @db.VarChar(255)
album String? @db.VarChar(255)
artist String @db.VarChar(255)
genre String @db.VarChar(255)
}We directly modified the declared Song model to remove the column songNumber entirely. In order for the database to recognize this change we need to execute a migration:
npx prisma migrate dev --name remove-songNumber-from-songs
A new migration folder and file should be created, and the change should be reflected in the Song table of the database.
If your application and database ever get out of sync with one another, you can always drop your database, dropdb music_development, re-create the database, and re-run the prisma migrate command to build the database tables.
As we run migrations, the database will track which migration files have been run thus far. This helps us avoid issues whereby we accidentally try to run a migration more than once. Prisma stores this migration data in an additional table called _prisma_migrations:
psql music_development
music_development=# SELECT id, finished_at, migration_name, started_at FROM _prisma_migrations;
id | finished_at | migration_name | started_at
--------------------------------------+-------------------------------+--------------------------------------+-------------------------------
762a96f1-f616-4ff1-aa8b-edc696daf3a6 | 2023-10-02 14:56:14.861012-04 | 20230725190122_create_songs | 2023-10-02 14:56:14.85269-04
b5535634-e9cd-4770-bc9d-4fc40744531b | 2023-10-02 14:56:14.864041-04 | 20230801180057_add_genre_to_songs | 2023-10-02 14:56:14.861814-04
(2 rows)Every time we run a new migration, a row is added to the _prisma_migrations table that remembers the last migration run (timestamps of when the migration started and finished, and the name we provided the migration). When we run the migrate command again, it will only try to run any migrations that it hasn't seen before.
Thankfully, you can largely ignore the _prisma__migrations table as this will be managed for you by Prisma itself.
The command npx prisma migrate dev (without any flags or arguments) can also be used to run any existing migrations in an application. This is particularly helpful when you are working with an existing app and want to get it up and running on your machine.
We've provided a pre-configured Express app for you to use. Focus on the files in the server folder, as the Prisma configuration we set up will be housed on our backend server.
Within the package.json we have added three key libraries to ensure our app can communicate with our database (please don't edit this file):
// ...
"dependencies": {
"@prisma/client": "^5.0.0",
// ...
"pg": "^8.11.1",
"prisma": "^5.0.0",
// ...
}Prisma is of course the primary technology we will use to manage changes to our database schema. The pg library will help ensure we can connect to our local PostgreSQL database without issue. @prisma/client will help us to query the data stored in our database.
Note: these are dependencies of the server application which houses our Express app. These should already be installed.
Now we have the libraries we need to use Prisma!
Prisma migrations provide a convenient mechanism for managing changes to the database schema. A migration specifies the changes required to transition from one state to the next.
The npx prisma migrate dev command is used to create new migrations and run available migrations. Once a migration has been run it is recorded within the _prisma_migrations table in the database. If a table in the database needs to be changed, we make the change directly in the schema.prisma file and create a new migration to record the change.
To recap:
createdb database_namecreates a new database called "database_name"dropdb database_namedrops the database called "database_name"npx prisma migrate devwill run all existing migrationsnpx prisma migrate dev --name create-tablecreates a new migration named "create-table"
Using an extension of Prisma, Prisma Client, acts as an object-layer on top of our SQL database, abstracting us away from the complexity of SQL queries. We can utilize the features of Prisma Client to perform database query actions without writing any SQL.
- Introduce the configuration necessary for Prisma Client
- Understand the interface Prisma provides for communicating with our database
- See the most commonly used Prisma queries for each of the CRUD methods
et get queries-with-prisma
cd queries-with-prisma
yarn install
cd server
dropdb music_development
createdb music_development
The Prisma toolkit includes Prisma Client as an ORM to help us access data records stored in our databases. Many ORMs, or Object-Relational-Mapping libraries, have been created for various technologies to allow developers to spend less time writing SQL queries, and instead use our knowledge of Object Oriented Programming to abstract said queries into commonly used methods.
For instance, in SQL, in order to retrieve all song records we have in our database we would need to establish a connection to the database, then set up our query correctly, and convert the results of our query into an object usable in JavaScript notation.
With Prisma Client configured in our Node/Express apps, fetching our song records is as simple as:
prisma.song.findMany()Any model that we define within schema.prisma for a table in the database, Prisma creates a corresponding class provided with methods that skip the SQL in favor of simpler query methods that each of our models can use out of the box. In this way, these models become the managers of the data in our database. This is "Object Relational Mapping", or ORM. A song model that we have access to in our JavaScript code which is tied to the Song table in our database. In fact, said song model becomes the key interface we have with the data stored in our database altogether.
Once we are done exploring the configuration for Prisma Client, we will be able to use a collection of methods to make SQL queries for us. We will then use those methods in our API endpoints in order to interact with the data in our database.
The primary file that connects Prisma Client with our database is the server/src/prisma/prisma.js:
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();
export default prisma;Here, a new instance of PrismaClient is created with a connection to our postgres database that is defined in schema.prisma. That's it! We can start interacting with the information stored in our database using this new prisma instance. We just have to make a table:
model Song {
id Int @id @default(autoincrement())
name String @db.VarChar(255)
artist String? @db.VarChar(255)
album String? @db.VarChar(255)
createdAt DateTime @default(now()) @db.Timestamp(6)
updatedAt DateTime @default(now()) @db.Timestamp(6)
}It's good practice to add createdAt and updatedAt columns to all tables in our database. This ensures when we persist information to a row in our database, the record will be automatically assigned date and time values to these columns (when an object is created or updated respectively). Both columns will @default to a value for the current date and time of right now(). Lastly, we specify to Prisma that these columns should be treated as a Timestamp and have a 6 digit precision.
We can now create the migration to execute it against the database to create this table and model. From the server folder, run the following:
npx prisma migrate dev --name create-songs
This concludes the setup that is needed to use Prisma for queries. Now we can create, read, update, and destroy records in our database with fewer lines of code.
The reason we have set up Prisma is so that we can define queries in JavaScript instead of submitting raw SQL queries directly to the database.
Below we will be introducing you to some of the more essential Prisma queries that you can use in your applications. These queries allow you to do many of the same things we could do with SQL, but more simply. We've selected these queries with a combination of simplicity, function, and efficiency in mind, but we highly suggest you review the Prisma Guide and documentation for more context and more queries that you can potentially run!
In order to see our Prisma classes in action, we'll want an interface to experiment with them in a console. This is where a "repl" comes in. The repl library in this case provides us that console with access to our Prisma models. This in turn will allow us to call the methods on our Song model from a command line to interact with our database in real time.
If you are curious about the configuration, you can review this in server/src/console.js:
import repl from "repl";
import { connection } from "./boot.js";
import prisma from "./prisma/prisma.js";
const replServer = repl.start({
prompt: "> ",
});
replServer.context.prisma = prisma;
replServer.on("close", () => {
connection.destroy();
});Thankfully, you will not need to edit this file as it is connected with our Prisma configuration from server/src/prisma/prisma.js.
From the server folder, running yarn run console will open up our REPL. From here, enter in the word prisma:
> prisma
Proxy [
{},
{
get: [Function: get],
has: [Function: has],
ownKeys: [Function: ownKeys],
set: [Function: set],
getOwnPropertyDescriptor: [Function: getOwnPropertyDescriptor],
defineProperty: [Function: defineProperty]
}
]
We see a Proxy object returned which is a special kind of object in JavaScript that Prisma uses to help with connecting and interacting with the database on our behalf.
Let's run the following in the console to test our Prisma model:
await prisma.song.findMany()
[]Our findMany method from Prisma works! Alas, we don't have any song records in the database yet, so an empty array is returned... but it's still pretty cool, and confirms to us that we are successfully connected.
Prisma queries are inherently asynchronous because they must communicate with an external interface - PostgreSQL. As such, each of our Prisma queries should have an await keyword to ensure that our code execution is paused until the result is returned.
The findMany method returns all of the associated Song records, and then instantiates them as Song objects contained in an array. Currently, we have no Songs in our database though, so let's change that by running the following in the console:
const yesterdaySong = await prisma.song.create({data: { name: "Yesterday", artist: "The Beatles",album: "Help!" }})
const letItBeSong = await prisma.song.create({data: { name: "Let It Be", artist: "The Beatles", album: "Let It Be" }})That should add two song records. Let's check out the first:
yesterdaySong
Song {
id: 1
name: 'Yesterday',
artist: 'The Beatles',
album: 'Help!',
createdAt: 2023-11-12T14:14:39.346Z,
updatedAt: 2023-11-12T14:14:39.347Z,
}With this implementation, we use the method create that has a required argument for data: an object whose properties coincide with our songs table's column names and row values respectively. From the data object the key designates the column, and the value represents the information that will be stored in that column in the Song table.
Prisma ensures each of the name, artist, and album properties are available to be called upon:
yesterdaySong.name
'Yesterday'
yesterdaySong.artist
'The Beatles'Let's cover findMany, findUnique, and findFirst queries, and how they can be used with the where option.
To see all records in a table we can use the findMany query to return an array of objects:
await prisma.song.findMany()
[
Song {
id: 1,
name: 'Yesterday',
artist: 'The Beatles',
album: 'Help!',
createdAt: 2023-11-12T14:14:39.346Z,
updatedAt: 2023-11-12T14:14:39.347Z
},
Song {
id: 2,
name: 'Let It Be',
artist: 'The Beatles',
album: 'Let It Be',
createdAt: 2023-11-12T14:17:02.604Z,
updatedAt: 2023-11-12T14:17:02.604Z
}
]We can combine findMany with the where option to return all records whose properties match any supplied properties:
await prisma.song.findMany({ where: { artist: "The Beatles" } })
[
Song {
id: 1,
name: 'Yesterday',
artist: 'The Beatles',
album: 'Help!',
createdAt: 2023-11-12T14:14:39.346Z,
updatedAt: 2023-11-12T14:14:39.347Z
},
Song {
id: 2,
name: 'Let It Be',
artist: 'The Beatles',
album: 'Let It Be',
createdAt: 2023-11-12T14:17:02.604Z,
updatedAt: 2023-11-12T14:17:02.604Z
}
]The fastest way to retrieve an individual record is with the findUnique query. As long as we know the id or a unique attribute of the record we want (say, if we receive the id from the params at a dynamic route in Express), we can retrieve our Song:
await prisma.song.findUnique({ where: { id: 1 } })
Song {
id: 1,
name: 'Yesterday',
artist: 'The Beatles',
album: 'Help!',
createdAt: 2023-11-12T14:14:39.346Z,
updatedAt: 2023-11-12T14:14:39.347Z
}findUnique requires where as an argument to either specify an id value to search by or a column attribute that is unique.
If we don't have an id or unique attribute, but we know which attributes we want to use to search for our Song record, we could use the findFirst query with where:
await prisma.song.findFirst({ where: { name: "Let It Be" } })
Song {
id: 2,
name: 'Let It Be',
artist: 'The Beatles',
album: 'Let It Be',
createdAt: 2023-11-12T14:17:02.604Z,
updatedAt: 2023-11-12T14:17:02.604Z
}When findFirst is used without any arguments it returns the first record stored in the table:
await prisma.song.findFirst()
Song {
id: 1,
name: 'Yesterday',
artist: 'The Beatles',
album: 'Help!',
createdAt: 2023-11-12T14:14:39.346Z,
updatedAt: 2023-11-12T14:14:39.347Z
}We can update a record with Prisma using the update query:
await prisma.song.update({ where: { id: 1 }, data: { artist: "The Beatlemen" } })This method requires both where and data as options. The argument where is used to find the record to update and must supply an attribute that is unique (such as id) to find the single song to update. Then data includes the properties we want to update with their new values.
To delete a single record, the delete method similarly takes where as an argument with a unique attribute, like id:
await prisma.song.delete({ where: { id: 1 } })There isn't much more to deletion, unless we want to delete more than one record. If so, we can use deleteMany to delete all records:
await prisma.song.deleteMany()Like we have done with models in the past to interact with file storage, we can utilize the models from Prisma inside of our Express routes in order to interact with our data stored in the database.
Previously, we have built API endpoints that looked something like this:
// server/src/routes/api/v1/songsRouter.js
...
songsRouter.get("/", (req, res) => {
return res.status(200).json({ songs: Song.findAll })
})
...Now, however, we will update the syntax to use Prisma methods:
// server/src/routes/api/v1/songsRouter.js
import prisma from "../../../prisma/prisma.js"
...
songsRouter.get("/", async (req, res) => {
try {
const songs = await prisma.song.findMany()
return res.status(200).json({ songs: songs })
} catch (error) {
console.log(error)
return res.status(404).json({ errors: error })
}
})
...Notice that while interacting with JSON file storage in the past was synchronous, while reaching out to a database for data is an asynchronous process. As such, we will need to give our route an async function, so that we can add await prior to prisma.song.findMany() like we did in the console. Additionally, because we don't know if Objection will encounter errors when trying to interact with the database, we wrap it all up in a try/catch to handle errors elegantly. If we fail to get our songs for whatever reason, we consider that a server error and respond with a 500 status code.
Finally, we are importing prisma from the server/src/prisma/prisma.js file at the top of the server so that we have access to all tables in the database through the models created by Prisma.
With Prisma, our application is empowered by the connection Prisma makes to the database, and models it creates to query the data stored. Each model defined in the schema.prisma file will build a corresponding table in the database, and Prisma creates a model interface for various queries. This reduces the amount of code and complexity necessary for retrieving, creating, updating, and deleting data in our database.
For retrieving a given record, Prisma provides us the findMany, findUnique, and findFirst queries which are differentiated by the objects they return. Each query can be paired with the where option to help indicate the record we are looking for.
When creating a new record, create() should be used with the data argument containing the object we want to add. Mutating existing records requires the use of update with arguments of where and data to find the record to update and the new information for the record. delete or deleteMany are the queries available to remove a single record or many records from the database.
The methods we introduces to you here are only a few of the numerous methods Prisma provides. In fact, there are other query methods that will be either more efficient or more useful for certain features that you are building in your applications. However, the methods above will get the job done while you are starting out, and will generally require less complexity than other methods. It's important to have an understanding of these foundational queries before working with ones that are more involved. As long as you have methods for each of the CRUD actions, create, read, update, and destroy - then you will be able to manage data in your applications with success.