{"id":16537,"library":"sql-fixtures","title":"SQL Fixtures","description":"sql-fixtures is a JavaScript library designed to populate SQL databases with structured test data, commonly referred to as 'fixtures'. It automatically handles foreign key dependencies, making it suitable for integration testing and generating dummy data for development environments. The current stable version is 1.0.4, last published approximately five years ago. The package maintainer has stated it is 'dormant but stable,' indicating a maintenance-only release cadence where new features are not expected, but critical issues will be addressed. Internally, it leverages the `knex` SQL query builder, supporting PostgreSQL, MySQL, MariaDB, and SQLite. Its key differentiator is the ability to define data specifications in a simple JavaScript object format and have the library intelligently insert rows, resolving dependencies to ensure data integrity during population.","status":"maintenance","version":"1.0.4","language":"javascript","source_language":"en","source_url":"git://github.com/city41/node-sql-fixtures","tags":["javascript","fixtures","sql","postgres","sqlite","mysql","testing","dummy","data"],"install":[{"cmd":"npm install sql-fixtures","lang":"bash","label":"npm"},{"cmd":"yarn add sql-fixtures","lang":"bash","label":"yarn"},{"cmd":"pnpm add sql-fixtures","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Used internally for database interaction and query building.","package":"knex","optional":false},{"reason":"Required for PostgreSQL database connections.","package":"pg","optional":true},{"reason":"Required for MySQL/MariaDB database connections.","package":"mysql","optional":true},{"reason":"Required for SQLite database connections.","package":"sqlite3","optional":true}],"imports":[{"note":"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.","wrong":"import sqlFixtures from 'sql-fixtures';","symbol":"sqlFixtures","correct":"const sqlFixtures = require('sql-fixtures');"},{"note":"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`.","wrong":"import { create } from 'sql-fixtures';","symbol":"create","correct":"const { create } = require('sql-fixtures');"}],"quickstart":{"code":"const sqlFixtures = require('sql-fixtures');\nconst Knex = require('knex');\n\n// Ensure you have a running PostgreSQL database and 'pg' driver installed (npm install pg)\n// For a real application, use environment variables for sensitive data.\nconst dbConfig = {\n  client: 'pg',\n  connection: {\n    host: process.env.DB_HOST || 'localhost',\n    user: process.env.DB_USER || 'testuser',\n    password: process.env.DB_PASSWORD || 'testpassword',\n    database: process.env.DB_NAME || 'testdb',\n    port: process.env.DB_PORT ? parseInt(process.env.DB_PORT) : 5432\n  }\n};\n\nconst knex = Knex(dbConfig);\n\nconst dataSpec = {\n  users: [\n    { id: 1, username: 'alice', email: 'alice@example.com' },\n    { id: 2, username: 'bob', email: 'bob@example.com' }\n  ],\n  posts: [\n    { id: 101, title: 'First Post', content: 'Lorem ipsum...', author_id: '@user->id (id=1)' },\n    { id: 102, title: 'Second Post', content: 'Dolor sit amet...', author_id: '@user->id (id=2)' }\n  ]\n};\n\nasync function runFixtures() {\n  try {\n    // Ensure tables exist before inserting data\n    await knex.schema.dropTableIfExists('posts');\n    await knex.schema.dropTableIfExists('users');\n    await knex.schema.createTable('users', table => {\n      table.integer('id').primary();\n      table.string('username').notNullable().unique();\n      table.string('email').notNullable().unique();\n    });\n    await knex.schema.createTable('posts', table => {\n      table.integer('id').primary();\n      table.string('title').notNullable();\n      table.text('content');\n      table.integer('author_id').unsigned().notNullable();\n      table.foreign('author_id').references('id').inTable('users');\n    });\n\n    console.log('Database schema created/reset.');\n\n    const result = await sqlFixtures.create(dbConfig, dataSpec);\n    console.log('Fixtures inserted successfully:');\n    console.log(JSON.stringify(result, null, 2));\n    console.log('Users inserted:', result.users.length);\n    console.log('Posts inserted:', result.posts.length);\n\n  } catch (err) {\n    console.error('Error running fixtures:', err);\n  } finally {\n    await knex.destroy();\n  }\n}\n\nrunFixtures();","lang":"javascript","description":"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."},"warnings":[{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=0.4.0 (MySQL/MariaDB), >=0.3.0 (SQLite)"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"fix":"Change your import statement to `const sqlFixtures = require('sql-fixtures');`.","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`.","error":"TypeError: sqlFixtures.create is not a function"},{"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.","cause":"The package is not installed or the Node.js runtime cannot locate it.","error":"Error: Cannot find module 'sql-fixtures'"},{"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.","cause":"The database specified in `dbConfig.connection.database` does not exist on the database server.","error":"error: database \"your_database_name\" does not exist"},{"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.","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.","error":"SQLITE_CONSTRAINT: FOREIGN KEY constraint failed"}],"ecosystem":"npm"}