SQL Fixtures

Common errors

• TypeError: sqlFixtures.create is not a function

  cause This typically happens when trying to use ESM `import sqlFixtures from 'sql-fixtures';` instead of CommonJS `require()`. In ESM, `require()` might return the CommonJS module as `module.default`.
  fix Change your import statement to `const sqlFixtures = require('sql-fixtures');`.

• Error: Cannot find module 'sql-fixtures'

  cause The package is not installed or the Node.js runtime cannot locate it.
  fix Run `npm install sql-fixtures` or `yarn add sql-fixtures` to install the package. Verify that your `node_modules` directory is correctly set up and accessible.

• error: database "your_database_name" does not exist

  cause The database specified in `dbConfig.connection.database` does not exist on the database server.
  fix Ensure the database specified in your configuration is already created on your PostgreSQL, MySQL, or SQLite server before running `sql-fixtures`. For local development/testing, you might need to manually create it or add a step in your test runner.

• SQLITE_CONSTRAINT: FOREIGN KEY constraint failed

  cause This usually means that a foreign key reference in your fixture data points to a record that either does not exist or has not yet been inserted. Although `sql-fixtures` attempts to resolve dependencies, complex or circular dependencies can sometimes lead to this.
  fix Review your `dataSpec` to ensure that parent records (e.g., users) are defined before child records (e.g., posts) that reference them. If using `id` references like `@user->id (id=1)`, confirm that a `user` with `id: 1` is indeed defined in your fixtures. Simplify complex dependency chains where possible.

Warnings

• gotcha The package is 'dormant but stable' since its last update approximately five years ago (v1.0.4). While functional, new features are not expected, and community support might be limited. Consider alternatives for actively developed projects or if cutting-edge database features are needed.
  Fix: Evaluate if the current feature set meets your needs. For active development or broader ecosystem support, explore alternatives like `typeorm-fixtures-cli` (for TypeORM) or custom Knex/Sequelize seeding scripts.
• gotcha When using MySQL or MariaDB, issues can arise if tables lack a singular primary key. SQLite can also face similar problems if tables are created 'without rowid'. Ensure your database schema defines explicit primary keys.
  Fix: Always define a singular primary key for tables where `sql-fixtures` will insert data. For SQLite, avoid creating tables 'without rowid' if you intend to use this library.
• gotcha The package uses CommonJS (`require`) for module loading. Direct ESM `import` statements (e.g., `import sqlFixtures from 'sql-fixtures';`) will not work as expected and may cause runtime errors or incorrect module resolution, especially in modern Node.js environments configured for ESM.
  Fix: Always use `const sqlFixtures = require('sql-fixtures');` to import the module. If integrating into an ESM-only project, you might need to use dynamic `import()` or transpilation with tools like Rollup.
• gotcha Schema changes in your database can break existing fixture definitions. If your schema evolves frequently, monolithic fixture files can become hard to maintain. `sql-fixtures` relies on the database schema being present and matching the fixture data structure.
  Fix: Adopt a compositional approach to fixtures: break large fixture files into smaller, focused ones, ensuring each fixture is responsible for a single aspect of your data. This minimizes impact when schema changes occur, as only relevant fixture files need updating. Ensure your test setup includes schema migration/creation before running fixtures.

Install

• npm install sql-fixtures npm
• yarn add sql-fixtures yarn
• pnpm add sql-fixtures pnpm

Imports

• sqlFixtures
  wrong:

  import sqlFixtures from 'sql-fixtures';

  correct:

  const sqlFixtures = require('sql-fixtures');

  The package is a CommonJS module and does not officially support ESM imports. Using `import` will likely result in a runtime error or an object with a `.default` property containing the module.
• create
  wrong:

  import { create } from 'sql-fixtures';

  correct:

  const { create } = require('sql-fixtures');

  While CommonJS allows destructuring, the primary export is a single function/object. It's more idiomatic to import the whole module and access `sqlFixtures.create`.

Quickstart

This quickstart demonstrates how to use `sql-fixtures` to define and insert data into a PostgreSQL database, including handling foreign key relationships. It first sets up a basic schema using `knex` and then populates it with users and posts.

const sqlFixtures = require('sql-fixtures');
const Knex = require('knex');

// Ensure you have a running PostgreSQL database and 'pg' driver installed (npm install pg)
// For a real application, use environment variables for sensitive data.
const dbConfig = {
  client: 'pg',
  connection: {
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'testuser',
    password: process.env.DB_PASSWORD || 'testpassword',
    database: process.env.DB_NAME || 'testdb',
    port: process.env.DB_PORT ? parseInt(process.env.DB_PORT) : 5432
  }
};

const knex = Knex(dbConfig);

const dataSpec = {
  users: [
    { id: 1, username: 'alice', email: 'alice@example.com' },
    { id: 2, username: 'bob', email: 'bob@example.com' }
  ],
  posts: [
    { id: 101, title: 'First Post', content: 'Lorem ipsum...', author_id: '@user->id (id=1)' },
    { id: 102, title: 'Second Post', content: 'Dolor sit amet...', author_id: '@user->id (id=2)' }
  ]
};

async function runFixtures() {
  try {
    // Ensure tables exist before inserting data
    await knex.schema.dropTableIfExists('posts');
    await knex.schema.dropTableIfExists('users');
    await knex.schema.createTable('users', table => {
      table.integer('id').primary();
      table.string('username').notNullable().unique();
      table.string('email').notNullable().unique();
    });
    await knex.schema.createTable('posts', table => {
      table.integer('id').primary();
      table.string('title').notNullable();
      table.text('content');
      table.integer('author_id').unsigned().notNullable();
      table.foreign('author_id').references('id').inTable('users');
    });

    console.log('Database schema created/reset.');

    const result = await sqlFixtures.create(dbConfig, dataSpec);
    console.log('Fixtures inserted successfully:');
    console.log(JSON.stringify(result, null, 2));
    console.log('Users inserted:', result.users.length);
    console.log('Posts inserted:', result.posts.length);

  } catch (err) {
    console.error('Error running fixtures:', err);
  } finally {
    await knex.destroy();
  }
}

runFixtures();