There’s no way to sugarcoat it: databases are hard.
The traditional workflow for SQL databases in particular is quite complicated: define a schema, create tables, add fields, link tables together with foreign keys, apply your changes to your DB… Good luck if you need to change a field’s datatype, cause trying to apply your migrations without breaking everything is a headache. When it comes to querying data, you’ll need to write raw SQL which can get pretty ugly, especially with JOIN queries.
This was the workflow I used for building Tank Battle, back in October 2021. And it sucked.
It’s all very intimidating for beginners who just want to dip their toes in some backend programming. And it’s a slow process too — I’ve always had a hard time trying to get in the flow state whenever I’m doing SQL development.
But what if I told you — that there was another way?
What if I told you that there’s a world out there where you don’t need to write god-awful raw SQL? Where your schemas are basically plain English? Where your queries are fully type-safe? Where modeling relationships isn’t a huge drag anymore? Where relational databases are, dare I say, actually exciting?
Enter Prisma — the database tool of the future. Wanna head down this rabbit hole with me?
What is Prisma?
Okay, I feel like I should get this out of the way first — the title of this article is a bit misleading. Prisma is a tool that abstracts away SQL from you instead of replacing it — it makes it easier for you, as a backend dev, to build, connect and run queries on relational databases (and MongoDB too!).
Prisma is a pretty extensive open-source project. It’s an NPM package with a TypeScript library, a CLI, and a full-fledged web GUI. But in short, it’s comprised of three main parts:
- Prisma Client: a fully type-safe object-relational mapper library for Node.js.
- Prisma Migrate: a set of tools for applying database migrations.
- Prisma Studio: a visual, web-based database browser and editor.
Well, there’s an extra fourth part that’s common to all the items above: the Prisma Schema. The schema is the single source of truth for your data — it’s where you define your DB connection, how the Prisma client library is generated (more on that later), and how you want to represent your data models. It allows you to define a declarative schema for your DB (or even generate it via introspection!) using the Prisma Schema Language (PSL).
The Prisma client’s type safety is a bit peculiar, as it relies on code generation, just like the client library itself. Still, it’s a huge win for server-side TypeScript programs.
The Prisma docs do a much better job than I ever could of explaining these topics so I’ll just you refer to them - these docs are phenomenal and I highly recommend reading through the first few pages before moving on.
Fireship also made an awesome YouTube video explaining Prisma in 100 seconds.
Why Prisma?
The docs also do a great job of explaining the motivation for Prisma. The TL;DR is that in the world of database tooling, there’s an inherent trade-off to be made between control over the SQL that’s being run against your DB and your overall productivity as a backend developer.
Prisma aims to strike a balance between both but chooses to prioritize productivity over control, abstracting complex queries from you in an attempt to address common SQL anti-patterns and overcome issues faced by other tools in the Node.js ecosystem.
The Should you use Prisma? page is a very interesting read too - it shows a list of compelling use cases for Prisma as an ORM for Node.js + TypeScript apps. But the docs also acknowledge the fact that developers can have different priorities and that Prisma might not be a good fit for all projects — particularly if you need full control over queries, if you don’t want to write backend code at all, or you just need a dead-simple GraphQL API.
One thing that has really stood out to me about Prisma is how fast it feels — just make changes to your schema, apply it with npx prisma db push, test your server-side code, and repeat. No need to worry about migrations or losing your data. And even if you have to reset your DB, you can re-populate it easily by setting up a simple DB seeding command.
How to learn Prisma?
Going back to the point I made at the start of this post — databases are hard, but Prisma won’t magically make them trivial either. Don’t get me wrong, it makes working with DBs a lot easier, but you’ll still need to do some work to get familiar with these tools.
If you’re just getting started, I can’t recommend Web Dev Simplified’s Learn Prisma in 60 minutes video highly enough. It’s an expertly-made tutorial that covers all of the topics in this post in greater detail (and tons more that I’ve probably forgotten about) in just under an hour.
Once again, I’ll refer you to the Prisma docs, specifically the Guides section, which has several detailed articles that cover just about anything, from seeding your database to deploying it to production.
Local development workflow
Also, I think it’s worth pointing out what the typical Prisma workflow looks like:
- Create a schema, either by writing one yourself or by introspecting an existing database.
- Push the schema to your database using Prisma Migrate.
- Generate the client library (usually done automatically during the previous step).
- Run your DB queries using the Prisma Client ORM in your server-side application code.
You’ll be following the workflow above as you’re prototyping your database and doing local development. As you get used to running queries with the ORM, updating the schema, and applying migrations, this workflow should become second nature after a while.
Integrating Prisma into my personal projects
As I’ve covered in a previous post, I’ve been devoting a lot of time over the past year to learning full-stack web dev, and I’ve been really hyped about learning TypeScript and TS-related stuff like the T3 stack. In fact, Prisma is one of the main technologies used in the T3 stack due to its robust type safety.
And as I’ve mentioned at the beginning of this post, I hate the traditional SQL workflow. It’s slow, tedious, error-prone, and just plain hard AF. I knew I needed to look for an alternative, but NoSQL DBs didn’t seem like a suitable replacement either.
LuccaBoard — learning the API
I was looking to improve my DB development workflow and I had heard about Prisma from watching YouTube videos from Fireship, Ben Awad and Theo, so I felt it was worth giving it a shot. This was back in September 2022, and at the time I was building LuccaBoard, a Reddit-like comments section app with a React frontend, a Node.js + Express backend, and a SQLite database.
If possible, I highly recommend incrementally learning and adopting Prisma by integrating it into existing projects, which I did by migrating the app’s backend from vanilla JS and better-sqlite3 to TypeScript and Prisma. I started by installing it from NPM and running npx prisma db pull to introspect my SQLite DB and generate a schema. By introspection an existing DB, I only had to worry about learning the Prisma ORM syntax and could leave the schema part for a later project. This proved to be a really nice way to break down the Prisma learning curve into smaller and more manageable chunks, which made the process a lot easier.
Now, I’ll admit, it took me a while to get familiar with the ORM’s API, but it’s still pretty straightforward for the most part. Methods like .findUnique() and .findMany() replace SQL’s SELECT, .create() and .createMany() replace INSERT, and so on. Relation queries are a bit confusing at first: joining tables through read operations can be done through nested reads. You can also use nested writes for creating multiple related records in a single query, and also link or unlink two distinct models together by connecting/disconnecting existing records
LuccaChat — learning to build schemas
The next full-stack project I tackled with Prisma was LuccaChat. I was starting from scratch this time around, so I had to create my own Prisma schema and models for my PostgreSQL database. This process was fairly straightforward as well, but the hardest part turned out to be representing relations between models in the schema.
Prisma can model the three basic types of database model relations — one-to-one (1-1), one-to-many (1-n) and many-to-many (m-n) — which are represented in Prisma Schema Language as relation fields, as opposed to your regular scalar data fields. The syntax for representing these relations is pretty concise (check out this example), and it does a good job of abstracting the messy underlying foreign keys relationships in the actual database.
It’s also worth noting that many-to-many relations can be either explicit or implicit, depending on whether or not you’d like to represent the mapping table in your schema.
This is an important consideration to make when building and connecting your models. In LuccaChat, for instance, I needed a many-to-many relation between users and chats (after all, users can participate in many chats and chats can have many users) but since I didn’t need a mapping table, an implicit relation was enough. But in LuccaBoard, I needed a many-to-many relation for comment ratings (users can upvote many comments, and comments can be upvoted by many users) so an explicit mapping table is necessary to keep track of who was upvoting or downvoting what and to compute the comment’s score based on the ratings.
But I digress — after the schema was complete and the models are all properly connected, I could focus on more important parts of the chat app, such as building a live messaging protocol over WebSockets with end-to-end type safety.
New challenger approaching?
Just as I was wrapping up this post, Dax Raad posted a YouTube video titled The Prisma killer is finally here, which immediately grabbed my attention.
Enter Drizzle — a bleeding-edge SQL ORM which doubles down on deep TypeScript integrations. Like Prisma, Drizzle is a fully type-safe Node.js ORM (drizzle-orm) and migration tool (drizzle-kit) for relational databases — but that’s about where the similarities end. Drizzle also has its schema, but it doesn’t use a dedicated language — it’s just TypeScript. This allows Drizzle to just use TS inference for its strong typing instead of having an additional code generation step.
That said, the API for the schema and ORM looks a lot more verbose than their Prisma equivalents. Going back to the ORM control vs. productivity trade-off I mentioned earlier, Drizzle appears to go in the opposite direction of Prisma by building an ORM that maps a lot more closely to SQL, giving you more control over your queries. Their readme outright states that the project’s main philosophy is “If you know SQL, you know Drizzle ORM”.
Drizzle is still very early-stage software — but I’m super hyped to see where it’s headed!
Conclusion
We are currently experiencing a full-stack revolution in the web dev world.
We’ve seen huge leaps in the past few years in the simplicity and quality of technologies across the entire web stack: frontend, backend, infra, and databases. The entry barrier to becoming a full-stack dev has never been lower. And not just that — the underlying tech has just gotten better too. And that’s thanks in no small part to awesome tools like Prisma and TS.
Prisma is the database toolkit I’ve always wanted in my JS/TS stack. The ORM is intuitive to use, the migration tools are great, and the schema language makes representing models and relations a breeze. And my favorite part: working with databases isn’t a drag anymore. It’s a fantastic piece of tech and I can totally see myself using it in 2023 and beyond.
It’s far from being perfect though, and like any technology, it’s not without its faults and limitations. But that’s what alternatives like Drizzle are for — and it’s great to see more competition in the DB tooling space.
I hope this post has helped you learn a bit more about this awesome piece of DB tooling, and maybe even convinced you to try it out for yourself. 😉
Special thanks to Alana Caporale and Tracy Black for sharing their feedback on this post before it went live.
Peace ✌