Handling JSON data with PostgreSQL and the Drizzle ORM

PostgreSQL is well-suited for handling structured data and keeping everything organized and consistent. On the other hand, MongoDB stores data in flexible JSON-like documents, making it ideal for data with varying attributes and changing requirements.

While SQL databases have many advantages, there are times when flexibility is needed. Fortunately, PostgreSQL bridges this gap by allowing the storage and querying of loosely structured JSON data. In this article, we will examine the benefits and features of these JSON columns and demonstrate how to use them effectively. We’ll provide examples using NestJS, PostgreSQL, and the Drizzle ORM.

The JSON data type

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

1import { serial, text, pgTable, json } from 'drizzle-orm/pg-core';
2 
3export const products = pgTable('products', {
4  id: serial('id').primaryKey(),
5  name: text('name').notNull(),
6  properties: json('properties').notNull(),
7});
8 
9export const databaseSchema = {
10  products,
11};

Now, we need to create a migration using the Drizzle Kit.

If you want to know more about migrations with the Drizzle ORM, check out API with NestJS #149. Introduction to the Drizzle ORM with PostgreSQL

1npx drizzle-kit generate --name create-products-table

The last step is to run our migration to create the products table.

1npx drizzle-kit migrate

One of the most important benefits of the JSON column is that PostgreSQL verifies the data format for us. If the JSON is not valid, it throws an error. To prevent that, let’s use the class-validator library.

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}

The JSON format can also store strings, numbers, booleans, and arrays. However, thanks to using the @IsObject() decorator, we can narrow it down to the Record<string, unknown> type.

Creating new records in the database that use a JSON column is very straightforward with the Drizzle ORM.

1import { Injectable } from '@nestjs/common';
2import { ProductDto } from './dto/product.dto';
3import { DrizzleService } from '../database/drizzle.service';
4import { databaseSchema } from '../database/database-schema';
5 
6@Injectable()
7export class ProductsService {
8  constructor(private readonly drizzleService: DrizzleService) {}
9 
10  async create(data: ProductDto) {
11    const createdProducts = await this.drizzleService.db
12      .insert(databaseSchema.products)
13      .values({
14        name: data.name,
15        properties: data.properties,
16      })
17      .returning();
18 
19    return createdProducts.pop();
20  }
21 
22  // ...
23}

With the flexibility of the JSON column, we can store different types of products.

Above, we’ve inserted our first record into the products column. Since it’s a book, it includes the publication year and a list of authors. The advantage of using a JSON column is that we don’t need to create separate publication_year and authors columns in our table. However, we can still take advantage of the name column being a regular text data type. Thanks to that, we could use various SQL features such as constraints.

If you want to know more about constraints with the Drizzle ORM, check out API with NestJS #152. SQL constraints with the Drizzle ORM

Creating separate tables for each might still be practical if we only deal with books and cars. However, managing separate tables would become quite a chore with a wide variety of product types.

More advanced queries

PostgreSQL provides many built-in operators and functions for handling JSON data. One of the most important is the -> operator, which allows us to access object fields using keys.

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

Thankfully, we can implement the above with Drizzle ORM using raw SQL.

1const publicationYears = await this.drizzleService.db
2  .select({
3    publicationYear: sql`${databaseSchema.products.properties} -> 'publicationYear'`,
4  })
5  .from(databaseSchema.products);

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

1const firstAuthors = await this.drizzleService.db
2  .select({
3    firstAuthor: sql`${databaseSchema.products.properties} -> 'authors' -> 0`,
4  })
5  .from(databaseSchema.products);

It also makes sense to use operators like the one above when filtering. For example, let’s write a query that returns only Audi.

1const audi = await this.drizzleService.db
2  .select()
3  .from(databaseSchema.products)
4  .where(sql`${databaseSchema.products.properties} ->> 'brand' = 'Audi'`);

It’s important to notice that the -> operator accesses the data in the JSON format. To get it in a string format above, we use the ->> operator instead.

The JSONB column

PostgreSQL offers two types of columns for handling JSON data: JSON and JSONB. The JSON column stores data in its original text format, preserving the exact structure and order of elements, including whitespace. This allows for fast data insertion but requires parsing the JSON data each time a query is made, which can slow down complex queries.

On the other hand, the JSONB column type changes how PostgreSQL stores the data. When we put the data into our database into a JSONB column, it converts it into a binary format. It may make the insertion slightly slower but significantly reduces query processing time. Additionally, JSONB does not preserve whitespace, duplicates, or the order of keys.

In addition to performance improvements, the JSONB column offers more operators and additional indexing capabilities.

Using JSONB with the Drizzle ORM is as simple as using the jsonb() function.

1import { serial, text, pgTable, jsonb } from 'drizzle-orm/pg-core';
2 
3export const products = pgTable('products', {
4  id: serial('id').primaryKey(),
5  name: text('name').notNull(),
6  properties: jsonb('properties').notNull(),
7});
8 
9export const databaseSchema = {
10  products,
11};

Summary

In this article, we’ve explored how to store JSON in a PostgreSQL database. We’ve done that using the JSON and JSONB columns with NestJS and the Drizzle ORM. With this approach, we can work with semi-structured or loosely structured data within SQL databases.

However, while JSON and JSONB columns offer flexibility, they require sacrificing some benefits of relational databases. Therefore, it’s important to use this method sparingly and consider it carefully before making the choice.