[BUG]: Cannot run drizzle-studio with DATA-API. Unsupported data type "CHAR"

[valentinbeggi]

What version of drizzle-orm are you using?

0.34.1

What version of drizzle-kit are you using?

0.25.0

Describe the Bug

It was working fine last week but now,
when trying to run the studio, I'm getting this error.

Error: The result contains the unsupported data type "CHAR".
at _ (https://local.drizzle.studio/index.js:14138:33349)
at async https://local.drizzle.studio/index.js:14138:33539
at async Promise.all (index 1)
at async $oi (https://local.drizzle.studio/index.js:13940:1587)
at async Xkl (https://local.drizzle.studio/index.js:14138:33520)

I managed to debug and get the query responsible for this error.
I reproduce this error within the query editor of RDS

SELECT 
    CASE 
        WHEN c.relkind = 'r' THEN 'table'
        WHEN c.relkind = 'v' THEN 'view'
        WHEN c.relkind = 'm' THEN 'mat_view'
    END AS entity_type,
    n.nspname AS table_schema,
    c.relname AS table_name,
    a.attname AS column_name,
    pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
    CASE a.attnotnull 
        WHEN TRUE THEN 'NO' 
        ELSE 'YES' 
    END AS is_nullable,
    CASE 
        WHEN pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) LIKE 'nextval(%' 
             AND format_type(a.atttypid, a.atttypmod) = 'integer' THEN 'serial'
        WHEN pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) LIKE 'nextval(%' 
             AND format_type(a.atttypid, a.atttypmod) = 'bigint' THEN 'bigserial'
        WHEN pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) LIKE 'nextval(%' 
             AND format_type(a.atttypid, a.atttypmod) = 'smallint' THEN 'smallserial'
        ELSE format_type(a.atttypid, a.atttypmod)
    END AS data_type,
    CASE 
        WHEN t.typcategory = 'A' THEN 'ARRAY'
        WHEN t.typtype = 'b' THEN a.atttypid::regtype::text
        ELSE 'USER-DEFINED'
    END AS additional_dt,
    a.attndims AS array_dimensions,
    a.attnum,
    a.attidentity AS identity_type,
    a.attgenerated AS generated_type,
    seq_class.relname AS identity_name,
    seq.seqincrement AS identity_increment,
    seq.seqmax AS identity_max,
    seq.seqmin AS identity_min,
    seq.seqstart AS identity_start,
    seq.seqcache AS identity_cache,
    seq.seqcycle AS identity_cycle
FROM 
    pg_catalog.pg_attribute a
JOIN 
    pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN 
    pg_catalog.pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN 
    pg_catalog.pg_depend dep ON dep.refobjid = c.oid AND dep.refobjsubid = a.attnum
LEFT JOIN 
    pg_catalog.pg_class seq_class ON seq_class.oid = dep.objid
LEFT JOIN 
    pg_catalog.pg_sequence seq ON seq_class.oid = seq.seqrelid
LEFT JOIN 
    pg_catalog.pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
JOIN 
    pg_catalog.pg_type t ON a.atttypid = t.oid
WHERE 
    c.relkind IN ('r', 'v', 'm')
    AND a.attnum > 0 
    AND NOT a.attisdropped
    AND n.nspname NOT LIKE 'pg\\_%'
    AND n.nspname != 'information_schema'
ORDER BY 
    attnum;

Casting these 2 lines seems to fix the issue:

    a.attidentity::text AS identity_type,
    a.attgenerated::text AS generated_type

It seems similar to this issue: https://github.com/drizzle-team/drizzle-kit-mirror/issues/542

Thank you for your help 😊

Expected behavior

No response

Environment & setup

No response

[MaximeVivier]

I'm getting the same kind of error on my project.
Hoping someone has an answer to this issue. 🤞
Thanks !!!

[thehamsti]

Also getting this same error on same versions as OP.

[eshrager]

same error all of a sudden. it was fixed a couple of months back but now broken again

[larryonward]

here's the patch while we're waiting for proper fix

sed -i '' 's/const proxy = async (params) => {/const proxy = async (params) => {\n  params.sql = params.sql.replace(\/a.attidentity AS identity_type\/i, '\''a.attidentity::text AS identity_type'\''); \n  params.sql = params.sql.replace(\/a.attgenerated AS generated_type\/i, '\''a.attgenerated::text AS generated_type'\'');/g' node_modules/drizzle-kit/bin.cjs

[peterhanania]

+1

Getting the error all of a sudden

[valentinbeggi]

These are different issues but i'm linking them here because they are related to type casting in drizzle-studio.
They make relation visualization / drizzle query editor unusable in the studio.

#2583
#3409

Would really be awesome if these were fixed 🙏

[NikolaRusakov]

@larryonward, thank you for the sed expression.

I made a patch file out of your sed exp for pnpm patch / npx patch-package.
This applies to the latest drizzle-kit version 0.28.0

https://gist.github.com/NikolaRusakov/88858b5b6a4e1585ca772f40f4e4d559#file-drizzle-kit-0-28-0-patch

[rmarscher]

The patches were working for me but they suddenly stopped today. 😢

[valentinbeggi]

Adding this to bin.js > proxy

params.sql = params.sql.replace(/con\.contype AS constraint_type/i, 'con.contype::text AS constraint_type');

Fixes the new unsupported data type issue, but I have another one popping when trying to access table data

ERROR: bind message supplies 0 parameters, but prepared statement "sqlx_s_xxx" requires 1; SQLState: 08P01

[larryonward]

@valentinbeggi yeah, stumbled on that too.

ERROR: bind message supplies 0 parameters, but prepared statement "sqlx_s_xxx" requires 1; SQLState: 08P01

temporary fixed by adding

this.rawQuery.input.sql = this.rawQuery.input.sql.replace('$1', ':1');

here

drizzle-orm/drizzle-orm/src/aws-data-api/pg/session.ts

Line 103 in 599da5e

(should be a better way)

[eshrager]

This fix doesn't seem to work.

This is the resulting code that isn't working but includes this suggested change:
async values(placeholderValues = {}) { const params = (0, import_sql.fillPlaceholders)(this.params, placeholderValues ?? {}); this.rawQuery.input.sql = this.rawQuery.input.sql.replace('$1', ':1'); this.rawQuery.input.parameters = params.map((param, index) => ({ name: ${index + 1}, ...(0, import_common.toValueParam)(param, this.typings[index]) })); this.options.logger?.logQuery(this.rawQuery.input.sql, this.rawQuery.input.parameters); const result = await this.client.send(this.rawQuery); const rows = result.records?.map((row) => { return row.map((field) => (0, import_common.getValueFromDataApi)(field)); }) ?? []; return { ...result, rows }; }

[valentinbeggi]

@larryonward thx, fixes it for me (at least for now)

Patches are getting out of hand tho 🙈

[larryonward]

Patches are getting out of hand tho 🙈

agree

So, after updating to the latest versions all the issues are still there.

"drizzle-orm": "^0.37.0",
"drizzle-kit": "^0.29.0"

reproducible with following config:

  driver: 'aws-data-api',
  dialect: 'postgresql',

I've compiled all the patches here:
https://gist.github.com/larryonward/5c845651b9a9a64875864b7ddcfc7323

1. create something like drizzle-patch.sh in root of your project
2. copy content of gist
3. allow to run it with chmod +x drizzle-patch.sh
4. run with ./drizzle-patch.sh