How to setup DB for existing rails application locally

[smilyalexey]

@lvonk going back to this https://github.com/zilverline/sequent/pull/376. I am trying to follow your suggestion and I faced with issue now. I am trying to re-create db for my existing app locally. I am following this:

and when I run the bundle exec rake sequent:migrate:offline - I have this error:

PG::DuplicateColumn: ERROR: column "XXX" of relation "YYY_records" already exists. That happens because YYY_records table has all fields that was added later by alter. But at the same time on the top of this it runs all alter migration. To make it working I had to comment all alter migration files and re-create db and schemas.

What do I do wrong here?

[lvonk]

Can you provide an example project that faces this issue?

[smilyalexey]

@lvonk sorry but I can't give you any details about that project. If you let me know what kind of information you need - I can try to prepare some modified examples, but tbh I am not sure what is not clear here.

I have db/sequent folder like this:

object_records.sql
object_records.indexes.sql
object_records_2.sql

object_records.sql is something like that:

CREATE TABLE objects_records%SUFFIX% (
  id BIGSERIAL NOT NULL,
  name VARCHAR(255),
  lastname VARCHAR(255), # this one was added in object_records_2.sql and I added it here based on your suggestion
  CONSTRAINT object_records_pkey%SUFFIX% PRIMARY KEY (id)
);

object_records.indexes.sql is something like that:

CREATE UNIQUE INDEX name%SUFFIX%_index ON objects_records%SUFFIX% USING btree (name);

and object_records_2.sql is the file where I included a new field lastname

alter table object_records add column lastname VARCHAR(255);

and when I create a db + run bundle exec rake sequent:migrate:online and bundle exec rake sequent:migrate:offline it raises an error in object_records_2.sql because lastname was already added to objects_records table.

[lvonk]

I am not sure what is not clear here.

I have no idea from your initial description where to look for. So a failing example or step by step description would be very helpful.

Are you still using the monkey patches as described here https://github.com/zilverline/sequent/pull/376? Because these will run the alter tables in that case and can cause your problem.

If this is not the case can you share:

• What sequent version are you using?
• What ruby version are you on?
• What Rails version are you on?
• How does your database.yml look like
• How does your sequent migration file look like

Also can you describe the exact sequence of steps in running the commands and adding the migrations?

[smilyalexey]

@lvonk I prepared some rails demo example here https://github.com/smilyalexey/sequent_example.

Let me try to describe what I did. I created projector but without 1 field (I forgot to add lastname) - https://github.com/smilyalexey/sequent_example/commit/887aaf6811269b411bbc98164f2d595326f00217

then I added a new field https://github.com/smilyalexey/sequent_example/commit/d383cccd950e0c951899ee7fe8bff95579497bb9

And now if a new developer try to setup this project like that:

 # create db
 bundle exec rake db:create

 # create command and view schemas
 bundle exec rake sequent:db:create_event_store
 bundle exec rake sequent:db:create_view_schema

 # only run this when you add or change projectors in SequentMigrations
 bundle exec rake sequent:migrate:online
 bundle exec rake sequent:migrate:offline 

he/she will see this error message:

bundle exec rake sequent:migrate:offline    

I, [2023-07-10T13:44:34.545541 #29433]  INFO -- : group_exponent: 1
I, [2023-07-10T13:44:34.545594 #29433]  INFO -- : Start replaying events
I, [2023-07-10T13:44:34.546479 #29433]  INFO -- : Number of groups 16
rake aborted!
ActiveRecord::StatementInvalid: PG::DuplicateColumn: ERROR:  column "lastname" of relation "user_records" already exists
...

I hope it's clear now. Thanks.

[coalest]

I think the issue with your commit is that you are modifying both the user_records.sql file and the user_records_3.sql file. So you are attempting to add the column twice. If you wanted to perform a ReplayTable migration, you would just edit the user_records.sql file and add the lastname column. Or if you wanted to perform an AlterTable migration (with I think is more appropriate in this case), you would leave user_records.sql file as it is, and only add the user_records_3.sql file.

[smilyalexey]

@coalest thanks for your reply. Yeah I know that. I opened this issue because I tried to fix another one issue here https://github.com/zilverline/sequent/pull/376#discussion_r1236810578 and the suggestion was instead of that PR, modify original file where I create the table + create a new version. I wanted to show here that it produces another issue.

[coalest]

@smilyalexey Whoops, didn't see that issue. Pretty sure I actually ran into this same issue with AlterTable migrations, so I will follow this thread closely.

[lvonk]

Hi, I can reproduce the issue. I also understand why I haven't encounter this (we have a similar setup where the last migration is an alter table) but our sql looks like this:

alter table object_records add column IF NOT EXISTS lastname VARCHAR(255);

So the IF NOT EXISTS is key here which will make it "work".

This will work, but we should fix this properly. You can use the above workaround to mitigate this issue for now. Thanks for reporting and taking the time to create an example project 🙏🏼 . For now I will add this to the documentation that alter tables need to handle this scenario until fixed.

[smilyalexey]

yeah that will work. thanks a lot for your time looking into this 👍

[lvonk]

For now I added a note on this in the docs https://github.com/zilverline/sequent/pull/386