Track IDs for fixtures

[pdobrigkeit]

When adding data to tables is there a way to track IDs that are auto-generated? Currently we have to manually add those back into the migration files to be consistent across foreign relations.

[shahidhk]

@pdobrigkeit Can you give an example?

A simple solution I can think of is using UUID columns so that you can generate and save them yourself and then the default will be auto-generated.

[pdobrigkeit]

Yeah, I have a test table with two columns, id and test, id is UUID and default gen_random_uuid()

If I run the following SQL to enter fixtures I would either use:

INSERT INTO test (test) VALUES (1);
INSERT INTO test (id, test) VALUES (gen_random_uuid(), 2);

Then I would get the following migrations:

- args:
    sql: CREATE EXTENSION IF NOT EXISTS pgcrypto;
  type: run_sql
- args:
    sql: CREATE TABLE "public"."test"("id" uuid NOT NULL DEFAULT gen_random_uuid(),
      "test" integer NOT NULL, PRIMARY KEY ("id") );
  type: run_sql
- args:
    name: test
    schema: public
  type: add_existing_table_or_view

- args:
    cascade: false
    sql: |-
      INSERT INTO test (test) VALUES (1);
      INSERT INTO test (id, test) VALUES (gen_random_uuid(), 2);
  type: run_sql

That makes it rather difficult if there would be relationships to the test table because then the uuid would be different between the test system and the live system.

The wish would be that in the migration gen_random_uuid() would be replaced with the actual uuid set in the row.

[shahidhk]

Why don't you use an actual UUID in the test column if you need to refer to it later?

[pdobrigkeit]

That’s what we are doing now, but you need an external UUID generator and it is a lot of copy and paste if you have lots of rows. But I’ll try to figure out a workflow.

[shahidhk]

I'd like to understand the use case here to see if we can do anything to make the experience better. Are you trying to create some sample data and run some tests on it?

[pdobrigkeit]

I'll try to explain. The App we are building is a survey tool. So our database contains lots of questions, these questions have references to different tables which store which selections are offered as answers (foreign_key), also the weighting of those different anwers used in the evaluation. So our use-case is that we INSERT those questions and of course we would write regular SQL. If we would have to pre-generate the UUIDs (we have a couple hundred questions), we could add that to our data which is in Excel right now, but then we need an outside tool to generate those UUIDs paste those into the Excel, generate the SQL containing the INSERTS.

Example:
questions: would contain "Do you like XYZ"
answer_possibilities: "Yes", "No", "Maybe" and others
answer_weight: 1, 2, 3, 4, 5

That is only one part of the database which contains fixture data which we provide as initial data and has to be consistend across development and live systems.

Of course I need to copy those values then for the relationships, but that might be easier directly from the migration file?

Writing this down I guess I could optimze our process, but in my prior projects I always had the generated ID directly in the migration file and that was quite convinient because the ORM I was using added the relationships directly with the correct IDs and I only had to copy the code for those, now I have to manually edit my relationship queries as well.