Category: SQL

JavaScript NestJS SQL

API with NestJS #85. Defining constraints with raw SQL

This entry is part 85 of 187 in the API with NestJS

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 […]

JavaScript NestJS SQL

API with NestJS #84. Implementing filtering using subqueries with raw SQL

This entry is part 84 of 187 in the API with NestJS

In this series, we’ve often had to filter the records in our database. We can achieve that with a simple clause.

In this article, we go through different use cases of more advanced filtering. We achieve it by using the keyword with subqueries. EXISTS In some of the previous parts of this series, […]

JavaScript NestJS SQL

API with NestJS #83. Text search with tsvector and raw SQL

This entry is part 83 of 187 in the API with NestJS

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 a simple way using pattern matching. Today we take it a step further and learn about the data types explicitly designed for full-text search. Text Search […]

JavaScript NestJS SQL

API with NestJS #81. Soft deletes with raw SQL queries

This entry is part 81 of 187 in the API with NestJS

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 database. In this article, we implement soft deletes that only mark records as deleted. You can find the code from this article in this repository. The idea behind soft […]

JavaScript NestJS SQL

API with NestJS #79. Implementing searching with pattern matching and raw SQL

This entry is part 79 of 187 in the API with NestJS

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 Elasticsearch. Even though that’s the case, PostgreSQL also has the functionality of matching a given string pattern. In this article, we explore what PostgreSQL offers and use […]