DEV Community

Cover image for Use Prisma Instead of SQL
Shubham Patil
Shubham Patil

Posted on

Use Prisma Instead of SQL

Table of Contents

  1. What is an ORM?
  2. Prisma Schemas
  3. Benefits of this schema approach
  4. Relations
  5. Prisma Client
  6. Benefits Compared To SQL
  7. Learning Resources

Whenever I have to do anything regarding databases, my top pick with dealing with that stuff is usually Prisma. If you didn't know, Prisma is an ORM for SQL databases (and recently, MongoDB).

database

What is an ORM?

You might be wondering what an ORM is? Well, let me explain. Usually, with SQL databases, you have to write out queries in the SQL language. That includes statements like DROP TABLE STUDENTS; or SELECT * FROM STUDENTS WHERE emotion="stressed".

frustrated developer

That mess is SQL, and in the 2000s, that was mainly how data was pulled from the server. Developers had to learn an entirely new "language" (not Turing-Complete of course) just to get some data from their database. This added layer of difficulty resulted in the need of specialization in these languages, and just slowed down the developer when they were making their applications.

Since this was obviously really painful, solutions were made, these being ORMs. If you were building an application in Go, for example, you could directly interact with your database using Go, not the SQL query language. This was really convenient as developers could now do complex things without the hassle of learning a new language.

ORM diagram that shows code being translated to SQL tables

Prisma is awesome!

Prisma logo

This brings me to my next point: Prisma. Prisma is one of these ORMs, but it has a ton of intuitive and awesome features.

For example, if you wanted to select all the users that have signed up with Google, all you have to do in Prisma is

prisma.users.findMany({
    where: {
        OAuthMethod: "Google"
    }
}
Enter fullscreen mode Exit fullscreen mode

Prisma Schemas

Where do we define the data? That leads me to the Prisma Schema.

This is an example schema in the context of the users:

model Users {
    id String @default(uuid())
    OAuthMethod: String
    name String
    email String?
}
Enter fullscreen mode Exit fullscreen mode

Let me break down the model. In the first field, we are creating an id for each user. This id defaults to a random uuid that is generated. The id field has a type of String, since uuid() contains non-integer values. Next, the OAuthMethod and name are two other strings, but they are not default and we have to manually provide the values. The last value email is another string, but it is optional in the user model. It is not mandatory to give it a value, and the Prisma Client SDK won't scream at you with errors if you don't provide it.

Benefits of this schema approach

Prisma (or for that matter any ORM), is declarative compared to SQL's imperative model creation. In your schema.prisma file, all you do is declare model and Prisma handles the rest for you. However, in SQL, you have to manually say CREATE TABLE. This declarative approach makes it much easier to work with and understand, and allows the developer to ship their product faster.

Relations

Relations are also pretty straight forward in Prisma. This is how we would do a one-to-many relation, where the User has a bunch of Posts.

model User {
    id String @default(uuid())
    OAuthMethod: String
    name String
    email String?
    user_posts Posts[]
}

model Posts {
    id String @default(uuid())
    title String
    body String
    ownerId String
    owner User @relation(fields:[ownerId], references:[id]) 
}
Enter fullscreen mode Exit fullscreen mode

What we do here is that we define a primary key (id), and a foreign key ownerId. The ownerId links the two tables together, hence why we create a relation for it. The fields parameter for the relation is the foreign key, while the references parameter is the primary key. The owner in the Posts model is the type of User, and the relation makes it link back to the actual owner. This link between the two tables makes it so that the user has many posts, but each post has only one owner.

Prisma Client

The Prisma client is also very intuitive to use. For example, to select a user that has an OAuthMethod of "Google", and list their email and their posts, (using the schema above), we can simply do this:

prisma.users.findMany({
   where:{
       OAuthMethod: "Google"
   },
   include:{
       user_posts: true   
   },
   select: {
       email: true
   }
})
Enter fullscreen mode Exit fullscreen mode

First, with prisma.users, we access the Users data model. Then, we use findMany to, you guessed it, find multiple records that match out criteria.

Everything in this function is a JSON object, and the where field is another object. Inside the where field, we can specify that we want OAuthMethod to be equal to "Google".

Now, with include we can include the posts (as defined in the schema) that the user has by settings the user_posts parameter to true (user_posts is a field in the User model). If you don't have the include with the user_posts, or user_posts is set to false, it won't include the user's posts in the output.

Next, select allows us to only select the user's email out of the user's data. We can also set email to true here.

Benefits Compared To SQL

One of the clear advantages of Prisma is that you're not spending precious developing time to learn a completely new query language, Prisma allows the developer to get their work done quickly and efficiently without going through the extra steps to do the same thing. You could go through enormous efforts to learn SQL, but Prisma and other ORMs will be there to lessen the load, and at that point, you're basically choosing to be inefficient.

Learning Resources

Here are some learning resources:

  • Tutorial by Laith Harb:

  • Quick rundown of Prisma by Fireship.io:

Anyways, I hope you enjoyed my blog! This one might be a bit controversial though 🙃. If you enjoyed/didn't enjoy this little blog, I would appreciate it if you could like and share (it's really simple for you but means a lot to me). Also do let me know your opinion of Prisma/SQL in the comments below!

I'll see you all in March!

Discussion (2)

Collapse
blake_seipler_9a529405ec1 profile image
Blake Seipler

Nice write-up! Prisma is also my top choice of ORM. Very easy to work with and easy to understand what's going on.

Collapse
handsomebwonderful profile image
Mark Dochstader

I Love ORMs and they are super-helpful for building huge parts of an application, but using ORMs without an understanding of the underlying SQL is not something I'd recommend. ORMs ultimately need to convert your queries to the underlying langauage, and for a huge percentage of data stores that is SQL. You also don't need to get too complex before your ORM can start to write very inefficent queries; if you're using it for more than basic CRUD you might already be in this situation.

SQL is probably the most durable and useful general purpose language we have; I would highly recommend that every developer spend some time learning it as there's not much more efficient and versatile than being able to write a query directly against your database using the standard tools provided. Also, while ANSI92 SQL might not be turing complete, pretty much every implementation adds some flavor of stored program making itself complete :)