How to push to an array?

I have a text array column, not nullable, and am having trouble pushing to the array in PG

await db
  .update(table1)
  .set({
    textArrayField: sql`${table1.textArrayField} || 'someString`
  })
  .where(eq(table1.id, someId));

await db
  .update(table1)
  .set({
    textArrayField: sql`${table1.textArrayField} || 'someString`
  })
  .where(eq(table1.id, someId));

and i get a very unhelpful error:

TypeError: value.map is not a function

31 Replies

Mykhailo•11mo ago

Hello, @jakeleventhal! There are 2 options to push an element to array in pg:

  await db
    .update(table1)
    .set({
      arrayField: sql`${table1.arrayField} || '{c}'`,
    })
    .where(eq(table1.id, someId));

// or

  await db
    .update(table1)
    .set({
      arrayField: sql`array_append(${table1.arrayField}, 'd')`,
    })
    .where(eq(table1.id, someId));

  await db
    .update(table1)
    .set({
      arrayField: sql`${table1.arrayField} || '{c}'`,
    })
    .where(eq(table1.id, someId));

// or

  await db
    .update(table1)
    .set({
      arrayField: sql`array_append(${table1.arrayField}, 'd')`,
    })
    .where(eq(table1.id, someId));

jakeleventhalOP•11mo ago

@solo i just tried both. both give me "TypeError: value.map is not a function"

arrayField: text('arrayField')
    .array()
    .$default(() => [])
    .notNull()

arrayField: text('arrayField')
    .array()
    .$default(() => [])
    .notNull()

Mykhailo•11mo ago

What driver do you use and can you share more code with your query? I just created new db, schema with your arrayField column, insert one row (arrayField by default is []) and then pushed an element using my code above, everything worked out. For this I used postgres-js driver.

jakeleventhalOP•11mo ago

  db.query.marketplaceIntegrations
        .findFirst({ where: sql`${marketplaceIntegrations.data}->>'etsyUserId' = 'etsy-id'` })
        .toSQL()

  db.query.marketplaceIntegrations
        .findFirst({ where: sql`${marketplaceIntegrations.data}->>'etsyUserId' = 'etsy-id'` })
        .toSQL()

TypeError: Cannot read properties of undefined (reading 'map') for jsonb

import { neonConfig } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import ws from 'ws';

import * as schema from './schema';

neonConfig.fetchConnectionCache = true;
neonConfig.webSocketConstructor = ws;

const drizzleClient = (datasourceUrl: string | undefined) => {
  const connectionString =
    datasourceUrl || (process.env.ECS ? process.env.DATABASE_DIRECT_URL : process.env.DATABASE_URL);

  const client = () => drizzle(postgres(connectionString!), { schema });

  if (!connectionString) {
    return null as any as ReturnType<typeof client>;
  }

  return client();
};

declare global {
  // eslint-disable-next-line vars-on-top, no-var
  var localDrizzle: ReturnType<typeof drizzleClient>;
}

export const getDrizzleClient = (url?: string): ReturnType<typeof drizzleClient> => {
  if (process.env.SERVER || url) {
    return drizzleClient(url);
  }

  if (!global.localDrizzle) {
    global.localDrizzle = drizzleClient(url);
  }
  return global.localDrizzle;
};

export const db = getDrizzleClient();
export * from './schema';
export * from 'drizzle-orm';

import { neonConfig } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import ws from 'ws';

import * as schema from './schema';

neonConfig.fetchConnectionCache = true;
neonConfig.webSocketConstructor = ws;

const drizzleClient = (datasourceUrl: string | undefined) => {
  const connectionString =
    datasourceUrl || (process.env.ECS ? process.env.DATABASE_DIRECT_URL : process.env.DATABASE_URL);

  const client = () => drizzle(postgres(connectionString!), { schema });

  if (!connectionString) {
    return null as any as ReturnType<typeof client>;
  }

  return client();
};

declare global {
  // eslint-disable-next-line vars-on-top, no-var
  var localDrizzle: ReturnType<typeof drizzleClient>;
}

export const getDrizzleClient = (url?: string): ReturnType<typeof drizzleClient> => {
  if (process.env.SERVER || url) {
    return drizzleClient(url);
  }

  if (!global.localDrizzle) {
    global.localDrizzle = drizzleClient(url);
  }
  return global.localDrizzle;
};

export const db = getDrizzleClient();
export * from './schema';
export * from 'drizzle-orm';

db.query.marketplaceIntegrations.findFirst({ where: sql${marketplaceIntegrations.id} = 'asdfasdf' }).toSQL() -> fails db.query.marketplaceIntegrations.findFirst({ where: eq(marketplaceIntegrations.id, 'asdfasdf' }).toSQL() -> works seems the issue is with the sql operator

Mykhailo•11mo ago

For me both queries worked out, I used your code above, just with users table, where id has serial type

  const result1 = db.query.users.findFirst({ where: eq(schema.users.id, 1) }).toSQL();
  const result2 = db.query.users.findFirst({ where: sql`${schema.users.id} = 1` }).toSQL();

  console.log('result1', result1);
  console.log('result2', result2);

  const result1 = db.query.users.findFirst({ where: eq(schema.users.id, 1) }).toSQL();
  const result2 = db.query.users.findFirst({ where: sql`${schema.users.id} = 1` }).toSQL();

  console.log('result1', result1);
  console.log('result2', result2);

result1 {
  sql: 'select "id", "name", "arrayField" from "usersTable" "users" where "users"."id" = $1 limit $2',
  params: [ 1, 1 ],
  typings: [ 'none' ]
}
result2 {
  sql: 'select "id", "name", "arrayField" from "usersTable" "users" where "users"."id" = 1 limit $1',
  params: [ 1 ]
}

result1 {
  sql: 'select "id", "name", "arrayField" from "usersTable" "users" where "users"."id" = $1 limit $2',
  params: [ 1, 1 ],
  typings: [ 'none' ]
}
result2 {
  sql: 'select "id", "name", "arrayField" from "usersTable" "users" where "users"."id" = 1 limit $1',
  params: [ 1 ]
}

sorry, I am a bit confused. What is not working for you right now? Cause firstly we were speaking about pushing an element to array and now we are speaking about another query:)

jakeleventhalOP•11mo ago

well, i am trying to narrow down the issue, but it seems the root issue is the sql operator not working properly because any query with the sql operator gives a similar error Cannot read properties of undefined (reading 'map')

TypeError: Cannot read properties of undefined (reading 'map')
    at mapColumnsInSQLToAlias (webpack-internal:///../../../node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected]_pg@_qsji62spejhvnj6ejdenhavnei/node_modules/drizzle-orm/alias.js:106:78)
    at PgDialect.buildRelationalQueryWithoutPK (webpack-internal:///../../../node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected]_pg@_qsji62spejhvnj6ejdenhavnei/node_modules/drizzle-orm/pg-core/dialect.js:888:95)
    at QueryPromise._getQuery (webpack-internal:///../../../node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected]_pg@_qsji62spejhvnj6ejdenhavnei/node_modules/drizzle-orm/pg-core/query-builders/query.js:89:25)
    at QueryPromise._toSQL (webpack-internal:///../../../node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected]_pg@_qsji62spejhvnj6ejdenhavnei/node_modules/drizzle-orm/pg-core/query-builders/query.js:104:24)
    at QueryPromise.toSQL (webpack-internal:///../../../node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected]_pg@_qsji62spejhvnj6ejdenhavnei/node_modules/drizzle-orm/pg-core/query-builders/query.js:109:17)
    at __WEBPACK_DEFAULT_EXPORT__.originIsSelf (webpack-internal:///(api)/./src/pages/api/app-data/get.ts:20:8)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async eval (webpack-internal:///(api)/../../../packages/riptech/utils/src/errors/client/clientErrorWrapper.ts:74:13)

TypeError: Cannot read properties of undefined (reading 'map')
    at mapColumnsInSQLToAlias (webpack-internal:///../../../node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected]_pg@_qsji62spejhvnj6ejdenhavnei/node_modules/drizzle-orm/alias.js:106:78)
    at PgDialect.buildRelationalQueryWithoutPK (webpack-internal:///../../../node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected]_pg@_qsji62spejhvnj6ejdenhavnei/node_modules/drizzle-orm/pg-core/dialect.js:888:95)
    at QueryPromise._getQuery (webpack-internal:///../../../node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected]_pg@_qsji62spejhvnj6ejdenhavnei/node_modules/drizzle-orm/pg-core/query-builders/query.js:89:25)
    at QueryPromise._toSQL (webpack-internal:///../../../node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected]_pg@_qsji62spejhvnj6ejdenhavnei/node_modules/drizzle-orm/pg-core/query-builders/query.js:104:24)
    at QueryPromise.toSQL (webpack-internal:///../../../node_modules/.pnpm/[email protected]_@[email protected]_@[email protected][email protected]_pg@_qsji62spejhvnj6ejdenhavnei/node_modules/drizzle-orm/pg-core/query-builders/query.js:109:17)
    at __WEBPACK_DEFAULT_EXPORT__.originIsSelf (webpack-internal:///(api)/./src/pages/api/app-data/get.ts:20:8)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async eval (webpack-internal:///(api)/../../../packages/riptech/utils/src/errors/client/clientErrorWrapper.ts:74:13)

https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/alias.ts#L110

Mykhailo•11mo ago

do you use backticks with sql operator? db.query.marketplaceIntegrations.findFirst({ where: sql${marketplaceIntegrations.id} = 'asdfasdf' }).toSQL() -> fails here for example there is no `` with sql operator.

jakeleventhalOP•11mo ago

yes it got formatted strangely The root error is https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/alias.ts#L110 when i log query, i get this:

e {
  values: [
    PgCustomColumn {
      name: 'data',
      primary: false,
      notNull: true,
      default: undefined,
      defaultFn: undefined,
      hasDefault: false,
      isUnique: false,
      uniqueName: 'MarketplaceIntegration_data_unique',
      uniqueType: undefined,
      dataType: 'custom',
      columnType: 'PgCustomColumn',
      enumValues: undefined,
      config: [Object],
      table: [PgTable],
      sqlName: 'jsonb',
      mapTo: [Function: toDriver],
      mapFrom: [Function: fromDriver]
    }
  ],
  strings: [ '', "->>'etsyUserId' = 'etsy-id'" ]
}

e {
  values: [
    PgCustomColumn {
      name: 'data',
      primary: false,
      notNull: true,
      default: undefined,
      defaultFn: undefined,
      hasDefault: false,
      isUnique: false,
      uniqueName: 'MarketplaceIntegration_data_unique',
      uniqueType: undefined,
      dataType: 'custom',
      columnType: 'PgCustomColumn',
      enumValues: undefined,
      config: [Object],
      table: [PgTable],
      sqlName: 'jsonb',
      mapTo: [Function: toDriver],
      mapFrom: [Function: fromDriver]
    }
  ],
  strings: [ '', "->>'etsyUserId' = 'etsy-id'" ]
}

note that queryChunks is not a field on query, thus it fails on queryChunks.map seems like a legitimate bug

Mykhailo•11mo ago

I found probably related issue if you are using Next https://discord.com/channels/1043890932593987624/1166089749614825654

jakeleventhalOP•11mo ago

i am using next, but i also get this in an express node server

Mykhailo•11mo ago

your code with my queries

import 'dotenv/config';
import { neonConfig } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from '../drizzle/schema';
import ws from 'ws';
import { eq, sql } from 'drizzle-orm';

neonConfig.fetchConnectionCache = true;
neonConfig.webSocketConstructor = ws;

const drizzleClient = (datasourceUrl: string | undefined) => {
  const connectionString =
    datasourceUrl || (process.env.ECS ? process.env.DATABASE_DIRECT_URL : process.env.DATABASE_URL);

  const client = () => drizzle(postgres(connectionString!), { schema });

  if (!connectionString) {
    return null as any as ReturnType<typeof client>;
  }

  return client();
};

declare global {
  // eslint-disable-next-line vars-on-top, no-var
  var localDrizzle: ReturnType<typeof drizzleClient>;
}

export const getDrizzleClient = (url?: string): ReturnType<typeof drizzleClient> => {
  if (process.env.SERVER || url) {
    return drizzleClient(url);
  }

  if (!global.localDrizzle) {
    global.localDrizzle = drizzleClient(url);
  }
  return global.localDrizzle;
};

export const db = getDrizzleClient(process.env.NEON_DATABASE_URL!);

(async () => {
  const result = await db.query.users.findFirst({
    where: sql`${schema.users.data}->>'etsyUserId' = 'etsy-id'`,
  });

  const sqlResult = db.query.users
    .findFirst({
      where: sql`${schema.users.data}->>'etsyUserId' = 'etsy-id'`,
    })
    .toSQL();

  console.log(result);

  console.log(sqlResult);
})();

export * from '../drizzle/schema';
export * from 'drizzle-orm';

import 'dotenv/config';
import { neonConfig } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from '../drizzle/schema';
import ws from 'ws';
import { eq, sql } from 'drizzle-orm';

neonConfig.fetchConnectionCache = true;
neonConfig.webSocketConstructor = ws;

const drizzleClient = (datasourceUrl: string | undefined) => {
  const connectionString =
    datasourceUrl || (process.env.ECS ? process.env.DATABASE_DIRECT_URL : process.env.DATABASE_URL);

  const client = () => drizzle(postgres(connectionString!), { schema });

  if (!connectionString) {
    return null as any as ReturnType<typeof client>;
  }

  return client();
};

declare global {
  // eslint-disable-next-line vars-on-top, no-var
  var localDrizzle: ReturnType<typeof drizzleClient>;
}

export const getDrizzleClient = (url?: string): ReturnType<typeof drizzleClient> => {
  if (process.env.SERVER || url) {
    return drizzleClient(url);
  }

  if (!global.localDrizzle) {
    global.localDrizzle = drizzleClient(url);
  }
  return global.localDrizzle;
};

export const db = getDrizzleClient(process.env.NEON_DATABASE_URL!);

(async () => {
  const result = await db.query.users.findFirst({
    where: sql`${schema.users.data}->>'etsyUserId' = 'etsy-id'`,
  });

  const sqlResult = db.query.users
    .findFirst({
      where: sql`${schema.users.data}->>'etsyUserId' = 'etsy-id'`,
    })
    .toSQL();

  console.log(result);

  console.log(sqlResult);
})();

export * from '../drizzle/schema';
export * from 'drizzle-orm';

{
  id: 1,
  name: 'test',
  arrayField: [],
  data: { etsyUserId: 'etsy-id' }
}
{
  sql: `select "id", "name", "arrayField", "data" from "usersTable" "users" where "users"."data"->>'etsyUserId' = 'etsy-id' limit $1`,
  params: [ 1 ]
}

{
  id: 1,
  name: 'test',
  arrayField: [],
  data: { etsyUserId: 'etsy-id' }
}
{
  sql: `select "id", "name", "arrayField", "data" from "usersTable" "users" where "users"."data"->>'etsyUserId' = 'etsy-id' limit $1`,
  params: [ 1 ]
}

@Angelelz could you please help to find out what is wrong?

Angelelz•11mo ago

Sure, give me a couple minutes Let's start from the beginning, what is the query that you're trying to run? In SQL

jakeleventhalOP•11mo ago

simplest repro is: "db.query.users.findFirst({ where: sql`${users.id} = 'asdf'` }).toSQL()"

Angelelz•11mo ago

What type of column is users.id?

jakeleventhalOP•11mo ago

    id: text('id')
      .primaryKey()
      .$defaultFn(() => sql`gen_random_uuid()::text`)
      .notNull(),

    id: text('id')
      .primaryKey()
      .$defaultFn(() => sql`gen_random_uuid()::text`)
      .notNull(),

Angelelz•11mo ago

So just a refular text column

jakeleventhalOP•11mo ago

yeah

Angelelz•11mo ago

And you're obviously using pg right? What driver?

jakeleventhalOP•11mo ago

all shown here^

Angelelz•11mo ago

Does it work with another driver?

jakeleventhalOP•11mo ago

  db:
    image: postgres:15.0
    restart: always
    ports:
      - 5432:5432
    environment:
      POSTGRES_PASSWORD: local
    volumes:
      - ./localDBData/localDB:/var/lib/postgresql/data
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U postgres']
      interval: 1s
      retries: 20

  db:
    image: postgres:15.0
    restart: always
    ports:
      - 5432:5432
    environment:
      POSTGRES_PASSWORD: local
    volumes:
      - ./localDBData/localDB:/var/lib/postgresql/data
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U postgres']
      interval: 1s
      retries: 20

my docker image reproduced same issue with node-postgres

Angelelz•11mo ago

Ok, so it seems like the problem is with the postgres driver? Alright one sec

jakeleventhalOP•11mo ago

i linked to the relevant line of failure above

Angelelz•11mo ago

You'll have to put together a reproduction repo

Angelelz•11mo ago

I can't reproduce

jakeleventhalOP•11mo ago

will do ty for trying/looking

Angelelz•11mo ago

Are you sure you're importing sql from drizzle-orm? I think postgres.js has an import with the same name I saw somebody a long time ago with a similar issue, and it was because of this

jakeleventhalOP•11mo ago

ok holy smokes i figured it out

export * from '@prisma/client/appname'
export * from 'drizzle-orm';

export * from '@prisma/client/appname'
export * from 'drizzle-orm';

causes name conflict and prefers the prisma one

Angelelz•11mo ago

Uff, that line hurts my eyes Why would you export * from drizzle?

jakeleventhalOP•11mo ago

this made more sense to do in prisma since that is where all your generated types live. i was mirroring this pattern for drizzle. youre right that it actually doesnt make sense to do that with drizzle though

Gaming

Programming

How to push to an array?