How to Use the Postgres Jsonb Type in EF Core (1)

Postgres has a special type called Jsonb. You can use it to store data in Json format, and the database organizes the nodes of the Json document for you. This type gives you some flexibility inside the strict rules of a relational database. You keep the guarantees of SQL and you also get the flexibility of NoSQL. But EFCore is an ORM framework that abstracts the database away. Queries and writes against Jsonb are a Postgres-specific feature. You therefore need an extra approach to support them. My current project stores part of its data as Jsonb again, so I read the documentation once more and adjusted my approach this time.

---

Project requirements

The customer on this project asked for a scheduled-change feature. They want to change different pieces of data at a time that they specify. For several reasons, we decided to store the schedule information in the database. We designed this table:

CREATE TABLE schedule ( 
  id INTEGER NOT NULL,
  execute_date_time TIMESTAMP,
  target_id INTEGER NOT NULL,
  target_table VARCHAR,
  status VARCHAR,
  process_data JSONB,
  CONSTRAINT schedule_pk PRIMARY KEY (id)
);

When you add a schedule, you put the table that you want to change into target_table and the row that you want to change into target_id. You store the change that you plan to apply in process_data, in json format.

The default behavior

My team uses the DB First approach, so the database generates the Entity classes that the application uses. Without any extra configuration, the generator turns Jsonb into a string:

public class Schedule
{
    public long Id { get; set; }
    public DateTime ExecuteDateTime { get; set; } 
    public long TargetId { get; set; }
    public string TargetTable { get; set; }
    public string Status { get; set; }
    public string ProcessData { get; set; }
}

An insert or an update then looks like this:

// Add a schedule
var schedule = new Schedule
{
    ExecuteDateTime = DateTime.UtcNow.AddMinutes(10),
    TargetId = 123,
    TargetTable = nameof(Product),
    Status = nameof(ScheduleStatus.Waiting),
    ProcessData = JsonSerializer.Serialize(new UpdatePrice(100))
};

dbContext.Schedules.Add(schedule);
await dbContext.SaveChangesAsync();

// Update the schedule data
var data = JsonSerializer.Deserialize<UpdatePrice>(schedule.ProcessData);
data = data with { Price: data.Price - 10 };
schedule.ProcessData = JsonSerializer.Serialize(data);
await dbContext.SaveChangesAsync();

On SaveChangesAsync, EFCore updates the whole Jsonb column. Postgres itself has syntax to update only part of the column. However, it is very hard for an ORM to track changes in data that is not strongly typed. Next I will replace the string with a JsonDocument. The difference for writes and updates is small, but queries become much easier.

JsonDocument

Npgsql.EntityFrameworkCore.PostgreSQL supports a custom type that maps to Jsonb, which gives you the benefits of a strong type. But if you can define the type in advance, you can simply add a normal column to the table instead. That is why this approach is not common. Npgsql also supports JsonDocument as the mapping for Jsonb, and that gives you far more flexibility. The first thing to note is that JsonDocument implements IDisposable, so you must release it yourself. You therefore need to change Schedule a little:

public class Schedule : IDisposable
{
    public long Id { get; set; }
    public DateTime ExecuteDateTime { get; set; } 
    public long TargetId { get; set; }
    public string TargetTable { get; set; }
    public string Status { get; set; }
    public string ProcessData { get; set; }
    public void Dispose => ProcessData.Dispose();
}

EFCore now releases ProcessData when it releases the context. Look at the insert and the update again:

// Add a schedule
var schedule = new Schedule
{
    ExecuteDateTime = DateTime.UtcNow.AddMinutes(10),
    TargetId = 123,
    TargetTable = nameof(Product),
    Status = nameof(ScheduleStatus.Waiting),
    ProcessData = JsonSerializer.SerializeToDocument(
        new UpdatePrice(100))
};

dbContext.Schedules.Add(schedule);
await dbContext.SaveChangesAsync();

// Update the schedule data
using schedule.ProcessData;
var data = schedule.ProcessData.Deserialize<UpdatePrice>();
data = data with { Price: data.Price - 10 };
schedule.ProcessData = JsonSerializer.SerializeToDocument(data);
await dbContext.SaveChangesAsync();

A JsonDocument is read-only, so a change means that you replace the original instance. Also, when you update the data, schedule.ProcessData points at a new instance instead of the original one. EFCore no longer manages the old instance, so you must release it yourself.

Summary

Support for the Json format is a fairly new feature in relational databases. The ORM integration also works differently from what you are used to. In EFCore you can work with the data through JsonDocument. You only need to watch out for a few traits of JsonDocument (yes, I mean the part where you release it). The next post covers queries. Honestly, that is the part that really unlocks Jsonb!

Extra notes

I ran a small test to see what happens when you do not release the document.

using System.Text.Json;

const int numObjects = 1000000;
var random = new Random();

Console.WriteLine("With using statement:");
foreach (var _ in Enumerable.Range(0, numObjects))
{
    using var document = JsonSerializer.SerializeToDocument(new { Id = Guid.NewGuid(), number = random.Next() });
}


Console.WriteLine("Without using statement:");
foreach (var _ in Enumerable.Range(0, numObjects))
{
    var document = JsonSerializer.SerializeToDocument(new { Id = Guid.NewGuid(), number = random.Next() });
}

If you do not release the document, memory use does not explode. The GC just gets very busy. The picture below shows the memory analysis from DotMemory, and each yellow dot marks a GC run. After the seven-second mark, the GC runs far more often for the loop that does not release the documents.

You can also read this issue.