@os-team/slonik-seeder

A simple helper that seeds data to the database using slonik.

Why

Usually, the process of seeding the data looks like this:

const users = await pool.many(sql.unsafe`
  INSERT INTO users (name, email)
  VALUES
    ('Ilya', '[email protected]'),
    ('Mikhail', '[email protected]')
  RETURNING *
`);

or

const users = await pool.many(sql.unsafe`
  INSERT INTO users (name, email)
  SELECT * FROM ${sql.unnest(
    [
      ['Ilya', '[email protected]'],
      ['Mikhail', '[email protected]'],
    ],
    ['text', 'text']
  )}
  RETURNING *
`);

Since this code snippet is repeated many times in each test case, I wanted to make it as short as possible in order to write tests faster.

Using this library, seeding the data looks as follows:

const users = await seed('users', [
  { name: 'Ilya', email: '[email protected]' },
  { name: 'Mikhail', email: '[email protected]' },
]);

Usage

Install the package in devDependencies (-D) using the following command:

yarn add -D @os-team/slonik-seeder

Create a seeder and use it as follows:

import { createSeeder } from '@os-team/slonik-seeder';

// You can initialize it only once in the global setup script if you want (`setupFilesAfterEnv`).
const seed = createSeeder(pool);

// Seed a user
const user = await seed('users', {
  name: 'Ilya',
  email: '[email protected]',
});
console.log(user); // { id: 1, name: 'Ilya', email: '[email protected]' }

// Seed multiple users
const users = await seed('users', [
  { name: 'Ilya', email: '[email protected]' },
  { name: 'Mikhail', email: '[email protected]' },
]);
console.log(users); // [{ id: 1, name: 'Ilya', email: '[email protected]' }, ...]

Custom types

The seeder under the hood uses the following query to insert new rows:

const users = await pool.many(sql.unsafe`
  INSERT INTO users (name, email)
  SELECT * FROM ${sql.unnest(
    [
      ['Ilya', '[email protected]'],
      ['Mikhail', '[email protected]'],
    ],
    ['text', 'text'] // Types of values
  )}
  RETURNING *
`);

Primitive types are detected automatically:

• string is text.
• number is int4 (if the number is an integer) or numeric.
• bigint is int8.
• boolean is bool.

If you want to set a value that has another type (e.g. timestamptz), you need to use a helper function:

import { timestamptz } from '@os-team/slonik-seeder';

const users = await seed('users', {
  name: 'Ilya',
  createdAt: timestamptz(new Date()), // The timestamp value can be: string, number (microseconds), Date
});

Available helper functions: bytea, timestamp, timestamptz, date, time, timetz, interval, point, path, line, lseg, box, polygon, circle, cidr, inet, macaddr, macaddr8, varbit, tsvector, tsquery, uuid, xml, json, jsonb, intRange, bigintRange, numRange, tsRange, tstzRange, dateRange.

You can also specify any type, as follows:

const users = await seed('users', {
  name: 'Ilya',
  createdAt: [new Date.toISOString(), 'timestamptz'], // [value, PostgreSQL type]
});

Transforming column names

Note that I named the columns in the camelCase format (e.g. createdAt), but the snake_case format is used in the database. Thus, it is necessary to transform column names before inserting rows.

You can set a column name transformer as follows:

const toSnakeCase = (value: string) =>
  value.replace(/[A-Z]/g, (c) => `_${c.toLowerCase()}`);

const seed = createSeeder(pool, { columnTransformer: toSnakeCase });