drizzle-orm/seeding

102 notes in this subject, read out of this brain and free to use. This is page 1 of 2.

Seeding self-referencing table with 'with' is not allowed

Self-referencing tables (where a table references itself, like users.reportsTo referencing users.id) cannot use the 'with' option for seeding. Attempting to do so will cause the error "table has self reference. You can't specify table as parameter in table.with object".

Seeding error: table doesn't have reference to related table

When using the 'with' option in seeding, if you get the error "table doesn't have a reference to the related table or you didn't include your one-to-many relation in the seed function schema", you can resolve it in two ways: (1) Add a foreign key reference to the column in your schema using .references(), or (2) Add the one-to-many relation to your schema using relations() and include it in the seed function schema parameter.

Seeding 'with' option requires correct relation direction

The 'with' option must match the actual relationship direction in the schema. If posts reference users (one user has many posts), then you must seed it as users: { with: { posts: 3 } }, not posts: { with: { users: 3 } }. Attempting to seed it in the wrong direction will cause an error.

Adding foreign key reference for seeding

To enable seeding with the 'with' option, add a .references() call to the foreign key column. For example: authorId: integer('author_id').notNull().references(() => users.id) establishes that posts reference users and enables seeding with one-to-many relationships.

Seeding with 'with' option requires one-to-many relationship

The 'with' option in the seed function is used to define one-to-many relationships. For example, if one user has many posts, you can use 'with' to seed related data: users: { count: 2, with: { posts: 3 } } will create 2 users and 3 posts for each user.

Seeding supported databases

Database seeding with the 'with' option is supported on PostgreSQL, MySQL, SQLite, MSSQL, and CockroachDB.

Adding explicit relations for seeding

To enable seeding with the 'with' option using explicit relations, define a relations object and include it in the seed function schema. Example: export const postsRelations = relations(posts, ({ one }) => ({ author: one(users, { fields: [posts.authorId], references: [users.id] }) })), then pass { users, posts, postsRelations } to the seed function.

datetime generator parameters

The datetime generator accepts three parameters: min (string | Date, optional), max (string | Date, optional), and arraySize (number, optional). It generates datetime objects. If only one parameter is provided, the unspecified parameter is calculated by adding or subtracting 4 years to/from the specified one (or 2 years if only one is set). Default range is from 2024-05-08 minus 2 years to 2024-05-08 plus 2 years if neither is set.

text generator

The text generator accepts one parameter: arraySize (number, optional). It generates JSON objects with a fixed structure containing fields like email, name, isGraduated, hasJob, salary, startedWorking, visitedCountries. The JSON structure is picked randomly.

string generator parameters

The string generator accepts two parameters: isUnique (bit, defaults to database column uniqueness) and arraySize (number, optional). It generates random strings.

firstName generator parameters

The firstName generator accepts two parameters: isUnique (bit, defaults to database column uniqueness) and arraySize (number, optional). It generates a person's first name.

lastName generator parameters

The lastName generator accepts two parameters: isUnique (bit, defaults to database column uniqueness) and arraySize (number, optional). It generates a person's last name.

fullName generator parameters

The fullName generator accepts two parameters: isUnique (bit, defaults to database column uniqueness) and arraySize (number, optional). It generates a person's full name.

email generator parameters

The email generator accepts one parameter: arraySize (number, optional). It generates unique email addresses.

phoneNumber generator parameters and modes

The phoneNumber generator accepts four parameters: template (string, optional), prefixes (string[], optional), generatedDigitsNumbers (number | number[], optional, default 7 if prefixes defined), and arraySize (number, optional). It can generate phone numbers using either a template property (where '#' symbols are substituted with generated digits) or prefixes and generatedDigitsNumbers properties (not compatible with template). The dataset for default prefixes is available at https://github.com/OleksiiKH0240/drizzle-orm/blob/main/drizzle-seed/src/datasets/phonesInfo.ts. generatedDigitsNumbers can be an array to specify different digit counts for different prefixes.

country generator parameters

The country generator accepts two parameters: isUnique (bit, defaults to database column uniqueness) and arraySize (number, optional). It generates country names.

city generator parameters

The city generator accepts two parameters: isUnique (bit, defaults to database column uniqueness) and arraySize (number, optional). It generates city names.

streetAddress generator parameters

The streetAddress generator accepts two parameters: isUnique (bit, defaults to database column uniqueness) and arraySize (number, optional). It generates street addresses.

jobTitle generator parameters

The jobTitle generator accepts one parameter: arraySize (number, optional). It generates job titles.

postcode generator parameters

The postcode generator accepts two parameters: isUnique (bit, defaults to database column uniqueness) and arraySize (number, optional). It generates postal codes.

state generator parameters

The state generator accepts one parameter: arraySize (number, optional). It generates US states.

companyName generator parameters

The companyName generator accepts two parameters: isUnique (bit, defaults to database column uniqueness) and arraySize (number, optional). It generates random company names.

loremIpsum generator parameters

The loremIpsum generator accepts two parameters: sentencesCount (number, default 1) and arraySize (number, optional). It generates lorem ipsum text sentences.

point generator parameters

The point generator accepts eight parameters: isUnique (bit, defaults to database column uniqueness), maxXValue (number, default: 10 * 1000 if isUnique false, 10 * count if isUnique true), minXValue (number, default -maxXValue), maxYValue (number, default: 10 * 1000 if isUnique false, 10 * count if isUnique true), minYValue (number, default -maxYValue), and arraySize (number, optional). It generates 2D points within specified ranges for x and y coordinates.

inet generator parameters

The inet generator accepts four parameters: isUnique (bit, defaults to database column uniqueness), arraySize (number, optional), ipAddress (string, default 'ipv4', accepts 'ipv4' or 'ipv6'), and includeCidr (bit, default true). It generates IP addresses based on specified parameters.

geometry generator MSSQL arraySize limitation

In MSSQL, if you set arraySize to a value greater than 1 or try to insert more than one geometry point element into a geometry(point, 0)[] column via drizzle-orm, an error will occur. This is a known bug in the backlog. Use arraySize: 1 as a workaround.

geometry generator MSSQL SRID limitation

In MSSQL, if you set the SRID of a geometry(point) column to anything other than 0 (for example, 4326) in your drizzle-orm table declaration, an error will occur during the seeding process. This is a known bug in the backlog. Set SRID to 0 in the column definition and pass the desired SRID value to the generator instead.

geometry generator parameters

The geometry generator accepts five parameters: isUnique (bit, defaults to database column uniqueness), arraySize (number, optional), type (string, default 'point', currently only 'point' is supported), srid (number, default 4326, accepts 4326 or 3857), and decimalPlaces (number, default 6, accepts 1-7). It generates geometry objects based on the given parameters. When srid is 4326, decimalPlaces controls the decimal precision for point coordinates (e.g., decimalPlaces = 3 produces values like 'point(30.723 46.482)').

vector generator parameters

The vector generator accepts six parameters: isUnique (bit, defaults to database column uniqueness), arraySize (number, optional), decimalPlaces (number, default 2), dimensions (number, defaults to database column's dimensions), minValue (number, default -1000), and maxValue (number, default 1000). It generates vectors based on provided parameters. decimalPlaces controls decimal precision for each vector element (e.g., decimalPlaces = 3 produces values like 1.123). dimensions specifies the number of elements in each generated vector (e.g., dimensions = 3 produces values like [1,2,3]).

timestamp generator parameters

The timestamp generator accepts three parameters: min (string | Date, optional), max (string | Date, optional), and arraySize (number, optional). It generates timestamps. If only one parameter is provided, the unspecified parameter is calculated by adding or subtracting 4 years to/from the specified one (or 2 years if only one is set). Default range is from 2024-05-08 minus 2 years to 2024-05-08 plus 2 years if neither is set.

default generator parameters

The default generator accepts two parameters: defaultValue (any, required) and arraySize (number, optional). It generates the same given value each time it is called.

valuesFromArray generator parameters

The valuesFromArray generator accepts three parameters: values (any[] | { weight: number; values: any[] }[], required), isUnique (bit, defaults to database column uniqueness), and arraySize (number, optional). It generates values from a given array, optionally with weighted values.

number generator parameters

The number generator accepts five parameters: isUnique (bit, defaults to database column uniqueness), precision (number, default 100), maxValue (number, default: precision * 1000 if isUnique false, precision * count if isUnique true), minValue (number, default -maxValue), and arraySize (number, optional). It generates floating point numbers within the given range.

number generator precision meaning

In the number generator, precision equals 10 means values are accurate to one tenth (1.2, 34.6), and precision equals 100 means values are accurate to one hundredth (1.23, 34.67).

int generator parameters

The int generator accepts four parameters: isUnique (bit, defaults to database column uniqueness), maxValue (number | bigint, default: 1000 if isUnique false, count * 10 if isUnique true), minValue (number | bigint, default -maxValue), and arraySize (number, optional). It generates integers within the given range.

bit generator parameters

The bit generator accepts one parameter: arraySize (number, optional). It generates bit values (true or false).

drizzle-seed v2 string generator enhancement

In version 2, both unique and non-unique string generators were changed to add the ability to generate unique strings based on the length of the text column (e.g., varchar(20)). This upgrade affects tables that include columns of text-like types (char, varchar, binary, varbinary) with a maximum length parameter or unique constraints.

drizzle-seed version downgrade for generator compatibility

When specifying a maximum version to use, older generators are used while newer ones remain unavailable. For example, with version: '2', you get v1 firstName generator and v2 lastName generator. With version: '1', both firstName and lastName generators use v1. This allows using a mix of old and new generator versions.

drizzle-seed MSSQL interval and string seeding example

Example of seeding MSSQL interval columns with version 2: ```ts import { binary, char, mssqlTable, text, varbinary, varchar } from 'drizzle-orm/mssql-core'; import { drizzle } from 'drizzle-orm/node-mssql'; import { seed } from "drizzle-seed"; const intervals = mssqlTable('intervals', { interval1: char({ length: 255 }).unique(), interval2: char({ length: 255 }), interval3: varchar({ length: 255 }).unique(), interval4: varchar({ length: 255 }), interval5: binary({ length: 255 }).unique(), interval6: binary({ length: 255 }), interval7: varbinary({ length: 255 }).unique(), interval8: varbinary({ length: 255 }), interval9: text(), }); async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, { intervals }, { version: '2' }).refine((f) => ({ intervals: { columns: { interval: f.interval({ isUnique: true }), interval1: f.interval({ isUnique: true }), interval2: f.interval({ isUnique: true }), interval3: f.interval({ isUnique: true }), interval4: f.interval({ isUnique: true }), interval5: f.interval({ isUnique: true }), interval6: f.interval({ isUnique: true }), interval7: f.interval({ isUnique: true }), interval8: f.interval({ isUnique: true }), interval9: f.interval({ isUnique: true }), }, }, })); } main(); ```

drizzle-seed MSSQL string seeding example

Example of seeding MSSQL string columns with custom refine: ```ts import { binary, char, mssqlTable, text, varbinary, varchar } from 'drizzle-orm/mssql-core'; import { drizzle } from 'drizzle-orm/node-mssql'; import { seed } from "drizzle-seed"; const strings = mssqlTable('strings', { string1: char({ length: 255 }).unique(), string2: char({ length: 255 }), string3: char({ length: 255 }), string4: varchar({ length: 255 }).unique(), string5: varchar({ length: 255 }), string6: varchar({ length: 255 }), string7: binary({ length: 255 }).unique(), string8: binary({ length: 255 }), string9: binary({ length: 255 }), string10: varbinary({ length: 255 }).unique(), string11: varbinary({ length: 255 }), string12: varbinary({ length: 255 }), string13: text(), }); async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, { strings }).refine((f) => ({ strings: { columns: { string1: f.string({ isUnique: true }), string2: f.string({ isUnique: true }), string3: f.string(), string4: f.string({ isUnique: true }), string5: f.string({ isUnique: true }), string6: f.string(), string7: f.string({ isUnique: true }), string8: f.string({ isUnique: true }), string9: f.string(), string10: f.string({ isUnique: true }), string11: f.string({ isUnique: true }), string12: f.string(), string13: f.string({ isUnique: true }), }, }, })); } main(); ```

drizzle-seed v4 uuid validation with Zod example

Example of using uuid generator with Zod schema validation: ```ts import { createSelectSchema } from 'drizzle-zod'; import { seed } from 'drizzle-seed'; await seed(db, { uuidTest: schema.uuidTest }, { count: 1 }).refine((funcs) => ({ uuidTest: { columns: { col1: funcs.uuid() } } })); const uuidSelectSchema = createSelectSchema(schema.uuidTest); const res = await db.select().from(schema.uuidTest); // With v4 generator, this parsing succeeds; with old version, this would throw an error uuidSelectSchema.parse(res[0]); ``` This shows that v4 uuid generator produces valid v4 UUIDs that pass Zod validation, unlike the previous version.

drizzle-seed v2 interval generator fix

In version 2, the unique interval generator was changed to fix a bug where it could produce intervals like '1 minute 60 seconds' and '2 minutes 0 seconds' as distinct values. When '1 minute 60 seconds' is inserted into MSSQL, it is automatically converted to '2 minutes 0 seconds', causing unique constraint violations. Version 2 fixes this issue by generating normalized intervals that respect MSSQL's interval normalization.

drizzle-seed version history

API version v1 was released in npm version 0.1.1 with no changed generators. API version v2 was released in npm version 0.2.1 with changed generators: string() and interval({ isUnique: true }). API version v3 was released in npm version 0.4.0 with hash generating function changed. API version v4 (LTS) was released in npm version 1.0.0-beta.8 with uuid generator changed.

seed function version parameter

The seed function accepts a version option to specify which version of generators to use. Example: await seed(db, schema, { version: '2' });

arraySize and isUnique combined behavior pitfall

When specifying arraySize along with isUnique in generators that support both, the isUnique constraint generates unique individual values (not unique arrays), which are then packed into arrays. This means each element in the array will be unique, not the array itself.

Example: seed with vector generator

```ts import { seed } from "drizzle-seed"; await seed(db, schema, { count: 1000 }).refine((funcs) => ({ vectorTable: { columns: { vector: funcs.vector({ isUnique: true, arraySize: 3, decimalPlaces: 5, dimensions: 12, minValue: -100, maxValue: 100, }), }, }, })); ```

drizzle-seed generator functions overview

The drizzle-seed library provides generator functions within the seed().refine() method for creating test data. Generator functions are called with parameters to configure data generation for table columns. Most generators support an arraySize parameter to generate arrays of values.

geometry generator - generates geometry objects

The geometry generator produces geometry objects, currently supporting points only. Parameters: isUnique (boolean, default: database column uniqueness) - controls uniqueness; arraySize (number, optional) - generates one-dimensional arrays if specified, but currently arraySize > 1 is not supported with geometry arrays; type (string, default: 'point') - geometry type, only 'point' is currently supported; srid (number, default: 4326) - Spatial Reference System Identifier, either 4326 or 3857; decimalPlaces (number, default: 6, allowed: 1-7) - decimal precision for point coordinates (e.g., decimalPlaces=3 produces values like 'point(30.723 46.482)'). Known limitation: SRID must be 0 in table declaration even if generating points with srid 4326.

Example: seed with default generator

```ts import { seed } from "drizzle-seed"; await seed(db, schema, { count: 1000 }).refine((funcs) => ({ posts: { columns: { content: funcs.default({ defaultValue: "post content", arraySize: 3 }), }, }, })); ```

Example: seed with valuesFromArray generator

```ts import { seed } from "drizzle-seed"; await seed(db, schema, { count: 1000 }).refine((funcs) => ({ posts: { columns: { title: funcs.valuesFromArray({ values: ["Title1", "Title2", "Title3", "Title4", "Title5"], isUnique: true, arraySize: 3 }), }, }, })); ```

Example: seed with intPrimaryKey generator

```ts import { seed } from "drizzle-seed"; await seed(db, schema, { count: 1000 }).refine((funcs) => ({ posts: { columns: { id: funcs.intPrimaryKey(), }, }, })); ```

Example: seed with int generator

```ts import { seed } from "drizzle-seed"; await seed(db, schema, { count: 1000 }).refine((funcs) => ({ products: { columns: { unitsInStock: funcs.int({ minValue: 0, maxValue: 100, isUnique: false, arraySize: 3 }), }, }, })); ```

Example: seed with date generator

```ts import { seed } from "drizzle-seed"; await seed(db, schema, { count: 1000 }).refine((funcs) => ({ users: { columns: { birthDate: funcs.date({ minDate: "1990-01-01", maxDate: "2010-12-31", arraySize: 3 }), }, }, })); ```

Example: seed with phoneNumber generator using template

```ts import { seed } from "drizzle-seed"; await seed(db, schema, { count: 1000 }).refine((funcs) => ({ users: { columns: { phoneNumber: funcs.phoneNumber({ template: "+(380) ###-####", arraySize: 3 }), }, }, })); ```

Example: seed with phoneNumber generator using prefixes

```ts import { seed } from "drizzle-seed"; await seed(db, schema, { count: 1000 }).refine((funcs) => ({ users: { columns: { phoneNumber: funcs.phoneNumber({ prefixes: ["+380 99", "+380 67"], generatedDigitsNumbers: 7, arraySize: 3 }), }, }, })); ```

Example: seed with geometry generator

```ts import { seed } from "drizzle-seed"; await seed(db, schema, { count: 1000 }).refine((funcs) => ({ geometryTable: { columns: { geometryPointTuple: funcs.geometry({ isUnique: true, arraySize: 1, type: "point", srid: 4326, decimalPlaces: 5, }), }, }, })); ```

drizzle-seed version 2 interval generator affected use case

You are affected by the version 2 interval generator changes if you use the unique interval generator in your seeding script, such as f.interval({ isUnique: true }) on columns with interval data.

drizzle-seed version backwards compatibility

You can downgrade to previous versions by specifying the version parameter. For example, to use v1 generators: await seed(db, schema, { version: '1' }). To use v2 generators with v1 hash function: await seed(db, schema, { version: '2' }). To use v3 generators with v1 uuid generator: await seed(db, schema, { version: '3' }).

drizzle-seed version 4 usage

To use version 4 of drizzle-seed (the latest LTS): await seed(db, schema) or explicitly await seed(db, schema, { version: '4' }).

seed() function version parameter

The seed() function accepts a version parameter to specify which API version to use for deterministic output. Syntax: await seed(db, schema, { version: '2' }). If no version is specified, the latest version is used by default.

Give your agent this brain