SQL Archives - Page 5 of 8 - Marcin Wanago Blog - JavaScript, both frontend and backend

API with NestJS #90. Using various types of SQL joins

This entry is part 90 of 168 in the API with NestJS

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

JavaScript NestJS SQL

API with NestJS #88. Testing a project with raw SQL using integration tests

This entry is part 88 of 168 in the API with NestJS

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

JavaScript NestJS SQL

API with NestJS #87. Writing unit tests in a project with raw SQL

This entry is part 87 of 168 in the API with NestJS

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

JavaScript NestJS SQL

API with NestJS #86. Logging with the built-in logger when using raw SQL

This entry is part 86 of 168 in the API with NestJS

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

JavaScript NestJS SQL

API with NestJS #85. Defining constraints with raw SQL

This entry is part 85 of 168 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 168 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 WHERE clause.

1 2	SELECT * FROM posts WHERE author_id = 1

In this article, we go through different use cases of more advanced filtering. We achieve it by using the WHERE 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 168 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 #82. Introduction to indexes with raw SQL queries

This entry is part 82 of 168 in the API with NestJS

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 this article, we look into doing that with raw SQL in a project that does not use ORM. Introducing indexes So far, in this series, we’ve created a […]

JavaScript NestJS SQL

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

This entry is part 81 of 168 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 #80. Updating entities with PUT and PATCH using raw SQL queries

This entry is part 80 of 168 in the API with NestJS

A significant thing to realize when developing a REST API is that HTTP methods are a matter of convention. For example, in theory, we could delete entities with the POST method. However, our job is to create an API that is consistent with the REST standard and works predictably. Updating existing rows in the database […]