enigmare/v2-crawler

1{"id":"stack-76840558","source":"stackoverflow","questionId":76840558,"title":"Drizzle ORM: Infer type of schema including the relations","tags":["drizzle"],"text":"Title: Drizzle ORM: Infer type of schema including the relations\nTags: drizzle\nSource: Stack Overflow\n\nQuestion:\nI am working on an Express App which uses Drizzle as ORM connected to Postgres Database. When I Infered the type of a specific schema, only the declared columns are added as attributes of the generated type. Is it possible to include the type for the declared relations?\n\nHere is the code for the scenario above:\n\n```\nimport { relations, type InferModel } from \"drizzle-orm\"\nimport { integer, pgTable, primaryKey } from \"drizzle-orm/pg-core\"\n\nimport { privileges } from \"@config/db/schema/privilege\"\nimport { roles, type Role } from \"@config/db/schema/role\"\n\nexport const rolePrivileges = pgTable(\"role_privileges\", {\n roleId: integer(\"role_id\").notNull().references(() => roles.id, { onDelete: \"cascade\" }),\n privilege: privileges(\"privilege\")\n}, (rolePrivileges) => ({\n pk: primaryKey(rolePrivileges.roleId, rolePrivileges.privilege)\n}))\n\nexport const rolePrivilegesRelations = relations(rolePrivileges, ({ one }) => ({\n role: one(roles, {\n fields: [rolePrivileges.roleId],\n references: [roles.id]\n })\n}))\n\nexport type RolePrivilege = InferModel\n```\n\nI tried to manually add the type for the relations by changing the value of type RolePrivilege to the code below and it worked, but I wanted to know if there is a more direct and less tedious way in doing so:\n\n```\nexport type RolePrivilege = InferModel & {\n role: Role\n}\n```\n\n========================================\n\nTop Answer:\nAs of 0.28.3 (August 2022):\n\n`InferModel` is now deprecated in favour of `InferSelectModel` and `InferInsertModel`.\n\nYou can update your code as follows, depending on whether your use case for the type is selecting or inserting:\n\n```\nimport { relations, type InferSelectModel } from \"drizzle-orm\"\n\n...\n\nexport type RolePrivilege = InferSelectModel & {\n role: Role\n}\n```\n\nPlease see the updated documentation for the Type API.\n\n========================================\n\nCode:\n```js\nimport { relations, type InferModel } from \"drizzle-orm\"\nimport { integer, pgTable, primaryKey } from \"drizzle-orm/pg-core\"\n\nimport { privileges } from \"@config/db/schema/privilege\"\nimport { roles, type Role } from \"@config/db/schema/role\"\n\nexport const rolePrivileges = pgTable(\"role_privileges\", {\n   roleId: integer(\"role_id\").notNull().references(() => roles.id, { onDelete: \"cascade\" }),\n   privilege: privileges(\"privilege\")\n}, (rolePrivileges) => ({\n   pk: primaryKey(rolePrivileges.roleId, rolePrivileges.privilege)\n}))\n\nexport const rolePrivilegesRelations = relations(rolePrivileges, ({ one }) => ({\n   role: one(roles, {\n      fields: [rolePrivileges.roleId],\n      references: [roles.id]\n   })\n}))\n\nexport type RolePrivilege = InferModel<typeof rolePrivileges>\n```\n\n```js\nexport type RolePrivilege = InferModel<typeof rolePrivileges> & {\n   role: Role\n}\n```\n\n```text\nimport { relations, type InferSelectModel } from \"drizzle-orm\"\n\n...\n\nexport type RolePrivilege = InferSelectModel<typeof rolePrivileges> & {\n   role: Role\n}\n```\n\n```text\nInferModel\n```\n\n```text\nInferSelectModel\n```\n\n```text\nInferInsertModel\n```\n\n```text\nexport type Item = typeof items.$inferSelect;\n```\n\n========================================\n\nComments:\n- Hi Man! how did you use the infer after? I'm trying to do something like export const eyeSchema = pgTable('eye', { id: defaults.id, value: varchar('value', { length: 256 }).notNull(), }); export type Eye = typeof eyeSchema.$inferSelect; // return type when queried const a: Eye[] = await db.query.eye.findMany(); const b: Eye[] = await db.select().from(eyeSchema); But both are failing in compiling\n- @JulianMendez can you tell me where did you get the \"defaults.id\" value for your column \"id\". Normally, in Drizzle \"id\" columns are usually defined of type \"serial or bigserial\" for auto-increments (e.g. serial(\"id\").primaryKey()) or \"uuid\" (e.g. uuid(\"id\").defaultRandom().primaryKey()).\n- Also check if you defined your schema in your drizzle configuration. Like the following... import { drizzle } from \"drizzle-orm/node-postgres\"; import { Pool } from \"pg\"; import * as schema from \"@/config/db/schema\"; import { databaseUrl } from \"@/config/env\"; const pool = new Pool({ connectionString: databaseUrl, }); export const db = drizzle(pool, { schema, }); You might also want to reply with the message from the error that occured. So that we can narrow down the cause of the error. :)\n- Did you have to manually do that or did it get generated somehow?\n- No, he didn't make manually,,, drizzle provide this infer option to get table type.","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":7,"totalLines":129,"estimatedTokens":1139}}2{"id":"stack-78329576","source":"stackoverflow","questionId":78329576,"title":"How to declare self-referencing foreign key with Drizzle ORM","tags":["typescript","postgresql","orm","node-postgres","drizzle"],"text":"Title: How to declare self-referencing foreign key with Drizzle ORM\nTags: typescript, postgresql, orm, node-postgres, drizzle\nSource: Stack Overflow\n\nQuestion:\nIn a Typescript project, declaring a table using Drizzle on postgres-node as follows:\n\n```\nconst contractsTable = pgTable(\"contracts\", {\n id: serial(\"id\").primaryKey(),\n underlyingId: integer(\"underlying_id\").references(() => contractsTable.id),\n //...\n})\n```\n\nresults in the following Typescript error:\n\n`'contractsTable' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.`\n\nMaintaining a separate type would be impractical because the schema is massive and is subject to change.\n\nIs there a way to get Typescript to infer the correct type? I failed to do this through aliasing or casting the PgIntegerBuilderInitial type.\n\nWe also define a relationship as follows:\n\n```\nconst contractsRelations = relations(\n contractsTable,\n ({ one, many }) => ({\n underlying: one(contractsTable, {\n fields: [contractsTable.underlyingId],\n references: [contractsTable.id],\n }),\n //...\n })\n);\n```\n\nbut I do need the database level constraint. Any ideas?\n\n========================================\n\nTop Answer:\nAccording to the [BUG]: Self reference foreign key break relational query types #1607, you can solved like this:\n\n```\nexport const contractsTable = pgTable(\"contracts\", {\n id: serial(\"id\").primaryKey(),\n underlyingId: integer(\"underlying_id\").references((): AnyPgColumn => contractsTable.id),\n})\n\nexport const contractsRelations = relations(\n contractsTable,\n ({ one, many }) => ({\n underlying: one(contractsTable, {\n fields: [contractsTable.underlyingId],\n references: [contractsTable.id],\n }),\n })\n);\n```\n\n========================================\n\nCode:\n```js\nconst contractsTable = pgTable(\"contracts\", {\n    id: serial(\"id\").primaryKey(),\n    underlyingId: integer(\"underlying_id\").references(() => contractsTable.id),\n    //...\n})\n```\n\n```text\nconst contractsRelations = relations(\n    contractsTable,\n    ({ one, many }) => ({\n        underlying: one(contractsTable, {\n            fields: [contractsTable.underlyingId],\n            references: [contractsTable.id],\n        }),\n        //...\n    })\n);\n```\n\n```text\n'contractsTable' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.\n```\n\n```js\nexport const contractsTable = pgTable(\n  \"contracts\",\n  {\n    id: serial(\"id\").primaryKey(),\n    underlyingId: integer(\"underlying_id\"),\n  },\n  (table) => {\n    return {\n      parentReference: foreignKey({\n        columns: [table.underlyingId],\n        foreignColumns: [table.id],\n        name: \"contracts_underlying_id_fkey\",\n      }),\n    };\n  }\n);\n```\n\n```text\ndrizzle-kit 0.21.4\n   drizzle-orm 0.30.10\n```\n\n```text\nexport const contractsTable = pgTable(\"contracts\", {\n    id: serial(\"id\").primaryKey(),\n    underlyingId: integer(\"underlying_id\").references((): AnyPgColumn => contractsTable.id),\n})\n\nexport const contractsRelations = relations(\n    contractsTable,\n    ({ one, many }) => ({\n        underlying: one(contractsTable, {\n            fields: [contractsTable.underlyingId],\n            references: [contractsTable.id],\n        }),\n    })\n);\n```\n\n```js\nimport { serial, text, integer, foreignKey, pgTable, AnyPgColumn } from \"drizzle-orm/pg-core\";\n\nexport const user = pgTable(\"user\", {\n  id: serial(\"id\"),\n  name: text(\"name\"),\n  parentId: integer(\"parent_id\").references((): AnyPgColumn => user.id)\n});\n\n// or\nexport const user = pgTable(\"user\", {\n  id: serial(\"id\"),\n  name: text(\"name\"),\n  parentId: integer(\"parent_id\"),\n}, (table) => [\n  foreignKey({\n    columns: [table.parentId],\n    foreignColumns: [table.id],\n    name: \"custom_fk\"\n  })\n]);\n```\n\n========================================\n\nComments:\n- See Drizzle ORM issue: Self reference foreign key break relational query types #1607\n- i think you just need to define the column types? so `underlyingId: integer(\"underlying_id\").references((): PgColumn => contractsTable.id),`\n- This is the one, this is the one you want!\n- This Approach helped me","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":7,"totalLines":161,"estimatedTokens":1035}}3{"id":"stack-5830914","source":"stackoverflow","questionId":5830914,"title":"Real-time statistics: MySQL(/Drizzle) or MongoDB?","tags":["mysql","mongodb","statistics","drizzle"],"text":"Title: Real-time statistics: MySQL(/Drizzle) or MongoDB?\nTags: mysql, mongodb, statistics, drizzle\nSource: Stack Overflow\n\nQuestion:\nWe are working on a project that will feature real-time statistics of some actions (e.g. clicks).\nOn every click, we will log information like date, age and gender (these come from Facebook), location, etc.\n\nWe are discussing about the best place to store these information and use them for real-time stats. We will display aggregate statistics: for example, number of clicks, number of clicks made by male/female, number of clicks divided by age groups (e.g. 18-24, 24-30...).\n\nSince on the site we are using MongoDB everywhere, my colleague thought we should store statistics inside it as well.\nI, however, would prefer a SQL-based database for this task, like MySQL (or maybe Drizzle), because I believe SQL is better when doing operations like data aggregation. Although there's the overhead of parsing the SQL, I think MySQL/Drizzle may actually be faster than No-SQL databases here. And inserts are not slow too, when using INSERT DELAYED queries.\n\nPlease note that we do not need to perform JOINS or collect data from multiple tables/collections. Thus, we don't care if the database is different.\nHowever, we do care about scalability and reliability. We are building something that will (hopefully) become very big, and we've designed every single line of code with scalability in mind.\n\nWhat do you think about this?\nIs there any reason to prefer MongoDB over MySQL/Drizzle for this? Or is it indifferent?\nWhich one would you use, if you were us?\n\nThank you,\nAlessandro\n\n========================================\n\nTop Answer:\nMongoDB is great for this kind of thing and will certainly be faster than MySQL will be, although don't underestimate how powerful MySQL can be - many companies have built analytics tools with it.\n\nHave a look at this presentation by Patrick Stokes of BuddyMedia on how they used MongoDB for their analytic system.\n\nhttp://www.slideshare.net/pstokes2/social-analytics-with-mongodb\n\n========================================\n\nCode:\n```text\n{\n  date: \"20110430\",\n  gender: \"M\",\n  age: 1, // 1 is probably a bucket\n  impression_hour: [ 100, 50, ...], // 24 of these\n  impression_minute: [ 2, 5, 19, 8, ... ], // 1440 of these\n  clicks_hour: [ 10, 2, ... ],\n  ...\n}\n```\n\n```text\n_id\n```\n\n```text\n{ $inc : { clicks_hour.0 : 1 } }\n```\n\n========================================\n\nComments:\n- Just as a suggestion -> have a look at RddTool mrtg.org/rrdtool it might be usefull\n- Thanks, but this is not the kind of statistics we are looking for! We will have something more like YouTube's video statistics...Something like: reelseo.com/wp-content/uploads/2010/06/youtube-video-stats.p&zwnj;&#8203;ng\n- Actually, I'm the one who was on the \"MySQL-side\" :) My colleague was the one who voted for MongoDb... Anyway, thanks for those slide. I ended up understanding we are doing it wrong! We will need to re-think the structure of our collection.\n- how would you aggregate that then for stats?\n- The best way to aggregate a large amount of data is to use some form of Map / Reduce framework (think Hadoop). If you are tracking something like a click, you would first validate the click, then you would ping the real-time counters, then you would pass off the click data to the M/R system for full aggregation. This real-time stuff only works if you know what you want in advance.","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":3,"totalLines":63,"estimatedTokens":858}}4{"id":"stack-76399047","source":"stackoverflow","questionId":76399047,"title":"How to represent 'bytea' datatype from pg inside new drizzle orm?","tags":["postgresql","bytea","drizzle"],"text":"Title: How to represent 'bytea' datatype from pg inside new drizzle orm?\nTags: postgresql, bytea, drizzle\nSource: Stack Overflow\n\nQuestion:\nIm trying to learn new drizzle orm for node js, and im here trying to create a small auth database to see how the orm works.\n\nusing 'pnpm drizzle-kit generate:pg' i generated a schema from a pg database, but bytea datatype was not parsed to ts. as the drizzle is new a orm, **the doc dosen't have solution for my problem. i needed a way to represent bytea pg datatype inside drizzle orm.**\n\nhere is the schema code generated by drizzle kit.\n\n```\nexport const user = pgTable(\n \"user\",\n {\n id: uuid(\"id\").primaryKey().notNull(),\n firstname: varchar(\"firstname\", { length: 35 }).notNull(),\n middlename: varchar(\"middlename\", { length: 35 }),\n lastname: varchar(\"lastname\", { length: 35 }).notNull(),\n // TODO: failed to parse database type 'bytea'\n passphrase: unknown(\"passphrase\").notNull(),\n // TODO: failed to parse database type 'bytea'\n salt: unknown(\"salt\").notNull(),\n email: varchar(\"email\", { length: 50 }).notNull(),\n },\n (table) => {\n return {\n email: uniqueIndex(\"user_email\").on(table.email),\n };\n }\n);\n```\n\n========================================\n\nCode:\n```typescript\nexport const user = pgTable(\n  \"user\",\n  {\n    id: uuid(\"id\").primaryKey().notNull(),\n    firstname: varchar(\"firstname\", { length: 35 }).notNull(),\n    middlename: varchar(\"middlename\", { length: 35 }),\n    lastname: varchar(\"lastname\", { length: 35 }).notNull(),\n    // TODO: failed to parse database type 'bytea'\n    passphrase: unknown(\"passphrase\").notNull(),\n    // TODO: failed to parse database type 'bytea'\n    salt: unknown(\"salt\").notNull(),\n    email: varchar(\"email\", { length: 50 }).notNull(),\n  },\n  (table) => {\n    return {\n      email: uniqueIndex(\"user_email\").on(table.email),\n    };\n  }\n);\n```\n\n```js\nconst bytea = customType<{ data: Buffer; notNull: false; default: false }>({\n  dataType() {\n    return \"bytea\";\n  },\n});\n```\n\n```js\nconst bytea = customType<{ data: string; notNull: false; default: false }>({\n  dataType() {\n    return \"bytea\";\n  },\n  toDriver(val) {\n    let newVal = val;\n    if (val.startsWith(\"0x\")) {\n      newVal = val.slice(2);\n    }\n\n    return Buffer.from(newVal, \"hex\");\n  },\n  fromDriver(val) {\n    return val.toString(\"hex\");\n  },\n});\n```\n\n========================================\n\nComments:\n- what types of `passphrase` and `salt` do you have in the database? It seems like those are some types drizzle doesn't have support for\n- Relevant issue: github.com/drizzle-team/drizzle-orm/issues/298","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":3,"totalLines":90,"estimatedTokens":641}}5{"id":"stack-77593688","source":"stackoverflow","questionId":77593688,"title":"Unable to Load .env Database URL in Drizzle-Kit's drizzle.config.ts with Next.js 14","tags":["next.js","drizzle"],"text":"Title: Unable to Load .env Database URL in Drizzle-Kit's drizzle.config.ts with Next.js 14\nTags: next.js, drizzle\nSource: Stack Overflow\n\nQuestion:\nI am currently leveraging Drizzle-Kit to manage my database tables, specifically using `drizzle-kit push:pg`. However, in order to execute this command, it necessitates a `drizzle.config.ts` file containing essential configurations like schema, output, and credentials.\n\nTraditionally, I rely on `.env` files for storing sensitive credentials. But when integrating with Next.js 14, `drizzle-kit push:pg` seems unable to access the database URL stored within the `.env` file. It appears that the environment variable isn't being picked up.\n\nConsidering security best practices and the risk associated with storing credentials directly in the `drizzle.config.ts` file, especially when dealing with version control (Git), I'm reluctant to include these sensitive details there. This approach also requires managing credentials in two separate places, which is far from ideal.\n\nIs there a recommended method or workaround to enable `drizzle.config.ts` to access the `.env` file for the database URL while utilizing Drizzle Kit in conjunction with Next.js 14? I'm aiming to maintain a secure approach to handling credentials without compromising on functionality. Any insights or alternative strategies would be greatly appreciated.\n\n========================================\n\nTop Answer:\nThis is already answered but just adding that since you are using nextjs there is a built in way using their @next/env package. Does the same thing as dotenv but once less dependancy.\n\nSource:\nhttps://nextjs.org/docs/app/building-your-application/configuring/environment-variables#loading-environment-variables-with-nextenv\n\n========================================\n\nCode:\n```text\ndrizzle-kit push:pg\n```\n\n```text\ndrizzle.config.ts\n```\n\n```text\n.env\n```\n\n```text\ndrizzle-kit push:pg\n```\n\n```text\n.env\n```\n\n```text\ndrizzle.config.ts\n```\n\n```text\ndrizzle.config.ts\n```\n\n```text\n.env\n```\n\n```js\nimport type { Config } from \"drizzle-kit\";\nimport dotenv from \"dotenv\";\n\ndotenv.config({\n  path: \".env.local\",\n});\n\nexport default {\n  schema: \"src/db/schema/index.ts\",\n  out: \"src/db/migrations\",\n  driver: \"mysql2\",\n  dbCredentials: {\n    uri: process.env.DATABASE_URL!,\n  }\n} satisfies Config\n```\n\n```text\ndrizzle-kit generate\n```\n\n```text\nnode --env-file=.env.development ./node_modules/drizzle-kit/bin.cjs generate\n```\n\n```json\n\"scripts\": {\n  \"generate-migrations\": \"node --env-file=.env.development ./node_modules/drizzle-kit/bin.cjs generate\"\n},\n```\n\n```text\nv20.6.0+\n```\n\n```text\n--env-file-if-exists\n```\n\n```text\nv22.9.0+\n```\n\n========================================\n\nComments:\n- What drizzle-kit version do you have?\n- should be version 0.20.6\n- But what do you do when it is time to run the migration in production?\n- The environment variables hold the dbCredentials for which ever environment you are running the app in (dev, test, prod etc). The migration files are added to the repo, so it should just work the same way regardless of the current environment. Have I misunderstood the question?","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":15,"totalLines":107,"estimatedTokens":783}}6{"id":"stack-76082778","source":"stackoverflow","questionId":76082778,"title":"Drizzle-Orm: How do you insert in a parent and child table?","tags":["sql","postgresql","drizzle"],"text":"Title: Drizzle-Orm: How do you insert in a parent and child table?\nTags: sql, postgresql, drizzle\nSource: Stack Overflow\n\nQuestion:\nNew to SQL... how does one insert into a parent table and a child one?\n\nAssuming the following tables\n\n```\nimport { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';\n\nexport const users = pgTable('user', {\n id: serial('id').primaryKey(),\n name: text('name'),\n});\n\nexport const tokens = pgTable('token', {\n id: serial('id').primaryKey(),\n userId: text(\"userId\").notNull().references(() => users.id, { onDelete: \"cascade\" }),\n token: string(\"token\"),\n});\n```\n\nTo create a new user with a token... I think manually looks like this...\n\n```\nconst newUser = await db.insert(users).values({name: \"Billy\"}).returning();\nconst token = await db.insert(token).values({userId: newUser.id, token: \"123\"}).returning();\n```\n\nIs this the proper way or is this transaction supposed to be using a view or transactions?\n\n========================================\n\nCode:\n```text\nimport { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';\n\nexport const users = pgTable('user', {\n    id: serial('id').primaryKey(),\n    name: text('name'),\n});\n\nexport const tokens = pgTable('token', {\n    id: serial('id').primaryKey(),\n        userId: text(\"userId\").notNull().references(() => users.id, { onDelete: \"cascade\" }),\n        token: string(\"token\"),\n});\n```\n\n```js\nconst newUser = await db.insert(users).values({name: \"Billy\"}).returning();\nconst token = await db.insert(token).values({userId: newUser.id, token: \"123\"}).returning();\n```\n\n```js\nawait db.transaction(async (tx) => {\n  const result = await tx.insert(users).values({ name: 'billy' }).returning({ userId: users.id });\n  \n  await tx.insert(tokens).values({ userId: result[0].userId, token: '123' })\n  \n});\n```\n\n```text\nid\n```\n\n```text\ntokens\n```\n\n========================================\n\nComments:\n- How about with CTEs?: `db.execute(sql``WITH sq AS (INSERT INTO users (name) VALUES 'Billy' RETURNING id) INSERT INTO tokens (userId, token) VALUES ((SELECT id FROM sq), '123')``)`\n- I don't think 'returning' is available when using Mysql.\n- Not sure because I don’t use MySQL, but the question is for postgres.\n- You can use the `insertId` from `(await tx.insert(table).values(values)).insertId`. But I don't know not it works, it is not documented...\n- I think acording to the documentation here: orm.drizzle.team/docs/transactions the correct syntax would be `await tx.insert(users)...`. Instead of `await db.insert(users)...` Not sure as I did not see any insert example, but I think the pattern would\n- Yeah you’re right good catch.","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":5,"totalLines":82,"estimatedTokens":657}}7{"id":"stack-76503705","source":"stackoverflow","questionId":76503705,"title":"Drizzle Columns Schema JSON stores my JSON as text in PostgreSQL","tags":["json","typescript","postgresql","orm","drizzle"],"text":"Title: Drizzle Columns Schema JSON stores my JSON as text in PostgreSQL\nTags: json, typescript, postgresql, orm, drizzle\nSource: Stack Overflow\n\nQuestion:\nI'm using Drizzle for a Typescript backend that serves a few API endpoints. My database is Postgresql and there's a JSON column.\n\n```\nexport const transactions = pgTable(\"transactions\", {\n id: serial(\"id\").primaryKey(),\n my_json: json('my_json')\n});\n```\n\nI if try to store `{\"hello\":\"world\"}` from a supabase table editor, this is what I'm getting:\n\nhttps://i.sstatic.net/cxxIw.png\n\nIf I try to insert my {\"hello\":\"world\"} using TS/Drizzle, this is what I'm getting:\n\nhttps://i.sstatic.net/VI11L.png\n\nSomehow I can't find a setting or a way to make drizzle store it as a real JSON object and not a string version of it.\n\n========================================\n\nTop Answer:\nI add a solution from the issue: https://github.com/drizzle-team/drizzle-orm/issues/724#issuecomment-1650670298\n\nReplace with a `::json` whenever you write or update\n\n```\nconst product = await tx\n .insert(products)\n .values({\n entityId: input.entityId,\n eventType: input.eventType,\n // payload: input.payload, Instead use 👇\n payload: sql`${input.payload}::json`, // or ::jsonb\n })\n```\n\n========================================\n\nCode:\n```text\nexport const transactions = pgTable(\"transactions\", {\n    id: serial(\"id\").primaryKey(),\n    my_json: json('my_json')\n});\n```\n\n```text\n{\"hello\":\"world\"}\n```\n\n```text\nconst statement = sql`\n        INSERT INTO wikidata_article (wikidata_id, category, grade, en_raw_length, en_url_title, labels, sitelinks)\n        VALUES (${articleDetails.wikiDataId}, ${articleDetails.category}, ${articleDetails.grade}, ${articleDetails.enBytes}, ${articleDetails.enUrlTitle}, ${articleDetails.labels}, ${articleDetails.sitelinks})\n        ON CONFLICT (wikidata_id) DO UPDATE\n        SET \n            category = ${articleDetails.category},\n            grade = ${articleDetails.grade},    \n            en_raw_length = ${articleDetails.enBytes},\n            en_url_title = ${articleDetails.enUrlTitle},\n            labels = ${articleDetails.labels},\n            sitelinks = ${articleDetails.sitelinks}\n        `;\n        await db.execute(statement);\n```\n\n```text\ndb.execute(sql`INSERT INTO table(foo_id, foo_json)...VALUES(123,${yourObject})`)\n```\n\n```js\nimport type { SQL } from 'drizzle-orm';\nimport type { PgColumn } from 'drizzle-orm/pg-core';\n\nexport const sqlJSON = <TC extends PgColumn, TD = Exclude<TC['default'], SQL<unknown>>>(column: TC, data: TD) => {\n  return sql`${JSON.stringify(data)}::json`;\n};\n\n\n// usage:\n\nconst MyTable = pgTable('my_table', {\n  jsonData: json('json_data').$type<{ data: string; }>\n})\n\nsqlJSON(MyTable.jsonData, { data: 'to make JSON' })\n```\n\n```text\nconst product = await tx\n  .insert(products)\n  .values({\n    entityId: input.entityId,\n    eventType: input.eventType,\n    // payload: input.payload, Instead use 👇\n    payload: sql`${input.payload}::json`, // or ::jsonb\n  })\n```\n\n```text\n::json\n```\n\n========================================\n\nComments:\n- Looks like it's a bug. There is an issue open on the Drizzle ORM Github github.com/drizzle-team/drizzle-orm/issues/724","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":7,"totalLines":113,"estimatedTokens":792}}8{"id":"stack-77856818","source":"stackoverflow","questionId":77856818,"title":"Get table name from a PGTable","tags":["typescript","postgresql","drizzle"],"text":"Title: Get table name from a PGTable\nTags: typescript, postgresql, drizzle\nSource: Stack Overflow\n\nQuestion:\nI am starting out a new project with Drizzle ORM. Starting out with some assumptions:\n\nThe primary keys on all the tables in my database will be uniformly uniformly typed and they will all be called `id`.\n\nAll the foreign keys will be in the form of `table_name_id` in the database and the model property will always be `tableNameId`.\n\nSince I can make these assumptions, I thought I could significantly reduce the amount of boilerplate required for specifying foreign keys:\n\n```\nimport {\n type PgColumn,\n type PgTableWithColumns,\n pgTableCreator,\n serial,\n} from \"drizzle-orm/pg-core\";\n\nimport { camelCase, snakeCase } from \"lodash\";\n\nexport const pgTable = pgTableCreator((name) => {\n return `some-app_${name}`;\n});\n\nexport function id() {\n return serial(\"id\").primaryKey();\n}\n\ntype TableWithId = PgTableWithColumns;\n\nexport function fk(...relations: (TableWithId | TableWithId[])[]) {\n return Object.fromEntries(\n relations.flatMap((entry) => {\n const isOptional = Array.isArray(entry);\n const tables = isOptional ? entry : [entry];\n\n return tables.map((table) => {\n const columnName = `${snakeCase(`)}_id`;\n const propertyName = `${camelCase(table._.name)}Id`;\n const column = bigint(columnName, { mode: \"number\" }).references(() => {\n return table.id;\n });\n\n return [propertyName, isOptional ? column : column.notNull()];\n });\n }),\n );\n}\n```\n\nThis code is intended to be used as such:\n\n```\nexport const contact = pgTable(\"contacts\", {\n id: id(),\n firstName: varchar(\"first_name\", { length: 128 }).notNull(),\n lastName: varchar(\"last_name\", { length: 128 }).notNull(),\n ...fk(phone, [email], [address]),\n ...timestamps(),\n});\n```\n\nHere a relation to the phone and email tables are mandatory (not null), but the relation to the email and address are optional.\n\nThe issue arises with accessing the name for a given `PgTable`. When I inspect the type of `contact` in my editor I see that they are type hinted with `PgTableWithColumns`. Which is what I used in the type hint to the `fk` function.\n\nAs a result as far as Typescript is concerned the function is correct. However when I run the code, I get the error:\n\n```\nTypeError: Cannot read properties of undefined (reading 'name')\n```\n\nThis is referring to the expression: `table._.name`. When I log out table, I see that it does not match the type. The underscore property is infact missing, but the name provided to the table is present in a symbol key `Symbol(drizzle:BaseName)`.\n\nHowever, I am not sure how to access this property as I have not been able to import the symbol from anywhere.\n\nIt seems like the types for this package are broken and I might end up filing a bug with drizzle. But I kind of want to get this to work.\n\nAny ideas?\n\n========================================\n\nTop Answer:\nSomething like this worked for me\n\n\r\n\r\n\n```\nimport { ExtractTablesWithRelations } from \"drizzle-orm\";\nimport * as schema from './schema.ts'\n\nexport type RawTableNames = keyof ExtractTablesWithRelations;\n```\n\n\r\n\r\n\r\n\nWhere `schema` is the same object passed to your drizzle db init.\n\n========================================\n\nCode:\n```text\nimport {\n  type PgColumn,\n  type PgTableWithColumns,\n  pgTableCreator,\n  serial,\n} from \"drizzle-orm/pg-core\";\n\nimport { camelCase, snakeCase } from \"lodash\";\n\nexport const pgTable = pgTableCreator((name) => {\n  return `some-app_${name}`;\n});\n\nexport function id() {\n  return serial(\"id\").primaryKey();\n}\n\ntype TableWithId = PgTableWithColumns<{\n  name: string;\n  schema: string | undefined;\n  dialect: \"pg\";\n  columns: {\n    id: PgColumn;\n  };\n}>;\n\nexport function fk(...relations: (TableWithId | TableWithId[])[]) {\n  return Object.fromEntries(\n    relations.flatMap((entry) => {\n      const isOptional = Array.isArray(entry);\n      const tables = isOptional ? entry : [entry];\n\n      return tables.map((table) => {\n        const columnName = `${snakeCase(`)}_id`;\n        const propertyName = `${camelCase(table._.name)}Id`;\n        const column = bigint(columnName, { mode: \"number\" }).references(() => {\n          return table.id;\n        });\n\n        return [propertyName, isOptional ? column : column.notNull()];\n      });\n    }),\n  );\n}\n```\n\n```text\nexport const contact = pgTable(\"contacts\", {\n  id: id(),\n  firstName: varchar(\"first_name\", { length: 128 }).notNull(),\n  lastName: varchar(\"last_name\", { length: 128 }).notNull(),\n  ...fk(phone, [email], [address]),\n  ...timestamps(),\n});\n```\n\n```text\nTypeError: Cannot read properties of undefined (reading 'name')\n```\n\n```text\nid\n```\n\n```text\ntable_name_id\n```\n\n```text\ntableNameId\n```\n\n```text\nPgTable\n```\n\n```text\ncontact\n```\n\n```text\nPgTableWithColumns\n```\n\n```text\nfk\n```\n\n```text\ntable._.name\n```\n\n```text\nSymbol(drizzle:BaseName)\n```\n\n```text\nimport { getTableName } from \"drizzle-orm\";\nconst tableName = getTableName(table);\n```\n\n```text\ngetTableName\n```\n\n```js\nimport { ExtractTablesWithRelations } from \"drizzle-orm\";\nimport * as schema from './schema.ts'\n\nexport type RawTableNames = keyof ExtractTablesWithRelations<typeof schema>;\n```\n\n```text\nschema\n```\n\n========================================\n\nComments:\n- Where in the documentation did you find this?","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":16,"totalLines":228,"estimatedTokens":1308}}9{"id":"stack-76737007","source":"stackoverflow","questionId":76737007,"title":"Drizzle ORM not support Insert Returning","tags":["mysql","typescript","sql-insert","drizzle"],"text":"Title: Drizzle ORM not support Insert Returning\nTags: mysql, typescript, sql-insert, drizzle\nSource: Stack Overflow\n\nQuestion:\nI have a question while working with Drizzle ORM and MySQL.\n\nCurrently, Drizzle ORM does not provide insert returning function for MySQL.\nCheck this link.\n\nMy website adds users to the database and issues JWT tokens when they sign up. Since the payload of the JWT must include the id of the newly added user, it is essential to know the id of the user that was just added.\n\nIn this case, how do I get the id which is an auto-incrementing integer for the record I added?\n\n========================================\n\nTop Answer:\nDrizzle ORM doesn't support the returning function for MySQL, but it does have a way of giving you the auto-incremented ID by using the insertId property\n\n**Example:**\n\n```\nconst userTable = await db.insert(user).values({ name: \"Jorge\"})\n\nconst walletTable = await db.insert(wallet).values({ userId: userTable.insertId)\n```\n\n========================================\n\nCode:\n```text\nawait this.db.insert(users).values({\"login\": \"xxxx\"})\n```\n\n```text\n[\n  ResultSetHeader {\n    fieldCount: 0,\n    affectedRows: 1,\n    insertId: 33,\n    info: '',\n    serverStatus: 2,\n    warningStatus: 0,\n    changedRows: 0\n  },\n  undefined\n]\n```\n\n```text\nconst result = await db.insert(users).values([{ name: 'John' }, { name: 'John1' }]).$returningId(); \n//    ^? { id: number }[]\n```\n\n```text\nINSERT INTO users (username, email, password)  VALUES ('new_user', 'new_user@example.com', 'hashed_password'); SELECT LAST_INSERT_ID() as id;\n```\n\n```text\nconst userTable = await db.insert(user).values({ name: \"Jorge\"})\n\n\nconst walletTable = await db.insert(wallet).values({ userId: userTable.insertId)\n```\n\n```text\nconst newUser = await db.insert(schema.users).values({ email: email, username: username, password: password })\nreturn newUser[0].insertId;\n```\n\n```text\n0.30.7\n```\n\n```text\nawait db.insert(users).values({ name: \"Dan\" }).returning();\n\n// partial return\nawait db.insert(users).values({ name: \"Partial Dan\" }).returning({ insertedId: users.id });\n```\n\n```text\nconst newUser = await db.insert(users).values({ name: \"Partial Dan\" }).returning({ insertedId: users.id });\n\nreturn newUser[0];\n```\n\n```text\nconst newUser = await db.insert(schema.users).values({ email: email, username: username, password: password })\nreturn newUser[0].insertId;\n```\n\n```text\ninsertId\n```\n\n========================================\n\nComments:\n- Plnatescale is mysql compatible (more or less), but is not mysql. Btw, mysql does not support the returning close, so not such a surprise that an ORM using it cannot either.\n- This is not problem with PlanetScale. If you check the link you will see that Drizzle ORM currently does not provide insert return functionality for MySQL.\n- mysql doesn't support that in bthe first palce, so an ORM that mimics MySQL functions can't do it either. Mariadb supports it, so maybe you will find there more function like you need\n- I have been using Prisma until now, so I didn't know that MySQL does not support insert return. Thank you for your kind and helpful information!\n- This is more reliable than executing a separate query to get the last insert id\n- `returning` it works for me","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":11,"totalLines":105,"estimatedTokens":810}}10{"id":"stack-78784936","source":"stackoverflow","questionId":78784936,"title":"Does drizzle ORM auto rollbacks when there is an exception or do I need to call tx.rollback?","tags":["typescript","drizzle","drizzle-orm"],"text":"Title: Does drizzle ORM auto rollbacks when there is an exception or do I need to call tx.rollback?\nTags: typescript, drizzle, drizzle-orm\nSource: Stack Overflow\n\nQuestion:\nI'm building an API with nestjs and drizzle. When there is an error and I need to early return and respond the Request with an HTTP error, I do:\n\n```\nthrow new HttpException('message', code);\n```\n\nand nest will catch it and send the http response. Drizzles documentation do not mention auto rollback and only shows rolling back a transaction by calling `tx.rollback()`.\n\nWhen I want to rollback a transaction is it enough to throw an `HttpException` or do I need to first explicitly call `tx.rollback()` and then throw the exception?\n\n========================================\n\nCode:\n```text\nthrow new HttpException('message', code);\n```\n\n```text\ntx.rollback()\n```\n\n```text\nHttpException\n```\n\n```text\ntx.rollback()\n```\n\n```text\npg\n```\n\n```text\ntx.rollback\n```\n\n```text\ntry..catch\n```\n\n```text\ntx.rollback\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":8,"totalLines":49,"estimatedTokens":245}}11{"id":"stack-79158227","source":"stackoverflow","questionId":79158227,"title":"pgTable from drizzle relations many to many is deprecated","tags":["reactjs","next.js","drizzle","drizzle-orm"],"text":"Title: pgTable from drizzle relations many to many is deprecated\nTags: reactjs, next.js, drizzle, drizzle-orm\nSource: Stack Overflow\n\nQuestion:\nFollowing the docs to create a many to many relation it suggests to create a junction or join table. The ts deprecation error is below.\n\n@deprecated — This overload is deprecated. Use the other method overload instead.\n\nThe suggested code is as follows:\n\n```\nexport const usersToGroups = pgTable(\n 'users_to_groups',\n {\n userId: integer('user_id')\n .notNull()\n .references(() => users.id),\n groupId: integer('group_id')\n .notNull()\n .references(() => groups.id),\n },\n (t) => ({\n pk: primaryKey({ columns: [t.userId, t.groupId] }),\n }),\n);\n```\n\nThe error occurs when trying to generate the primary key.\n\nI followed all the docs looking for a suggestion. I tried delving into the generated types to find the error too.\n\n========================================\n\nCode:\n```text\nexport const usersToGroups = pgTable(\n  'users_to_groups',\n  {\n    userId: integer('user_id')\n      .notNull()\n      .references(() => users.id),\n    groupId: integer('group_id')\n      .notNull()\n      .references(() => groups.id),\n  },\n  (t) => ({\n    pk: primaryKey({ columns: [t.userId, t.groupId] }),\n  }),\n);\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":1,"totalLines":51,"estimatedTokens":309}}12{"id":"stack-77803745","source":"stackoverflow","questionId":77803745,"title":"Drizzle ORM how to use subquery inside WHERE","tags":["node.js","typescript","sqlite","subquery","drizzle"],"text":"Title: Drizzle ORM how to use subquery inside WHERE\nTags: node.js, typescript, sqlite, subquery, drizzle\nSource: Stack Overflow\n\nQuestion:\nsuppose you have the following SQLite data , in a table named \"A\"\n\n```\nid name last_updated\n1 Apple 100\n2 Banana 100\n3 Apple 200\n4 Banana 200\n5 Carrot 200\n6 Banana 300\n7 Apple 300\n```\n\nyou want to get the entry for each name that corresponds to the biggest last_updated, given last_updated is In Raw SQL, the setup will be:\n\n```\n-- INIT database\nCREATE TABLE IF NOT EXISTS A (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n last_updated INTEGER NOT NULL\n);\nINSERT INTO A (name, last_updated) VALUES ('Apple', 100);\nINSERT INTO A (name, last_updated) VALUES ('Banana', 100);\nINSERT INTO A (name, last_updated) VALUES ('Apple', 200);\nINSERT INTO A (name, last_updated) VALUES ('Banana', 200);\nINSERT INTO A (name, last_updated) VALUES ('Carrot', 200);\nINSERT INTO A (name, last_updated) VALUES ('Banana', 300);\nINSERT INTO A (name, last_updated) VALUES ('Apple', 300);\n```\n\nHow to write a Drizzle Query to achieve this?\n\nSo in Raw SQL, I can achieve this via subquery inside WHERE clause:\n\n```\n-- QUERY database\nSELECT a.id, a.name, a.last_updated\nFROM A AS a\nWHERE a.last_updated = (\n SELECT MAX(last_updated)\n FROM A\n WHERE name = a.name\n AND last_updated I tried so many variations in Drizzle ORM, but it seems I just can't get it right.\nMy latest attempt looks something like this:\n\n```\nconst dayLuxonToBigint = 250\n\nreturn await db\n .select()\n .from(A)\n .where(\n and(\n lte(a.last_updated, dayLuxonToBigint),\n sql`SELECT max(${A.last_updated}) FROM ${A}\nWHERE id = a.id\nAND ${A.last_updated} I hope somebody can help, thanks!\n\n========================================\n\nCode:\n```text\nid  name    last_updated\n1   Apple   100\n2   Banana  100\n3   Apple   200\n4   Banana  200\n5   Carrot  200\n6   Banana  300\n7   Apple   300\n```\n\n```text\n| id  | name   | last_updated |\n| --- | ------ | ------------ |\n| 3   | Apple  | 200          |\n| 4   | Banana | 200          |\n| 5   | Carrot | 200          |\n```\n\n```text\n-- INIT database\nCREATE TABLE IF NOT EXISTS A (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    name TEXT NOT NULL,\n    last_updated INTEGER NOT NULL\n);\nINSERT INTO A (name, last_updated) VALUES ('Apple', 100);\nINSERT INTO A (name, last_updated) VALUES ('Banana', 100);\nINSERT INTO A (name, last_updated) VALUES ('Apple', 200);\nINSERT INTO A (name, last_updated) VALUES ('Banana', 200);\nINSERT INTO A (name, last_updated) VALUES ('Carrot', 200);\nINSERT INTO A (name, last_updated) VALUES ('Banana', 300);\nINSERT INTO A (name, last_updated) VALUES ('Apple', 300);\n```\n\n```text\n-- QUERY database\nSELECT a.id, a.name, a.last_updated\nFROM A AS a\nWHERE a.last_updated = (\n    SELECT MAX(last_updated)\n    FROM A\n    WHERE name = a.name\n    AND last_updated <= 250\n)\nAND a.last_updated <= 250;\n```\n\n```text\nconst dayLuxonToBigint = 250\n\nreturn await db\n  .select()\n  .from(A)\n  .where(\n    and(\n      lte(a.last_updated, dayLuxonToBigint),\n      sql`SELECT max(${A.last_updated}) FROM ${A}\nWHERE id = a.id\nAND ${A.last_updated} <= ${dayLuxonToBigint}`,\n    ),\n  );\n```\n\n```text\nlast_updated <= 250\n```\n\n```js\nconst subQuery = db.select({\n   name: A.name,\n   max_last_updated: sql<number>`cast(max(${A.last_updated}) as int)`.as('max_last_updated')\n })\n  .from(A)\n  .where(lte(a.last_updated, dayLuxonToBigint))\n  .groupBy(A.name)\n  .as('subQuery');\n\nawait db.select()\n  .from(A)\n  .innerJoin(subQuery, and(\n     eq(A.name, subQuery.name),\n     eq(A.last_updated, subQuery.max_last_updated)\n   ))\n```\n\n========================================\n\nComments:\n- @Sveloslav thank you! the complete solution btw is adding the `.as()` for the max_last_updated subQuery: `max_last_updated: sql`cast(max(${A.last_updated}) as int).as('max_last_updated')` or else it complains\n- Thanks - I have tuned the answer according to your change so that the users can have a complete answer","metadata":{"transformedAt":"2026-08-18T18:32:26.960Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":7,"totalLines":158,"estimatedTokens":981}}13{"id":"stack-78177831","source":"stackoverflow","questionId":78177831,"title":"drizzle: db.query throwing undefined is not an object (evaluating 'relation.referencedTable')","tags":["typescript","postgresql","relationship","drizzle","drizzle-orm"],"text":"Title: drizzle: db.query throwing undefined is not an object (evaluating 'relation.referencedTable')\nTags: typescript, postgresql, relationship, drizzle, drizzle-orm\nSource: Stack Overflow\n\nQuestion:\nso i have these schema called `products.ts` and `category.ts`, the relationship between those files is **one-to-many**.\n\nproduct.ts\n\n```\nimport { pgTable, timestamp, uuid, varchar } from \"drizzle-orm/pg-core\";\nimport { categories } from \"./category\";\nimport { relations } from \"drizzle-orm\";\n\nexport const products = pgTable(\"products\", {\n id: uuid(\"id\").primaryKey().defaultRandom(),\n name: varchar(\"name\", { length: 255 }),\n categoryId: uuid(\"category_id\").notNull().references(() => categories.id),\n createdAt: timestamp(\"created_at\").notNull().defaultNow(),\n updatedAt: timestamp(\"updated_at\").defaultNow(),\n deletedAt: timestamp(\"deleted_at\"),\n})\n\nexport const productsRelations = relations(products, ({ one }) => ({\n category: one(categories,{\n fields: [products.categoryId],\n references: [categories.id],\n })\n}))\n\nexport type Product = typeof products.$inferSelect\nexport type NewProduct = typeof products.$inferInsert\n```\n\ncategory.ts\n\n```\nimport { relations } from \"drizzle-orm\";\nimport { pgTable, timestamp, uniqueIndex, uuid, varchar } from \"drizzle-orm/pg-core\";\nimport { products } from \"./product\";\n\nexport const categories = pgTable(\"categories\", {\n id: uuid(\"id\").primaryKey().defaultRandom(),\n name: varchar(\"name\", { length: 255 }),\n createdAt: timestamp(\"created_at\").notNull().defaultNow(),\n updatedAt: timestamp(\"updated_at\").defaultNow(),\n}, (category) => {\n return {\n nameIndex: uniqueIndex(\"name_index\").on(category.name),\n }\n})\n\nexport const categoriesRelations = relations(categories, ({ many }) => ({\n products: many(products)\n}))\n\nexport type Category = typeof categories.$inferSelect\nexport type NewCategory = typeof categories.$inferInsert\n```\n\nBut when i try to do query findMany, it throws an error\n\n```\nawait db.query.categories.findMany({\n with: {\n products: true,\n },\n });\n```\n\nhttps://i.sstatic.net/H4ht7.png\n\nCan you help me to fix this ?\n\nThe expected output is the data from this query are showing and its something like this\n\n```\ntype CategoryWithProducts = {\n id: string;\n name: string | null;\n createdAt: Date;\n updatedAt: Date | null;\n products: Products[];\n}\n```\n\n========================================\n\nTop Answer:\nThank you @elbajo, your answer finally got it working for me :-)\n\nHere is a shortcut to get all schemas and relations in one go:\n\n```\nimport * as schema from './schema.js'\nimport * as relations from './relations.js'\nconst client = postgres(process.env.POSTGRES_URL)\n\nexport const db = drizzle(client, {\n schema: {\n ...schema,\n ...relations,\n }\n});\n```\n\n(upvotes should go to elbajo's answer)\n\n========================================\n\nCode:\n```js\nimport { pgTable, timestamp, uuid, varchar } from \"drizzle-orm/pg-core\";\nimport { categories } from \"./category\";\nimport { relations } from \"drizzle-orm\";\n\nexport const products = pgTable(\"products\", {\n    id: uuid(\"id\").primaryKey().defaultRandom(),\n    name: varchar(\"name\", { length: 255 }),\n    categoryId: uuid(\"category_id\").notNull().references(() => categories.id),\n    createdAt: timestamp(\"created_at\").notNull().defaultNow(),\n    updatedAt: timestamp(\"updated_at\").defaultNow(),\n    deletedAt: timestamp(\"deleted_at\"),\n})\n\nexport const productsRelations = relations(products, ({ one }) => ({\n    category: one(categories,{\n        fields: [products.categoryId],\n        references: [categories.id],\n    })\n}))\n\nexport type Product = typeof products.$inferSelect\nexport type NewProduct = typeof products.$inferInsert\n```\n\n```js\nimport { relations } from \"drizzle-orm\";\nimport { pgTable, timestamp, uniqueIndex, uuid, varchar } from \"drizzle-orm/pg-core\";\nimport { products } from \"./product\";\n\nexport const categories = pgTable(\"categories\", {\n    id: uuid(\"id\").primaryKey().defaultRandom(),\n    name: varchar(\"name\", { length: 255 }),\n    createdAt: timestamp(\"created_at\").notNull().defaultNow(),\n    updatedAt: timestamp(\"updated_at\").defaultNow(),\n}, (category) => {\n    return {\n        nameIndex: uniqueIndex(\"name_index\").on(category.name),\n    }\n})\n\nexport const categoriesRelations = relations(categories, ({ many }) => ({\n    products: many(products)\n}))\n\nexport type Category = typeof categories.$inferSelect\nexport type NewCategory = typeof categories.$inferInsert\n```\n\n```js\nawait db.query.categories.findMany({\n      with: {\n        products: true,\n      },\n    });\n```\n\n```js\ntype CategoryWithProducts = {\n    id: string;\n    name: string | null;\n    createdAt: Date;\n    updatedAt: Date | null;\n    products: Products[];\n}\n```\n\n```text\nproducts.ts\n```\n\n```text\ncategory.ts\n```\n\n```text\nconst db = drizzle(client, {\n    schema: {\n       products,\n       categories,\n    },\n});\n```\n\n```text\nconst db = drizzle(client, {\n    schema: {\n       products,\n       productsRelations,\n       categories,\n       categoriesRelations,\n    },\n});\n```\n\n```text\ncategories\n```\n\n```text\nproducts\n```\n\n```js\nimport * as schema from './schema.js'\nimport * as relations from './relations.js'\nconst client = postgres(process.env.POSTGRES_URL)\n\nexport const db = drizzle(client, {\n    schema: {\n        ...schema,\n        ...relations,\n    }\n});\n```\n\n========================================\n\nComments:\n- Ah no, this got me for longer than I'd care to admit! Such a simple thing to miss.","metadata":{"transformedAt":"2026-08-18T18:32:26.961Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":11,"totalLines":231,"estimatedTokens":1351}}14{"id":"stack-58033692","source":"stackoverflow","questionId":58033692,"title":"Dispatch actions the proper way","tags":["javascript","reactjs","react-redux","redux-thunk","drizzle"],"text":"Title: Dispatch actions the proper way\nTags: javascript, reactjs, react-redux, redux-thunk, drizzle\nSource: Stack Overflow\n\nQuestion:\n**Please, check the *Edit***\n\nI'm trying to implement sagas in my app.\n\nRight now I am fetching the props in a really bad way.\nMy app consists mainly on polling data from other sources.\n\nCurrently, this is how my app works:\n\nI have **containers** which have mapStateToProps, mapDispatchToProps.\n\n```\nconst mapStateToProps = state => {\n return {\n someState: state.someReducer.someReducerAction,\n };\n};\n\nconst mapDispatchToProps = (dispatch) => {\n return bindActionCreators({someAction, someOtherAction, ...}, dispatch)\n};\n\nconst something = drizzleConnect(something, mapStateToProps, mapDispatchToProps);\n\nexport default something;\n```\n\nand then, I have **actions**, like this:\n\n```\nimport * as someConstants from '../constants/someConstants';\n\nexport const someFunc = (someVal) => (dispatch) => {\n someVal.methods.someMethod().call().then(res => {\n dispatch({\n type: someConstants.FETCH_SOMETHING,\n payload: res\n })\n\n })\n}\n```\n\nand **reducers**, like the below one:\n\n```\nexport default function someReducer(state = INITIAL_STATE, action) {\n switch (action.type) {\n case types.FETCH_SOMETHING:\n return ({\n ...state,\n someVar: action.payload\n });\n```\n\nI combine the reducers with redux's combineReducers and export them as a single reducer, which, then, I import to my store.\n\nBecause I use drizzle, my rootSaga is this:\n\n```\nimport { all, fork } from 'redux-saga/effects'\nimport { drizzleSagas } from 'drizzle'\n\nexport default function* root() {\n yield all(\n drizzleSagas.map(saga => fork(saga)),\n )\n}\n```\n\nSo, now, when I want to update the props, inside the `componentWillReceiveProps` of the component, I do:\n`this.props.someAction()`\n\nOkay, it works, but I know that this is not the proper way. Basically, it's the worst thing I could do.\n\nSo, now, what I think I should do:\n\nCreate distinct sagas, which then I'll import inside the rootSaga file. These sagas will poll the sources every some predefined time and update the props if it is needed.\n\nBut my issue is how these sagas should be written.\n\nIs it possible that you can give me an example, based on the actions, reducers and containers that I mentioned above?\n\n**Edit:**\n\nI managed to apachuilo's directions.\n\nSo far, I made these adjustments:\n\nThe **actions** are like this:\n\n```\nexport const someFunc = (payload, callback) => ({\n type: someConstants.FETCH_SOMETHING_REQUEST,\n payload,\n callback\n})\n```\n\nand the **reducers**, like this:\n\n```\nexport default function IdentityReducer(state = INITIAL_STATE, {type, payload}) {\n switch (type) {\n case types.FETCH_SOMETHING_SUCCESS:\n return ({\n ...state,\n something: payload,\n });\n...\n```\n\nI also created **someSagas**:\n\n```\n...variousImports\n\nimport * as apis from '../apis/someApi'\n\nfunction* someHandler({ payload }) {\n const response = yield call(apis.someFunc, payload)\n\n response.data\n ? yield put({ type: types.FETCH_SOMETHING_SUCCESS, payload: response.data })\n : yield put({ type: types.FETCH_SOMETHING_FAILURE })\n}\n\nexport const someSaga = [\n takeLatest(\n types.FETCH_SOMETHING_REQUEST,\n someHandler\n )\n]\n```\n\nand then, updated the **rootSaga**:\n\n```\nimport { someSaga } from './sagas/someSagas'\n\nconst otherSagas = [\n ...someSaga,\n]\n\nexport default function* root() {\n yield all([\n drizzleSagas.map(saga => fork(saga)),\n otherSagas\n ])\n}\n```\n\nAlso, the api is the following:\n\n```\nexport const someFunc = (payload) => {\n payload.someFetching.then(res => {\n return {data: res}\n }) //returns 'data' of undefined but just \"return {data: 'something'} returns that 'something'\n```\n\nSo, I'd like to *update* my questions:\n\nMy APIs are depended to the store's state. As you may understood,\nI'm building a dApp. So, Drizzle (a middleware that I use in order\nto access the blockchain), needs to be initiated before I call\nthe APIs and return information to the components. Thus, \n\na. Trying reading the state with getState(), returns me empty contracts\n(contracts that are not \"ready\" yet) - so I can't fetch the info - I\ndo not like reading the state from the store, but...\n\nb. Passing the state through the component (this.props.someFunc(someState), returns me `Cannot read property 'data' of undefined` The funny thing is that I can console.log the\nstate (it seems okay) and by trying to just `return {data:\n'someData'}, the props are receiving the data.\n\n- Should I run this.props.someFunc() on, for e.g., componentWillMount()? Is this the proper way to update the props?\n\nSorry for the very long post, but I wanted to be accurate.\n\n**Edit for 1b**: Uhh, so many edits :) \nI solved the issue with the undefined resolve. Just had to write the API like this:\n\n```\nexport function someFunc(payload) {\n\n return payload.someFetching.then(res => {\n return ({ data: res }) \n }) \n}\n```\n\n========================================\n\nCode:\n```text\nconst mapStateToProps = state => {\n  return {\n    someState: state.someReducer.someReducerAction,\n  };\n};\n\nconst mapDispatchToProps = (dispatch) => {\n  return bindActionCreators({someAction, someOtherAction, ...}, dispatch)\n};\n\nconst something = drizzleConnect(something, mapStateToProps, mapDispatchToProps);\n\nexport default something;\n```\n\n```text\nimport * as someConstants from '../constants/someConstants';\n\nexport const someFunc = (someVal) => (dispatch) => {\n    someVal.methods.someMethod().call().then(res => {\n        dispatch({\n            type: someConstants.FETCH_SOMETHING,\n            payload: res\n        })\n\n    })\n}\n```\n\n```text\nexport default function someReducer(state = INITIAL_STATE, action) {\n    switch (action.type) {\n        case types.FETCH_SOMETHING:\n            return ({\n                ...state,\n                someVar: action.payload\n            });\n```\n\n```text\nimport { all, fork } from 'redux-saga/effects'\nimport { drizzleSagas } from 'drizzle'\n\nexport default function* root() {\n  yield all(\n    drizzleSagas.map(saga => fork(saga)),\n  )\n}\n```\n\n```text\nexport const someFunc = (payload, callback) => ({\n            type: someConstants.FETCH_SOMETHING_REQUEST,\n            payload,\n            callback\n})\n```\n\n```text\nexport default function IdentityReducer(state = INITIAL_STATE, {type, payload}) {\n    switch (type) {\n        case types.FETCH_SOMETHING_SUCCESS:\n            return ({\n                ...state,\n                something: payload,\n            });\n...\n```\n\n```text\n...variousImports\n\nimport * as apis from '../apis/someApi'\n\nfunction* someHandler({ payload }) {\n    const response = yield call(apis.someFunc, payload)\n\n    response.data\n        ? yield put({ type: types.FETCH_SOMETHING_SUCCESS, payload: response.data })\n        : yield put({ type: types.FETCH_SOMETHING_FAILURE })\n}\n\nexport const someSaga = [\n    takeLatest(\n        types.FETCH_SOMETHING_REQUEST,\n        someHandler\n    )\n]\n```\n\n```text\nimport { someSaga } from './sagas/someSagas'\n\nconst otherSagas = [\n  ...someSaga,\n]\n\nexport default function* root() {\n  yield all([\n    drizzleSagas.map(saga => fork(saga)),\n    otherSagas\n  ])\n}\n```\n\n```text\nexport const someFunc = (payload) => {\n    payload.someFetching.then(res => {\n        return {data: res}\n    }) //returns 'data' of undefined but just \"return {data: 'something'} returns that 'something'\n```\n\n```text\nexport function someFunc(payload)  {\n\n    return payload.someFetching.then(res => {\n            return ({ data: res })   \n    }) \n}\n```\n\n```text\ncomponentWillReceiveProps\n```\n\n```text\nthis.props.someAction()\n```\n\n```text\nCannot read property    'data' of undefined\n```\n\n```text\nimport React, { Component } from 'react'\n\nimport { connect } from 'react-redux'\nimport { bindActionCreators } from 'redux'\nimport { getResource } from '../actions/resource'\n\nconst mapDispatchToProps = dispatch =>\n  bindActionCreators(\n    {\n      getResource\n    },\n    dispatch\n  )\n\nclass Example extends Component {\n  handleLoad = () => {\n    this.props.getResource({\n      id: 1234\n    })\n  }\n\n  render() {\n    return <button onClick={this.handleLoad}>Load</button>\n  }\n}\n\nexport default connect(\n  null,\n  mapDispatchToProps\n)(Example)\n```\n\n```text\nimport { useDispatch } from 'react-redux'\n\nconst noop = () => {}\nconst empty = []\n\nexport const GET_RESOURCE_REQUEST = 'GET_RESOURCE_REQUEST'\nexport const getResource = (payload, callback) => ({\n  type: GET_RESOURCE_REQUEST,\n  payload,\n  callback,\n})\n\n// I use this for projects with hooks!\nexport const useGetResouceAction = (callback = noop, deps = empty) => {\n  const dispatch = useDispatch()\n\n  return useCallback(\n    payload =>\n      dispatch({ type: GET_RESOURCE_REQUEST, payload, callback }),\n    // eslint-disable-next-line react-hooks/exhaustive-deps\n    [dispatch, ...deps]\n  )\n}\n```\n\n```text\nexport const GET_RESOURCE_SUCCESS = 'GET_RESOURCE_SUCCESS'\n\nconst initialState = {\n  resouce: null\n}\n\nexport default (state = initialState, { type, payload }) => {\n  switch (type) {\n    case GET_RESOURCE_SUCCESS: {\n      return {\n        ...state,\n        resouce: payload.Data,\n      }\n    }\n}\n```\n\n```text\nimport { takeLatest } from 'redux-saga/effects'\n\nimport { GET_RESOUCE_REQUEST } from '../actions/resource'\n\n// need if not using the util\nimport { GET_RESOURCE_SUCCESS } from '../reducers/resource'\n\nimport * as resouceAPI from '../api/resource'\n\nimport { composeHandlers } from './sagaHandlers'\n\n// without the util\nfunction* getResourceHandler({ payload }) {\n    const response = yield call(resouceAPI.getResouce, payload);\n\n    response.data\n      ? yield put({ type: GET_RESOURCE_SUCCESS, payload: response.data })\n      : yield put({\n          type: \"GET_RESOURCE_FAILURE\"\n        });\n  }\n\nexport const resourceSaga = [\n  // Example that uses my util\n  takeLatest(\n    GET_RESOUCE_REQUEST,\n    composeHandlers({\n      apiCall: resouceAPI.getResouce\n    })\n  ),\n  // Example without util\n  takeLatest(\n    GET_RESOUCE_REQUEST,\n    getResourceHandler\n  )\n]\n```\n\n```text\nimport { all } from 'redux-saga/effects'\n\nimport { resourceSaga } from './resource'\n\nexport const sagas = [\n  ...resourceSaga,\n]\n\nexport default function* rootSaga() {\n  yield all(sagas)\n}\n```\n\n```text\nexport function* apiRequestStart(action, apiFunction) {\n  const { payload } = action\n\n  let success = true\n  let response = {}\n  try {\n    response = yield call(apiFunction, payload)\n  } catch (e) {\n    response = e.response\n    success = false\n  }\n\n  // Error response\n  // Edit this to fit your needs\n  if (typeof response === 'undefined') {\n    success = false\n  }\n\n  return {\n    action,\n    success,\n    response,\n  }\n}\n\nexport function* apiRequestEnd({ action, success, response }) {\n  const { type } = action\n  const matches = /(.*)_(REQUEST)/.exec(type)\n  const [, requestName] = matches\n\n  if (success) {\n    yield put({ type: `${requestName}_SUCCESS`, payload: response })\n  } else {\n    yield put({ type: `${requestName}_FAILURE` })\n  }\n\n  return {\n    action,\n    success,\n    response,\n  }\n}\n\n// External to redux saga definition -- used inside components\nexport function* callbackHandler({ action, success, response }) {\n  const { callback } = action\n  if (typeof callback === 'function') {\n    yield call(callback, success, response)\n  }\n\n  return action\n}\n\nexport function* composeHandlersHelper(\n  action,\n  {\n    apiCall = () => {}\n  } = {}\n) {\n  const { success, response } = yield apiRequestStart(action, apiCall)\n\n  yield apiRequestEnd({ action, success, response })\n\n  // This callback handler is external to saga\n  yield callbackHandler({ action, success, response })\n}\n\nexport function composeHandlers(config) {\n  return function*(action) {\n    yield composeHandlersHelper(action, config)\n  }\n}\n```\n\n========================================\n\nComments:\n- Thank you very much for your effort and time answering my question, apachuilo! I will try what you suggested and I'll post again.\n- So, I've tried everything you suggested. I think that I accomplished the \"basic\" thing of setting the actions, but still have issues. Please, check the question. I updated it and added the changes that I've made.","metadata":{"transformedAt":"2026-08-18T18:32:26.961Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":19,"totalLines":544,"estimatedTokens":3002}}15{"id":"stack-77357250","source":"stackoverflow","questionId":77357250,"title":"Drizzle not recognizing defined table","tags":["typescript","orm","bun","drizzle"],"text":"Title: Drizzle not recognizing defined table\nTags: typescript, orm, bun, drizzle\nSource: Stack Overflow\n\nQuestion:\nI am just getting started using drizzle and created a simple table in a seperate schema file:\n\n```\nimport { pgTable, serial, text, varchar } from \"drizzle-orm/pg-core\";\n \n\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n email: text('email').unique(),\n password: varchar('password_hash', { length: 256 }),\n});\n\nexport type User = typeof users.$inferSelect\nexport type NewUser = typeof users.$inferInsert\n```\n\nAfter that I am trying to generate the corresponding SQL files with:\n`bunx drizzle-kit generate:pg --schema=src/db/schema/users.ts`\n\nI do get the output: `0 tables No schema changes, nothing to migrate 😴`\n\nThis is kinda odd because I just used the example code of the website and just tweaked the properties. I expect drizzle-kit to recognize the defined table called 'users'.\nAm I missing something here?\n\nGreetings :)\n\nI also tried to rewrite the table definition with a schema definition like this:\n\n```\nimport { pgSchema, serial, text, varchar } from \"drizzle-orm/pg-core\";\n\nexport const mSchema = pgSchema(\"my_schema\")\n\nexport const users = mSchema.table('users', {\nid: serial('id').primaryKey(),\nemail: text('email').unique(),\npassword: varchar('password_hash', { length: 256 })\n});\n```\n\nStill the same output: `0 tables No schema changes, nothing to migrate 😴`\n\nI will also add my config file, just in case it matters:\n\n```\n{\n \"driver\": \"pg\",\n \"schema\": [\"src/db/schema/users.ts\"],\n \"dbCredentials\": {\n \"connectionString\": \"postgres://localdev:localdev@localhost:5432/thesecondbrain\"\n },\n \"out\": \"./drizzle\",\n \"verbose\": true\n}\n```\n\n========================================\n\nTop Answer:\nInstall NodeJS and run it with the \"npx\" prefix , worked for me just now\n\n========================================\n\nCode:\n```text\nimport { pgTable, serial, text, varchar } from \"drizzle-orm/pg-core\";\n \n\nexport const users = pgTable('users', {\n  id: serial('id').primaryKey(),\n  email: text('email').unique(),\n  password: varchar('password_hash', { length: 256 }),\n});\n\nexport type User = typeof users.$inferSelect\nexport type NewUser = typeof users.$inferInsert\n```\n\n```text\nimport { pgSchema, serial, text, varchar } from \"drizzle-orm/pg-core\";\n\nexport const mSchema = pgSchema(\"my_schema\")\n\nexport const users = mSchema.table('users', {\nid: serial('id').primaryKey(),\nemail: text('email').unique(),\npassword: varchar('password_hash', { length: 256 })\n});\n```\n\n```json\n{\n  \"driver\": \"pg\",\n  \"schema\": [\"src/db/schema/users.ts\"],\n  \"dbCredentials\": {\n    \"connectionString\": \"postgres://localdev:localdev@localhost:5432/thesecondbrain\"\n  },\n  \"out\": \"./drizzle\",\n  \"verbose\": true\n}\n```\n\n```text\nbunx drizzle-kit generate:pg --schema=src/db/schema/users.ts\n```\n\n```text\n0 tables No schema changes, nothing to migrate 😴\n```\n\n```text\n0 tables No schema changes, nothing to migrate 😴\n```\n\n```text\ndrizzle-kit\n```\n\n========================================\n\nComments:\n- Update Feb 2024: Seems like `drizzle-kit` works with `bun` now - by using `bunx` (i.e `bunx drizzle-kit generate:sqlite --schema .&#47;schema.ts`) bun.sh/guides/ecosystem/drizzle\n- Version 1.1.1 seems to work with `drizzle-kit`, but version `1.1.3` seems to be broken again\n- weird as it seems when i'm using bun version 1.1.8 it works when i have npm/npx installed but when i uninstall it doesn't work (i'm still using bunx not npx).\n- That is the same thing as what the other answer said\n- As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.","metadata":{"transformedAt":"2026-08-18T18:32:26.961Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":7,"totalLines":131,"estimatedTokens":936}}16{"id":"stack-76678778","source":"stackoverflow","questionId":76678778,"title":"Drizzle timestamp datenow invalid default value","tags":["mysql","next.js","planetscale","drizzle"],"text":"Title: Drizzle timestamp datenow invalid default value\nTags: mysql, next.js, planetscale, drizzle\nSource: Stack Overflow\n\nQuestion:\nI am new to Drizzle ORM and I am trying to make a simple schema where there is a created_at column that has default value of date of when did the account got created. This is the schema\n\n```\nexport const users = mysqlTable('users', {\n id: serial('id').primaryKey(),\n email: varchar('email', { length: 100 }).notNull(),\n accountType: varchar('accountType', { length: 15 }).references(() => accountTypes.types),\n password: varchar('password', { length: 100 }),\n emailVerified: boolean('verified').default(false),\n verificationToken: varchar('verificationToken', { length: 256 }),\n createdAt: timestamp('created_at').notNull().defaultNow(),\n}, (users) => ({\n userIndex: uniqueIndex('user_idx').on(users.email)\n})\n)\n```\n\nI am getting this error whenever I push it to planetscale\n\n```\nError: target: quiz.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'created_at' (errno 1067) (sqlstate 42000) (CallerID: r6e50nzpgoxvw6iizt6g): Sql: \"alter table users add UNIQUE INDEX user_idx (email)\", BindVars: {REDACTED}\n at PromiseConnection.query (D:\\xampp2\\htdocs\\quiz\\node_modules\\drizzle-kit\\index.cjs:34740:26) \n at Command. (D:\\xampp2\\htdocs\\quiz\\node_modules\\drizzle-kit\\index.cjs:52122:33) \n at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {\n code: 'ER_INVALID_DEFAULT',\n errno: 1067,\n sql: 'CREATE UNIQUE INDEX `user_idx` ON `users` (`email`);',\n sqlState: '42000',\n sqlMessage: `target: quiz.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'created_at' (errno 1067) (sqlstate 42000) (CallerID: r6e50nzpgoxvw6iizt6g): Sql: \"alter table users add UNIQUE INDEX user_idx (email)\", BindVars: {REDACTED}`\n}\n```\n\nIt was so weird, It says that the default value for created_at is invalid but it still got pushed to my database.\n\nhttps://i.sstatic.net/KtmMa.png\n\n========================================\n\nTop Answer:\n`import { sql } from \"drizzle-orm\";`\n\nand in your table insert this:\n\n```\nupdatedAt: timestamp(\"updated_at\").default(sql`CURRENT_TIMESTAMP`),\n```\n\nThat fixes it!\n\n========================================\n\nCode:\n```text\nexport const users = mysqlTable('users', {\n    id: serial('id').primaryKey(),\n    email: varchar('email', { length: 100 }).notNull(),\n    accountType: varchar('accountType', { length: 15 }).references(() => accountTypes.types),\n    password: varchar('password', { length: 100 }),\n    emailVerified: boolean('verified').default(false),\n    verificationToken: varchar('verificationToken', { length: 256 }),\n    createdAt: timestamp('created_at').notNull().defaultNow(),\n}, (users) => ({\n    userIndex: uniqueIndex('user_idx').on(users.email)\n})\n)\n```\n\n```text\nError: target: quiz.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'created_at' (errno 1067) (sqlstate 42000) (CallerID: r6e50nzpgoxvw6iizt6g): Sql: \"alter table users add UNIQUE INDEX user_idx (email)\", BindVars: {REDACTED}\n    at PromiseConnection.query (D:\\xampp2\\htdocs\\quiz\\node_modules\\drizzle-kit\\index.cjs:34740:26)  \n    at Command.<anonymous> (D:\\xampp2\\htdocs\\quiz\\node_modules\\drizzle-kit\\index.cjs:52122:33)      \n    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {\n  code: 'ER_INVALID_DEFAULT',\n  errno: 1067,\n  sql: 'CREATE UNIQUE INDEX `user_idx` ON `users` (`email`);',\n  sqlState: '42000',\n  sqlMessage: `target: quiz.-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'created_at' (errno 1067) (sqlstate 42000) (CallerID: r6e50nzpgoxvw6iizt6g): Sql: \"alter table users add UNIQUE INDEX user_idx (email)\", BindVars: {REDACTED}`\n}\n```\n\n```text\ndefault(sql`CURRENT_TIMESTAMP`)\n```\n\n```text\nupdatedAt: timestamp(\"updated_at\").default(sql`CURRENT_TIMESTAMP`),\n```\n\n```text\nimport { sql } from \"drizzle-orm\";\n```\n\n========================================\n\nComments:\n- BUG: unable to set timestamp field to be NOT NULL #657","metadata":{"transformedAt":"2026-08-18T18:32:26.961Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":5,"totalLines":101,"estimatedTokens":1017}}17{"id":"stack-77293233","source":"stackoverflow","questionId":77293233,"title":"Drizzle ORM How do I include one side of a many to many as an array of objects in a select many?","tags":["javascript","node.js","typescript","postgresql","drizzle"],"text":"Title: Drizzle ORM How do I include one side of a many to many as an array of objects in a select many?\nTags: javascript, node.js, typescript, postgresql, drizzle\nSource: Stack Overflow\n\nQuestion:\nI have three tables with a many to many relationship, `Games`, `GamesToPlatforms`, `Platforms`. How do I query all `Games` so that it has a key of `platforms` that is an array of platform objects associated to it threw the join table.\n\n```\nconst GamesTable = pgTable(\n 'games',\n {\n id: uuid('id').primaryKey().defaultRandom().notNull(),\n name: varchar('name', { length: 255 }).notNull(),\n backgroundImage: text('background_image').notNull(),\n })\n\nconst GamesRelations = relations(GamesTable, ({ one, many }) => ({\n platforms: many(GamesToPlatformsTable)\n})\n\nconst GamesToPlatformsTable = pgTable(\n 'games_to_platforms',\n {\n gameId: uuid('game_id').notNull(),\n platformId: smallint('platform_id').notNull(),\n },\n (t) => {\n return {\n uniqueIdx: uniqueIndex(`unique_idx`).on(t.gameId, t.platformId),\n }\n }\n)\n\nconst GamesToPlatformsRelations = relations(\n GamesToPlatformsTable,\n ({ one }) => {\n return {\n platform: one(PlatformsTable, {\n fields: [GamesToPlatformsTable.platformId],\n references: [PlatformsTable.id],\n }),\n })\n\nconst PlatformsTable = pgTable(\n 'platforms',\n {\n id: smallint('id').primaryKey().notNull(),\n name: varchar('name', { length: 255 }).notNull(),\n imageBackground: text('image_background').notNull(),\n },\n (platforms) => {\n return {\n uniqueIdx: uniqueIndex(`unique_idx`).on(platforms.slug),\n }\n }\n)\n\nconst PlatformsRelations = relations(PlatformsTable, ({ many }) => {\n return {\n games: many(GamesToPlatformsTable),\n }\n})\n```\n\n========================================\n\nCode:\n```text\nconst GamesTable = pgTable(\n    'games',\n    {\n        id: uuid('id').primaryKey().defaultRandom().notNull(),\n        name: varchar('name', { length: 255 }).notNull(),\n        backgroundImage: text('background_image').notNull(),\n    })\n\nconst GamesRelations = relations(GamesTable, ({ one, many }) => ({\n    platforms: many(GamesToPlatformsTable)\n})\n\nconst GamesToPlatformsTable = pgTable(\n    'games_to_platforms',\n    {\n        gameId: uuid('game_id').notNull(),\n        platformId: smallint('platform_id').notNull(),\n    },\n    (t) => {\n        return {\n            uniqueIdx: uniqueIndex(`unique_idx`).on(t.gameId, t.platformId),\n        }\n    }\n)\n\nconst GamesToPlatformsRelations = relations(\n    GamesToPlatformsTable,\n    ({ one }) => {\n        return {\n            platform: one(PlatformsTable, {\n                fields: [GamesToPlatformsTable.platformId],\n                references: [PlatformsTable.id],\n            }),\n    })\n\nconst PlatformsTable = pgTable(\n    'platforms',\n    {\n        id: smallint('id').primaryKey().notNull(),\n        name: varchar('name', { length: 255 }).notNull(),\n        imageBackground: text('image_background').notNull(),\n    },\n    (platforms) => {\n        return {\n            uniqueIdx: uniqueIndex(`unique_idx`).on(platforms.slug),\n        }\n    }\n)\n\nconst PlatformsRelations = relations(PlatformsTable, ({ many }) => {\n    return {\n        games: many(GamesToPlatformsTable),\n    }\n})\n```\n\n```text\nGames\n```\n\n```text\nGamesToPlatforms\n```\n\n```text\nPlatforms\n```\n\n```text\nGames\n```\n\n```text\nplatforms\n```\n\n```js\nconst result: Response = await db.query.GamesTable.findMany({\n    with: {\n        platforms: {\n            columns: {},\n            with: {\n                platform: true\n            }\n        }\n    }\n})\n```\n\n```text\ntype Response = {\n    id: string;\n    name: string;\n    backgroundImage: string;\n    platforms: {\n        platform: {\n            id: number;\n            name: string;\n            imageBackground: string;\n        };\n    }[];\n}[]\n```\n\n```text\nplatform\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.961Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":9,"totalLines":175,"estimatedTokens":932}}18{"id":"stack-77519641","source":"stackoverflow","questionId":77519641,"title":"Drizzle ORM Add where clause to `findMany`","tags":["typescript","postgresql","drizzle"],"text":"Title: Drizzle ORM Add where clause to `findMany`\nTags: typescript, postgresql, drizzle\nSource: Stack Overflow\n\nQuestion:\nI am attempting to add a where clause to my find many query but I'm not seeing any documentation for how this syntax should look. I just want to search where `name` is like \"whatever\"\n\n```\nconst data = await db.query.GamesTable.findMany({\n with: {\n platforms: {\n columns: {},\n with: {\n platform: true,\n },\n },\n },\n where: {\n name: {\n contains: query.searchText,\n },\n },\n offset: page * 20,\n limit: 20,\n })\n```\n\nI get the following type error\n\nType  { name: { contains: string; }; }  is not assignable to type\nSQL | ((fields: { id: PgColumn;\n\n========================================\n\nCode:\n```text\nconst data = await db.query.GamesTable.findMany({\n            with: {\n                platforms: {\n                    columns: {},\n                    with: {\n                        platform: true,\n                    },\n                },\n            },\n            where: {\n                name: {\n                    contains: query.searchText,\n                },\n            },\n            offset: page * 20,\n            limit: 20,\n        })\n```\n\n```text\nname\n```\n\n```text\nconst data = await db.query.GamesTable.findMany({\n            with: {\n                platforms: {\n                    columns: {},\n                    with: {\n                        platform: true,\n                    },\n                },\n            },\n            where: (game, { ilike }) => ilike(game.name, `%${query.searchText}%`),\n            offset: page * 20,\n            limit: 20,\n        })\n```\n\n```text\nwhere\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.961Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":4,"totalLines":78,"estimatedTokens":407}}19{"id":"stack-76876763","source":"stackoverflow","questionId":76876763,"title":"Drizzle ORM - decimal MySQL is a string?","tags":["typescript","drizzle"],"text":"Title: Drizzle ORM - decimal MySQL is a string?\nTags: typescript, drizzle\nSource: Stack Overflow\n\nQuestion:\nThe schema:\n\n```\nexport const myTable = mysqlTable(\n \"MyTable\",\n {\n id: varchar(\"id\", { length: 191 }).notNull(),\n value: decimal(\"value\", { precision: 7, scale: 4 }).notNull(),\n createdAt: datetime(\"createdAt\", { mode: \"date\" })\n .default(sql`CURRENT_TIMESTAMP`)\n .notNull(),\n },\n (table) => {\n return {\n myTableId: primaryKey(table.id),\n };\n }\n);\n```\n\nThe code:\n\n```\ntype MyTable = InferModel;\n\nconst values: MyTable[] = await db.select().from(myTable);\n```\n\nThe type of `values[0].value` is `string`, and I figure it should be a `number`.\n\nI could not find anything related to this on Drizzle docs, Github Issues or StackOverflow, and I would like to understand why this happens, or, if I'm making any mistakes.\n\nEDIT: I added an answer that \"fixes\" the type, but does not answer why `double` becomes a `number` and `decimal` becomes a `string`, which is enough for me.\n\nEDIT 2: Thanks @ColouredPanda and @andrew-allen: https://github.com/drizzle-team/drizzle-orm/issues/570#issuecomment-1646033240\n\n========================================\n\nTop Answer:\nYou can try any of the ff.:\n\n**1**. Customize column data type. Converts the typescript data type into a number, but you'll still get a string as a result:\n\n```\n{\n value: decimal('value', { precision: 7, scale: 4 }).$type().notNull()\n}\n```\n\n**2**. Custom types to make the data type a number, and also accept/return a number:\n\n```\nimport { customType, mysqlTable, int } from 'drizzle-orm/mysql-core';\n\nconst decimalNumber = customType({\n dataType() {\n return 'decimal(7, 4)';\n },\n fromDriver(value) {\n return Number(value);\n },\n});\n\nexport const myTable = mysqlTable('MyTable', {\n id: varchar('id', { length: 191 }).notNull(),\n value: decimalNumber('value').notNull(),\n});\n```\n\n**3**. Use double.\n\nBe aware that JavaScript number is a 64-bit floating point value, so at some point, you wind up losing precision (why Drizzle end up using strings for decimals).\n\n========================================\n\nCode:\n```text\nexport const myTable = mysqlTable(\n  \"MyTable\",\n  {\n    id: varchar(\"id\", { length: 191 }).notNull(),\n    value: decimal(\"value\", { precision: 7, scale: 4 }).notNull(),\n    createdAt: datetime(\"createdAt\", { mode: \"date\" })\n      .default(sql`CURRENT_TIMESTAMP`)\n      .notNull(),\n  },\n  (table) => {\n    return {\n      myTableId: primaryKey(table.id),\n    };\n  }\n);\n```\n\n```text\ntype MyTable = InferModel<typeof myTable, \"select\">;\n\nconst values: MyTable[] = await db.select().from(myTable);\n```\n\n```text\nvalues[0].value\n```\n\n```text\nstring\n```\n\n```text\nnumber\n```\n\n```text\ndouble\n```\n\n```text\nnumber\n```\n\n```text\ndecimal\n```\n\n```text\nstring\n```\n\n```text\nexport const myTable = mysqlTable(\n  \"MyTable\",\n  {\n    id: varchar(\"id\", { length: 191 }).notNull(),\n    value: double(\"value\", { precision: 7, scale: 4 }).notNull(),\n    createdAt: datetime(\"createdAt\", { mode: \"date\" })\n      .default(sql`CURRENT_TIMESTAMP`)\n      .notNull(),\n  },\n  (table) => {\n    return {\n      myTableId: primaryKey(table.id),\n    };\n  }\n);\n```\n\n```text\ndecimal\n```\n\n```text\ndouble\n```\n\n```text\ndouble\n```\n\n```text\nnumber\n```\n\n```js\nexport const db = mysqlTable(\"someDb\", {\n  weight: decimal(\"weight\", {\n    precision: 6, scale: 2\n  }) as unknown as MySqlDoubleBuilderInitial<\"weight\">\n});\n```\n\n```text\nnumber\n```\n\n```text\ntypeof weight\n```\n\n```text\n\"number\"\n```\n\n```text\n{\n  value: decimal('value', { precision: 7, scale: 4 }).$type<number>().notNull()\n}\n```\n\n```text\nimport { customType, mysqlTable, int } from 'drizzle-orm/mysql-core';\n\nconst decimalNumber = customType<{ data: number }>({\n  dataType() {\n    return 'decimal(7, 4)';\n  },\n  fromDriver(value) {\n    return Number(value);\n  },\n});\n\nexport const myTable = mysqlTable('MyTable', {\n  id: varchar('id', { length: 191 }).notNull(),\n  value: decimalNumber('value').notNull(),\n});\n```\n\n```text\nprice: \n  decimal(\"price\", {\n    precision: 10,\n    scale: 2,\n  }).notNull() as unknown as PgDoublePrecisionBuilderInitial<\"price\">\n```\n\n```text\nprice: (\n  decimal(\"price\", {\n    precision: 10,\n    scale: 2,\n  }) as unknown as PgDoublePrecisionBuilderInitial<\"price\">\n).notNull()\n```\n\n```js\nprice: decimal('Price', { precision: 10, scale: 2, mode: 'number' })\n```\n\n========================================\n\nComments:\n- Javascript's `number` does not support the precision as required by mysql's decimal type. To not lose data, its read as a string\n- I know they do this on purpose for numeric (postgres) due to precision\n- @ColouredPanda is this the official answer from Drizzle? That sounds like reasonable, I just want to be sure.\n- @AlanSikora it will be e.g. github.com/drizzle-team/drizzle-orm/issues/&hellip;\n- yes, we can set it as the default value is string here is the example. `discount: decimal(\"discount\", { precision: 5, scale: 2 }).default(\"0\"),`\n- use mode: 'number' in the v1 beta, see my answer below\n- Instead of asserting the type like that, you can do `decimal('weight').$type()` instead\n- 1 and 2 solution seems to break `db.insert()` typescript when using `createInsertSchema()` via drizzle-zod, at least at the moment of this writing\n- @arvil good to know that issue. I might try drizzle-zod one day and report back to you.\n- @wobsoriano we will wait to see your report bro :)\n- This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review","metadata":{"transformedAt":"2026-08-18T18:32:26.961Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":23,"totalLines":246,"estimatedTokens":1384}}20{"id":"stack-77268508","source":"stackoverflow","questionId":77268508,"title":"how to delete in Drizzle ORM with several \"where\"","tags":["reactjs","next.js","drizzle"],"text":"Title: how to delete in Drizzle ORM with several \"where\"\nTags: reactjs, next.js, drizzle\nSource: Stack Overflow\n\nQuestion:\nI have a function:\n\n```\nexport async function deleteFavoriteTrack({profileId, trackId}) {\n await db.delete(favoriteTracks).where(eq(favoriteTracks.profileId, profileId));\n}\n```\n\nI can put only one \"eq\".\nHow can i make like in prisma like:\n\n```\nwhere {\n profileId,\n trackId\n}\n```\n\n========================================\n\nCode:\n```text\nexport async function deleteFavoriteTrack({profileId, trackId}) {\n   await db.delete(favoriteTracks).where(eq(favoriteTracks.profileId, profileId));\n}\n```\n\n```text\nwhere {\n   profileId,\n   trackId\n}\n```\n\n```js\nawait db.delete(favoriteTracks).where(\n  and(\n    eq(favoriteTracks.profileId, profileId),\n    eq(favoriteTracks.trackId, trackId),\n  )\n)\n```\n\n```text\nfavoriteTrack\n```\n\n```text\nAND\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.961Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":5,"totalLines":55,"estimatedTokens":214}}21{"id":"stack-77260229","source":"stackoverflow","questionId":77260229,"title":"Drizzle ORM date conversion problem between Javascript and PostgreSQL?","tags":["postgresql","orm","timezone","utc","drizzle"],"text":"Title: Drizzle ORM date conversion problem between Javascript and PostgreSQL?\nTags: postgresql, orm, timezone, utc, drizzle\nSource: Stack Overflow\n\nQuestion:\nPrerequisite: my timezone is GMT+3\n\nI have a model drizzle model\n\n```\nexport const events = pgTable('events', {\n id: serial('id').primaryKey(),\n name: varchar('name', {length: 256}).notNull(),\n date: date('date').notNull(),\n});\n```\n\nI've inserted a record to the database\n\n```\n# insert into events (name, date) values ('Foobar', '2023-03-30');\n```\n\nWhen I query it it shows correctly in the result relation\n\n```\nid | name | date\n----+---------+------------\n100 | Foobar | 2023-03-30\n```\n\nNow if I run the following code\n\n```\nconst foo = await db.query.events.findFirst({\n where: eq(events.id, 100),\n});\nconsole.log(foo.date);\n```\n\nI get\n\n```\n2023-03-30T00:00:00.000Z\n```\n\nBut if I view the database via drizzle-kit Studio, it shows the date as\n\n```\n2023-03-29T21:00:00.000Z\n```\n\nAND, if I use the foo's date value in another query\n\n```\nawait db.query.events.findFirst({\n where: lt(events.date, foo.date),\n orderBy: desc(races.date),\n});\n```\n\nI get **the same record** as I already have in the foo, **not** the one with earlier date - probably due to the fact that my filter value is 2023-03-30 but the database sees it as 2023-03-29. My analysis is that the lt (less than) comparison compares apple's and orange's. But I don't understand why? What should I do to fix it?\n\nDo I need to set the database's timezone to UTC, and if then how? Or is there some other way to solve this?\n\n========================================\n\nCode:\n```text\nexport const events = pgTable('events', {\n    id: serial('id').primaryKey(),\n    name: varchar('name', {length: 256}).notNull(),\n    date: date('date').notNull(),\n});\n```\n\n```text\n# insert into events (name, date) values ('Foobar', '2023-03-30');\n```\n\n```text\nid |  name   |    date\n----+---------+------------\n100 | Foobar  | 2023-03-30\n```\n\n```text\nconst foo = await db.query.events.findFirst({\n    where: eq(events.id, 100),\n});\nconsole.log(foo.date);\n```\n\n```text\n2023-03-30T00:00:00.000Z\n```\n\n```text\n2023-03-29T21:00:00.000Z\n```\n\n```text\nawait db.query.events.findFirst({\n    where: lt(events.date, foo.date),\n    orderBy: desc(races.date),\n});\n```\n\n```text\ndate: date(\"date\", {mode: \"date\"}).notNull()\n```\n\n========================================\n\nComments:\n- 1) Is the `date`(I would suggest using another name) field actually a `date` type in Postgres? 2) What `show timezone;` return in Postgres? **Add answers as text update to question**.\n- Yes the type of the field is `date` is PostgreSQL - and you are correct, the name could be something else. The `show timezone;` shows `Europe&#47;Helsinki`. I changed it to UTC. I still see the -3 hours times in the drizzle kit and the lt query still returns the same entity.\n- Then Drizzle Studio/Kit is doing a conversion somewhere. 1) A `date` does not have a timezone: `select '2023-10-09'::date; 10&#47;09&#47;2023` 2) The value you show is a `datetime&#47;timestamp` so there a CAST being applied. You might want to crank up the logging in Postgres and see what is actually hitting the database.\n- Thanks, totally agree. The drizzle kit can be viewed via a browser and somehow it \"adds\" the timezone information (incorrectly?) to the dates, that indeed are `date` type in db. I'll check the db logs, and am willing to change the date type to string, if necessary. :)\n- A search here Drizzle Issues found multiple date/datetime issues. This one Issue 971 looked particularly relevant if you are using node-postgres as it points at node-postgres datetime types\n- So it seems. I think I found a solution in my scenario. Not quite sure what is happening, but the fix was to change the date column's definition to `date: date(\"date\", {mode: \"date\"}).notNull()`, i.e. add the mode as \"date\". Then it started treating my comparison right. And if I tested correctly, changing the database timezone didn't change the behaviour. Thanks @AdrianKlaver.\n- I'm going to say `{mode: \"date\"}` is overriding `node-postgres` behavior of *node-postgres converts DATE and TIMESTAMP columns into the local time of the node process set at process.env.TZ.*.\n- @Janne - If that worked, please supply it as an answer instead of just a comment. Stack Overflow allows (and encourages) you to answer your own questions. Thanks.\n- this doesn't work for me, i still have the problem you described where when the Date is loaded out of the database, it incorrectly is a full timestamp with timezone, and it's adjusted based on my machine's timezone, so `2023-03-30` has become `2023-03-29T21:00:00.000Z`. Frustrating.\n- this also didn't solve it for me. i already had that config and am running into this same issue.","metadata":{"transformedAt":"2026-08-18T18:32:26.961Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":8,"totalLines":125,"estimatedTokens":1185}}22{"id":"stack-77891139","source":"stackoverflow","questionId":77891139,"title":"Drizzle ORM : Internal error: Error: There is not enough information to infer relation \"users.contacts\"","tags":["node.js","database","orm","backend","drizzle"],"text":"Title: Drizzle ORM : Internal error: Error: There is not enough information to infer relation \"users.contacts\"\nTags: node.js, database, orm, backend, drizzle\nSource: Stack Overflow\n\nQuestion:\nI'm using Drizzle ORM with PostgreSQL, and this is how my schema looks. In a simple manner, there is a 'users' table and a 'contacts' table. Each user can have multiple contacts.\n\n```\nexport const users = pgTable(\"users\", {\n id: uuid(\"id\").defaultRandom().primaryKey(),\n email: text(\"email\").unique().notNull(),\n about: varchar(\"about\", { length: 100 }),\n created_at: timestamp(\"created_at\").defaultNow(),\n workspace_id: uuid(\"workspace_id\").references(() => workspaces.id, {\n onDelete: \"cascade\",\n onUpdate: \"cascade\"\n })\n});\n\nexport const usersRelations = relations(users, ({ one, many }) => ({\n permission: one(permissions, {\n fields: [users.email],\n references: [permissions.user_email]\n }),\n contacts: many(contacts,{\n relationName: 'users_contacts',\n })\n}));\n\nexport const contactType = pgEnum(....);\n\nexport const contacts = pgTable(\"contacts\", {\n id: uuid(\"id\").defaultRandom().primaryKey(),\n type: contactType(\"type\").default(\"phone\").notNull(),\n value: text(\"value\").notNull(),\n created_at: timestamp(\"created_at\").defaultNow(),\n user_email: text(\"user_email\")\n .references(() => users.email, {\n onDelete: \"cascade\",\n onUpdate: \"cascade\"\n })\n .unique()\n .notNull()\n});\n\nexport const contactsRelations = relations(contacts, ({ many }) => ({\n users: many(users)\n}));\n```\n\nbut when i do like this\n\n```\nconst workspaceUsers = await db.query.users.findMany({\n where: eq(users.workspace_id, loggedWorkspace.data.id),\n with: {\n permission:true,\n contacts: true\n }\n });\n```\n\nHowever, I don't know why I'm encountering this error. Please let me know how I can fix it.\n\n```\n⨯ Internal error: Error: There is not enough information to infer relation \"users.contacts\"\n```\n\n========================================\n\nCode:\n```text\nexport const users = pgTable(\"users\", {\n  id: uuid(\"id\").defaultRandom().primaryKey(),\n  email: text(\"email\").unique().notNull(),\n  about: varchar(\"about\", { length: 100 }),\n  created_at: timestamp(\"created_at\").defaultNow(),\n  workspace_id: uuid(\"workspace_id\").references(() => workspaces.id, {\n    onDelete: \"cascade\",\n    onUpdate: \"cascade\"\n  })\n});\n\nexport const usersRelations = relations(users, ({ one, many }) => ({\n  permission: one(permissions, {\n    fields: [users.email],\n    references: [permissions.user_email]\n  }),\n  contacts: many(contacts,{\n    relationName: 'users_contacts',\n  })\n}));\n\nexport const contactType = pgEnum(....);\n\nexport const contacts = pgTable(\"contacts\", {\n  id: uuid(\"id\").defaultRandom().primaryKey(),\n  type: contactType(\"type\").default(\"phone\").notNull(),\n  value: text(\"value\").notNull(),\n  created_at: timestamp(\"created_at\").defaultNow(),\n  user_email: text(\"user_email\")\n    .references(() => users.email, {\n      onDelete: \"cascade\",\n      onUpdate: \"cascade\"\n    })\n    .unique()\n    .notNull()\n});\n\nexport const contactsRelations = relations(contacts, ({ many }) => ({\n  users: many(users)\n}));\n```\n\n```text\nconst workspaceUsers = await db.query.users.findMany({\n    where: eq(users.workspace_id, loggedWorkspace.data.id),\n    with: {\n      permission:true,\n      contacts: true\n    }\n  });\n```\n\n```text\n⨯ Internal error: Error: There is not enough information to infer relation \"users.contacts\"\n```\n\n```text\nexport const usersRelations = relations(users, ({ man }) => ({\n  contacts: many(contacts, {\n    relationName: \"users_contacts\"\n  })\n}));\n\nexport const contactsRelations = relations(contacts, ({ one }) => ({\n  user: one(users, {\n    fields: [contacts.user_email],\n    references: [users.email],\n    relationName: \"users_contacts\"\n  })\n}));\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.962Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":4,"totalLines":143,"estimatedTokens":930}}23{"id":"stack-79271295","source":"stackoverflow","questionId":79271295,"title":"drizzle-kit not using .env.test or .env.development in bun.js","tags":["bun",".env","drizzle","drizzle-orm","drizzle-kit"],"text":"Title: drizzle-kit not using .env.test or .env.development in bun.js\nTags: bun, .env, drizzle, drizzle-orm, drizzle-kit\nSource: Stack Overflow\n\nQuestion:\nI'm working with bun.js and I'm trying to do `bun run drizzle-kit push`, `bun run drizzle-kit generate`, `bun run drizzle-kit migrate` but no any of these commands works for testing environment, the drizzle cannot get my `DATABASE_URL` from `.env.test` or `.env.development` or `.env.local`...etc ***I discovered that drizzle-kit can get the environment variables only from*** `.env`.\n\nThis is my `drizzle.config.ts` file:\n\n```\nimport { defineConfig } from \"drizzle-kit\";\nimport config from \"config\";\n\nexport default defineConfig({\n out: \"./drizzle\",\n schema: \"./src/db/schema.ts\",\n dialect: \"postgresql\",\n dbCredentials: {\n url: process.env.DATABASE_URL as string,\n },\n});\n```\n\nI'm trying to generate migrations for my `DATABASE_URL` in `.env.test` but its always getting the URL from `.env` only, and if I don't have `.env` file it will give me error says:\n\n```\nError Either connection \"url\" or \"host\", \"database\" are required for PostgreSQL database connection\n```\n\nIs there any way to use different `.env` file for drizzle?\n\n***Note: I am using built in bun.js .env, I am not using any additional dotenv packages***\n\n========================================\n\nTop Answer:\nUsing `bun --bun run` or `bunx --bun drizzle-kit` forces it to respect bun's env file loading\n\n========================================\n\nCode:\n```js\nimport { defineConfig } from \"drizzle-kit\";\nimport config from \"config\";\n\nexport default defineConfig({\n  out: \"./drizzle\",\n  schema: \"./src/db/schema.ts\",\n  dialect: \"postgresql\",\n  dbCredentials: {\n    url: process.env.DATABASE_URL as string,\n  },\n});\n```\n\n```text\nError  Either connection \"url\" or \"host\", \"database\" are required for PostgreSQL database connection\n```\n\n```text\nbun run drizzle-kit push\n```\n\n```text\nbun run drizzle-kit generate\n```\n\n```text\nbun run drizzle-kit migrate\n```\n\n```text\nDATABASE_URL\n```\n\n```text\n.env.test\n```\n\n```text\n.env.development\n```\n\n```text\n.env.local\n```\n\n```text\n.env\n```\n\n```text\ndrizzle.config.ts\n```\n\n```text\nDATABASE_URL\n```\n\n```text\n.env.test\n```\n\n```text\n.env\n```\n\n```text\n.env\n```\n\n```text\n.env\n```\n\n```text\nbun run --env-file=.env.test drizzle-kit push\n```\n\n```json\n\"scripts\": {\n    \"start\": \"bun run server.ts\",\n    \"dev\": \"bun run --watch server.ts\",\n    \"test\": \"NODE_ENV=test bun test --preload ./tests/setup.ts\",// this is a bonus line. this line will make sure the tests will use .env.test when test is running and we load a global setup file before starting the tests\n    \"test-drizzle-generate\": \"bun --env-file=.env.test drizzle-kit generate\",\n    \"test-drizzle-migrate\": \"bun --env-file=.env.test drizzle-kit migrate\",\n    \"test-drizzle-push\": \"bun --env-file=.env.test drizzle-kit push\"\n  }\n```\n\n```text\n--env-file\n```\n\n```text\npackage.json\n```\n\n```text\nbun --bun run\n```\n\n```text\nbunx --bun drizzle-kit\n```\n\n========================================\n\nComments:\n- Thank you, but I dont use dotenv packages, bun has built in dotenv handling without any extra packages\n- Your answer need many improvments, its need to be clear and covers all the code aspects and also related to the quesion specifically. please the good answer guide here stackoverflow.com/help/how-to-answer","metadata":{"transformedAt":"2026-08-18T18:32:26.962Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":22,"totalLines":151,"estimatedTokens":828}}24