ESLint Drizzle Plugin all config
The plugin exports an 'all' configuration that enables all rules except deprecated ones. Use it by extending 'plugin:drizzle/all' in the eslintrc configuration.
drizzle-orm
282 notes in this subject, read out of this brain and free to use. This is page 2 of 5.
ESLint Drizzle Plugin installation
Install the ESLint Drizzle plugin with the command: npm install eslint-plugin-drizzle. Also install peer dependencies @typescript-eslint/eslint-plugin and @typescript-eslint/parser.
enforce-update-with-where drizzleObjectName option
The 'drizzle/enforce-update-with-where' rule accepts a 'drizzleObjectName' option in the plugin options that takes a string or string[] to specify Drizzle object names (like 'db'). When configured, the rule only triggers for update methods from the specified objects, avoiding false positives from non-Drizzle update methods.
Initialize Drizzle with Gel using connection options
Import drizzle from 'drizzle-orm/gel'. Create a database instance by calling drizzle with an object containing a connection property. The connection object can include any gel connection option such as dsn and tlsSecurity. Query with db.execute('select 1').
Initialize Drizzle with existing Gel client
Import drizzle from 'drizzle-orm/gel' and createClient from 'gel'. Create a gel client with createClient(). Pass the client to drizzle by calling drizzle({ client: gelClient }). Query with db.execute('select 1').
Initialize Drizzle with Gel using DATABASE_URL
Import drizzle from 'drizzle-orm/gel'. Create a database instance by calling drizzle(process.env.DATABASE_URL). Query with db.execute('select 1').
Drizzle native support for Gel
Drizzle has native support for Gel connections using the gel-js client.
Install Drizzle with Gel support
To use Drizzle with Gel, install drizzle-orm@rc and gel as dependencies, and drizzle-kit@rc as a dev dependency.
Bun SQL concurrent statements limitation in version 1.2.0
In Bun version 1.2.0, there is a known issue with executing concurrent statements that may lead to errors when trying to run several queries simultaneously. This is a limitation on Bun's SQL side. The issue has been tracked on GitHub at https://github.com/oven-sh/bun/issues/16774, and once it is fixed, this error should no longer occur.
Prerequisites for Drizzle with Bun SQL
To work with Drizzle ORM and Bun SQL, you need: dotenv package for managing environment variables, bun JavaScript all-in-one toolkit, and Bun SQL native bindings for working with PostgreSQL databases.
Install Drizzle ORM with Bun for existing PostgreSQL project
To set up Drizzle ORM with Bun for an existing PostgreSQL project, install the following packages: drizzle-orm@rc, dotenv as regular dependencies, and drizzle-kit@rc and @types/bun as dev dependencies.
Run Bun scripts with Drizzle
To run a script with Bun, use the command: bun src/index.ts
Run TypeScript files with Bun
To run a script with bun, use the command: bun src/index.ts
Connect Drizzle ORM to D1 database
Import drizzle from 'drizzle-orm/d1' and create a database instance by calling drizzle(env.<BINDING_NAME>) where <BINDING_NAME> is the binding name configured in wrangler.toml.
Type inference for insert values in Durable Objects
Use 'typeof usersTable.$inferInsert' to type insert parameters, allowing TypeScript to infer the correct type for user data being inserted into a table defined in the schema.
Type inference in Effect PostgreSQL
When inserting into a table with Effect PostgreSQL, you can infer the insert type using typeof usersTable.$inferInsert to get proper TypeScript type checking.
Gel table imports from drizzle-orm
When using Gel with Drizzle, import table definition function and column types from 'drizzle-orm/gel-core'. Available imports include: gelTable, uniqueIndex, uuid, smallint, text.
Type inference for selected rows in Expo
Use typeof table.$inferSelect to infer the type of rows returned from database queries. Example: const [items, setItems] = useState<typeof usersTable.$inferSelect[] | null>(null);
SQLite table definition example with Expo
SQLite tables in Drizzle are defined using sqliteTable() with column definitions. Example schema: import { int, sqliteTable, text } from 'drizzle-orm/sqlite-core'; export const usersTable = sqliteTable('users_table', { id: int().primaryKey({ autoIncrement: true }), name: text().notNull(), age: int().notNull(), email: text().notNull().unique() });
SQLite Cloud type inference with $inferInsert
Use typeof usersTable.$inferInsert to define the TypeScript type for inserting records into a SQLite Cloud table. This infers the correct types from the schema definition.
Type inference for table insert operations
Use typeof usersTable.$inferInsert to infer the type of an object that will be inserted into the table, ensuring type safety when creating records.
Type inference for insert values from Drizzle table schema
When inserting data into a Drizzle table, use typeof tableName.$inferInsert to get the correct TypeScript type for the insert object. This ensures the insert object matches the table schema definition.
Infer insert type from Drizzle table schema
Use typeof tableName.$inferInsert to get the inferred TypeScript type for inserting records into a table. This type reflects all columns that can be inserted, excluding auto-generated fields.
Customizing GraphQL schema with entities
To customize the GraphQL schema generated by buildSchema(), destructure the entities object and build a new GraphQLSchema manually. You can select specific queries and mutations from entities.queries and entities.mutations, reuse types from entities.types and inputs from entities.inputs in custom fields, and provide custom resolver logic using Drizzle queries. Include all types and inputs in the schema types array using Object.values(entities.types) and Object.values(entities.inputs).
buildSchema() function creates GraphQL schema
The buildSchema(db) function takes a Drizzle database instance and returns an object with a schema property containing the generated GraphQL schema. The output uses the standard graphql SDK and is compatible with any library that supports it.
NPM packages for GraphQL Yoga with drizzle-graphql
To use drizzle-graphql with GraphQL Yoga, install: drizzle-graphql, graphql-yoga, and graphql.
drizzle-graphql package for creating GraphQL servers
drizzle-graphql is a package that creates a GraphQL server from a Drizzle schema. It requires drizzle-orm version at least 0.30.9. The main entry point is the buildSchema() function which generates a GraphQL schema from a Drizzle database instance.
GraphQL Yoga setup with drizzle-graphql
To set up GraphQL Yoga with drizzle-graphql: import buildSchema from 'drizzle-graphql', create a Drizzle instance with const db = drizzle({ schema: dbSchema }), call const { schema } = buildSchema(db), create a Yoga instance with createYoga({ schema }), wrap it in a Node.js HTTP server with createServer(yoga), and listen on a port such as 4000.
buildSchema() returns schema and entities for customization
The buildSchema() function returns an object with both schema and entities properties. The schema property contains the generated GraphQL schema, while the entities property contains generated queries, mutations, types, and inputs that can be reused and customized to build a custom schema.
Apollo Server setup with drizzle-graphql
To set up Apollo Server with drizzle-graphql: import buildSchema from 'drizzle-graphql', create a Drizzle instance with const db = drizzle({ client, schema: dbSchema }), call const { schema } = buildSchema(db), create an ApolloServer with new ApolloServer({ schema }), and start it with startStandaloneServer(server).
entities object structure from buildSchema()
The entities object returned by buildSchema() contains: entities.queries (object with query field names as keys), entities.mutations (object with mutation field names as keys), entities.types (object with GraphQL type definitions), and entities.inputs (object with GraphQL input type definitions). These can be selectively included in a custom GraphQL schema.
NPM packages for Apollo Server with drizzle-graphql
To use drizzle-graphql with Apollo Server, install: drizzle-graphql, @apollo/server, and graphql.
Generated Gel User table with auth relationship
The User table from Gel auth has the following columns: id (uuid, primary key, generated with uuid_generate_v4()), email (text), identityId (uuid, foreign key to ext::auth.Identity.id), username (text). The table includes a unique index on id and a foreign key constraint named User_fk_identity.
Generated Gel auth Identity table structure
The Identity table from ext::auth has the following columns: id (uuid, primary key, generated with uuid_generate_v4()), createdAt (timestamptz, generated with clock_timestamp()), issuer (text), modifiedAt (timestamptz), subject (text). The table includes a unique index on id using btree with uuid_ops.
Gel imports for auth schema
When working with Gel auth schemas, import from drizzle-orm/gel-core: gelTable, uniqueIndex, uuid, text, gelSchema, timestamptz, foreignKey. Also import sql from drizzle-orm for default values.
Define tsvector custom type for PostgreSQL full-text search
Create a custom type for tsvector data in Drizzle using customType() with a dataType method that returns 'tsvector'. This custom type is needed to store computed text search vectors in PostgreSQL.
Create generated column with generatedAlwaysAs for full-text search
Use the generatedAlwaysAs() method with an SQL expression to create a generated column that computes a tsvector for full-text search. The method takes an SQL function that references other columns in the table.
Combine multiple generated tsvector columns with setweight
Use PostgreSQL's setweight() function inside a generated column to assign different weights to text from different columns. The weights 'A', 'B', 'C', 'D' mark entries from different parts of a document (typically title vs body). Combine weighted vectors using the || operator.
Example: Generated column with weighted multiple columns
This example shows a 'search' generated column that combines title and body text vectors with different weights. The title gets weight 'A' (highest) and body gets weight 'B', allowing full-text search to prioritize title matches.
Generated column is computed automatically on insert
When you insert a row into a table with a generated column, the value of the generated column is automatically computed from the expression defined in the schema. You do not need to provide a value for the generated column.
Example: Simple full-text search generated column
This example shows a 'bodySearch' generated column that converts the 'body' column to a tsvector using PostgreSQL's to_tsvector function with English language configuration.
Index generated tsvector columns with GIN for performance
Use the using('gin') method when creating an index on a tsvector generated column. GIN (Generalized Inverted Index) is the appropriate index type for full-text search vectors in PostgreSQL.
Insert geometry point data with mode 'xy'
When inserting geometry data with mode: 'xy', pass the location as an object with x and y properties. Example: db.insert(stores).values({ name: 'Test', location: { x: -90.9, y: 18.7 } }).
Insert geometry point data with mode 'tuple'
When inserting geometry data with mode: 'tuple', pass the location as an array [x, y]. Example: db.insert(stores).values({ name: 'Test', location: [-90.9, 18.7] }).
Insert geometry point data with raw SQL
To insert geometry data using raw SQL, use the sql function with PostGIS functions. Example: db.insert(stores).values({ name: 'Test', location: sql`ST_SetSRID(ST_MakePoint(-90.9, 18.7), 4326)` }).
Query nearest location by coordinates with PostGIS
Example query: db.select({ ...getColumns(stores), distance: sql`ST_Distance(${stores.location}, ${sqlPoint})` }).from(stores).orderBy(sql`${stores.location} <-> ${sqlPoint}`).limit(1). This uses getColumns() (available from drizzle-orm@1.0.0-beta.2; use getTableColumns() for pre-1 versions) to spread all columns and compute distance.
Filter locations within rectangular area with ST_MakeEnvelope and ST_Within
Use ST_MakeEnvelope() to create a rectangular polygon from minimum and maximum X and Y values, and ST_Within() to test if a geometry is within that polygon. Example: db.select().from(stores).where(sql`ST_Within(${stores.location}, ST_MakeEnvelope(${x1}, ${y1}, ${x2}, ${y2}, 4326))`). ST_Within() returns TRUE if geometry A is within geometry B.
ST_Distance and <-> operator for nearest location queries
Use the <-> operator in orderBy() and ST_Distance() function to compute distance between geometries and find the nearest location. The <-> operator returns the minimum planar distance between two geometries for geometry types.
PostGIS ST_MakePoint function usage
ST_MakePoint() creates a geometric object of type point using specified coordinates. It is commonly used with ST_SetSRID() to set the SRID (unique identifier associated with a specific coordinate system, tolerance, and resolution) on the geometry.
Match exact phrases with phraseto_tsquery
Use the phraseto_tsquery function to match exact phrases where word order matters. Example: `where(sql`to_tsvector('english', ${posts.title}) @@ phraseto_tsquery('english', 'family trip')`)` converts the input to 'family <-> trip' internally and only returns results where 'family' and 'trip' appear adjacent in that order.
Web search syntax for full-text search with websearch_to_tsquery
Use the websearch_to_tsquery function which provides a simplified syntax similar to web search engines. Example: `where(sql`to_tsvector('english', ${posts.title}) @@ websearch_to_tsquery('english', 'family or first trip Europe or Asia')`)` converts the input to 'family | first & trip & europ | asia' internally.
Match multiple keywords with plainto_tsquery
Use the plainto_tsquery function to match multiple keywords in a phrase. Example: `where(sql`to_tsvector('english', ${posts.title}) @@ plainto_tsquery('english', 'discover Italy')`)` converts the input to 'discover & Italy' internally and returns results containing both terms.
Full-text search on multiple columns in PostgreSQL
To implement full-text search on multiple columns, create a GIN index using setweight to concatenate tsvectors from multiple columns. Example: `index('search_index').using('gin', sql`(setweight(to_tsvector('english', ${table.title}), 'A') || setweight(to_tsvector('english', ${table.description}), 'B'))`)`. The setweight function labels entries with weights A, B, C, or D to mark entries from different parts of a document, such as title versus body.
Full-text search query with to_tsquery in Drizzle
Use the sql operator with to_tsvector and to_tsquery functions in a where clause. Example: `await db.select().from(posts).where(sql`to_tsvector('english', ${posts.title}) @@ to_tsquery('english', ${title})`)` searches for exact keyword matches in the title column.
Match any keywords with | operator in PostgreSQL full-text search
Use the pipe operator (|) in to_tsquery to match by any of the keywords. Example: `where(sql`to_tsvector('english', ${posts.title}) @@ to_tsquery('english', 'Europe | Asia')`)` returns results containing either 'Europe' or 'Asia'.
Query full-text search across multiple columns
When querying multiple columns with full-text search, use setweight to concatenate tsvectors and then match against tsquery. Example: `where(sql`(setweight(to_tsvector('english', ${posts.title}), 'A') || setweight(to_tsvector('english', ${posts.description}), 'B')) @@ to_tsquery('english', ${title})`)` searches both title and description columns with weighted importance.
PostgreSQL full-text search with to_tsquery and @@ operator
The to_tsquery function converts a keyword to normalized tokens and returns a tsquery that matches the lexemes in a tsvector. The @@ operator is used for direct matches between tsvector and tsquery.
Create GIN index for full-text search in PostgreSQL
To enhance the performance of full-text search, create a GIN index on a text column using to_tsvector. Example schema: `index('title_search_index').using('gin', sql`to_tsvector('english', ${table.title})`)`. This generates SQL: `CREATE INDEX IF NOT EXISTS "title_search_index" ON "posts" USING gin (to_tsvector('english', "title"));`
PostgreSQL full-text search with to_tsvector
The to_tsvector function parses a textual document into tokens, reduces the tokens to lexemes, and returns a tsvector which lists the lexemes together with their positions in the document. Example: `sql`select to_tsvector('english', 'Guide to PostgreSQL full-text search with Drizzle ORM')`` returns `"'drizzl':9 'full':5 'full-text':4 'guid':1 'orm':10 'postgresql':3 'search':7 'text':6"`.
Rank full-text search results with ts_rank and ts_rank_cd
Use ts_rank or ts_rank_cd functions to rank search results. The ts_rank function focuses on the frequency of query terms throughout the document. The ts_rank_cd function focuses on the proximity of query terms within the document. Example: `select({ ...getColumns(posts), rank: sql`ts_rank(${matchQuery})`, rankCd: sql`ts_rank_cd(${matchQuery})` }).orderBy((t) => desc(t.rank))` returns results ordered by relevance score.