{"bugs":{"url":"https://github.com/drizzle-team/drizzle-orm/issues"},"dist":{"shasum":"9a998699afea837f205346788d2d19d0a3c74aca","tarball":"https://registry.npmjs.org/drizzle-orm-pg/-/drizzle-orm-pg-0.14.0.tgz","fileCount":156,"integrity":"sha512-fe0Yczs9LzVATQbeRvnBn4KKfT8w6ZfVO1r1crUl4MS9BvWaD3FvqQpb+RlvJysSgWS7Boq6JjgVxfGNLB6hPg==","signatures":[{"sig":"MEUCIHqnq+jQjeFz4YaHNxzL0FDMsj7cpIzsTssCFNFtt/+bAiEAgpBPXUPAKn9Kas8p7fYMNlJdq0V/mCXE0xoD8llSVr0=","keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA"}],"unpackedSize":222848,"npm-signature":"-----BEGIN PGP SIGNATURE-----\r\nVersion: OpenPGP.js v4.10.10\r\nComment: https://openpgpjs.org\r\n\r\nwsFzBAEBCAAGBQJjl2c8ACEJED1NWxICdlZqFiEECWMYAoorWMhJKdjhPU1b\r\nEgJ2VmrjyhAAmcc5AEEXysuefRaYSsjMq19mGWlNFCjyCcvRHcxTCGinXkf5\r\n4osDHPZ0TN5kvB0PzPmdHSqYIBtd+GtcKi+dS5TZGtBt35Bwtokwev7heeFT\r\nBoA8KjWmYY/jbsXU3bPL6dvlTjO0CqRKryMk0Sw/2XZC7xq+e4qSolHrLufQ\r\nugN7bwAUhXrNj7lRQ+Kd0ToQ7mbByOgHA9ghVPpk9dc+25Lu9DrsB78RTmEz\r\nnKmAKax2/AJHjTRzDe3QaDTg3FuKhwghd6nvAtxhQnfVBMDmLB0tBD6b/iB6\r\nZi1qGa3l6qql4xnojK5ppYIi7tV93HMR30LLR+0AfARRw4viqecFRbji84bW\r\nKJEZdUZzsBNly7rkO/9I5DkCJxzZtpALug3koAifoyEcJBC3U4oFUopP1E71\r\n2pmozyOYAxvEBd+jyjiwJ7boRJnyvcXuWDlU/4LXWsGwi2xIXi4wWbJeUcYn\r\nMVYxqwEJTR6Js1rDphNyG+qB6SRD4qyu3KwfmASFoGtz9JtFPSf3SwJzgdAq\r\nYTjzRtJxrnkxNw3cznl8TiTnkWvA4pZQ1gnVnxeGtreUd4x1sUyKXwqstKmd\r\n1dyLabvLIBaGxhCSrRBAnMMg6xmbbfHnjRKnHqi0kkgjA9fV+AuZbTeW8T1l\r\nFJjCEPVkGDkR5ACzcU7zdoGBHbOsdu1axg4=\r\n=Q1gH\r\n-----END PGP SIGNATURE-----\r\n"},"main":"index.js","name":"drizzle-orm-pg","_from":"file:package.tgz","types":"index.d.ts","author":"","readme":"## DrizzleORM [PostgreSQL]\r\nDrizzleORM is a TypeScript ORM library with a [drizzle-kit](#migrations) CLI companion for automatic SQL migrations generation. \r\nHere you can find extensive docs for PostgreSQL module.\r\n\r\n### Installation\r\n```bash\r\n// postgresql\r\nnpm install drizzle-orm drizzle-orm-pg\r\nnpm install -D drizzle-kit\r\n```\r\n\r\n### SQL schema declaration\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📦project\r\n ├ 📂src\r\n │ ├ 📂data\r\n │ │ └ 📜schema.ts\r\n │ └ ...\r\n ├ ...\r\n └ 📜package.json\r\n \r\n## or multiple schema files\r\n├ 📂data\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```typescript\r\nimport { PgConnector, pgTable, serial, text, varchar } from \"drizzle-orm-pg\";\r\nimport { Pool } from \"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\nconst pool = new Pool({ connectionString: \"postgres://user:password@host:port/db\" });\r\nconst connector = new PgConnector(pool);\r\nconst db = await connector.connect();\r\n\r\nconst users = await db.select(users);\r\n```\r\n\r\n### Connecting to database\r\n```typescript\r\nimport { PgConnector } from \"drizzle-orm-pg\";\r\nimport { Pool } from \"pg\";\r\n\r\nconst pool = new Pool({ connectionString: \"postgres://postgres:password@127.0.0.1:5432/postgres\" });\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 connector = new PgConnector(pool);\r\nconst db = await connector.connect();\r\n```\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```typescript\r\n// declaring enum in database\r\nexport const popularityEnum = pgEnum(\"popularity\", [\"unknown\", \"known\", \r\n\"popular\"]);\r\n\r\nexport const countries = pgTable(\"countries\", {\r\n    id: serial(\"id\").primaryKey(),\r\n    name: varchar(\"name\", 256),\r\n  }, (table) => ({\r\n    nameIndex: index(\"name_idx\", table.name, { unique: true });\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\nDatabase and table entity types\r\n```typescript\r\nimport { PgConnector, PgDatabase, pgTable, InferModel, serial, text, varchar } 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 InsertUser = InferModel<typeof users, \"insert\"> // insert type\r\n...\r\n\r\nconst connector = new PgConnector(pool);\r\nconst db: PgDatabase = await connector.connect();\r\n\r\nconst result: User[] = await db.select(users)\r\n\r\nconst insertUser = (user: InsertUser) => {\r\n  return db.insert(users).values(user)\r\n}\r\n```\r\n\r\n\r\nThe list of all column types. You can also create custom types - !!see here!!.\r\n```typescript\r\nexport const popularityEnum = pgEnum(\"popularity\", [\"unknown\", \"known\", \"popular\"]);\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\n```\r\n\r\nDeclaring indexes and foreign keys\r\n```typescript\r\nimport { foreignKey, index, 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    nameIdx: index(\"name_idx\", table.name), // one column\r\n    namePopulationIdx: index(\"name_population_idx\", [table.name, table.population]), // multiple columns\r\n    uniqueIdx: index(\"unique_idx\", table.name, { unique: true }), // unique index\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}, (table) => ({\r\n  // explicit foreign key with 1 column\r\n  countryFk: foreignKey(() => ({\r\n    columns: [table.countryId],\r\n    foreignColumns: [countries.id],\r\n  })),\r\n  // explicit foreign key with multiple columns\r\n  countryIdNameFk: foreignKey(() => ({\r\n    columns: [table.countryId, table.countryName],\r\n    foreignColumns: [countries.id, countries.name],\r\n  })),\r\n}));\r\n\r\n// list of all index params\r\nunique?: boolean;\r\nconcurrently?: boolean;\r\nonly?: boolean;\r\nusing?: sql``; // sql expression\r\norder?: 'asc' | 'desc';\r\nnulls?: 'first' | 'last';\r\nwhere?: sql``; // sql expression\r\n```\r\n\r\n### Create Read Update Delete\r\nQuerying, sorting and filtering. We also support partial select.\r\n```typescript\r\n...\r\nimport { PgConnector, pgTable, serial, text, varchar } from \"drizzle-orm-pg\";\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 connector = new PgConnector(...);\r\nconst db = await connector.connect();\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(exressions: Expr[])\r\nor(exressions: Expr[])\r\n```\r\n\r\nInserting\r\n```typescript\r\nimport { PgConnector, pgTable, serial, text, timestamp } from \"drizzle-orm-pg\";\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\nconst connector = new PgConnector(...);\r\nconst db = await connector.connect();\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\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\n\r\nUpdate and Delete\r\n```typescript\r\nawait db.update(users)\r\n  .set({ name: 'Mr. Dan' })\r\n  .where(eq(usersTable.name, 'Dan'));\r\n\t\r\nawait db.delete(users)\r\n  .where(eq(usersTable.name, 'Dan'));\r\n```\r\n\r\n### Joins\r\nLast but not least. Probably the most powerful feature in the library🚀\r\n### Many-to-one\r\n```typescript\r\nimport { PgConnector, pgTable, serial, text, timestamp } from \"drizzle-orm-pg\";\r\n\r\nconst cities = pgTable(\"cities\", {\r\n  id: serial(\"id\").primaryKey(),\r\n  name: text(\"name\"),\r\n});\r\n\r\nconst users = pgTable(\"users\", {\r\n  id: serial(\"id\").primaryKey(),\r\n  name: text(\"name\"),\r\n  cityId: integer(\"city_id\").references(() => cities.id)\r\n});\r\n\r\nconst connector = new PgConnector(...);\r\nconst db = await connector.connect();\r\n\r\nconst result = db.select(cities).leftJoin(users, eq(cities2.id, users2.cityId))\r\n```\r\n\r\n### Many-to-many\r\n```typescript\r\nconst users = pgTable(\"users\", {\r\n  id: serial(\"id\").primaryKey(),\r\n  name: text(\"name\"),\r\n});\r\n\r\nconst chatGroups = pgTable(\"chat_groups\", {\r\n  id: serial(\"id\").primaryKey(),\r\n  name: text(\"name\"),\r\n});\r\n\r\nconst usersToChatGroups = pgTable(\"usersToChatGroups\", {\r\n  userId: integer(\"user_id\").notNull().references(() => users.id),\r\n  groupId: integer(\"group_id\").notNull().references(() => chatGroups.id),\r\n});\r\n\r\n...\r\nconst connector = new PgConnector(...);\r\nconst db = await connector.connect();\r\n\r\n// querying user group with id 1 and all the participants(users)\r\ndb.select(usersToChatGroups)\r\n  .leftJoin(users, eq(usersToChatGroups.userId, users.id))\r\n  .leftJoin(chatGroups, eq(usersToChatGroups.groupId, chatGroups.id))\r\n  .where(eq(chatGroups.id, 1));\r\n```\r\n\r\n### Join aliases and selfjoins\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\n...\r\nconst connector = new PgConnector(...);\r\nconst db = await connector.connect();\r\n\r\nconst nestedFiles = alias(files, \"nested_files\");\r\n\r\nawait db.select(files)\r\n  .leftJoin(nestedFiles, eq(files.name, nestedFiles.name))\r\n  .where(eq(files.parent, \"/\"));\r\n// will return files and folers and nested files for each folder at root dir\r\n```\r\n\r\n### Join using partial field select\r\nJoin Cities with Users getting only needed fields form request\r\n```typescript\r\nawait db.select(cities).fields({\r\n  id: cities.id,\r\n  cityName: cities.name\r\n}).leftJoin(users, eq(users.cityId, cities.id));\r\n```\r\n\r\n## Migrations\r\n### Automatic SQL migrations generation with drizzle-kit\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 delitions and renames by prompting user input.\\\r\nCheck out the [docs for DrizzleKit](https://github.com/drizzle-team/drizzle-kit-mirror)\r\n\r\nFor schema file:\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}, (table)=>({\r\n  nameIdx: index(\"name_idx\", table.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\nIt will generate:\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```typescript\r\nimport { PgConnector } from \"drizzle-orm-pg\";\r\nimport { Pool } from \"pg\";\r\n\r\nconst pool = new Pool({ connectionString: \"postgres://user:password@host:port/db\" });\r\nconst connector = new PgConnector(pool);\r\nconst db = await connector.connect();\r\n\r\n// this will automatically run needed migrations on the database\r\nawait connector.migrate({ migrationsFolder: \"./drizzle\" })\r\n```\r\n\r\n## Raw query usage\r\n#### If you have some complex queries to execute and drizzle-orm can't handle them yet, then you could use `rawQuery` execution\r\n\r\n##### Execute custom raw query\r\n```typescript\r\n// it will automatically run a parametrized query!\r\nconst res: QueryResult<any> = await db.execute(sql`SELECT * FROM users WHERE user.id = ${userId}`)\r\n```\r\n","license":"Apache-2.0","scripts":{"pack":"cp README.md package.json dist/ && (cd dist && pnpm pack --pack-destination ..) && rm -f package.tgz && mv *.tgz package.tgz","build":"tsc -p tsconfig.build.json && resolve-tspaths","release":"pnpm build && pnpm run pack && pnpm run publish","build:int":"pnpm build && pnpm run pack","test:types":"cd tests && tsc","release:beta":"pnpm build && pnpm run pack && pnpm run publish --tag beta"},"_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-fe0Yczs9LzVATQbeRvnBn4KKfT8w6ZfVO1r1crUl4MS9BvWaD3FvqQpb+RlvJysSgWS7Boq6JjgVxfGNLB6hPg==","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.2","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.18.1","publishConfig":{"access":"public"},"_hasShrinkwrap":false,"readmeFilename":"README.md","devDependencies":{"pg":"^8.8.0","@types/pg":"^8.6.5","drizzle-orm":"link:../drizzle-orm/src","@neondatabase/serverless":"^0.1.13"},"peerDependencies":{"pg":">=8 <9","@types/pg":">=8 <9","drizzle-orm":">=0.13 <0.14"},"peerDependenciesMeta":{"pg":{"optional":true},"@types/pg":{"optional":true}},"_npmOperationalInternal":{"tmp":"tmp/drizzle-orm-pg_0.14.0_1670866748614_0.04602236731214804","host":"s3://npm-registry-packages"},"_id":"drizzle-orm-pg@0.14.0","version":"0.14.0"}