Cannot insert NULL into JSONB column: "cannot scan NULL into *string" error

[miles-kt-inkeep]

I am working on a Typescipt application using Doltgres and Drizzle. When inserting a row with an explicit NULL value in a JSONB column using Drizzle ORM and the Node.js pg driver, I get the error cannot scan NULL into *string. The same operation works successfully with a Postgres db.

Environment:

• doltgres: 0.53.0
• drizzle-orm: 0.44.7
• pg: 8.16.3

Schema

import {  jsonb,  pgTable,  text } from 'drizzle-orm/pg-core';

export const components = pgTable(
  'components',
  {
    id: text('id').notNull(),
    name: text('name'),
    description: text('description'),
    render: jsonb('render'),  
  }
);

SQL:

CREATE TABLE "data_components" (
	"id" varchar(256) NOT NULL,
	"name" varchar(256) NOT NULL,
	"description" text NOT NULL,
	"render" jsonb,
);

Example

import { Pool } from 'pg';
import { drizzle } from 'drizzle-orm/node-postgres';

const params = {
  id: 'test',
  render: null,  
  name: 'Test',
  description: test,
};

const pool = new Pool({ connectionString });

const db = drizzle(pool, { schema });

await db
  .insert(components)
  .values(params)

The insert fails with the following error:

  cause: error: cannot scan NULL into *string
      at node_modules/.pnpm/[email protected][email protected]/node_modules/pg-pool/index.js:45:11
      at process.processTicksAndRejections (node:internal/process/task_queues:105:5)

[zachmu]

Thanks for the great issue and repro. We'll get on this today and should have it fixed soon.

[zachmu]

So far I've confirmed that inserting NULL into a JSONB column works, e.g.:

CREATE TABLE t_jsonb (id INTEGER primary key, v1 JSONB);
INSERT INTO t_jsonb VALUES (3, NULL);

So the problem must be something in the specific SQL syntax that drizzle is using for the insert, or for a subsequent statement.

When I try to reproduce this using the latest drizzle, I run into an error because Doltgres doesn't support the constraint_column_usage system view. We can add that easy enough, but I'm curious how you got this working well enough to see this error. I ran into this problem when performing the initial migration with npx drizzle-kit push. This is with the same version of drizzle-orm you're using:

% cat package.json 
{
  "dependencies": {
    "dotenv": "^17.2.3",
    "drizzle-orm": "^0.44.7",
    "knex": "^3.1.0",
    "pg": "^8.16.3",
    "wtfnode": "^0.10.1"
  },
  "devDependencies": {
    "@types/pg": "^8.15.6",
    "drizzle-kit": "^0.31.7",
    "tsx": "^4.20.6"
  }
}

I'm still learning drizzle, but I'm curious if they have an a config option that will log the SQL being executed. If you can capture the actual SQL that it's issuing, that should help me get to a root cause quicker, without having to fix the system view problem first. If you can't capture the SQL query with drizzle, you can turn on debug logging in doltgres with the log_level setting as described here:

https://docs.doltgres.com/reference/server/configuration

[miles-kt-inkeep]

hmm I am using drizzle-kit generate to generate the migration SQL from the schema and then drizzle-kit migrate to apply the SQL. drizzle-kit push fails for me as well with the error you mentioned.

[zachmu]

Ok, that's helpful, I can try that to start.

[miles-kt-inkeep]

here is the full log from drizzle

Query: insert into "data_components" ("tenant_id", "id", "project_id", "name", "description", "props", "render", "created_at", "updated_at") values ($1, $2, $3, $4, $5, default, $6, default, default) returning "tenant_id", "id", "project_id", "name", "description", "props", "render", "created_at", "updated_at" -- params: ["default", "test", "test", "Test", "A test data component", null]
/Users/mileskaming-thanassi/dolt-implementation/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@opentelemetry+ap_dab9a81a2cf287da90c32264187601bd/node_modules/src/pg-core/session.ts:73
                                throw new DrizzleQueryError(queryString, params, e as Error);
                                      ^

DrizzleQueryError: Failed query: insert into "data_components" ("tenant_id", "id", "project_id", "name", "description", "props", "render", "created_at", "updated_at") values ($1, $2, $3, $4, $5, default, $6, default, default) returning "tenant_id", "id", "project_id", "name", "description", "props", "render", "created_at", "updated_at"
params: default,test,test,Test,A test data component,
    at NodePgPreparedQuery.queryWithCache (/Users/mileskaming-thanassi/dolt-implementation/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@opentelemetry+ap_dab9a81a2cf287da90c32264187601bd/node_modules/src/pg-core/session.ts:73:11)
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async <anonymous> (/Users/mileskaming-thanassi/dolt-implementation/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@opentelemetry+ap_dab9a81a2cf287da90c32264187601bd/node_modules/src/node-postgres/session.ts:154:19)
    at async <anonymous> (/Users/mileskaming-thanassi/dolt-implementation/packages/agents-core/src/data-access/dataComponents.ts:141:27)
    at async main (/Users/mileskaming-thanassi/dolt-implementation/packages/agents-core/src/data-access/test.ts:50:18) {
  query: 'insert into "data_components" ("tenant_id", "id", "project_id", "name", "description", "props", "render", "created_at", "updated_at") values ($1, $2, $3, $4, $5, default, $6, default, default) returning "tenant_id", "id", "project_id", "name", "description", "props", "render", "created_at", "updated_at"',
  params: [ 'default', 'test', 'test', 'Test', 'A test data component', null ],
  cause: error: cannot scan NULL into *string
      at /Users/mileskaming-thanassi/dolt-implementation/node_modules/.pnpm/[email protected][email protected]/node_modules/pg-pool/index.js:45:11
      at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
      at async <anonymous> (/Users/mileskaming-thanassi/dolt-implementation/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@opentelemetry+ap_dab9a81a2cf287da90c32264187601bd/node_modules/src/node-postgres/session.ts:161:13)
      at async NodePgPreparedQuery.queryWithCache (/Users/mileskaming-thanassi/dolt-implementation/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@opentelemetry+ap_dab9a81a2cf287da90c32264187601bd/node_modules/src/pg-core/session.ts:71:12)
      at async <anonymous> (/Users/mileskaming-thanassi/dolt-implementation/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@opentelemetry+ap_dab9a81a2cf287da90c32264187601bd/node_modules/src/node-postgres/session.ts:154:19)
      at async <anonymous> (/Users/mileskaming-thanassi/dolt-implementation/packages/agents-core/src/data-access/dataComponents.ts:141:27)
      at async main (/Users/mileskaming-thanassi/dolt-implementation/packages/agents-core/src/data-access/test.ts:50:18) {
    length: 50,
    severity: 'ERROR',
    code: 'XX000',
    detail: undefined,
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: undefined,
    line: undefined,
    routine: undefined
  }
}

[zachmu]

OK, that's very helpful, thanks. I have it reproduced on my end as well, so I should be able to get to the bottom of it now.

[zachmu]

I have a fix for the immediate issue that should be ready to release first thing Monday. We also want to get drizzle push working, and get comprehensive drizzle tests, before we close this out.

[zachmu]

Keeping this open while we get drizzle push working.