Why SQL Still Matters and What I Built
Many developers tend to walk the same path in life. They start out naive and enthusiastic, learning relational databases like SQL in college or at a boot camp. Then they learn about all these fancy ultra-fast, web-scale, horizontal sharding, schema-less futuristic NoSQL databases that power billion-dollar businesses at scale. Then finally, after years of premature optimization and denial, they realize that SQL was probably the best choice all along. Databases like Postgres and MySQL are proven workhorses that are flexible and scalable enough to meet the needs of the vast majority of apps out there.
But one does not just write raw SQL code. If you raw-dog it, you leave your app vulnerable to digitally transmitted diseases like SQL injection. That's why some people use libraries and ORMs to connect to the database, handle security, model relationships, deal with migrations, and most importantly get your IDE to give you some of that sweet Intellisense so you don't screw things up this time. We'll look at eight different ways to use a SQL database in a back-end JavaScript runtime like Node or Deno and in frameworks like Next.js and SvelteKit. As you know by now, everything in software and web development has trade-offs, so we'll look at the pros and cons of each approach.
To compare these tools I built a simple application with all eight of them that can query a list of tweets owned by a user from the database, then run an insert operation to add a new row to the tweets table. I'm using Postgres hosted by Neon in the cloud as my database, then Next.js with React Server Components and Actions to fetch the data and build the UI. I'm going to focus mostly on the developer experience, but keep in mind there can be runtime performance differences between these tools. It probably doesn't matter for what you're building, but you can find benchmarks out on the internet.
pg: The Low-Level Postgres Workhorse
First up we've got pg, which is the OG and the top G and my dog of Postgres database tools in the JavaScript ecosystem. It's been around for over 12 years and many of the ORMs on this list depend on it. It's light on abstractions by design, giving you the flexibility to build your own abstractions. To use it, the first thing you'll do is instantiate the client, which will automatically detect your Postgres environment variables. From there we can start making queries from a server component.
Before we can do that though, we need to create some tables in the database. One thing pg doesn't do out of the box is handle migrations. A migration is basically two functions that modify the schema of the database. The up function is what you're changing, like maybe adding a new table. Then the down function is the code to revert that change if needed, like dropping that table. Writing out migrations by hand is a lot of work, so what I'm going to do is take this raw SQL code and simply paste it into the Neon console to create a table for users and tweets, then insert a few dummy rows into it.
Now that we have that data, let's go ahead and make a query. We can do that with the query method, then pass it some raw SQL code that will return an object that contains an array of rows, which are the tweets that we want to display in the UI. That's pretty easy, but one drawback that you'll notice here is that we don't get any type safety or Intellisense on the return data. Now it is possible to write our own Tweet interface and then use it as a generic in the query, but that can be somewhat error-prone as the app grows more complex because the interface isn't tied directly to your database schema.
Another potential drawback here is that writing a SQL string is also error prone. You have no Intellisense and it's really easy to write a typo. If you're like me you'll use ChatGPT to write all your queries and the IDE won't catch errors with its hallucinations. But now let's check out the mutation in Next.js. We have a form action that runs the sendTweet function on the server. Inside this function we once again make a query, but what you'll notice is that it's a parameterized query that prevents hackers from exploiting SQL injection vulnerabilities. That's great, but it leads to code that's pretty ugly. Remember pg is a low-level building block that gives you maximum control over your queries.
postgres.js: A Modern Low-Level Alternative
PG is not the only game in town. A newer low-level tool is postgres.js. It's faster and uses some modern JavaScript features like tagged template literals. The setup is basically the same, but instead of a client with a query method we get this tagged SQL template. Then to make a query we simply pass the raw SQL statement to it. This makes the code more ergonomic because it's easier to handle multiple lines. It does have the same issue of no type inference by default, so we'll need to make our own interface and use it as a generic.
If we take a look at the mutation, the difference is that we're not passing an array of parameters but instead just interpolating values directly into the query string, and these parameters are automatically extracted by the database, making SQL injection impossible. In addition, you can make your code a lot more concise by nesting multiple tag templates, which is really cool but also kind of hard to wrap your head around at first.
Knex: The Classic SQL Query Builder
For most people tools like pg and postgres.js are a bit too low level. Let's now move up to the realm of SQL query builders like Knex.js. It's a tool that can handle multiple different types of databases, and in this case it relies on the pg library we looked at earlier. The first thing I want to point out is that Knex also has a CLI that allows us to automatically generate migration code. As you can see here we have an up and a down function that uses Knex schema to create multiple tables. Each column has a name, data type, and constraints that are defined by stringing various methods together.
There's a lot of method chaining going on here with the builder pattern, but overall I'd say this code is very readable. Most importantly, it allows you to manage the schema in a far more reliable way. Now if we take a look at the query, we don't just write a raw SQL string but instead we chain methods together to build out the query step by step. This tends to be a lot safer because it makes it harder to write code that doesn't work. The trade-off is that it abstracts you away from that code, so you may not fully understand how it works. Also when it comes to Intellisense we'll need to pass in our own interface as a generic.
When moving up to the mutation we can call the insert method and pass in a plain JavaScript object, which is a far more natural thing to do for the average JavaScript developer.
Kysely: Strong Types and Intellisense for SQL Builders
Now let's take a look at a newer tool called Kysely, which is inspired by Knex. It's also a SQL query builder but provides better type safety and Intellisense. It does that by having you represent your entire database as a TypeScript interface that for the most part looks like a regular TypeScript interface, but it also has some special types like Generated for the IDs and then also ColumnType which allows us to use different data types for select, insert, and update.
Writing this code could be very time-consuming if you have an existing database, but it also has a cogen tool that can write the code for you automatically by introspecting your existing schema. Once you have the interface defined you can use it to instantiate the client. At first glance the query code looks very similar to Knex; however, the big difference is that we get perfect Intellisense and it also magically works on joins and any other complex operations, and that provides a huge boost to the developer experience. Also in the mutation it makes it nearly impossible to insert bad values into the database. But it's still not really a full-blown object-relational mapper or ORM.
Sequelize: A Mature ORM with High-Level APIs
That brings us to Sequelize, where the goal is to map your database tables into objects that you can use naturally in your code to get started. We define objects like Tweet and User that correspond to the tables in the database, and that makes it very easy to represent relationships between different tables. It also has a CLI to manage migrations that can do all kinds of stuff similar to frameworks like Rails and Laravel.
Let's go ahead and take a look at the query. We actually get the most concise code here by using the findAll method on the Tweet object. That's pretty nice, but it's also a very high-level abstraction that looks nothing like the raw SQL code that it represents. What was most surprising though is that I wasn't getting any kind of type inference, so I went to the documentation and found out that TypeScript won't be very useful out of the box. It looks like it takes a lot of manual work to get things in a good place and that's a deal breaker for me. However this is a long-established project and it has a lot of features that you may not find in other tools. For example, you can define paranoid tables with a single configuration option.
TypeORM: Decorator-Based Entities and Full Type Safety
Now let's move on to an alternative called TypeORM. This tool is similar in the sense that you create entities that map tables to objects, but the code looks a lot different because it relies on TypeScript decorators. If you come from Java or C# you'll feel right at home with this code, and it vibes with Angular and Nest.js. It is very readable, in my opinion.
What's kind of annoying though is that you'll need to update a few things in your TypeScript config to get it working, and you'll have to import reflect-metadata. But once you have your entities defined you can then use the database to set them up as a repository. As you can see that makes it extremely easy to query a table, and this time we get full type safety without any issues. In the mutation you can actually just instantiate a new entity and modify its properties imperatively. You don't have to do things this way, but that's the way they show it in the documentation.
Prisma: Schema Language, Codegen, and a Polished DX
Now it's time to look at a tool that is far different than all the others: Prisma. It's the only tool on this list that doesn't use JavaScript to represent the schema because maybe JavaScript is not the best language to do that. Instead, it has its own custom language in the schema.prisma file that can represent the database structure in a more concise and readable way. Its CLI is really good at dealing with migrations and also has an introspection tool that can define the schema based on your existing database structure.
What's really unique about Prisma though is that it can take this schema and automatically generate a JavaScript client library to make type-safe queries in the application. It provides a really awesome developer experience and that's why I chose to use it in my full Next.js 13 course. In our query we get Intellisense on every table along with a bunch of ORM methods to query them easily. But the drawback is that we're now relying very heavily on third-party abstractions. Prisma has a bunch of Rust engines under the hood just to make all this black magic possible, but in exchange you get a great developer experience with minimal effort.
Drizzle: A Lightweight, Type-Safe ORM Balance
That brings us to the final tool on the list: Drizzle ORM. This is a relatively new tool that strikes a good balance of everything we've looked at so far. To use it, we first define our tables of Tweets and Users and then define the columns as properties on an object with various methods for the data types and constraints. This is a bit more straightforward than the other ORMs we looked at. What's interesting though is that once we have a table defined we can use it to infer a TypeScript type, and that allows us to enforce that type anywhere in the application outside of the database client itself.
Now in the component we can use the client to make queries and we'll get full type safety for anything we do. And with that we have eight different ways to use a SQL database in a back-end JavaScript project. But the big question though is which one is best? There's only one right way to do things, and because I'm a YouTuber that makes me the authority on the subject, and if you don't use what I tell you to your project is definitely going to fail. That being said, the best SQL library and the one you should use in your next project is the one and only
