{"bugs":{"url":"https://github.com/drizzle-team/drizzle-orm/issues"},"dist":{"shasum":"e6a44b14ad188f1971e1988408d2a6d433b40df5","tarball":"https://registry.npmjs.org/drizzle-orm-pg/-/drizzle-orm-pg-0.15.2.tgz","fileCount":199,"integrity":"sha512-RJxMhjjFmaNj3pkRit60M0V2M40jNZUNTYCDwE+2OoemjZ7Ml7DPNIZOD+oc3AUTpIVi+30iz3+LM1ZfNk2nCA==","signatures":[{"sig":"MEUCIDvbeF2JRaXAMIAb3D5/cOku6GrZCeYGDvvQoAmg0kYxAiEAgGV8FlSZAHIOjp42VkFW/14VohgiS6VJP5PrzDRtF9s=","keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA"}],"unpackedSize":300956,"npm-signature":"-----BEGIN PGP SIGNATURE-----\r\nVersion: OpenPGP.js v4.10.10\r\nComment: https://openpgpjs.org\r\n\r\nwsFzBAEBCAAGBQJjxVoxACEJED1NWxICdlZqFiEECWMYAoorWMhJKdjhPU1b\r\nEgJ2VmrKxRAAjETnIuIc+5CVd6GU+S0M8lxvV3zoTobg90Q6dzHi5BUE4rWr\r\nStoZHnrFKn8y6N1C550xH9uuA8jLZdi+l3tkZwNZ2BUBdllE9Zi5ecZ00r4w\r\n5/L57BGLKnVCNXtjIxRPMgzMvhg/+mjfsBWOgMV1eIBemqM0lVVklbFxT1eY\r\nJKV+hNjj+FeM/55Zq90lSEba5c+gFdEKPp+lsP6/zOBQ82IhbHOWZ0XnfuXw\r\nNd4VJOce5UQxM3XqvIeXhZQYYvN/V6Z5+r/+vAi+ptj+D2JkrGSDOf47h0E6\r\nOa5ZeoSZySPfnq74mZIje9fHhjlnGA1iHjidjBg/W2BxJ4D+Hy6q+hwbBJN8\r\ncQ6zjmer0URjy0J2WfPsBtpW+R8WqVVaR6fQhPfboZn1uCrIx1Sq0zrlKISO\r\n1iDe6bji0GGY+65XWm2CfZSuGbaY4MWXXvGdK4IonumbHrjXqzK6jD60TaNs\r\nYiKFUb2oCP0AOh/aABzfc+56vrCPMyPB/9LM5CallByZdSBNnvmrmLVw7TGq\r\nEbdbBTsCH7TcdcGrHK79Qzucv8mB3Q1sz4Wfcc3cmoHcxyTs8bThmNELlqTN\r\nGhwSMfhguGv1kQulCZB6246FA6Nx2jXSupVmtf8cL9zEfCSLEcTZdnjTciFL\r\nP13TkG/NJU69fjFrXBSpTXAJqWbFcuRHL5A=\r\n=oDsx\r\n-----END PGP SIGNATURE-----\r\n"},"name":"drizzle-orm-pg","_from":"file:package.tgz","types":"./index.d.ts","author":{"name":"Drizzle Team"},"readme":"<div align='center'>\r\n<h1>Drizzle ORM | PostgreSQL <a href=''><img alt='npm' src='https://img.shields.io/npm/v/drizzle-orm-pg?label='></a></h1>\r\n<img alt='npm' src='https://img.shields.io/npm/dm/drizzle-orm-pg'>\r\n<img alt='pg version' src='https://img.shields.io/npm/dependency-version/drizzle-orm-pg/peer/pg'>\r\n<img alt='npm bundle size' src='https://img.shields.io/bundlephobia/min/drizzle-orm-pg'>\r\n<a href='https://discord.gg/yfjTbVXMW4'><img alt='Discord' src='https://img.shields.io/discord/1043890932593987624'></a>\r\n<img alt='NPM' src='https://img.shields.io/npm/l/drizzle-orm-pg'>\r\n<h6><i>If you know SQL, you know Drizzle ORM</i></h6>\r\n<hr />\r\n</div>\r\n\r\nDrizzle ORM is a TypeScript ORM for SQL databases designed with maximum type safety in mind. It comes with a [drizzle-kit](https://github.com/drizzle-team/drizzle-kit-mirror) CLI companion for automatic SQL migrations generation. This is the documentation for Drizzle ORM version for PostgreSQL.\r\n\r\n## Installation\r\n\r\n```bash\r\n# npm\r\nnpm i drizzle-orm drizzle-orm-pg pg\r\nnpm i -D @types/pg\r\nnpm i -D drizzle-kit\r\n\r\n# yarn\r\nyarn add drizzle-orm drizzle-orm-pg pg\r\nyarn add -D @types/pg\r\nyarn add -D drizzle-kit\r\n\r\n# pnpm\r\npnpm add drizzle-orm drizzle-orm-pg pg\r\npnpm add -D @types/pg\r\npnpm add -D drizzle-kit\r\n```\r\n\r\n## SQL schema declaration\r\n\r\nWith `drizzle-orm` you declare SQL schema in TypeScript. You can have either one `schema.ts` file with all declarations or you can group them logically in multiple files. We prefer to use single file schema.\r\n\r\n### Single schema file example\r\n\r\n```\r\n📦 <project root>\r\n └ 📂 src\r\n    └ 📂 db\r\n       └ 📜schema.ts\r\n```\r\n\r\n### Multiple schema files example\r\n\r\n```\r\n📦 <project root>\r\n └ 📂 src\r\n    └ 📂 db\r\n       └ 📂 schema\r\n          ├ 📜users.ts\r\n          ├ 📜countries.ts\r\n          ├ 📜cities.ts\r\n          ├ 📜products.ts\r\n          ├ 📜clients.ts\r\n          ├ 📜enums.ts\r\n          └ 📜etc.ts\r\n```\r\n\r\n## Quick start\r\n\r\n```typescript\r\n// schema.ts\r\nexport const users = pgTable('users', {\r\n  id: serial('id').primaryKey(),\r\n  fullName: text('full_name'),\r\n  phone: varchar('phone', { length: 256 }),\r\n});\r\n```\r\n\r\n### Connect using node-postgres Pool (recommended)\r\n\r\n```typescript\r\n// db.ts\r\nimport { pgTable, serial, text, varchar } from 'drizzle-orm-pg';\r\nimport { drizzle } from 'drizzle-orm-pg/node';\r\nimport { Pool } from 'pg';\r\n\r\nimport { users } from './schema';\r\n\r\nconst pool = new Pool({\r\n  connectionString: 'postgres://user:password@host:port/db',\r\n});\r\n// or\r\nconst pool = new Pool({\r\n  host: '127.0.0.1',\r\n  port: 5432,\r\n  user: 'postgres',\r\n  password: 'password',\r\n  database: 'db_name',\r\n});\r\n\r\nconst db = drizzle(pool);\r\n\r\nconst allUsers = await db.select(users);\r\n```\r\n\r\n### Connect using node-postgres Client\r\n\r\n```typescript\r\n// db.ts\r\nimport { pgTable, serial, text, varchar } from 'drizzle-orm-pg';\r\nimport { drizzle } from 'drizzle-orm-pg/node';\r\nimport { Client } from 'pg';\r\n\r\nimport { users } from './schema';\r\n\r\nconst client = new Client({\r\n  connectionString: 'postgres://user:password@host:port/db',\r\n});\r\n// or\r\nconst client = new Client({\r\n  host: '127.0.0.1',\r\n  port: 5432,\r\n  user: 'postgres',\r\n  password: 'password',\r\n  database: 'db_name',\r\n});\r\n\r\nawait client.connect();\r\n\r\nconst db = drizzle(client);\r\n\r\nconst allUsers = await db.select(users);\r\n```\r\n\r\n## Schema declaration\r\n\r\nThis is how you declare SQL schema in `schema.ts`. You can declare tables, indexes and constraints, foreign keys and enums. Please pay attention to `export` keyword, they are mandatory if you'll be using [drizzle-kit SQL migrations generator](#migrations).\r\n\r\n```typescript\r\nimport { pgEnum, pgTable, serial, uniqueIndex, varchar } from 'drizzle-orm-pg';\r\n\r\n// declaring enum in database\r\nexport const popularityEnum = pgEnum('popularity', ['unknown', 'known', 'popular']);\r\n\r\nexport const countries = pgTable('countries', {\r\n  id: serial('id').primaryKey(),\r\n  name: varchar('name', 256),\r\n}, (countries) => {\r\n  return {\r\n    nameIndex: uniqueIndex('name_idx').on(countries.name),\r\n  }\r\n});\r\n\r\nexport const cities = pgTable('cities', {\r\n  id: serial('id').primaryKey(),\r\n  name: varchar('name', 256),\r\n  countryId: integer('country_id').references(() => countries.id),\r\n  popularity: popularityEnum('popularity'),\r\n});\r\n```\r\n\r\n### Database and table entity types\r\n\r\n```typescript\r\nimport { PgDatabase, pgTable, InferModel, serial, text, varchar } from 'drizzle-orm-pg';\r\nimport { drizzle } from 'drizzle-orm-pg';\r\n\r\nconst users = pgTable('users', {\r\n  id: serial('id').primaryKey(),\r\n  fullName: text('full_name'),\r\n  phone: varchar('phone', { length: 256 }),\r\n});\r\n\r\nexport type User = InferModel<typeof users>; // return type when queried\r\nexport type NewUser = InferModel<typeof users, 'insert'>; // insert type\r\n...\r\n\r\n// init node-postgres Pool or Client\r\nconst pool = new Pool(...);\r\n\r\nexport const db: PgDatabase = drizzle(pool);\r\n\r\nconst result: User[] = await db.select(users);\r\n\r\nexport async function insertUser(user: NewUser): Promise<User> {\r\n  return db.insert(users).values(user).returning();\r\n}\r\n```\r\n\r\n### Declaring indexes and foreign keys\r\n\r\n```typescript\r\nimport { foreignKey, index, uniqueIndex, integer, pgTable, serial, varchar } from 'drizzle-orm-pg';\r\n\r\nexport const countries = pgTable('countries', {\r\n    id: serial('id').primaryKey(),\r\n    name: varchar('name', { length: 256 }),\r\n    population: integer('population'),\r\n  }, (table) => {\r\n    return {\r\n      nameIdx: index('name_idx').on(table.name), // one column\r\n      namePopulationIdx: index('name_population_idx').on(table.name, table.population), // multiple columns\r\n      uniqueIdx: uniqueIndex('unique_idx').on(table.name), // unique index\r\n    }\r\n  })\r\n);\r\n\r\nexport const cities = pgTable('cities', {\r\n  id: serial('id').primaryKey(),\r\n  name: varchar('name', { length: 256 }),\r\n  countryId: integer('country_id').references(() => countries.id), // inline foreign key\r\n  countryName: varchar('country_id'),\r\n}, (cities) => {\r\n  return {\r\n    // explicit foreign key with 1 column\r\n    countryFk: foreignKey({\r\n      columns: [cities.countryId],\r\n      foreignColumns: [countries.id],\r\n    }),\r\n    // explicit foreign key with multiple columns\r\n    countryIdNameFk: foreignKey({\r\n      columns: [cities.countryId, cities.countryName],\r\n      foreignColumns: [countries.id, countries.name],\r\n    },\r\n  }\r\n}));\r\n\r\n// Index declaration reference\r\nindex('name')\r\n  .on(table.column1, table.column2, ...)\r\n  .onOnly(table.column1, table.column2, ...)  \r\n  .concurrently()\r\n  .using(sql``) // sql expression\r\n  .asc()\r\n  .desc()\r\n  .nullsFirst()\r\n  .nullsLast()\r\n  .where(sql``) // sql expression\r\n```\r\n\r\n## Column types\r\n\r\n```typescript\r\nexport const popularityEnum = pgEnum('popularity', ['unknown', 'known', 'popular']); // declare enum type\r\npopularityEnum('column_name') // declare enum column\r\n\r\nsmallint('...')\r\ninteger('...')\r\nbigint('...', { mode: 'number' | 'bigint' })\r\n\r\nboolean('...')\r\ntext('...');\r\ntext<'one' | 'two' | 'three'>('...');\r\nvarchar('...');\r\nvarchar<'one' | 'two' | 'three'>('...');\r\nvarchar('...', { length: 256 }); // with length limit\r\n\r\nserial('...');\r\nbigserial('...', { mode: 'number' | 'bigint' });\r\n\r\ndecimal('...', { precision: 100, scale: 2 });\r\nnumeric('...', { precision: 100, scale: 2 });\r\n\r\nreal('...')\r\ndoublePrecision('...')\r\n\r\njson<...>('...');\r\njson<string[]>('...');\r\njsonb<...>('...');\r\njsonb<string[]>('...');\r\n\r\ntime('...')\r\ntime('...', { precision: 6, withTimezone: true })\r\ntimestamp('...')\r\ntimestamp('...', { mode: 'date' | 'string', precision: 0..6, withTimezone: true })\r\ntimestamp('...').defaultNow()\r\ndate('...')\r\ndate('...', { mode: 'string' | 'date' })\r\ninterval('...')\r\ninterval('...', { fields: 'day' | 'month' | '...' , precision: 0..6 })\r\n\r\ncolumn.primaryKey()\r\ncolumn.notNull()\r\ncolumn.defaultValue(...)\r\ntimeColumn.defaultNow()\r\nuuidColumn.defaultRandom()\r\n```\r\n\r\n## Table schemas\r\nDrizzle won't append any schema before table definition by default. So if your tables are in `public` schema drizzle generate -> `select * from \"users\"`\r\n\r\nBut if you will specify any custom schema you want, then drizzle will generate -> `select * from \"custom_schema\".\"users\"`\r\n\r\n> **Warning**\r\n> If you will have tables with same names in different schemas then drizzle will respond with `never[]` error in result types and error from database\r\n> \r\n> In this case you may use [alias syntax](https://github.com/drizzle-team/drizzle-orm/tree/main/drizzle-orm-pg#join-aliases-and-self-joins)\r\n\r\n\r\n#### Usage example\r\n```typescript\r\n// Table in default schema\r\nconst publicUsersTable = pgTable('users', {\r\n  id: serial('id').primaryKey(),\r\n  name: text('name').notNull(),\r\n  verified: boolean('verified').notNull().default(false),\r\n  jsonb: jsonb<string[]>('jsonb'),\r\n  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),\r\n});\r\n\r\n\r\n// Table in custom schema\r\nconst mySchema = pgSchema('mySchema');\r\n\r\nconst usersTable = mySchema('users', {\r\n\tid: serial('id').primaryKey(),\r\n\tname: text('name').notNull(),\r\n\tverified: boolean('verified').notNull().default(false),\r\n\tjsonb: jsonb<string[]>('jsonb'),\r\n\tcreatedAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),\r\n});\r\n```\r\n\r\n## Select, Insert, Update, Delete\r\n\r\n### Select\r\n\r\nQuerying, sorting and filtering. We also support partial select.\r\n\r\n```typescript\r\n...\r\nimport { pgTable, serial, text, varchar } from 'drizzle-orm-pg';\r\nimport { drizzle } from 'drizzle-orm-pg/node';\r\nimport { and, asc, desc, eq, or } from 'drizzle-orm/expressions';\r\n\r\nconst users = pgTable('users', {\r\n  id: serial('id').primaryKey(),\r\n  name: text('full_name'),\r\n});\r\n\r\nconst db = drizzle(...);\r\n\r\nawait db.select(users);\r\nawait db.select(users).where(eq(users.id, 42));\r\n\r\n// you can combine filters with eq(...) or or(...)\r\nawait db.select(users)\r\n  .where(and(eq(users.id, 42), eq(users.name, 'Dan')));\r\n\r\nawait db.select(users)\r\n  .where(or(eq(users.id, 42), eq(users.id, 1)));\r\n\r\n// partial select\r\nconst result = await db.select(users).fields({\r\n    mapped1: users.id,\r\n    mapped2: users.name,\r\n  });\r\nconst { mapped1, mapped2 } = result[0];\r\n\r\n// limit, offset & order by\r\nawait db.select(users).limit(10).offset(10);\r\nawait db.select(users).orderBy(asc(users.name));\r\nawait db.select(users).orderBy(desc(users.name));\r\n// you can pass multiple order args\r\nawait db.select(users).orderBy(asc(users.name), desc(users.name));\r\n\r\n// list of all filter operators\r\neq(column, value)\r\neq(column1, column2)\r\nne(column, value)\r\nne(column1, column2)\r\n\r\nnotEq(column, value)\r\nless(column, value)\r\nlessEq(column, value)\r\n\r\ngt(column, value)\r\ngt(column1, column2)\r\ngte(column, value)\r\ngte(column1, column2)\r\nlt(column, value)\r\nlt(column1, column2)\r\nlte(column, value)\r\nlte(column1, column2)\r\n\r\nisNull(column)\r\nisNotNull(column)\r\n\r\ninArray(column, values[])\r\ninArray(column, sqlSubquery)\r\nnotInArray(column, values[])\r\nnotInArray(column, sqlSubquery)\r\n\r\nexists(sqlSubquery)\r\nnotExists(sqlSubquery)\r\n\r\nbetween(column, min, max)\r\nnotBetween(column, min, max)\r\n\r\nlike(column, value)\r\nlike(column, value)\r\nilike(column, value)\r\nnotIlike(column, value)\r\n\r\nnot(sqlExpression)\r\n\r\nand(expressions: SQL[])\r\nor(expressions: SQL[])\r\n```\r\n\r\n### Insert\r\n\r\n```typescript\r\nimport { pgTable, serial, text, timestamp, InferModel } from 'drizzle-orm-pg';\r\nimport { drizzle } from 'drizzle-orm-pg/node';\r\n\r\nconst users = pgTable('users', {\r\n  id: serial('id').primaryKey(),\r\n  name: text('name'),\r\n  createdAt: timestamp('created_at'),\r\n});\r\n\r\ntype NewUser = InferModel<typeof users>;\r\n\r\nconst db = drizzle(...);\r\n\r\nawait db.insert(users)\r\n  .values({\r\n    name: 'Andrew',\r\n    createdAt: new Date(),\r\n  });\r\n\r\n// accepts vararg of items\r\nawait db.insert(users)\r\n  .values(\r\n    {\r\n      name: 'Andrew',\r\n      createdAt: new Date(),\r\n    },\r\n    {\r\n      name: 'Dan',\r\n      createdAt: new Date(),\r\n    },\r\n  );\r\n\r\nconst newUsers: NewUser[] = [\r\n  {\r\n      name: 'Andrew',\r\n      createdAt: new Date(),\r\n  },\r\n  {\r\n    name: 'Dan',\r\n    createdAt: new Date(),\r\n  },\r\n];\r\n\r\nawait db.insert(users).values(...newUsers);\r\n\r\nconst insertedUsers: NewUser[] = await db.insert(users).values(...newUsers).returning();\r\n\r\nconst insertedUsersIds: { insertedId: number }[] = await db.insert(users)\r\n    .values(...newUsers)\r\n    .returning({ insertedId: users.id });\r\n```\r\n\r\n### Upsert (Insert with on conflict statement)\r\n\r\n```typescript\r\nawait db.insert(users)\r\n\t.values({ id: 1, name: 'Dan' })\r\n\t.onConflictDoUpdate({ target: users.id, set: { name: 'John' } });\r\n\r\nawait db.insert(users)\r\n\t.values({ id: 1, name: 'John' })\r\n\t.onConflictDoNothing();\r\n\r\nawait db.insert(users)\r\n\t.values({ id: 1, name: 'John' })\r\n\t.onConflictDoNothing({ target: users.id });\r\n\r\nawait db.insert(users)\r\n\t.values({ id: 1, name: 'John' })\r\n\t.onConflictDoUpdate({\r\n\t\ttarget: users.id,\r\n\t\tset: { name: 'John1' },\r\n\t\twhere: sql`${users.createdAt} > '2023-01-01'::date`,\r\n\t});\r\n```\r\n\r\n### Update and Delete\r\n\r\n```typescript\r\nawait db.update(users)\r\n\t.set({ name: 'Mr. Dan' })\r\n\t.where(eq(users.name, 'Dan'));\r\n\r\nconst updatedUser: InferModel<typeof users> = await db.delete(users)\r\n\t.set({ name: 'Mr. Dan' })\r\n\t.where(eq(users.name, 'Dan'))\r\n\t.returning();\r\n\r\nconst updatedUserId: { updatedId: number }[] = await db.update(users)\r\n\t.set({ name: 'Mr. Dan' })\r\n\t.where(eq(users.name, 'Dan'))\r\n\t.returning({ updatedId: users.id });\r\n\r\nawait db.delete(users)\r\n\t.where(eq(users.name, 'Dan'));\r\n\r\nconst deletedUser: InferModel<typeof users> = await db.delete(users)\r\n\t.where(eq(users.name, 'Dan'))\r\n\t.returning();\r\n\r\nconst deletedUserId: { deletedId: number }[] = await db.delete(users)\r\n\t.where(eq(users.name, 'Dan'))\r\n\t.returning({ deletedId: users.id });\r\n```\r\n\r\n### Joins\r\n\r\nLast but not least. Probably the most powerful feature in the library🚀\r\n\r\n#### Many-to-one\r\n\r\n```typescript\r\nconst cities = pgTable('cities', {\r\n\tid: serial('id').primaryKey(),\r\n\tname: text('name'),\r\n});\r\n\r\nconst users = pgTable('users', {\r\n\tid: serial('id').primaryKey(),\r\n\tname: text('name'),\r\n\tcityId: integer('city_id').references(() => cities.id),\r\n});\r\n\r\nconst result = db.select(cities)\r\n\t.leftJoin(users, eq(cities2.id, users2.cityId));\r\n```\r\n\r\n#### Many-to-many\r\n\r\n```typescript\r\nconst users = pgTable('users', {\r\n\tid: serial('id').primaryKey(),\r\n\tname: text('name'),\r\n});\r\n\r\nconst chatGroups = pgTable('chat_groups', {\r\n\tid: serial('id').primaryKey(),\r\n\tname: text('name'),\r\n});\r\n\r\nconst usersToChatGroups = pgTable('usersToChatGroups', {\r\n\tuserId: integer('user_id').notNull().references(() => users.id),\r\n\tgroupId: integer('group_id').notNull().references(() => chatGroups.id),\r\n});\r\n\r\n// querying user group with id 1 and all the participants(users)\r\nconst result = await db.select(usersToChatGroups)\r\n\t.leftJoin(users, eq(usersToChatGroups.userId, users.id))\r\n\t.leftJoin(chatGroups, eq(usersToChatGroups.groupId, chatGroups.id))\r\n\t.where(eq(chatGroups.id, 1));\r\n```\r\n\r\n#### Join aliases and self-joins\r\n\r\n```typescript\r\nimport { ..., alias } from 'drizzle-orm-pg';\r\n\r\nexport const files = pgTable('folders', {\r\n  name: text('name').notNull(),\r\n  parent: text('parent_folder')\r\n})\r\n\r\nconst nestedFiles = alias(files, 'nested_files');\r\n\r\n// will return files and folders and nested files for each folder at root dir\r\nconst result = await db.select(files)\r\n  .leftJoin(nestedFiles, eq(files.name, nestedFiles.name))\r\n  .where(eq(files.parent, '/'));\r\n```\r\n\r\n#### Join using partial select\r\n\r\n```typescript\r\n// Select user ID and city ID and name\r\nconst result1 = await db.select(cities).fields({\r\n\tuserId: users.id,\r\n\tcityId: cities.id,\r\n\tcityName: cities.name,\r\n}).leftJoin(users, eq(users.cityId, cities.id));\r\n\r\n// Select all fields from users and only id and name from cities\r\nconst result2 = await db.select(cities).fields({\r\n\t// Supports any level of nesting!\r\n\tuser: users,\r\n\tcity: {\r\n\t\tid: cities.id,\r\n\t\tname: cities.name,\r\n\t},\r\n}).leftJoin(users, eq(users.cityId, cities.id));\r\n```\r\n\r\n## Prepared statements\r\n\r\n```typescript\r\nconst query = db.select(users)\r\n\t.where(eq(users.name, 'Dan'))\r\n\t.prepare();\r\n\r\nconst result = await query.execute();\r\n```\r\n\r\n### Prepared statements with parameters\r\n\r\n```typescript\r\nimport { placeholder } from 'drizzle-orm-pg';\r\n\r\nconst query = db.select(users)\r\n\t.where(eq(users.name, placeholder('name')))\r\n\t.prepare();\r\n\r\nconst result = await query.execute({ name: 'Dan' });\r\n```\r\n\r\n## Raw queries execution\r\n\r\nIf you have some complex queries to execute and drizzle-orm can't handle them yet, you can use the `db.execute` method to execute raw queries.\r\n\r\n```typescript\r\n// it will automatically run a parametrized query!\r\nconst res: QueryResult<{ id: number; name: string }> = await db.execute<\r\n\t{ id: number; name: string }\r\n>(sql`select * from ${users} where ${users.id} = ${userId}`);\r\n```\r\n\r\n## Migrations\r\n\r\n### Automatic SQL migrations generation with drizzle-kit\r\n\r\n[DrizzleKit](https://www.npmjs.com/package/drizzle-kit) - is a CLI migrator tool for DrizzleORM. It is probably one and only tool that lets you completely automatically generate SQL migrations and covers ~95% of the common cases like deletions and renames by prompting user input.\r\n\r\nCheck out the [docs for DrizzleKit](https://github.com/drizzle-team/drizzle-kit-mirror)\r\n\r\nFor schema file:\r\n\r\n```typescript\r\nimport { index, integer, pgTable, serial, varchar } from 'drizzle-orm-pg';\r\n\r\nexport const users = pgTable('users', {\r\n  id: serial('id').primaryKey(),\r\n  fullName: varchar('full_name', { length: 256 }),\r\n}, (users) => ({\r\n  nameIdx: index('name_idx').on(users.fullName),\r\n}));\r\n\r\nexport const authOtps = pgTable('auth_otp', {\r\n  id: serial('id').primaryKey(),\r\n  phone: varchar('phone', { length: 256 }),\r\n  userId: integer('user_id').references(() => users.id),\r\n}\r\n```\r\n\r\nIt will generate:\r\n\r\n```SQL\r\nCREATE TABLE IF NOT EXISTS auth_otp (\r\n\t'id' SERIAL PRIMARY KEY,\r\n\t'phone' character varying(256),\r\n\t'user_id' INT\r\n);\r\n\r\nCREATE TABLE IF NOT EXISTS users (\r\n\t'id' SERIAL PRIMARY KEY,\r\n\t'full_name' character varying(256)\r\n);\r\n\r\nDO $$ BEGIN\r\n ALTER TABLE auth_otp ADD CONSTRAINT auth_otp_user_id_fkey FOREIGN KEY ('user_id') REFERENCES users(id);\r\nEXCEPTION\r\n WHEN duplicate_object THEN null;\r\nEND $$;\r\n\r\nCREATE INDEX IF NOT EXISTS users_full_name_index ON users (full_name);\r\n```\r\n\r\nAnd you can run migrations manually or using our embedded migrations module\r\n\r\n```typescript\r\nimport { drizzle } from 'drizzle-orm-pg/node';\r\nimport { migrate } from 'drizzle-orm-pg/node/migrator';\r\nimport { Pool } from 'pg';\r\n\r\nconst pool = new Pool({\r\n\tconnectionString: 'postgres://user:password@host:port/db',\r\n});\r\nconst db = drizzle(pool);\r\n\r\n// this will automatically run needed migrations on the database\r\nawait migrate(db, { migrationsFolder: './drizzle' });\r\n```\r\n","license":"Apache-2.0","scripts":{"pack":"(cd dist && pnpm pack --pack-destination ..) && rm -f package.tgz && mv *.tgz package.tgz","build":"tsc --build && resolve-tspaths && cp README.md package.json dist/","test:types":"cd tests && tsc"},"_npmUser":{"name":"dankochetov","email":"danil.kochetov@gmail.com"},"homepage":"https://github.com/drizzle-team/drizzle-orm#readme","keywords":["orm","pg","postgres","postgresql","database","sql","typescript"],"_resolved":"/home/runner/work/drizzle-orm/drizzle-orm/drizzle-orm-pg/package.tgz","_integrity":"sha512-RJxMhjjFmaNj3pkRit60M0V2M40jNZUNTYCDwE+2OoemjZ7Ml7DPNIZOD+oc3AUTpIVi+30iz3+LM1ZfNk2nCA==","deprecated":"This package is merged into drizzle-orm starting from 0.17.0. See https://www.npmjs.com/package/drizzle-orm","repository":{"url":"git+https://github.com/drizzle-team/drizzle-orm.git","type":"git"},"_npmVersion":"8.19.3","description":"Drizzle ORM package for PostgreSQL database","directories":{},"maintainers":[{"name":"alexblokh","email":"aleksandrblokh@gmail.com"},{"name":"sheriman","email":"andreysherman11@gmail.com"},{"name":"dankochetov","email":"danil.kochetov@gmail.com"}],"_nodeVersion":"16.19.0","_hasShrinkwrap":false,"readmeFilename":"README.md","devDependencies":{"pg":"^8.8.0","@types/pg":"^8.6.5","drizzle-orm":"link:../drizzle-orm/dist","@neondatabase/serverless":"^0.1.13"},"peerDependencies":{"pg":">=8 <9","@types/pg":">=8 <9","drizzle-orm":">=0.15 <0.16"},"peerDependenciesMeta":{"pg":{"optional":true},"@types/pg":{"optional":true}},"_npmOperationalInternal":{"tmp":"tmp/drizzle-orm-pg_0.15.2_1673878064982_0.09274583078960963","host":"s3://npm-registry-packages"},"_id":"drizzle-orm-pg@0.15.2","version":"0.15.2"}