Introduction

In this video, the speaker discusses different ways to use a SQL database in a back-end JavaScript runtime like Node or Dino and in frameworks like Next.js and Spell Kit. The speaker compares eight different tools by building a simple application with all 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.

The Journey of Developers

• Many developers start out learning relational databases like SQL light and 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.

• Finally, after years of premature optimization and denial, they realize that SQL was probably the best choice all along.

Pros and Cons of Different Approaches

In this section, the speaker talks about how everything in software development has its trade-offs so he will look at the pros and cons of each approach.

PG - OG Postgres Database Tool

• PG is the OG and 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.

• One drawback is that we don't get any type safety or intellisense on return data.

Postgres JS - A Newer Low-Level Tool

• Postgres JS is faster than PG and uses some Modern JavaScript features like tag template literals.

• It gives you maximum control over your queries but leads to code that's pretty ugly.

Sequelize - An ORM for Node.js

• Sequelize is an ORM for Node.js that supports MySQL, MariaDB, SQLite, and PostgreSQL.

• It has a lot of features like transactions, eager loading, and more.

• One drawback is that it can be slow for complex queries.

TypeORM - An ORM That Supports Multiple Databases

• TypeORM is an ORM that supports multiple databases like MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Oracle, WebSQL.

• It has a lot of features like migrations and schema synchronization.

• One drawback is that it can be slow for complex queries.

Prisma - A Modern Database Access Toolkit

• Prisma is a modern database access toolkit designed to replace traditional ORMs.

• It generates type-safe code based on your data model which gives you intellisense and prevents errors at compile time.

• One drawback is that it's still relatively new and doesn't support all databases yet.

Knex.js - A Query Builder for Node.js

• Knex.js is a query builder for Node.js that supports PostgreSQL, MySQL, MariaDB SQLite3, Oracle DB (including Amazon RDS), Microsoft SQL Server (including Azure).

• It's easy to use and has good documentation but can be verbose.

Objection.js - An ORM Built on Top of Knex.js

• Objection.js is an ORM built on top of Knex.js with a focus on relational databases.

• It has many features like eager loading and relations but can be slower than other ORMs.

MikroORM - TypeScript ORM for Node.js Based on Data Mapper Pattern

• MikroORM is a TypeScript ORM for Node.js based on the data mapper pattern.

• It supports MongoDB as well as SQL databases like PostgreSQL or MySQL/MariaDB

• One drawback is that it's still relatively new compared to other ORMs.

Conclusion

In this section the speaker concludes by saying there are many ways to use SQL databases in a back-end JavaScript runtime and it's important to choose the right tool for your project based on its specific needs.

SQL Query Builders

In this section, the speaker discusses different SQL query builders and how they can make code more concise and reliable.

Connects JS

• Uses PG Library

• Has a CLI to generate migration code

• Schema is created using multiple tables with each column having a name, data type, and constraints defined by stringing various methods together

• Query is built step-by-step using method chaining for safety purposes

Keysole

• Inspired by Connects JS

• Provides better type safety and intellisense

• Represents entire database as a TypeScript interface

• Has special types like generated IDS and column type which allows different data types for select insert and update

• Cogen tool writes code automatically by introspecting existing schema

• Perfect intellisense on joins and complex operations

Sequelize

• Maps database tables into objects that can be used naturally in code

• Defines objects like tweet and user that correspond to tables in the database

• Find all method on Tweet object provides most concise code but looks nothing like raw SQL code

• No type inference out of the box

TypORM

In this section, the speaker introduces TypORM as an alternative to Sequelize.

TypORM

• Creates entities that map to database tables

• Makes it easy to represent relationships between different tables

• Can define paranoid tables with a single configuration option

Using TypeScript with SQL Databases

In this section, the speaker discusses how to use TypeScript with SQL databases and introduces several tools that can be used for this purpose.

Updating TypeScript Config and Importing Reflect Metadata

• To use TypeScript with SQL databases, you need to update a few things in your TypeScript config.

• You also need to import reflect metadata.

• Once you have your entities defined, you can then use the database to set them up as a repository.

• This makes it extremely easy to query a table and get full type safety without any issues.

Introducing Prisma

• Prisma is a tool that uses its own custom language in the schema.prisma file to 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 an awesome developer experience but relies heavily on third-party abstractions.

Drizzle ORM

• Drizzle ORM strikes a good balance of everything we've looked at so far.

• To use it, first Define tables of tweets and users, then Define The Columns as properties on an object with various methods for the data types and constraints.

• Once we have a table defined we can use it to infer a typescript type which allows us to enforce that type anywhere in the application outside of the database client itself.

Conclusion

• There are eight different ways discussed here for using SQL databases in back-end JavaScript projects.

• The best one depends on individual needs.