Managing JSON data with PostgreSQL and Kysely

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.

1import { Kysely } from 'kysely';
2 
3export async function up(database: Kysely<unknown>): Promise<void> {
4  await database.schema
5    .createTable('products')
6    .addColumn('id', 'serial', (column) => column.primaryKey())
7    .addColumn('name', 'text', (column) => column.notNull())
8    .addColumn('properties', 'json')
9    .execute();
10}
11 
12export async function down(database: Kysely<unknown>): Promise<void> {
13  await database.schema.dropTable('products').execute();
14}

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.

1const databaseResponse = await this.database
2  .insertInto('products')
3  .values({
4    name: 'Introduction to Algorithms',
5    properties: {
6      publicationYear: 1990,
7      authors: [
8        'Thomas H. Cormen',
9        'Charles E. Leiserson',
10        'Ronald L. Rivest',
11        'Clifford Stein',
12      ],
13    },
14  })
15  .returningAll()
16  .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.

1const databaseResponse = await this.database
2  .insertInto('products')
3  .values({
4    name: 'Introduction to Algorithms',
5    properties: {
6      brand: 'Audi',
7      engine: {
8        fuel: 'petrol',
9        numberOfCylinders: 6,
10      },
11    },
12  })
13  .returningAll()
14  .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.

1import { IsString, IsNotEmpty, IsObject } from 'class-validator';
2 
3export class ProductDto {
4  @IsString()
5  @IsNotEmpty()
6  name: string;
7 
8  @IsObject()
9  properties: Record<string, unknown>;
10}

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.

1import { Generated } from 'kysely';
2 
3export interface ProductsTable {
4  id: Generated<number>;
5  name: string;
6  properties: unknown;
7}

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.

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

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

1const databaseResponse = await this.database
2  .selectFrom('products')
3  .select((expressionBuilder) =>
4    expressionBuilder.ref('properties', '->').key('brand').as('brand'),
5  )
6  .execute();

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

1import { Generated } from 'kysely';
2 
3export interface ProductsTable {
4  id: Generated<number>;
5  name: string;
6  properties?: Record<string, unknown>;
7}

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

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

Kysely supports that through the at function.

1const databaseResponse = await this.database
2  .selectFrom('products')
3  .select((expressionBuilder) =>
4    expressionBuilder.ref('properties', '->').key('authors').at(0).as('first_author'),
5  )
6  .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.

1import { Kysely } from 'kysely';
2 
3export async function up(database: Kysely<unknown>): Promise<void> {
4  await database.schema
5    .createTable('products')
6    .addColumn('id', 'serial', (column) => column.primaryKey())
7    .addColumn('name', 'text', (column) => column.notNull())
8    .addColumn('properties', 'jsonb')
9    .execute();
10}
11 
12export async function down(database: Kysely<unknown>): Promise<void> {
13  await database.schema.dropTable('products').execute();
14}

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.