How to Write Better SQL; TLDR- Don't write SQL, use Prisma.

by Tyler Farris

January 10th, 2023

A Brief History of SQL

In 1970 an article was published by computer scientist Edgar Frank (Ted) Codd, titled ‘A Relational Model of Data for Large Shared Data Banks’- this was the beginnings of relational databases and the idea of modeling data with the mathematical notion of relations (reference IDs) - This is how we’re able to create new relations without modifying existing ones. It wasn’t until a few years later in 1973, under the project name ‘System R’, funded by IBM, that two computer scientists, Don Chamberlin and Ray Boyce, were charged with exploring Codd’s ideas proposed in his article, and eventually creating a query language called SQUARE, which then became SEQUEL, which THEN, due to trademark issues, became what we know today as SQL.

Why We Need Prisma

It’s been almost 50 years since the creation of the query language SQL, since then not much has changed, SQL is still complex, taking on average 20% longer for developers to write, it still requires hand-written coordination between database and client, leaving systems vulnerable to injection attacks. SQL’s query results aren't type safe, developers are able to type the results themselves but that's time consuming. And of course, the barrier to entry for new developers is quite high.

Here, let’s look at a basic query.

Simple SQL example.

Simple enough, but suppose you have two database tables—a Posts table and a Ratings table—and you want to find the average ratings of all of the posts from a user. This is still possible in plain SQL, but it becomes more complicated, and it will slow you down in terms of speed and productivity.

Complex SQL example.

But what if we could do all this in JavaScript, and not worry about breaking out of our dev-flow.

Enter Prisma

prisma sql comparison example.

The above code is much more readable to front-end developers, and it can all be done in the same file, increasing productivity and improving the developer's experience, which is a win-win for any project.

Prisma is part Object Relational Mapper (ORM) part query builder, it converts data between a database and object-oriented entities using object oriented programing (OOP) language, resulting in a more streamlined developer experience. With ORMs and query builders, you don’t need to worry about writing queries, just about understanding your data and what you want to grab out of it.

Prisma Is Easy

Initializing a database connection is easy with Prisma. Currently Prisma can be used with PostgreSQL, MySQL, MongoDB, SQL Server, SQLite, and CockroachDB. To initialize a database it's as easy as supplying Prisma with the ‘provider’ (the database) , the database url, and prisma schema. The prisma schema is how a user initializes database tables and relations, check out the example below for a better understanding. If you’ve been exposed to Typescript or GraphQL the prisma model (schema definitions) should look familiar to you.

prisma schema example.

Notice the typings next to each field, with Prisma, the field’s type determines its structure, and fits into two categories:

  • Scalar types, which map to columns or document fields in the database, think Int or String
  • Model Types, this is how you set table relations, so the field type of post would be ‘Post’ or the field type for comments would be ’Comment[]’ (a list of comments).

Prisma provides an API for querying data rather than directly touching the data with plain, typo-prone SQL queries. Let's look at one of the most common database calls, retrieving a user by their ID.

prisma method.

That’s it! Super simple, straight forward, and human readable. You can modify the where clause to accept any field that you have in your table, ie. first name, last name, email address, phone number and so on. All this should look familiar to any developer doing object oriented programming. findUnique is just one of many model queries the Prisma Client API provides to perform CRUD operations. Checkout the Prisma docs on CRUD for a more in depth understanding and method list. Prisma also provides autocompletion whenever possible, making it easy for developers and ensuring a pleasant and expeditious developer experience.

Developers shouldn't have to waste time on complex SQL queries or manipulating query results to suit their needs. Instead, they should just be concerned with the data they need. Developers need the ability to quickly verify that their application is reading and writing data, enter Prisma Studio, a graphical user interface (GUI) that allows developers to explore and manipulate data as well as drill down into database tables quickly. It's just one more reason to abandon plain SQL queries in favor of this fantastic ORM.

A Plea For a Better Developer experience

Developers should enjoy their work, they should have tools that make their jobs easier, not harder, they should have a development cycle that doesn't slow them down and decrease productivity, and they absolutely should be provided with clear documentation with examples. With so many dev-tools available to us, there’s little excuse to be using a tool, SQL, that’s almost 50 years old. Prisma is the perfect level of abstraction that balances control with productivity.

Prisma makes working with data enjoyable; the small feedback loop provided by features such as Prisma studio and a long list of data-gathering methods truly empowers developers and increases productivity. Prisma comes with extensive documentation and references to help you get up and running quickly. Prisma's auto-complete feature makes writing calls to Prisma Client a breeze.

So, my question is, why aren't you using Prisma?

Like this post?

Try another post: 5 Essential VSCode Extensions for React Developers

Go Back

Kick your software into gear

hello@kickstand.work