Introduction à Prisma
Prisma peut être utilisé avec des bases de données relationnelles ou no-SQL ; on ne parlera ici que de bases relationnalles.
Créer un projet
Installer Prisma
Initialiser Prisma
- créé un fichier prisma/schema.prisma qui contient le schéma de la base de données
- créé un fichier .env avec des variables d’environnement
- créé un fichier prisma.config.ts
Adapter l’url de la base de données
Il est situé dans .env:
Créer le schéma de la base de données
Il est entièrement décrit dans un seul fichier qui est situé par défaut dans ./prisma/schema.prisma :
Créer la base de données
À exécuter à nouveau en cas de changement du schéma
Interface d’admin
Mise à jour de la fonction d’interface avec NodeJS
La commande suivante génère la librairie cliente nécessaire pour le dialogue avec NodeJS.
Un répertoire ‘generated’ est créé dans le répertoire projet.
Application Express de test
prisma.js
app.js
Reverse-engineering
Un killer-feature de Prisma : il permet de reconstruire le schéma d’une base existante. Il faut fournir au préalable un fichier prisma/schema.prisma contenant uniquement le type et l’url de la base. Après exécution, le fichier prisma/schema.prisma est complété avec le nouveau schéma.
Modelisation des relations
Référence : https://www.prisma.io/docs/orm/prisma-schema/data-model/relations
Relation one-to-many
prisma/schema.prisma
script1.js
script2.js
Relation one-to-one
prisma/schema.prisma
- User a une relation one-to-one optionnelle avec Profile
- Dans le modèle Profile, le champ userId a l’attribut @unique, qui garantit que chaque Profile appartient à exactement un User
- La directive @relation dans le modèle Profile indique que userId est la clé étrangère utilisée pour la relation avec le modèle User
Ce schéma garantit que chaque User a au plus un Profile, et que chaque Profile est associé à exactement un User.
script.js
Relation many-to-many
Une relation many-to-many peut être modélisée explicitement, en définissant une table intermédiaire pour la relation.
Elle peut aussi être modélisée implicitement, c’est à dire qu’aucune table intermédiaire n’est visible dans le schéma Prisma, même si elle existe dans la base de données, avec des noms de table et d’attributs gérés automatiquement par Prisma. C’est la méthode la plus simple pour des relations directes d’id à id.
Relation implicite
prisma/schema.prisma
script.js
API
Here is the database schema used in the examples:
Find
Find a single element
findUnique query lets you retrieve a single database record:
- By ID
- By a unique attribute
Example result:
Find multiple elements
Example result:
Select specific fields
Use select to return a limited subset of fields instead of all fields. The following example returns the email and name fields only:
Example result:
Include relations and select relation fields
To return specific relation fields, you can:
- Use a nested select
- Use a select within an include
To return all relation fields, use include only - for example, { include: { posts: true } }.
The following query uses a nested select to select each user’s name and the title of each related post:
Example result:
The following query uses select within an include, and returns all user fields and each post’s title field:
Result:
Filter conditions and operators
- equal: value equals n
- not: value does not equal n
- in: value n exists in list
- notIn: value n does not exist in list
- lt: value n is less than x
- lte: value n is less than or equal to x
- gt: value n is greater than x
- gte: value n is greater than or equal to x
- contains: value n exists in list
- search: value n exists in list
- startsWith: value n exists in list
- endsWith: value n exists in list
Combining operators
You can use operators (such as NOT and OR) to filter by a combination of conditions.
The following query returns all users with an email that ends in “prisma.io” or “gmail.com”, but not “hotmail.com”:
Result:
Filter on relations
ExpressX/Prisma supports filtering on related records. For example, in the following schema, a user can have many blog posts:
The one-to-many relation between User and Post allows you to query users based on their posts - for example, the following query returns all users where at least one post (some) has more than 10 views:
You can also query posts based on the properties of the author. For example, the following query returns all posts where the author’s email contains “prisma.io”:
Filter on scalar lists / arrays
Scalar lists (for example, String[]) have a special set of filter conditions.
The following query returns all posts where the tags array contains databases:
Case-insensitive filtering
Case-insensitive filtering is available as a feature for the PostgreSQL and MongoDB providers.
MySQL, MariaDB and Microsoft SQL Server are case-insensitive by default, and do not require a Prisma Client feature to make case-insensitive filtering possible.
To use case-insensitive filtering, add the mode property to a particular filter and specify insensitive:
Pagination
Offset pagination
Offset pagination uses skip and take to skip a certain number of results and select a limited range.
The following query skips the first 3 Post records and returns records 4 - 7:
The following query returns all records where the email field contains Prisma, and sorts the result by the title field.
The query skips the first 200 records and returns records 201 - 220.
Cursor-based pagination
Cursor-based pagination uses cursor and take to return a limited set of results before or after a given cursor. A cursor bookmarks your location in a result set and must be a unique, sequential column - such as an ID or a timestamp.
The following example returns the first 4 Post records that contain the word “Prisma” and saves the ID of the last record as myCursor:
Note: Since this is the first query, there is no cursor to pass in.
The second query returns the first 4 Post records that contain the word “Prisma” after the supplied cursor (in other words - IDs that are larger than 29):
Create
Create a single record
The following query creates a single user with two fields:
Create multiple records
The following createMany query creates multiple users and skips any duplicates (email must be unique):
Update
Update a single record
The following query uses update to find and update a single User record by email:
Result:
Update multiple records
The following query uses updateMany to update all User records that contain prisma.io:
Result:
Update or create records
The following query uses upsert to update a User record with a specific email address, or create that User record if it does not exist:
From version 4.6.0, Prisma carries out upserts with database native SQL commands where possible. Learn more.
Prisma does not have a findOrCreate query. You can use upsert as a workaround. To make upsert behave like a findOrCreate method, provide an empty update parameter to upsert.
A limitation to using upsert as a workaround for findOrCreate is that upsert will only accept unique model fields in the where condition. So it’s not possible to use upsert to emulate findOrCreate if the where condition contains non-unique fields.
Update a number field
Use atomic number operations to update a number field based on its current value - for example, increment or multiply. The following query increments the views and likes fields by 1:
Delete
Delete a single record
The following query uses delete to delete a single User record:
Attempting to delete a user with one or more posts result in an error, as every Post requires an author - see cascading deletes.
Delete multiple records
The following query uses deleteMany to delete all User records where email contains prisma.io:
Attempting to delete a user with one or more posts result in an error, as every Post requires an author - see cascading deletes.
Delete all records
The following query uses deleteMany to delete all User records:
Be aware that this query will fail if the user has any related records (such as posts). In this case, you need to delete the related records first.
Filter conditions and operators
equals
Value equals n.
Return all users where name equals “Eleanor”:
You can also exclude the equals:
not
Value does not equal n.
Return all users where name does not equal “Eleanor”:
in
Value n exists in list.
null values are not returned. For example, if you combine in and NOT to return user whose name is not in the list, users with null value names are not returned.
Get User records where the id can be found in the following list: [22, 91, 14, 2, 5]:
Get User records where the name can be found in the following list: [‘Saqui’, ‘Clementine’, ‘Bob’]:
Get User records where name is not present in the list (You can also use notIn):
Get a User record where at least one Post has at least one specified Category:
notIn
Value n does not exist in list.
Remark: null values are not returned.
Get User records where the id can not be found in the following list: [22, 91, 14, 2, 5]:
lt
Value n is less than x.
Get all Post records where likes is less than 9:
lte
Value n is less than or equal to x.
Get all Post records where likes is less or equal to 9:
gt
Value n is greater than x.
Get all Post records where likes is greater than 9:
gte
Value n is greater than or equal to x.
Get all Post records where likes is greater than or equal to 9:
Get all Post records where date_created is greater than March 19th, 2020:
contains
Value n contains x.
Count all Post records where content contains databases:
Count all Post records where content does not contain databases:
search
Use Full-Text Search to search within a String field.
Full-Text Search is currently in Preview and only available for PostgreSQL and MySQL. To use search, you’ll need to enable the fullTextSearch preview feature.
Find all posts with a title that contains cat or dog:
Find all posts with a title that contains cat and dog:
Find all posts with a title that doesn’t contain cat:
startsWith
Get all Post records where title starts with Pr (such as Prisma):
endsWith
Get all User records where email ends with prisma.io:
Sorting
Use orderBy to sort a list of records or a nested list of records by a particular field or set of fields. For example, the following query returns all User records sorted by role and name, and each user’s posts sorted by title:
Note: You can also sort lists of nested records to retrieve a single record by ID.
Sort by relation
You can also sort by properties of a relation. For example, the following query sorts all posts by the author’s email address:
Sort by relation aggregate value
In 2.19.0 and later, you can sort by the count of related records.
For example, the following query sorts users by the number of related posts:
Note: It is not currently possible to return the count of a relation.
Sort by relevance (PostgreSQL)
When using PostgreSQL you can sort records by relevance to the query using the _relevance keyword. This uses the relevance ranking functions from PostgreSQL’s full text search feature, which are explained further in the PostgreSQL documentation.
Enable order by relevance with the fullTextSearch preview feature:
Ordering by relevance can be used either separately from or together with the search filter: _relevance is used to order the list, while search filters the unordered list.
For example, the following query uses _relevance to order the list of users by relevance to the search term ‘developer’ in their bio, and search to filter the list:
Sort with null records first or last
Note: this feature is not supported for MongoDB.
You can sort the results so that records with null fields appear either first or last.
To use this feature, in the generator block of your schema.prisma file, enable the orderByNulls preview feature:
Note: You can only sort by nulls on optional scalar fields. If you try to sort by nulls on a required or relation field, Prisma throws a P2009 error.
Example: If updatedAt is an optional field, then the following query sorts posts by updatedAt, with null records at the end:
