28

On some of our developer machines rake db:migrate adds trailing whitespace to structure.sql which is really annoying because every time a change is made to the database we have to first remove all trailing whitespace from the file.

Anyone know what could be up with that? Where would the whitespace come from? Has it to do with PostgreSQL or is it something else?

John Bachir
  • 22,495
  • 29
  • 154
  • 227
mhenrixon
  • 6,179
  • 4
  • 40
  • 64

6 Answers6

11

Here's a solution that you can commit to version control: trim the trailing whitespace as a db:migrate hook.

In lib/tasks/db.rake:

namespace :db do
  def remove_whitespace_in_structure
    if Rails.env.development?
      `sed -i '' -e's/[[:space:]]*$//' db/structure.sql`
    end
  end

  task :migrate do
    remove_whitespace_in_structure
  end
end

The above code may look like it's overwriting db:migrate, but it's a hook that will run right after the normal db:migrate task.

Charlie Tran
  • 183
  • 2
  • 6
  • 3
    The advantage of using this solution is that the file is cleaned early. With the .gitattributes approach if you look at the file system before committing you will see the file with all the extra whitespace. – Rob Dawson Oct 14 '15 at 01:12
  • 3
    Superb answer. Worth noting that I found I also had to write a similar task for `db:test:prepare`, which also seems to generate a `structure.sql` – Hugh Jun 30 '16 at 14:31
7

I just set up a git filter for this. Unfortunately, this is not something you can add to the repo; each team member will have to set it up.

In .gitconfig (or .git/config)

[filter "remove-trailing-whitespace"]
  clean = sed -E 's/[[:space:]]*$//'
  smudge = cat

In .gitattributes or .git/info/attributes

db/structure.sql filter=remove-trailing-whitespace

See the documentation on gitattributes for more information on git filters.

Amiel Martin
  • 4,636
  • 1
  • 29
  • 28
1

Having the same problem, I add the changes that have an actual effect through a git add -i db/structure.sql and then git checkout db/structure.sql so that the differences in spaces will be omitted.

The alternative we consider in our team is to use another gem for keeping track of the database.

Dimitrios Mistriotis
  • 2,626
  • 3
  • 28
  • 45
1

It is the best day of my life, and maybe yours:

In PG 11, this error is not here anymore since Tablespace is not written to the dump. So you can upgrade your PG version and get rid of the hook you had for space deletion.

Ulysse BN
  • 10,116
  • 7
  • 54
  • 82
0

This happens because of the version differences of your database on each machine.

You can either all have the same version, or in your text editor have it trim trailing spaces on save. In sublime add this line to your settings: "trim_trailing_white_space_on_save": true

A. Maly
  • 305
  • 5
  • 8
-2

I am using this gem in one of my projects and it does the job.

https://github.com/jakeonrails/fix-db-schema-conflicts

When you run rake db:migrate it runs a couple of good things; one of them is the sed command that removes unnecessary whitespaces.

jpbalarini
  • 1,082
  • 1
  • 17
  • 23
  • This question is about structure.sql, not schema.rb. If you could remove your answer so that the question will receive more attention, that would be appreciated, thanks. – John Bachir Jul 29 '15 at 13:28