PostgreSQL stands out as a feature-reach solution among other relational databases. Most of the column types available in PostgreSQL allow storing a single value. However, PostgreSQL, unlike most SQL databases, enables us to define columns as arrays. With them, we can store collections of values within a single column, reducing the need to create separate […]
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. We also learn the concept of timezones and how to deal with them when designing our database. How PostgreSQL interprets dates We can learn how our […]
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 comes to performance. A common solution is to divide our data into chunks and present it to the user as infinite scrolling or multiple pages. […]
The bigger our database, the more we need to care about its performance. A common way of improving it is through indexes. Therefore, this article introduces the idea of indexes and implements them through Prisma. You can find the code from this article in this repository. Introduction to indexes In one of the recent articles, […]
When developing our REST APIs, we often focus on implementing the four fundamental operations: creating, reading, updating, and deleting (CRUD). The most basic approach to removing a record from our database is to delete it permanently. In this article, we explore the idea of soft deletes that allow us to keep the removed entities in the database. […]
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 types of joins along with real-life examples. Inner joins A few articles ago, we defined the users and posts tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE users ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, email text NOT NULL UNIQUE, name text NOT NULL, password text NOT NULL, address_id int UNIQUE REFERENCES addresses(id) ); CREATE TABLE posts ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, title text NOT NULL, post_content text NOT NULL, author_id int REFERENCES users(id) NOT NULL ); |
It’s typical to retrieve a particular post along […]
Covering our project with tests can help us ensure that our application works as expected and is reliable. While unit tests play a significant role, they are not enough. Therefore, this article explains the significance of integration tests and implements them in a NestJS project that uses raw SQL queries. The significance of integration tests […]
Writing tests is crucial when aiming to develop a solid and reliable application. In this article, we explain the idea behind unit tests and write them for our application that works with raw SQL queries. The idea behind unit tests The job of a unit test is to make sure that an individual part of […]
Troubleshooting a deployed application can be challenging. We can’t use the debugger and stop an application used by other people. Instead, we need to implement logs that we can inspect if we suspect that our API is not working as expected. In this article, we look at the logger built into NestJS and use it […]
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 constraints to go further and reject the data that does not match our guidelines. By doing that, we can have an additional layer of security […]
