Managing JSON data with NestJS, PostgreSQL and Kysely

October 9, 2023

This entry is part 128 of 177 in the API with NestJS

PostgreSQL is great for structured data and is known for its reliability in maintaining data organization and consistency. On the other hand, MongoDB stores data in flexible JSON-like documents, making it ideal for data with varying attributes and evolving requirements.

Using SQL has a lot of advantages over MongoDB, but we might need some flexibility in some cases. Fortunately, PostgreSQL supports columns that store JSON data. In this article, we’ll explore their benefits and features, showing how to use them to our benefit. We will implement examples using NestJS, PostgreSQL, and Kysely.

The JSON column

While we could store JSON in the database as a regular string, we would miss many features that PostgreSQL offers. Instead, let’s look into the JSON column type.

20231006050231_add_products_table.ts

import { Kysely } from 'kysely';

export async function up(database: Kysely<unknown>): Promise<void> {

await database.schema

.createTable('products')

.addColumn('id', 'serial', (column) => column.primaryKey())

.addColumn('name', 'text', (column) => column.notNull())

.addColumn('properties', 'json')

.execute();

}

export async function down(database: Kysely<unknown>): Promise<void> {

await database.schema.dropTable('products').execute();

}

The first advantage of the JSON column is that PostgreSQL validates if we use a valid JSON value. If we don’t, it throws an error.

const databaseResponse = await this.database

.insertInto('products')

.values({

properties: {

publicationYear: 1990,

authors: [

'Thomas H. Cormen',

'Charles E. Leiserson',

'Ronald L. Rivest',

'Clifford Stein',

})

.returningAll()

.executeTakeFirstOrThrow();

Here, we’ve inserted our initial record into the products column. Being a book, it includes the publication year and a list of authors. The advantage of a JSON column is that we don’t have to include separate publication_year and authors columns in our table.

Thanks to the flexibility of the properties column, we can use it to store various types of products.

const databaseResponse = await this.database

.insertInto('products')

.values({

properties: {

brand: 'Audi',

engine: {

fuel: 'petrol',

numberOfCylinders: 6,

})

.returningAll()

.executeTakeFirstOrThrow();

If we were dealing with just books and cars, creating distinct tables for each might have made sense. However, if we had numerous product types, managing them separately would become quite cumbersome.

Let’s validate the JSON value to ensure the user provides a valid dictionary.

product.dto.ts

import { IsString, IsNotEmpty, IsObject } from 'class-validator';

export class ProductDto {

@IsString()

@IsNotEmpty()

@IsObject()

properties: Record<string, unknown>;

}

It is crucial to remember that JSON values don’t have to be dictionaries. We can use the JSON column to put regular strings, numbers, or arrays. We can accommodate that by using a broader type in our table definition.

productsTable.ts

import { Generated } from 'kysely';

export interface ProductsTable {

id: Generated<number>;

properties: unknown;

}

If you want to be a bit more specific, you can use the JsonValue type from the type-fest library.

More advanced queries

PostgreSQL offers various built-in operators and functions for working with JSON data, with one of the most crucial being the -> operator, which enables us to access object fields using keys.

1 2	SELECT properties -> 'brand' AS brand FROM products

Fortunately, Kysely supports the above through its expression builder. To access a particular property, we need to use the key function.

const databaseResponse = await this.database

.selectFrom('products')

.select((expressionBuilder) =>

expressionBuilder.ref('properties', '->').key('brand').as('brand'),

)

.execute();

To prevent TypeScript from complaining, we need to narrow down the type of our properties column.

import { Generated } from 'kysely';

export interface ProductsTable {

id: Generated<number>;

properties?: Record<string, unknown>;

}

We can also use the -> operator to access array elements.

1 2	SELECT properties -> 'authors' -> 0 AS first_author FROM products

Kysely supports that through the at function.

const databaseResponse = await this.database

.selectFrom('products')

.select((expressionBuilder) =>

expressionBuilder.ref('properties', '->').key('authors').at(0).as('first_author'),

)

.execute();

The JSONB column

The JSON column stores data in its original text format. It preserves the exact structure and order of elements, including whitespace. While it’s fast for data insertion, it’s not the best solution for complex queries due to the need to parse the JSON data each time.

Alternatively, we can use the JSONB column. It has all of the functionalities of the JSON type and more but changes how PostgreSQL stores the data. When we input data to a JSONB column, the database converts it into a binary format. Although inserting the value might be slightly slower, it considerably reduces the processing time. Additionally, the JSONB format doesn’t preserve whitespace, duplicates, or the key order.

In addition to enhancing performance, the JSONB column offers a wider range of operators and extends the indexing capabilities.

20231006050231_add_products_table.ts

import { Kysely } from 'kysely';

export async function up(database: Kysely<unknown>): Promise<void> {

await database.schema

.createTable('products')

.addColumn('id', 'serial', (column) => column.primaryKey())

.addColumn('name', 'text', (column) => column.notNull())

.addColumn('properties', 'jsonb')

.execute();

}

export async function down(database: Kysely<unknown>): Promise<void> {

await database.schema.dropTable('products').execute();

}

Summary

In this article, we’ve explored the process of storing JSON in a PostgreSQL database using the JSON and JSONB columns through NestJS and Kysely.

We can use this approach in PostgreSQL when storing JSON data within your relational database, allowing you to work with semi-structured or loosely structured data. While the JSON and JSONB columns offer flexibility, they come at the cost of abandoning some of the advantages of relational databases. Therefore, we should use this approach sparingly.

Series Navigation<< API with NestJS #127. Arrays with PostgreSQL and KyselyAPI with NestJS #129. Implementing soft deletes with SQL and Kysely >>