- 1. API with NestJS #1. Controllers, routing and the module structure
- 2. API with NestJS #2. Setting up a PostgreSQL database with TypeORM
- 3. API with NestJS #3. Authenticating users with bcrypt, Passport, JWT, and cookies
- 4. API with NestJS #4. Error handling and data validation
- 5. API with NestJS #5. Serializing the response with interceptors
- 6. API with NestJS #6. Looking into dependency injection and modules
- 7. API with NestJS #7. Creating relationships with Postgres and TypeORM
- 8. API with NestJS #8. Writing unit tests
- 9. API with NestJS #9. Testing services and controllers with integration tests
- 10. API with NestJS #10. Uploading public files to Amazon S3
- 11. API with NestJS #11. Managing private files with Amazon S3
- 12. API with NestJS #12. Introduction to Elasticsearch
- 13. API with NestJS #13. Implementing refresh tokens using JWT
- 14. API with NestJS #14. Improving performance of our Postgres database with indexes
- 15. API with NestJS #15. Defining transactions with PostgreSQL and TypeORM
- 16. API with NestJS #16. Using the array data type with PostgreSQL and TypeORM
- 17. API with NestJS #17. Offset and keyset pagination with PostgreSQL and TypeORM
- 18. API with NestJS #18. Exploring the idea of microservices
- 19. API with NestJS #19. Using RabbitMQ to communicate with microservices
- 20. API with NestJS #20. Communicating with microservices using the gRPC framework
- 21. API with NestJS #21. An introduction to CQRS
- 22. API with NestJS #22. Storing JSON with PostgreSQL and TypeORM
- 23. API with NestJS #23. Implementing in-memory cache to increase the performance
- 24. API with NestJS #24. Cache with Redis. Running the app in a Node.js cluster
- 25. API with NestJS #25. Sending scheduled emails with cron and Nodemailer
- 26. API with NestJS #26. Real-time chat with WebSockets
- 27. API with NestJS #27. Introduction to GraphQL. Queries, mutations, and authentication
- 28. API with NestJS #28. Dealing in the N + 1 problem in GraphQL
- 29. API with NestJS #29. Real-time updates with GraphQL subscriptions
- 30. API with NestJS #30. Scalar types in GraphQL
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 flexibility, though.
Chances are you already know JSON and use it in your web applications. There are databases like MongoDB that store JSON-like documents. With this approach, we can describe any data structures with ease. Using MongoDB has its pros and cons, and we might still want to use an SQL database instead. Some parts of our schema might be fluid and change frequently, though. Fortunately, PostgreSQL has tools to deal with it.
Using a json column with PostgreSQL
In theory, we can store JSON as a regular string. We would miss on a ton of features that Postgres provides to help us work with JSON.
The first column type we want to look into is json. It stores the exact copy of the text we put in. When we use the data, Postgres has to reparse it on each execution.
The json type also preserves the order of keys, duplicates, and whitespace characters.
Creating tables with the json type
Let’s create a few tables and use the json type.
1 2 3 4 | CREATE TABLE product_categories ( id SERIAL PRIMARY KEY, name text ) |
1 2 3 4 5 6 7 | CREATE TABLE products ( id SERIAL PRIMARY KEY, category_id INT NOT NULL, name TEXT NOT NULL, properties json, FOREIGN KEY(category_id) REFERENCES product_categories(id) ) |
Above, we can see that our products table aside from json has other columns also. Even though some parts of our database might benefit from a flexible approach, other places might still use a more traditional approach.
Inserting JSON data
When inserting data, Postgres makes sure that it is formatted properly. If it is not, we can expect an error.
1 2 3 4 5 6 | INSERT INTO product_categories ( name ) VALUES ( 'Books' ) |
1 2 3 4 5 6 7 8 9 10 | INSERT INTO products ( category_id, name, properties ) VALUES ( 1, 'Introduction to Algorithms', '{ "authors": ["Thomas H. Cormen", "Charles E. Leiserson", "Ronald L. Rivest", "Clifford Stein"], "publicationYear": "1990" }' ) |
Above, we’ve added our first product. Since it is a book, it might have properties such as authors and publicationYear. Without using JSON, we would have to add those as additional columns of the products table.
1 2 3 4 5 6 | INSERT INTO product_categories ( name ) VALUES ( 'Cars' ) |
1 2 3 4 5 6 7 8 9 10 | INSERT INTO products ( category_id, name, properties ) VALUES ( 2, 'A8', '{ "brand": "Audi", "engine": { "fuel": "petrol", "numberOfCylinders": 6 } }' ) |
Thanks to the fact that our properties column is flexible, we can use it to manage any type of product.
If we would have only books and cars, it might have been a good idea to create separate books and cars tables. If we had tens or hundreds of types of products, it would have been quite a hassle, though.
Manipulating JSON data
Postgres has quite a few operators and functions built-in that handle JSON data. The most important is the -> operator that allows us to get object fields by key.
1 | SELECT properties->'engine'->'fuel' as fuel FROM products |
We can also use the -> operator to access array elements.
1 | SELECT properties->'authors'->0 as authors FROM products |
The jsonb column
There is a drawback of using the above operator with a json column. Unfortunately, Postgres has to parse the data on each execution.
With PostgreSQL, we can also use the jsonb column. When we put values in, the database parses our data into a binary format. While it might be a bit slower when inserting, it significantly reduces the processing time. The jsonb format also doesn’t preserve whitespace, duplicates, and the order of keys.
1 2 3 4 5 6 7 | CREATE TABLE products ( id SERIAL PRIMARY KEY, category_id INT NOT NULL, name TEXT NOT NULL, properties jsonb, FOREIGN KEY(category_id) REFERENCES product_categories(id) ) |
Doing that gives us all of the functionalities of the json type and more. Aside from the performance improvements when querying data, we also get more operators.
Another significant feature is creating indexes for our JSON data.
1 | CREATE INDEX brand_index ON products ((properties->>'brand')); |
Above, we use the ->> operator to convert the values to text. Thanks to that, it can be used for indexing.
If you want to know more about indexes with PostgreSQL, check out API with NestJS #14. Improving performance of our Postgres database with indexes
Using the jsonb type with TypeORM
The official Postgres documentation encourages the use of the jsonb format in most cases. With TypeORM, it is very straightforward to create a jsonb column.
product.entity.ts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | import { Column, Entity, PrimaryGeneratedColumn, ManyToOne } from 'typeorm'; import ProductCategory from '../productCategories/productCategory.entity'; import { CarProperties } from './types/carProperties.interface'; import { BookProperties } from './types/bookProperties.interface'; @Entity() class Product { @PrimaryGeneratedColumn() public id: number; @Column() public name: string; @ManyToOne(() => ProductCategory, (category: ProductCategory) => category.products) public category: ProductCategory; @Column({ type: 'jsonb' }) public properties: CarProperties | BookProperties; } export default Product; |
Above, we create a union between CarProperties and BookProperties.
carProperties.interface.ts
1 2 3 4 5 6 7 | export interface CarProperties { brand: string; engine: { fuel: string; numberOfCylinders: number; } } |
bookProperties.interface.ts
1 2 3 4 | export interface BookProperties { authors: string[]; publicationYear: string; } |
Creating such an entity allows us to start inserting the data into our database. From the API perspective, properties do not differ much from other columns.
Although the database does not check if our properties match any of the above interfaces, it would be a good idea to validate it. One of the possible approaches would be to save information about the fields in the category. When the user inserts a product, we would then check what fields should a product of that category contain.
Using more advanced queries with TypeORM
While TypeORM might not support all of the features that json and jsonb columns provide, we can work around it. Fortunately, we can use bare SQL queries with TypeORM.
1 2 3 4 | async getAllBrands() { return this.productsRepository .query(`SELECT properties->'brand' as brand from product`); } |
To do the above, we need to know some of the JSON operators that Postgres supports.
If we need to use parameters in our query and we worry about SQL injection, we can create a parameterized query.
1 2 3 4 | async getBrand(productId: number) { return this.productsRepository .query(`SELECT properties->'brand' as brand from product WHERE id = $1`, [productId]); } |
Summary
In this article, we’ve explored the idea of storing JSON within a PostgreSQL database. We’ve done that both through SQL queries and TypeORM. While it is a flexible solution, it is not always fitting. It has some drawbacks, such as slower queries and higher disk usage. Knowing how it works will help us decide if it is a valid approach to the issue that we want to solve.