https://github.com/Lambda-School-Labs/labs10-cleaner-tool/graphs/contributors

I think we're finally starting to get it. It's still slow going it feels like for every problem we solve 3 more pop up. As good as the previous team was it feels like the code isn't super cohesive after learning from one of the team member that they all just kind of worked on a single page a piece that does make sense. Individually the code is all very good but taken as a whole it definitely is lacking something.

PR's

https://github.com/Lambda-School-Labs/labs10-cleaner-tool/pull/25

https://github.com/Lambda-School-Labs/labs10-cleaner-tool/pull/22

https://github.com/Lambda-School-Labs/labs10-cleaner-tool/pull/21

https://github.com/Lambda-School-Labs/labs10-cleaner-tool/pull/20

I was basically working on a single trello card all week. Many of these are problems I ran into while working on that card.

I'm going to talk about pull 20 while it wasn't my primary goal for the week it was by far the most interesting I had. So we were having problems from the start with running seeds because of the incompatibilities between sqlite, and postgres syntax. So the primary difference we were running into was that postgres is far more aggressive about the way you handle foreign keys than something like sqlite. From all of our experiences with sql using knex when we wanted to run a seed we would just truncate the data with a cascade then run our seed. With postgres it's not so easy and there isn't a simple way to just say knex('tableName').truncate('RESTART') or something along those lines. I spent probably a good day figuring out this problem. Trying to come up with a really nice clean way to do it so that we could easily create a new table or field for a table and run a new seed without having to rollback, run migrations, then finally run seeds for every minor change. The solution I came up with which probably could be refined but nonetheless works is this.

First I check to see if we are in production, development, or a test environment. This is due to the fact that in production we are using PostgreSQL, in development we are using a sqlite database that is persisted to disk, and finally in test we are using a database that is on disk so there is no need to to remove the previous data.

    if (process.env.NODE_ENV === 'production) {
        // Do something
    } else if (process.env.NODE_ENV === 'development') {
        // Do something
    } else if (process.env.NODE_ENV === 'test') {
        // Do something
    }

So then for the actual seed functions this is what I came up with looking at it I'm actually already seeing a way this could have been further abstracted which I may try to do at some point.

For PostgreSQL the best option I was able to find was to pass a raw sql query I couldn't find a knex method for handling RESTART IDENTITY. Basically the way it works is TRUNCATE does at it would infer truncates the data on the table now in PostgreSQL truncating does not to the same thing as it does in from what I can tell literally every single other sql dialect in PostgreSQL truncate just deletes the data and doesn't reset the IDs to do that you need to add to that the RESTART IDENTITY then CASCADE pretty much works the same as it does in sqlit where it just propagates the changes to the foreign keys associated with this piece of data.

    exports.seed = async function(knex, Promise) {
        await knex.raw('TRUNCATE TABLE some_table RESTART IDENTITY CASCADE';
    }

This one is pretty straight forward just truncates the table deleting all of the data, and resetting the IDs.

    exports.seed = async function(knex, Promise) {
        await knex('some_table').truncate();
    }

For the in memory database we didn't need to clear out the data so I just passed back an empty function. If you don't do that knex yells at you for not passing one back.

    exports.seed = () => {};

So the final product looks like this it's a bit clunky but hey it works.

if (process.env.NODE_ENV === 'production') {
  exports.seed = async function(knex, Promise) {
    // Deletes ALL existing entries
    await knex.raw('TRUNCATE TABLE surveys RESTART IDENTITY CASCADE');
    await knex.raw('TRUNCATE TABLE questions RESTART IDENTITY CASCADE');
    await knex.raw('TRUNCATE TABLE item_complete RESTART IDENTITY CASCADE');
    await knex.raw('TRUNCATE TABLE after_list RESTART IDENTITY CASCADE');
    await knex.raw('TRUNCATE TABLE items RESTART IDENTITY CASCADE');
    await knex.raw('TRUNCATE TABLE list RESTART IDENTITY CASCADE');
    await knex.raw('TRUNCATE TABLE stay RESTART IDENTITY CASCADE');
    await knex.raw('TRUNCATE TABLE house RESTART IDENTITY CASCADE');
    await knex.raw('TRUNCATE TABLE manager_ast RESTART IDENTITY CASCADE');
    await knex.raw('TRUNCATE TABLE assistant RESTART IDENTITY CASCADE');
    await knex.raw('TRUNCATE TABLE manager RESTART IDENTITY CASCADE');
  };
} else if (process.env.NODE_ENV === 'development') {
  exports.seed = async function(knex, Promise) {
    await knex('surveys').truncate();
    await knex('questionAnswers').truncate();
    await knex('questions').truncate();
    await knex('house_ast').truncate();
    await knex('item_complete').truncate();
    await knex('after_list').truncate();
    await knex('items').truncate();
    await knex('list').truncate();
    await knex('stay').truncate();
    await knex('house').truncate();
    await knex('manager_ast').truncate();
    await knex('assistant').truncate();
    await knex('manager').truncate();
    await knex('user').truncate();
  };
} else if (process.env.NODE_ENV === 'test') {
  exports.seed = () => {};
}

As I mentioned earlier I see a possible way to abstract this out a bit so that I would only have to write one exports.seed function here is maybe a little peek of the idea I may update this post if I reimplement this with the new code.

This is what I cam up with in a couple minutes it doesn't as of yet work though that's probably just due to some weird issue with asynchronicity I'll have to think about it a bit more but here's the idea and in my opinion this is a much cleaner implementation. Less lines at the very least.

async function unifiedTruncate(table, NODE_ENV) {
  switch (NODE_ENV) {
    case 'production':
      return await knex.raw(`TRUNCATE TABLE ${table} RESTART IDENTITY CASCADE`);
    case 'development':
      return await knex(table).truncate();
    default:
      return () => {};
  }
}

exports.seed = async function(knex, Promise) {
  await unifiedTruncate('surveys');
  await unifiedTruncate('questionAnswers');
  await unifiedTruncate('questions');
  await unifiedTruncate('house_ast');
  await unifiedTruncate('item_complete');
  await unifiedTruncate('after_list');
  await unifiedTruncate('items');
  await unifiedTruncate('list');
  await unifiedTruncate('stay');
  await unifiedTruncate('house');
  await unifiedTruncate('manager_ast');
  await unifiedTruncate('assistant');
  await unifiedTruncate('manager');
  await unifiedTruncate('user');
};