sql-fixtures is a Node.js module that generates SQL data. It is ideal for integration tests and dummy data. It supports PostgreSQL, MySQL, MariaDB and SQLite, and is easy to use. It uses knex under the hood.
npm install --save-dev sql-fixtures
You will also need to install a driver for whatever database you are using, such as pg for PostgreSQL, for example.
var sqlFixtures = require('sql-fixtures');
// depending on which database engine you are using
// this is a typical PostgreSQL config for the pg driver
var dbConfig = {
client: 'pg',
connection: {
host: 'localhost',
user: 'testdb',
password: 'password',
database: 'testdb',
port: 15432
}
};
var dataSpec = {
users: {
username: 'Bob',
email: 'bob@example.com'
}
};
sqlFixtures.create(dbConfig, dataSpec, function(err, result) {
// at this point a row has been added to the users table
console.log(result.users[0].username);
});
Both callbacks and promises are supported by sqlFixtures.create(). You can either do
sqlFixtures.create(dbConfig, dataSpec, callback)
or
sqlFixtures.create(dbConfig, dataSpec).then(callback, errback).
When using callbacks, the signature is the Node standard function(err, result), with err being falsy if there is no error.
This object indicates what data should get added to the database. The root keys of the object must match your table names exactly. The keys of the objects under the table must match column names exactly.
var dataSpec = {
// this must match your table's name exactly
users: {
// this must match your column name exactly
username: 'Bob'
}
};
If you need to insert more than one record, use an array instead
var dataSpec = {
users: [
{ username: 'Bob' },
{ username: 'Sally'}
]
};
sql-fixtures will resolve foreign relation columns automatically for you. This feature is the real power behind the module. To do this, reference the desired foreign relation from another table using a string
var dataSpec = {
users: [
{ username: 'Bob' },
{ username: 'Sally'}
],
items: {
name: 'book',
// at database insertion time, this will be Bob's id
user_id: 'users:0'
}
};
sqlFixtures.create(dbConfig, dataSpec, function(err, result) {
console.log(result.items[0].user_id == result.users[0].id); // true
});
As of version 0.12.0, you can resolve foreign keys against data already in the database.
var dataSpec = {
items: {
name: 'book',
user_id: {from: 'users', where: {'name': 'Bob'}}
}
};
In the above, sql-fixtures will look for a user whose name is set to Bob. The found row's id will be set as the user_id before inserting the item row.
from must indicate a table name, and where can be any knex where expression. The result must return one row, otherwise an error will be thrown.
You can resolve other columns by appending their name to the resolution string. If left off it assumes you want id
var dataSpec = {
users: [
{ username: 'bob' },
{ username: 'users:0:username' }
]
};
Both of the above users will have their username set to "bob"
As of version 1.0.0, you can resolve other columns from existing data:
var dataSpec = {
items: {
name: {from: 'users', column: 'middle_name', where: {'name': 'Bob'}}
}
};
In the above, a query for Bob's middle name will be performed, then set as the item's name.
If you are generating a lot of data, indices can get tedious. You can use a specId instead.
var dataSpec = {
users: {
username: 'bob',
specId: 'mySpecialUser'
},
items: {
// this resolves to bob's id
// at creation time
user_id: 'users:mySpecialUser',
name: 'book'
}
};
var dbConfig = {...}; // see below
sqlFixtures.create(dbConfig, dataSpec, function(err, result) {
console.log(result.Items[0].userId === result.Users[0].id); // true
});
If you need a literal : in a string column, double it up to escape it
var dataSpec = {
websites: {
// https://github.com is what gets written to the database
url: 'https:://github.com'
}
};
sql-fixtures can run arbitrary sql by use of the sql key on the dataSpec object. This SQL must conform to your database. After any resolution has been done, the string is passed as is to the database.
var dataSpec = {
users: {
username: 'bob'
},
// notice the sql key also supports resolution
sql: 'insert into items ("name", "user_id") values (\'book\', {users:0})'
};
NOTE: the result object that is returned will have nothing in it in regards to arbitrary SQL. As far as that object is concerned, arbitrary sql does not exist.
Also: the sql key can be an array of sql statements. The statements will be performed in parallel with no guarantee of order.
When using Postgres, you can specify array columns. They support resolution as well.
var dataSpec = {
has_integer: {
integer: 7
},
needs_integer: {
// the last value will be 7 at insertion time
integer_array: [1, 2, 'has_integer:0:integer']
}
};
You can pass in an options object to create() to further control the data generation.
var options = {
unique: true,
showWarning: false
};
sqlFixtures.create(dbConfig, dataSpec, options, function(err, result) {
...
});
unique, If true, sqlFixtures will attempt to avoid inserting a new row that is identical to existing rows in the table.
NOTE: unique is slightly experimental and might change or go away in the future.
showWarning, if false, suppresses the warning about no primary key columns, see the No primary key warning section for more details.
The dbConfig object that you pass to sqlFixtures is handed off as-is to Knex. See Knex's documentation on clients for more info on what needs to be in this object.
calling sqlFixtures.destroy(callback) will close the connection to the database. destroy() both can take a callback and returns a promise to indicate when the connection has been closed or if there was an error.
Generally you work with sql-fixtures calling the static create() method. You can also create an instance of sql-fixtures if you like. This has the advantage of not having to pass the database config to create() every time, and also gives you access to the knex object that sql-fixtures creates right away.
var sqlFixtures = require('sql-fixtures');
var fixtureCreator = new sqlFixtures(dbConfig);
// fixtureCreator.knex is now available and ready to go
// when calling create(), omit the dbConfig parameter
fixtureCreator.create(dataSpec, callback);
If you already have your own instance of knex, you can have sql-fixtures use it by creating an instance of sql-fixtures and passing in the knex instance.
var sqlFixtures = require('sql-fixtures');
var myKnex = knex(myDbConfig);
var fixtureCreator = new sqlFixtures(myKnex);
// your instance of knex is now used to create data
fixtureCreator.create(dataSpec, callback);
If you are using your own instance of knex, then sqlFixtures.destroy() is not supported, you must destroy knex yourself.
If you are using MySQL, Maria or SQLite with "without rowids on your tables" and you want to insert records using sql-fixtures on a table that lacks a primary key, you may get unexpected results. Postgres does not have this limitation.
SQLite note: SQLite only has this limitation if you created your tables without rowids. If you don't know what rowids are, then you don't have this limitation.
This is due to how these databases perform inserts. After an insert, sql-fixtures needs to then retrieve the entire inserted row from the database. This is so data that is needed to resolve other dependencies in fixtures can be obtained.
The only way I know of to do this in non-postgres databases is to make a SELECT query using the id of the most recently inserted row. For example, in MySQL/Maria this can be done with SELECT * from table where id = LAST_INSERT_ID(). But if your table lacks a primary key column, then there's no way to do this. In this scenario, sql-fixtures will still do a SELECT and hope the row that comes back is the right one.
Most of the time this won't be a problem, but it can be in more advanced/unusual scenarios.
Postgres avoids this problem because you can have Postgres return the inserted rows as the result of the insert. This also means sql-fixtures is more performant with Postgres, as it can insert rows in bulk, where with the other databases records must be inserted one at a time.
You can suppress this warning by adding {showWarning: false} as an additional parameter to create().