Sequel Upgrade

[cmavromoustakos]

Hello,

We are upgrading from sequel (4.49.0) to sequel (5.66.0). In the process we noticed that JSON attributes which used to be returned as JSON objects are now being returned as JSON strings. So for example the possible_causes attribute is being returned as a JSON string. We started fixing these by adding a JSON.parse everywhere we are expecting an object but I wanted to ask here because that solution seems wrong. Any help would be appreciated.

The query we are using is database[:consolidated_events].all

And the DB migration:

    create_table :consolidated_events do
      primary_key :id, type: String
      String :name, null: false
      Jsonb :possible_causes
      Boolean :draft, null: false, default: true
      timestamp :created_at, null: false, default: Sequel::CURRENT_TIMESTAMP
      timestamp :updated_at, null: false, default: Sequel::CURRENT_TIMESTAMP
    end

[1] pry(#<Persistence::ConsolidatedEvents::ConsolidatedEventsRepository>)> database[:consolidated_events].all
=> [{:id=>"fake_event", :name=>"fake name", :possible_causes=>"[{\"details\": [\"mock_details1\", \"mock_details2\"], \"description\": \"mock_description\"}]", :draft=>true, :created_at=>2024-06-18 18:48:57.268643 +0000, :updated_at=>2024-06-18 18:48:57.268643 +0000, :effect_alerts=>"[\"fake_effect_alert\"]", :cause_alerts=>"[\"fake_cause_alert\"]"},

 {:id=>"fake_event_2", :name=>"fake name 2", :possible_causes=>"[{\"details\": [\"mock_details1\", \"mock_details2\"], \"description\": \"mock_description 2\"}]", :draft=>true, :created_at=>2024-06-18 18:48:57.271055 +0000, :updated_at=>2024-06-18 18:48:57.271055 +0000, :effect_alerts=>"[\"fake_effect_alert\"]", :cause_alerts=>"[\"fake_cause_alert\"]"}]
[2] pry(#<Persistence::ConsolidatedEvents::ConsolidatedEventsRepository>)> 
 

[jeremyevans]

What database are you using? Can you create a minimal self contained example showing the problem?

[cmavromoustakos]

We are using Postgres 13

[cmavromoustakos]

Here is the test we are running that is failiung

require_relative "../../../spec_helper"

module Persistence
  module ConsolidatedEvents
    describe ConsolidatedEventsRepository do
      let(:consolidated_events) do
        [
          {
            id: "fake_event",
            name: "fake name",
            possible_causes: [{
              description: "mock_description",
              details: %w[mock_details1 mock_details2]
            }].to_json,
            effect_alerts: ["fake_effect_alert"].to_json,
            cause_alerts: ["fake_cause_alert"].to_json
          },
          {
            id: "fake_event_2",
            name: "fake name 2",
            possible_causes: [{
              description: "mock_description 2",
              details: %w[mock_details1 mock_details2]
            }].to_json,
            effect_alerts: ["fake_effect_alert"].to_json,
            cause_alerts: ["fake_cause_alert"].to_json
          }
        ]
      end

      let(:device_consolidated_events) do
        [
          { device_id: device_id, date: date, consolidated_event_id: "fake_event", alerts: "[]" },
          { device_id: "00000000", date: date, consolidated_event_id: "fake_event", alerts: "[]" },
          { device_id: device_id, date: date - 31.days, consolidated_event_id: "fake_event", alerts: "[]" }
        ]
      end

      let(:device_id) { "12345678" }
      let(:date)      { Date.today }

      subject { ConsolidatedEventsRepository.new }

      before do
        consolidated_events.each do |consolidated_event|
          Db.get_database_with_write_access[:consolidated_events].insert(consolidated_event)
        end

        device_consolidated_events.each do |device_event|
          Db.get_database_with_write_access[:device_consolidated_events].insert(device_event)
        end
      end

      after do
        Db.get_database_with_write_access[:device_consolidated_events].delete
        Db.get_database_with_write_access[:consolidated_events].delete
      end

      describe "#get_device_events_for_date" do
        it "returns device events for the given date and device_id" do
          event_records = subject.get_device_events_for_date(device_id, date)

          expect(event_records.length).to eq(1)
          expect(event_records[0][:device_id]).to eq("12345678")
        end
      end

      describe "#get_event" do
        it "returns consolidated events details for the given id" do
          event = subject.get_event("fake_event")

          expect(event[:id]).to eq("fake_event")
          expect(event[:possible_causes]).to eq(
            Sequel.pg_jsonb([{
              "description" => "mock_description",
              "details" => %w[mock_details1 mock_details2]
            }])
          )
        end
      end

      describe "#get_events" do
        it "returns all consolidated events" do
          events = subject.get_events

          expect(events.count).to eq(2)

          expect(events[0][:id]).to eq("fake_event")
          expect(events[0][:possible_causes]).to eq(
            Sequel.pg_jsonb([{
              "description" => "mock_description",
              "details" => %w[mock_details1 mock_details2]
            }])
          )
          expect(events[0][:cause_alerts]).to eq(Sequel.pg_jsonb(["fake_cause_alert"]))
          expect(events[0][:effect_alerts]).to eq(Sequel.pg_jsonb(["fake_effect_alert"]))
        end
      end
, def
      describe "#get_device_events_for_past_days" do
        it "returns device events for the given device_id and number of past days" do
          device_events = subject.get_device_events_for_past_days(device_id, 30)

          expect(device_events.length).to eq(1)
          expect(device_events[0][:device_id]).to eq("12345678")
        end
      end
    end
  end
end

[jeremyevans]

If you are using PostgreSQL 13, make sure you are using the pg_json extension if you would like Sequel automatically parse json/jsonb columns.

You should also consider using the latest version of Sequel and not Sequel 5.66.

If you still need help, please create a minimal, self-contained example showing the problem. The example provided is not self-contained, and does not appear to be minimal.

[cmavromoustakos]

It turns out we were not using pg_json but for some reason this was working in prior versions. As soon as we added it it fixed our issue. Thank you for your fast response.