Database & ORM
TypeORM, Prisma, Drizzle, Mongo, raw SQL, migrations
108 articles
Add SSL to Drizzle + NestJS for AWS RDS (Postgres)
Encrypting database traffic isn’t optional—especially if you’re handling user data or building anything that might one day need audits or certifications.…
Distance and radius in PostgreSQL with Drizzle ORM
PostgreSQL provides many options for working with geographical data, especially with the PostGIS extension. In this article, we’ll explore how to calculate the…
Operations with PostGIS Polygons in PostgreSQL and Drizzle
When using PostgreSQL with PostGIS, we can do various operations using polygons. In this article, we learn how to do that both through raw SQL queries and the…
Organizing Drizzle ORM schema with PostgreSQL
As our application grows, it gets increasingly important to create a file structure that’s easy to maintain. Also, if we care about it from the start, it is…
Pattern matching search with Drizzle ORM and PostgreSQL
Searching through text documents is a very common feature in many web applications. In this article, we learn how to implement it using pattern matching using…
Prepared statements in PostgreSQL with Drizzle ORM
When we execute an SQL query, PostgreSQL follows a process that consists of multiple steps. First, it parses the SQL statement, checking for syntax errors.…
Storing coordinates in PostgreSQL with Drizzle ORM
Many applications rely on geographical data to calculate distances and track locations. PostgreSQL offers several ways to store geospatial data, each designed…
Storing files inside of a PostgreSQL database with Drizzle
When working with PostgreSQL, we can use the bytea column to store binary data, such as images, PDFs, or other small files. In this article, we explore how to…
Storing PostGIS Polygons in PostgreSQL with Drizzle ORM
PostgreSQL, together with PostGIS, allows us to store various types of geographical data. Besides working with simple coordinates, we can also store entire…
Response serialization with the Drizzle ORM
When fetching data from the database, we often don’t want to present it to the user in its raw form. To prevent that, we need to serialize the response in…
Database migrations with the Drizzle ORM
Relational databases are known for their strict data structures. Every table requires a defined schema, including columns, indexes, and relationships. Despite…
PUT and PATCH requests with PostgreSQL and Drizzle ORM
When users send an HTTP request to our API, they use a specific method to indicate whether they want to retrieve, send, delete, or update data. While we could…
Multiple PostgreSQL schemas with Drizzle ORM
PostgreSQL uses schemas as namespaces within the database to hold tables and other structures, such as indexes. In this article, we explain how to use them…
Storing money with Drizzle ORM and PostgreSQL
We can’t cut corners on certain aspects of web development, and storing monetary values is one of them. In this article, we explore various data types in…
Database normalization with Drizzle ORM and PostgreSQL
Database normalization is a key part of database design, but it’s often explained in complicated terms. In this article, we’ll break down the normalization…
Recursive relationships with Drizzle ORM and PostgreSQL
Managing relationships between the tables in our database is one of the fundamental aspects of using SQL. When a particular table points back to itself, it…
Polymorphic associations with PostgreSQL and Drizzle ORM
It’s not uncommon for a single table to be related to multiple similar tables. A good example is a comment the user can write under a photo, an article, or an…
Unique IDs with UUIDs using Drizzle ORM and PostgreSQL
All rows in our database need unique identifiers, typically a sequence of numbers. Alternatively, we can use Universally Unique Identifiers (UUID). In this…
Logging with the Drizzle ORM
Debugging is a great way to find issues when running an application locally. Unfortunately, we don’t have this option in a deployed application. Because of…
Time intervals with the Drizzle ORM and PostgreSQL
Sometimes, in our application, we would like to define a specific duration of time. We could represent it as a number of seconds, for example. However, we…
Improving the performance with indexes using Drizzle ORM
As our database grows, focusing more on its performance is important. SQL indexes act as guides within the database, helping to speed up data retrieval. In…
Full-text search with the Drizzle ORM and PostgreSQL
With PostgreSQL’s full-text search feature, we can quickly find documents that contain a particular word or phrase. It can also sort the results to show the…
Identity columns with the Drizzle ORM and PostgreSQL
Most of our SQL tables contain the ID column, which acts as a primary key and uniquely identifies each row. Historically, the most common way of defining them…
Generated columns with the Drizzle ORM and PostgreSQL
In SQL, generated columns automatically calculate their values using data from other fields in the same table. This can help ensure data consistency, improve…
Using views with the Drizzle ORM and PostgreSQL
Our SQL queries can become more complex as the application we develop grows. To deal with that, PostgreSQL implements views that act as virtual tables with…
Date and time with PostgreSQL and the Drizzle ORM
Storing date and time in a database can be challenging, but it’s important to get it right. In this article, we solve this problem using PostgreSQL and the…
Soft deletes with the Drizzle ORM
Deleting entities is a standard feature in most REST APIs. The most straightforward approach is to remove rows from the database permanently. However, we can…
Handling JSON data with PostgreSQL and the Drizzle ORM
PostgreSQL is well-suited for handling structured data and keeping everything organized and consistent. On the other hand, MongoDB stores data in flexible…
Arrays with PostgreSQL and the Drizzle ORM
Thanks to some of its features, PostgreSQL sets itself apart from other SQL databases. Unlike many SQL databases that limit columns to single entries,…
Offset and keyset pagination with the Drizzle ORM
As our database grows, maintaining good performance becomes more important. Returning large amounts of data at once through our API can negatively affect…
Many-to-many relationships with Drizzle ORM and PostgreSQL
Creating relationships across tables is a crucial aspect of working with SQL databases. Previously, this series focused on using the Drizzle ORM to create…
SQL transactions with the Drizzle ORM
Ensuring the integrity of the data is a fundamental responsibility of each developer. Fortunately, SQL databases give us the tools to ensure our database stays…
SQL constraints with the Drizzle ORM
When working with SQL databases, we can configure constraints to ensure our data does not get corrupted. In this article, we explore and implement different…
Implementing many-to-one relationships with Drizzle ORM
Managing relationships between tables is a significant part of dealing with SQL databases. In this article, we continue learning to use Drizzle ORM with NestJS…
One-to-one relationships with the Drizzle ORM
When building a database, the tables we set up often connect to each other. Managing these relationships is one of the crucial parts of working with databases.…
Introduction to the Drizzle ORM with PostgreSQL
Drizzle is a lightweight TypeScript ORM that lets us manage our database schema. Interestingly, it allows us to manage our data through a relational API or an…
The data types to store money with PostgreSQL and Prisma
Managing money is a part of developing web applications that we can’t cut corners on. This article explores various data types built into PostgreSQL and Prisma…
Polymorphic associations with PostgreSQL and Prisma
Often, we might have a situation where a single entity, such as a comment, needs to be associated with more than one type of table. For example, the user might…
Optimizing queries with views using PostgreSQL and Kysely
Some of our SQL queries can become quite complicated. Fortunately, we can create views that act as aliases for the select queries. They have a form of virtual…
Getting distinct records with Prisma and PostgreSQL
PostgreSQL allows us to filter a query’s results and ensure we don’t get duplicate rows. This can be helpful when your table has many rows where the data in…
Using multiple PostgreSQL schemas with Prisma
In PostgreSQL, schemas act as namespaces within the database and are containers for objects such as tables and indexes. In this article, we explain how they…
Using UUID as primary keys with Prisma and PostgreSQL
Each record in our database should have a unique identifier. Typically, we use a numerical sequence to generate them. However, we can use an alternative…
Filtering records with Prisma
Filtering records is one of the essential skills to have when working with SQL databases. In this article, we’ll implement various examples using NestJS and…
Recursive relationships with Prisma and PostgreSQL
When we work with SQL databases, we usually create tables that relate to each other in some way. Managing those relationships is one of the most fundamental…
Raw SQL queries with Prisma and PostgreSQL range types
While Prisma gradually adds various features, PostgreSQL still has a lot of functionalities that Prisma does not support yet. One of them is range types. In…
Referential actions and foreign keys in PostgreSQL with Prisma
A foreign key is a column that connects two tables. A constraint keeps this connection in check, and PostgreSQL ensures the foreign keys point to the correct…
Aggregating statistics with PostgreSQL and Prisma
We can learn much about how users use our app by looking at our database. With this information, we can improve the experience of the users of our application.…
Introducing database normalization with PostgreSQL and Prisma
Database normalization is a common topic in database design discussions. However, it’s usually explained using complicated terms, making it hard to understand.…
Handling date and time in PostgreSQL with Kysely
Saving date and time in our database can be tricky, but it’s crucial to do it correctly. In this article, we address this problem using PostgreSQL and Kysely.…
Implementing soft deletes with SQL and Kysely
When working on our REST APIs, we usually focus on implementing the four fundamental operations: creating, reading, updating, and deleting (CRUD). Deleting…
Managing JSON data with PostgreSQL and Kysely
PostgreSQL is great for structured data and is known for its reliability in maintaining data organization and consistency. On the other hand, MongoDB stores…
Arrays with PostgreSQL and Kysely
PostgreSQL outshines various other SQL databases with its feature set. Unlike most SQL databases, PostgreSQL offers extensive support for array columns. Using…
Improving the database performance with indexes and Kysely
SQL indexes act like guides in our database and help us retrieve the data faster. The bigger our database is, the more emphasis we need to put on its…
Offset and keyset pagination with Kysely
So far, when working with Kysely, we fetched all rows from our tables. However, this might not be the best solution when it comes to performance. A common…
Handling SQL constraints with Kysely
When using SQL databases, we can set constraints to ensure our data remains accurate and reliable during insertions, updates, or deletions. In this article,…
SQL transactions with Kysely
The integrity of our data should be one of the primary concerns of web developers. Thankfully, SQL databases equip us with tools that we can use to ensure the…
Many-to-many relationships with Kysely and PostgreSQL
Implementing relationships across tables is a crucial aspect of working with SQL databases. So far, this series covers using Kysely to design simple…
Many-to-one relationships with PostgreSQL and Kysely
Designing relationships is one of the crucial aspects of working with SQL databases. In this article, we continue using Kysely with NestJS and implement…
One-to-one relationships with the Kysely query builder
When we design the architecture of our database, we usually end up with tables that relate to each other in some way. Managing such relationships is one of the…
Type-safe SQL queries with Kysely and PostgreSQL
Object-Relational Mapping (ORM) libraries such as Prisma and TypeORM can help us produce code faster by avoiding writing SQL queries. They have a smaller…
Database migrations with Prisma
One of the characteristics of relational databases is a strict data structure. We need to specify the shape of every table with its fields, indexes, and…
Logging with Prisma
Using a debugger with an application running locally on our machine is a great way to troubleshoot. Unfortunately, we can’t do that with a deployed app. To be…
Serializing the response with Prisma
When fetching data from the database, we do not always want to present it to the user in the original form. When working with NestJS, the most popular way of…
Constraints with PostgreSQL and Prisma
One of the most important aspects of working with a database is ensuring the stored information is correct. One of the fundamental ways of doing that is by…
Managing JSON data with PostgreSQL and Prisma
Relational databases such as PostgreSQL are great for storing structured data. This approach has many advantages, but it can lack flexibility. On the other…
Arrays with PostgreSQL and Prisma
PostgreSQL stands out as a feature-reach solution among other relational databases. Most of the column types available in PostgreSQL allow storing a single…
Date and time with Prisma and PostgreSQL
Storing date and time in our database might be tricky, but it is essential to get it right. In this article, we tackle this issue using PostgreSQL and Prisma.…
Offset and keyset pagination with Prisma
The bigger our database, the more we need to care about the performance. Returning too much data at once through our API might not be the best approach when it…
Implementing soft deletes with Prisma and middleware
When developing our REST APIs, we often focus on implementing the four fundamental operations: creating, reading, updating, and deleting (CRUD). The most basic…
Writing transactions with Prisma
As web developers, one of our primary concerns is keeping the integrity of our data. Fortunately, SQL databases come equipped with tools that allow us to…
Using various types of SQL joins
It’s a very common case to need to combine multiple related tables. In SQL, we can do that using a join statement. Therefore, this article explains various…
Defining constraints with raw SQL
Having significant control over the data we store in our database is crucial. One of the ways to do that is to choose suitable column types. We can also use…
Implementing filtering using subqueries with raw SQL
In this series, we’ve often had to filter the records in our database. We can achieve that with a simple WHERE clause.SELECT * FROM posts WHERE author_id = 1In…
Text search with tsvector and raw SQL
It is very common to implement a feature of searching through the contents of the database. In one of the previous articles, we learned how to implement it in…
Introduction to indexes with raw SQL queries
As our database grows, we need to put more and more emphasis on performance. A popular approach to dealing with this problem is by implementing indexes. In…
Soft deletes with raw SQL queries
Removing entities is a very common feature in a lot of web applications. The most straightforward way of achieving it is permanently deleting rows from the…
Implementing searching with pattern matching and raw SQL
The possibility of searching through the contents of the database is a very common feature. There are great solutions built with that use case in mind, such as…
Generating statistics using aggregate functions in raw SQL
So far, we’ve been mostly writing SQL queries that either store or retrieve the data from the database. Besides that, we can rely on PostgreSQL to process the…
Offset and keyset pagination with raw SQL queries
So far, we have returned the full content of our tables. However, as our database grows, this might prove not to be the best approach in terms of performance.…
Working with transactions using raw SQL queries
One of the challenges when working with databases is keeping the integrity of the data. In this article, we learn how to deal with it using transactions. A…
Many-to-many relationships using raw SQL queries
Designing relationships between tables is one of the crucial parts of working with databases. In this article, we look into a more complex relationship called…
Designing many-to-one relationships using raw SQL queries
Learning how to design and implement relationships between tables is a crucial skill for a backend developer. In this article, we continue working with raw SQL…
One-to-one relationships with raw SQL queries
When designing a database, the tables we define often relate to each other. Managing those relationships is one of the essential parts of working with…
Working with PostgreSQL using raw SQL queries
Object-Relational Mapping (ORM) libraries can often help us write our code faster. The ORM allows us not to write raw SQL. Instead, we can manipulate the data…
Database migrations with TypeORM
When working with relational databases, we define the structure of the data rather strictly. For example, we need to specify the format of every table along…
Migrating to TypeORM 0.3
For a long time, TypeORM seemed to have a reputation of being somewhat stagnant. Lately, they stepped up their game, though, and started releasing many new…
Improving PostgreSQL performance with indexes using MikroORM
The complexity of our database queries grows together with our application. Due to that, the time necessary to complete the queries. A common way to address…
Implementing soft deletes using MikroORM and filters
In this article, we look into the filters feature and implement soft deletes. With them, we can mark an entity as deleted without removing it from the database…
Transactions with PostgreSQL and MikroORM
One of the most important things to care about as a web developer is the integrity of the data. In this article, we learn what a transaction is and how it can…
Relationships with PostgreSQL and MikroORM
A significant advantage of SQL databases is handling relationships between various tables. Since, in web applications, entities often relate to each other,…
Introduction to MikroORM with PostgreSQL
So far, in this series, we’ve used a few different solutions for managing databases. To work with MongoDB, we’ve used Mongoose. To manage a PostgreSQL…
Storing files inside a PostgreSQL database
In the previous parts of this series, we’ve learned how to upload files to Amazon S3 and store their metadata in our PostgreSQL database. While this is a…
Implementing soft deletes with PostgreSQL and TypeORM
In this series, we’ve implemented deleting functionalities for various entities. So far, it has always meant removing records permanently from our database.…
Definining indexes with MongoDB and Mongoose
The bigger our database is, the more demanding our queries become in terms of computing power. A common way of tackling this problem is by creating indexes. In…
Implementing pagination with MongoDB and Mongoose
When our application grows, so does the database. At some point, we might return a lot of data from our endpoints. It might prove to be too much for our…
Managing transactions with MongoDB and Mongoose
While working with databases, keeping the integrity of the data is crucial. For example, imagine transferring money from one bank account to another. To do…
Virtual properties with MongoDB and Mongoose
In this series, we’ve used Mongoose to define properties in our schemas and work with models for documents. We’ve also defined various relations between…
Implementing relationships with MongoDB
An essential thing about MongoDB is that it is non-relational. Therefore, it might not be the best fit if relationships are a big part of our database design.…
Introduction to MongoDB
So far, in this series, we’ve focused on working with SQL and the Postgres database. While PostgreSQL is an excellent choice, it is worth checking out the…
Managing PostgreSQL relationships with Prisma
As applications grow, the number of different entities also increases. A crucial part of learning how to design a database is creating relationships between…
Introduction to Prisma with PostgreSQL
So far, in this series, we’ve been using TypeORM to manage our data and connect to our Postgres database. In this article, we look into Prisma, which is a…
Storing JSON with PostgreSQL and TypeORM
So far, in this series, we’ve used PostgreSQL to store data structured in columns. This approach has many benefits, but sometimes, we want some more…
Offset and keyset pagination with PostgreSQL and TypeORM
As our database grows, so do the results of our queries. Returning a lot of data in our API might not be the best approach performance-wise. Dividing our…
Using the array data type with PostgreSQL and TypeORM
Storing arrays is not an obvious thing in the world of SQL databases. Solutions such as MySQL, MariaDB, or Microsoft SQL Server don’t have a straightforward…
Defining transactions with PostgreSQL and TypeORM
One of the important concepts to understand when dealing with databases is a transaction. It is a unit of work that we treat as a whole. It either happens…
Introduction to Elasticsearch
We can find some searching functionalities in a lot of web applications. While we might be fine when iterating through a small data set, the performance for…
Creating relationships with Postgres and TypeORM
When we build an application, we create many entities. They often somehow relate to each other, and defining such relationships is an essential part of…
Setting up a PostgreSQL database with TypeORM
The next important thing when learning how to create an API is how to store the data. In this article, we look into how to do so with PostgreSQL and NestJS. To…