62

rake db:schema:load will load a schema.rb file into a rails database. Is there a way to load a structure.sql file into the database through rake or do I just need to do this manually?

Raj
  • 22,346
  • 14
  • 99
  • 142
locoboy
  • 38,002
  • 70
  • 184
  • 260
  • What does it mean to load a file into a rails database? Does this mean to create a database structure (the tables, the table names, and the attributes) based off the file? – Jwan622 Jun 29 '16 at 20:38

6 Answers6

101

Use rake db:structure:load, which will load db/structure.sql.

[Update]

If you want to load another file, you can specify its path via

  • SCHEMA environment variable (Rails 5.0 or later)
  • DB_STRUCTURE environment variable (Rails 4.x)

For example, run

rake db:structure:load SCHEMA=db/another.sql

or

rake db:structure:load DB_STRUCTURE=db/another.sql
Harry Wood
  • 2,220
  • 2
  • 24
  • 46
Tsutomu
  • 4,848
  • 1
  • 46
  • 68
  • 3
    Or specify file path via ENV variable like this: `rake db:structure:load DB_STRUCTURE=db/another.sql` – Artur INTECH May 03 '17 at 20:34
  • In order to specify the path of SQL file, use the `SCHEMA` environment variable (not `DB_STRUCTURE`) like `rake db:structure:load SCHEMA=db/another.sql`. – Tsutomu Dec 30 '17 at 17:07
  • 1
    Don't confuse structure with schema. `SCHEMA` is for .rb files (schema) and `DB_STRUCTURE` is for .sql files (db structure) https://github.com/rails/rails/blob/1505f8b69209c02ff60f124220590bde20bb7c09/activerecord/lib/active_record/railties/databases.rake#L283 – Artur INTECH Jan 03 '18 at 02:36
  • 2
    It seems that on the latest code, the environment variable `SCHEMA` is used. See https://github.com/rails/rails/blob/b6d5e46311d7ea59539c1f45c6ffb269eeb23912/activerecord/lib/active_record/railties/databases.rake#L303 – Tsutomu Jan 04 '18 at 10:12
  • Indeed. I was referring to 4.2.5.2. – Artur INTECH Jan 05 '18 at 07:27
  • The behavior changed on this commit: https://github.com/rails/rails/commit/99e57a8dccb700375c674c6c7872025f0022f0db – Tsutomu Jan 05 '18 at 14:17
  • Thanks for sharing! – Artur INTECH Jan 05 '18 at 15:17
36

Just use

rake db:setup

which will use either schema.rb or structure.sql depending on your configuration.

nathanvda
  • 49,707
  • 13
  • 117
  • 139
  • 1
    Depending on what configuration? Could you please add links to some docs? – Envek Apr 01 '15 at 10:57
  • 15
    In your `application.rb` you can write the `config.active_record.schema_format = :sql`, default is `:schema`. The [guides](http://guides.rubyonrails.org/active_record_migrations.html#setup-the-database) are unclear on this: they just say `rake db:setup` will load from schema, which might or might take the configuration into account (does schema mean the database schema, which is stored as configured, or `schema.rb` only). As usual the source is definitive: https://github.com/rails/rails/blob/6201e62cc49720e9133759e9a19fd1cc11ab73e1/activerecord/lib/active_record/railties/databases.rake#L175 – nathanvda Apr 01 '15 at 21:16
8

Use the database's own SQL load mechanism.

For Postgres this should work (at least if the database exists and you don't need a password):

psql -d databaseName < db/structure.sql

On Heroku where rake db:setup doesn't work as you can't create a database like that you can do this:

heroku pg:psql < db/structure.sql
Joseph Lord
  • 6,446
  • 1
  • 28
  • 32
6

Once you load your schema, you can try: 

rails dbconsole < structure.sql

OR

rails db < structure.sql
JstRoRR
  • 3,693
  • 2
  • 19
  • 20
6

Sometimes you want to load a file that is not the default db/structure.sql.

For rails 4.2 and earlier, use

DB_STRUCTURE=some_file.sql rake db:structure:load

As of rails 5 use

SCHEMA=some_file.sql rake db:structure:load

jpayne
  • 111
  • 1
  • 8
3

Make your seeds.rb file like:

unless Rails.env.production?
  connection = ActiveRecord::Base.connection

  sql = File.read('db/structure.sql')
  statements = sql.split(/;$/)
  statements.pop  # the last empty statement

  ActiveRecord::Base.transaction do
    statements.each do |statement|
      connection.execute(statement)
    end
  end
end

Source.

Raj
  • 22,346
  • 14
  • 99
  • 142
  • I added `statements.reject! { |statement| statement.match?(%r{ar_internal_metadata|schema_migrations}) }` to remove rails default tables that somehow already exist. – Damien Metzger Dec 10 '20 at 13:11