I tried 8 different Postgres ORMs

many developers tend to walk the same path in life they start out naive and enthusiastic learning relational databases like SQL light and college or at a boot camp then they learned 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 squeal code if you raw dog it you leave your app vulnerable to digitally transmitted diseases like SQL injection that's why same 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 in today's video we'll look at eight different ways to use a SQL database in a back-end JavaScript runtime like node or Dino and in Frameworks like nextjs and spell kit as you know by now everything in software where development has its 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 nextjs 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 first up we've got PG which is the OG and the top G and my do double g 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 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 and if you're like me you'll use chat GPT 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 send tweet 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 but 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 tag 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 now 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 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 connects 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 now the first thing I want to point out is that connects 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 connect 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 but 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 now 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 that's definitely nice but now let's take a look at a new tool called keysole which is inspired by connects 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 column type which allows us to use different data types for select insert and update now 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 connects 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 buff 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 and that brings us to sqlize 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 but let's go ahead and take a look at the query we actually get the most concise code here by using the find all 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 a lot of manual works you 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 like you can Define paranoid tables with a single configuration option but now let's move on to an alternative called typo RM 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-sharp you'll feel right at home with this code and it'd also be vibing with angular and nest.js and 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 then as you can see that makes it extremely easy to query a table and this time we get full type safety without any issues now 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 now it's time to look at a tool that is far different than all the others Prisma it's the only 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 typesafe queries in the application it provides a really awesome developer experience and that's why I chose to use it in my full next js13 course and now 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 and 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 that 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 and 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

Channel: Beyond Fireship

Views: 352,729

Keywords:

Id: 4QN1BzxF8wM

Length: 9min 46sec (586 seconds)

Published: Thu May 25 2023