How to handle foreign key constraint with Jest/Supertest/Knex/Postgres

Question

I'm trying to start writing tests for my application. I'm using Jest & Supertest to run all of my tests. When I try and run my test suite, I'm getting an error regarding a foreign key constraint.

The error:

error: truncate "users" restart identity - cannot truncate a table referenced in a foreign key constrainterror: cannot truncate a table referenced in a foreign key constraint

This is my server.spec.js file:

const request = require('supertest');
const server = require('./server.js');
const db = require('../data/db-config.js');

describe('server.js', () => {
    describe('POST /register', () => {
        it('should return 201 created', async () => {
            const user = 
                { 
                    name: "test",
                    username: "test",
                    email: "test77@test.com",
                    password: "password"
                }
            const res = await request(server).post('/api/auth/register').send(user);
            expect(res.status).toBe(201);
        })

        beforeEach(async () => {
            await db("graphs").truncate();
            await db("users").truncate();
          });
    })
})

And here is my knex migration file:

exports.up = function(knex) {
  return (
      knex.schema
        .createTable('users', tbl => {
            tbl.increments();
            tbl.string('username', 255).notNullable();
            tbl.string('password', 255).notNullable();
            tbl.string('name', 255).notNullable();
            tbl.string('email', 255).unique().notNullable();
        })
        .createTable('graphs', tbl => {
          tbl.increments();
          tbl.string('graph_name', 255).notNullable();
          tbl.specificType('dataset', 'integer[]').notNullable();
          tbl
            .integer('user_id')
            .unsigned()
            .notNullable()
            .references('id')
            .inTable('users')
            .onDelete('CASCADE')
            .onUpdate('CASCADE');
        })
  )
};

exports.down = function(knex) {
  return (
      knex.schema
        .dropTableIfExists('graphs')  
        .dropTableIfExists('users')
  )
};

I came across this answer in my research: How to test tables linked with foreign keys?

I'm new to both Postgres as well as testing. It makes sense that I would need to drop the tables in the reverse order like I have in my migration. But when I try to truncate them in the beforeEach section of my test, it doesn't seem to matter what order the tables are listed in.

I'm not sure where exactly to go from here. Any help would be greatly appreciated.

score 1 · Accepted Answer · answered Dec 31 '19 at 22:53

I think the trick here will be to resort to a bit of knex.raw:

await db.raw('TRUNCATE graphs, users RESTART IDENTITY CASCADE');

CASCADE because you don't want foreign key constraints getting in the way, and RESTART IDENTITY because the default Postgres behaviour is not to reset sequences. See TRUNCATE.

While we're on a related subject, allow me to introduce something that might make your life a lot easier: template databases! Templates are databases that Postgres can use to very rapidly recreate a database from a known state (by copying it). This can be faster than even truncating tables, and it allows us to skip all the annoying foreign key stuff when testing.

For example, you could use raw to do the following:

DROP DATABASE testdb;
CREATE DATABASE testdb TEMPLATE testdb_template;

This is a surprisingly inexpensive operation, and is great for testing because you can begin with a known state (not necessarily an empty one) each time you do a test run. I guess the caveats are that your knexfile.js will need to specify a connection with a user sufficiently credentialled to create and delete databases (so maybe an 'admin' connection that knows about localhost only) and that the template must be created and maintained. See Template Databases for more.

How to handle foreign key constraint with Jest/Supertest/Knex/Postgres

1 Answers1