drizzle-orm/seed

42 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Seeding table with unexposed foreign key constraint error

When seeding a table with a not-null foreign key column, if the referenced table is not exposed to the seed function schema, an error occurs: 'Column has not null constraint, and you didn't specify a table for foreign key on column in table.' This happens because the seeding function cannot generate values for the foreign key column without access to the referenced table.

Seeding table with unexposed nullable foreign key warning

When seeding a table with a nullable foreign key column, if the referenced table is not exposed to the seed function schema and the column is not refined, a warning occurs: 'Column in table will be filled with Null values because you specified neither a table for foreign key on column nor a function for column in refinements.' The column will be populated with null values.

Seed function options for unexposed foreign key tables

When a table with foreign key constraints cannot be seeded because the referenced table is unexposed, there are three resolution options: remove the not-null constraint from the foreign key column; expose the referenced table to the seed function schema by passing it in the schema object (e.g., await seed(db, { bloodPressure, users })); or refine the foreign key column generator using refinements.

Refining foreign key column generator in seed

To refine a foreign key column generator during seeding, use the refine() method on the seed result. Call refine() with a function that returns an object with the table name as a key, containing a columns object with refinement functions. Use funcs.valuesFromArray({ values: [1, 2] }) to specify an array of values to populate the foreign key column with. Note that the referenced table must already have IDs in the database before using this approach.

Example: refining userId column in seeding with valuesFromArray

Example showing how to refine a foreign key column during seeding: ```ts import { bloodPressure } from './schema.ts'; async function main() { const db = drizzle(...); await seed(db, { bloodPressure }).refine((funcs) => ({ bloodPressure: { columns: { userId: funcs.valuesFromArray({ values: [1, 2] }) } } })); } main(); ``` This example refines the userId column to use specific values [1, 2] from an array instead of generating random values or null.

Drizzle seed supports multiple databases

Drizzle seed is supported on PostgreSQL, MySQL, SQLite, MSSQL, and Cockroach databases.

drizzle-seed columns refinement

Within the refine callback, you can specify a columns object for each table. Each column can use a generator function to refine its behavior, or you can set a column to false to exclude it from seeding and allow the database to use its default value.

drizzle-seed with property for related entities

The with property in the refine callback defines how many related entities to create for each parent table in one-to-many relationships. For example, with: { posts: 10 } creates 10 posts for each user. This works only for one-to-many relationships, not many-to-one.

drizzle-seed weighted random overview

Weighted random allows you to use multiple datasets with different priorities during seeding. It can be used in column refinements and in the with property to determine the amount of related entities. You can define weights (0 to 1) and associated values or count ranges.

drizzle-seed weighted random for column values

You can use f.weightedRandom() within column refinements to generate values with different probabilities. Pass an array of objects with weight (decimal 0-1) and value (generator function) properties. For example: f.weightedRandom([{ weight: 0.3, value: f.int({ minValue: 10, maxValue: 100 }) }, { weight: 0.7, value: f.number({ minValue: 100, maxValue: 300, precision: 100 }) }])

Benefits of drizzle-seed pRNG

The pseudorandom number generator in drizzle-seed provides three main benefits: Consistency ensures tests run on the same data every time; Debugging makes it easier to reproduce and fix bugs with consistent data sets; Collaboration allows team members to share seed numbers to work with identical data sets.

drizzle-seed with limitation - one-to-many only

The with option works only for one-to-many relationships. For example, if you have one user and many posts, you can use users with posts, but you cannot use posts with users. You cannot use the with option for many-to-one relationships.

drizzle-seed basic example

This example creates 10 users with random names and identity-based primary keys: ```ts import { mssqlTable, int, varchar } from "drizzle-orm/mssql-core"; import { drizzle } from "drizzle-orm/node-mssql"; import { seed } from "drizzle-seed"; const users = mssqlTable("users", { id: int().primaryKey().identity(), name: varchar({ length: 255 }).notNull(), }); async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, { users }); } main(); ```

drizzle-seed exclude column example

This example seeds the users table but excludes the name column from seeding, allowing the database to use its default value: ```ts import { drizzle } from "drizzle-orm/node-mssql"; import { seed } from "drizzle-seed"; import * as schema from './schema.ts' async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, { users: schema.users }).refine((f) => ({ users: { columns: { name: false, } } })); } main(); ```

drizzle-seed with related entities example

This example seeds 20 users and creates 10 posts for each user: ```ts import { drizzle } from "drizzle-orm/node-mssql"; import { seed } from "drizzle-seed"; import * as schema from './schema.ts' async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, schema).refine((f) => ({ users: { count: 20, with: { posts: 10 } } })); } main(); ```

drizzle-seed custom generator functions example

This example refines id generation for users (int from 10000 to 20000, unique) and populates posts with descriptions from a self-defined array: ```ts import { drizzle } from "drizzle-orm/node-mssql"; import { seed } from "drizzle-seed"; import * as schema from './schema.ts' async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, schema).refine((f) => ({ users: { count: 5, columns: { id: f.int({ minValue: 10000, maxValue: 20000, isUnique: true, }), } }, posts: { count: 100, columns: { description: f.valuesFromArray({ values: [ "The sun set behind the mountains, painting the sky in hues of orange and purple", "I can't believe how good this homemade pizza turned out!", "Sometimes, all you need is a good book and a quiet corner.", "Who else thinks rainy days are perfect for binge-watching old movies?", "Tried a new hiking trail today and found the most amazing waterfall!", ], }) } } })); } main(); ```

drizzle-seed weighted random for related entities

You can use weighted random in the with property to create variable numbers of related entities with different probabilities. Each object specifies a weight and a count range (array of possible counts). For example: details: [{ weight: 0.6, count: [1, 2, 3] }, { weight: 0.3, count: [5, 6, 7] }, { weight: 0.1, count: [8, 9, 10] }]

Basic drizzle-seed usage with MSSQL

To seed a database, import the seed function from drizzle-seed, create a drizzle database instance with your connection string, and call await seed(db, { tables }) to populate the database. By default, the seed function creates 10 entities per table.

drizzle-seed count option

By default, the seed function creates 10 entities per table. You can specify a different count using the count option in the seed options object: await seed(db, schema, { count: 1000 })

drizzle-seed seed option for reproducibility

You can specify a seed number in the seed options to generate a different set of values for all subsequent runs. Any new number will generate a unique set of values: await seed(db, schema, { seed: 12345 })

Reset database with drizzle-seed

You can reset your database and seed it with new values using the reset function from drizzle-seed. Import your schema and call await reset(db, schema) to truncate all tables and remove existing data.

MSSQL database reset strategy in drizzle-seed

For MSSQL, drizzle-seed first disables FOREIGN_KEY_CHECKS, then generates TRUNCATE statements to empty all tables, and finally re-enables FOREIGN_KEY_CHECKS. The SQL pattern is: SET FOREIGN_KEY_CHECKS = 0; TRUNCATE tableName1; TRUNCATE tableName2; ... SET FOREIGN_KEY_CHECKS = 1;

drizzle-seed overview and purpose

drizzle-seed is a TypeScript library that generates deterministic, yet realistic, fake data for database population. It uses a seedable pseudorandom number generator (pRNG) to ensure consistent and reproducible data generation across different runs, useful for testing, development, and debugging.

Deterministic data generation definition

Deterministic data generation means that the same input will always produce the same output. When drizzle-seed is initialized with the same seed number, it generates the same sequence of fake data every time, allowing for predictable and repeatable data sets.

Pseudorandom number generator benefits

Using a pseudorandom number generator with drizzle-seed provides: Consistency - ensures tests run on the same data every time; Debugging - easier reproduction and fixing of bugs with consistent data sets; Collaboration - team members can share seed numbers to work with the same data sets.

drizzle-seed installation

Install drizzle-seed using npm: npm install drizzle-seed

seed function basic usage example

This example creates 10 users with random names and auto-incremented ids. Import singlestoreTable, create a users table schema, then call seed(db, { users }) inside an async function to populate the database: ```ts import { singlestoreTable, int, varchar } from "drizzle-orm/singlestore-core"; import { drizzle } from "drizzle-orm/singlestore"; import { seed } from "drizzle-seed"; const users = singlestoreTable("users", { id: int().primaryKey().autoincrement(), name: varchar({ length: 255 }).notNull(), }); async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, { users }); } main(); ```

seed function count option

The seed function creates 10 entities by default. To generate more entities for tests, specify the count in the seed options object: await seed(db, schema, { count: 1000 });

seed function seed option for determinism

To generate a different set of values for all subsequent runs, define a different number in the seed option. Any new number will generate a unique set of values: await seed(db, schema, { seed: 12345 });

reset function for database seeding

The reset function resets the database and seeds it with new values, useful for test suites. Import the reset function and call it with the database instance and schema: await reset(db, schema);

SingleStore reset strategy in drizzle-seed

For SingleStore, drizzle-seed first disables FOREIGN_KEY_CHECKS to prevent constraint failures, then generates TRUNCATE statements to empty all tables, and finally re-enables FOREIGN_KEY_CHECKS. The SQL executed is: ```sql SET FOREIGN_KEY_CHECKS = 0; TRUNCATE tableName1; TRUNCATE tableName2; ... SET FOREIGN_KEY_CHECKS = 1; ```

refine method for customizing seed generation

The refine method is a callback that receives a list of all available generator functions from drizzle-seed and returns an object with keys representing tables to refine. Each table can specify: columns - to refine default behavior of each column by specifying a generator function or excluding with false; count - to specify the number of rows to insert (overrides global count if defined); with - to define how many referenced entities to create for each parent table.

refine method basic API

The refine method API syntax is: ```ts await seed(db, schema).refine((f) => ({ users: { columns: {}, count: 10, with: { posts: 10 } }, })); ```

refine columns to customize generation for single table

This example seeds only the users table with 20 entities using refined seed logic for the name column with fullName() generator: ```ts import { drizzle } from "drizzle-orm/singlestore"; import { seed } from "drizzle-seed"; import * as schema from './schema.ts' async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, { users: schema.users }).refine((f) => ({ users: { columns: { name: f.fullName(), }, count: 20 } })); } main(); ```

refine columns with false to exclude from seeding

To exclude a column from seeding and allow the database to use its default value, set the column to false in the columns object: ```ts import { drizzle } from "drizzle-orm/singlestore"; import { seed } from "drizzle-seed"; import * as schema from './schema.ts' async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, { users: schema.users }).refine((f) => ({ users: { columns: { name: false, // the name column will not be seeded } } })); } main(); ```

refine with property for related entity generation

This example seeds 20 users and creates 10 posts for each user by seeding the posts table and creating a reference from posts to users: ```ts import { drizzle } from "drizzle-orm/singlestore"; import { seed } from "drizzle-seed"; import * as schema from './schema.ts' async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, schema).refine((f) => ({ users: { count: 20, with: { posts: 10 } } })); } main(); ```

refine with custom int and valuesFromArray generators

This example seeds 5 users with custom id generation (10000-20000, unique) and 100 posts with predefined description values: ```ts import { drizzle } from "drizzle-orm/singlestore"; import { seed } from "drizzle-seed"; import * as schema from './schema.ts' async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, schema).refine((f) => ({ users: { count: 5, columns: { id: f.int({ minValue: 10000, maxValue: 20000, isUnique: true, }), } }, posts: { count: 100, columns: { description: f.valuesFromArray({ values: [ "The sun set behind the mountains, painting the sky in hues of orange and purple", "I can't believe how good this homemade pizza turned out!", "Sometimes, all you need is a good book and a quiet corner.", "Who else thinks rainy days are perfect for binge-watching old movies?", "Tried a new hiking trail today and found the most amazing waterfall!", ], }) } } })); } main(); ```

weightedRandom for column generation

The weightedRandom API allows specifying multiple datasets with different priorities during seeding. It takes an array of objects with weight (0 to 1) and value (generator function): ```ts await seed(db, schema).refine((f) => ({ orders: { count: 5000, columns: { unitPrice: f.weightedRandom( [ { weight: 0.3, value: funcs.int({ minValue: 10, maxValue: 100 }) }, { weight: 0.7, value: funcs.number({ minValue: 100, maxValue: 300, precision: 100 }) } ] ), } } })); ```

weightedRandom for related entity count

The with property can use weightedRandom to determine the count of related entities with different probability distributions. This example generates 1-3 details with 60% chance, 5-7 with 30% chance, and 8-10 with 10% chance per order: ```ts import { drizzle } from "drizzle-orm/singlestore"; import { seed } from "drizzle-seed"; import * as schema from './schema.ts' async function main() { const db = drizzle(process.env.DATABASE_URL!); await seed(db, schema).refine((f) => ({ orders: { with: { details: [ { weight: 0.6, count: [1, 2, 3] }, { weight: 0.3, count: [5, 6, 7] }, { weight: 0.1, count: [8, 9, 10] }, ] } } })); } main(); ```

with option works for one-to-many relationships only

The with option supports one-to-many relationships. For example, you can use users with posts (one user has many posts), but you cannot use posts with users (many posts do not have one user). If you have circular dependencies, the with option will display all tables and you must manually select the one with a one-to-many relationship.

TypeScript limitations in drizzle-seed with option

Due to TypeScript limitations and the current Drizzle API, it is not possible to properly infer references between tables, especially with circular dependencies. The with option will display all tables and requires manual selection of tables with one-to-many relationships.

TypeScript limitations for third parameter in Drizzle tables

Currently, drizzle-seed does not have type support for the third parameter in Drizzle tables. While it will work at runtime, it will not function correctly at the type level.

Give your agent this brain