Install & Compatibility
Where this runs
tested against v? · npm install
Install × environment matrix
Each cell = how many times install + import succeeded across repeated harness runs. Partial = flaky.
glibc = Debian/Ubuntu slim · musl = Alpine Linux
muslnode 18–226 runs
build_error
glibcnode 18–226 runs
build_error
Code
Verified usage
Verified import paths — ran on the pinned version, not inferred.
sqlFixtures
✓ const sqlFixtures = require('sql-fixtures');
✗ import sqlFixtures from '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
✓ const { create } = require('sql-fixtures');
✗ import { create } from '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`.
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();
Debug
Known issues
gotchaThe 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.fixEvaluate 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.
affects: >=1.0.0
gotchaWhen 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.fixAlways 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.
affects: >=0.4.0 (MySQL/MariaDB), >=0.3.0 (SQLite)
gotchaThe 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.fixAlways 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. affects: >=1.0.0
gotchaSchema 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.fixAdopt 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.
affects: >=1.0.0
Errors
Common errors & fixes
TypeError: sqlFixtures.create is not a function
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`.
fixChange your import statement to `const sqlFixtures = require('sql-fixtures');`. Error: Cannot find module 'sql-fixtures'
The package is not installed or the Node.js runtime cannot locate it.
fixRun `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
The database specified in `dbConfig.connection.database` does not exist on the database server.
fixEnsure 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
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.
fixReview 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.
Audit
Dependencies
knexrequiredUsed internally for database interaction and query building.
pgoptionalRequired for PostgreSQL database connections.
mysqloptionalRequired for MySQL/MariaDB database connections.
sqlite3optionalRequired for SQLite database connections.