Effect CommunityEC
Effect Community•4mo ago•
19 replies
sbs

Issue with JSONB Column Handling in @effect/sql-drizzle with PostgreSQL

When using @effect/sql-drizzle with postgresql, inserting JSONB columns where I directly provide a json value seems to convert them to string, but deserialization seems to still work as expected. This is still rather problematic since it makes it impossible to write queries that use the jsonb functionality of the database. Here is a reproduction:

const MyLayer = Layer.merge(DrizzleLive, AppEnv.Default);

const repro = Effect.gen(function* () {
  const env = yield* AppEnv;
  const rawClient = createDrizzleClient(env.DATABASE_URL.pipe(Redacted.value));

  yield* rawClient.delete(job);
  yield* rawClient.insert(job).values({
    uploaded_file_id: "f5eabf04-3158-400a-8932-922150cd3d61",
    payload: TranscriptionJobPayload.make({
      fileId: "1",
    }),
  });

  const sqlClient = yield* SqlClient.SqlClient;
  const stmt = yield* sqlClient.unsafe(
    `insert into job (uploaded_file_id, payload) values ($1, $2)`,
    [
      "f5eabf04-3158-400a-8932-922150cd3d61",
      TranscriptionJobPayload.make({
        fileId: "3",
      }) as never, // as never to silence a type error expecting `Primitive` - it seems to work either way.
    ],
  );

  const db = yield* DrizzleClient;
  yield* db.insert(job).values({
    uploaded_file_id: "f5eabf04-3158-400a-8932-922150cd3d61",
    payload: TranscriptionJobPayload.make({
      fileId: "2",
    }),
  });

  const res = yield* rawClient.execute(sql`select id, payload from job`);
  yield* Effect.logInfo(res);
}).pipe(Effect.provide(MyLayer));

NodeRuntime.runMain(repro);


The output of this is

[15:10:58.545] INFO (#1):
  Result(3) [
    { id: 84, payload: { _tag: 'TranscriptionJob', fileId: '1' } },
    { id: 85, payload: { _tag: 'TranscriptionJob', fileId: '3' } },
    { id: 86, payload: '{"_tag":"TranscriptionJob","fileId":"2"}' }
  ]
Was this page helpful?