𝗠𝗮𝘀𝘁𝗲𝗿𝗶𝗻𝗴 𝗣𝗿𝗶𝘀𝗺𝗮 𝗮𝗻𝗱 𝗦𝗤𝗟 𝗥𝗲𝗹𝗮𝘁𝗶𝗼𝗻𝘀𝗵𝗶𝗽𝘀

Prisma makes database work simple if you understand how it handles connections. Here is a guide to schemas, relations, and queries.

𝗧𝗵𝗲 𝗦𝗰𝗵𝗲𝗺𝗮 𝗮𝗻𝗱 𝗥𝗲𝗹𝗮𝘁𝗶𝗼𝗻𝘀𝗵𝗶𝗽𝘀

Relationships define how your data connects.

  • One-to-Many: One user has many posts. The Post model holds the foreign key (authorId). The User model just holds a reference.
  • Many-to-Many: One user belongs to many projects, and one project has many users. You need a junction table (ProjectMember) to sit in between. This table holds foreign keys for both sides.
  • Self-Relations: A user follows another user. You use named relations like "Follower" and "Following" so Prisma knows which side is which.

𝗞𝗲𝘆 𝗤𝘂𝗲𝗿𝘆 𝗠𝗲𝘁𝗵𝗼𝗱𝘀

  • findUnique: Use this for records with an @id or @unique field. It returns null if nothing is found.
  • findMany: Use this to get a list. You can filter, sort, and paginate.
  • findFirst: Use this when you need one record but the filter is not a unique field.

𝗙𝗶𝗹𝘁𝗲𝗿𝗶𝗻𝗴 𝘄𝗶𝘁𝗵 𝗪𝗵𝗲𝗿𝗲

The "where" clause maps directly to SQL.

  • Comparison: Use gt (greater than), lt (less than), or gte (greater than or equal).
  • Text Search: Use "contains" with "mode: insensitive" for case-insensitive searches.
  • Logical Operators: Use OR, AND, or NOT to combine conditions.
  • Relation Filters: Use "some", "every", or "none" to filter based on connected data. "some" finds records where at least one relation matches.

𝗗𝗮𝘁𝗮 𝗖𝗼𝗻𝘁𝗿𝗼𝗹: 𝗦𝗲𝗹𝗲𝗰𝘁 𝘃𝘀 𝗜𝗻𝗰𝗹𝘂𝗱𝗲

  • select: Choose exactly which fields to return. This keeps your data light and secure.
  • include: Join related data into your result. This brings in nested objects.
  • Note: You cannot use select and include at the same level. You must pick one. However, you can use select inside an include to pick specific fields from a joined relation.

𝗢𝗽𝗲𝗿𝗮𝘁𝗶𝗼𝗻𝘀 𝗮𝗻𝗱 𝗦𝗮𝗳𝗲𝘁𝘆

  • createMany: Create many rows at once. It is fast but does not support nested writes.
  • upsert: Updates a record if it exists or creates it if it does not. This is great for shopping carts.
  • update: Use atomic operations like increment or decrement to change numbers without errors.
  • $transaction: Group multiple writes together. If one part fails, the whole group rolls back. This prevents partial data errors.

𝗣𝗮𝗴𝗶𝗻𝗮𝘁𝗶𝗼𝗻

Combine "take" and "skip" to build pages.

  • take: How many records to show.
  • skip: How many records to bypass.

Source: https://dev.to/chinwuba_jeffrey/understanding-prisma-and-sql-relationships-schema-and-querying-l7k