ruby on rails

There are 10 posts tagged ruby on rails (this is page 1 of 2).

Rails mysql structure.sql dump contains AUTO_INCREMENT

When rails generates a structure.sql dump for MySQL it contains the AUTO_INCREMENT value. Which is anoying because this is not something you want to happen.

CREATE TABLE `active_storage_variant_records` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `blob_id` bigint NOT NULL,
  `variation_digest` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_active_storage_variant_records_uniqueness` (`blob_id`,`variation_digest`),
  CONSTRAINT `fk_rails_993965df05` FOREIGN KEY (`blob_id`) REFERENCES `active_storage_blobs` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=471 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Look at the part: AUTO_INCREMENT=471

After digging through the activerecord code, I saw I could sneak in a mysqldump option for the dump call. Yeah! 👍🏻

But there isn't such option 😕. It's an ancient BUG/Feature of mysql, which of course still isn't resolved in MySQL 8. https://bugs.mysql.com/bug.php?id=20786

Why would you like to dump the table structure (without data) with the AUTO_INCREMENT value!?

As a workaround it's possible to enhance the db:schema:dump task in a custom rake file (lib/tasks/remove_autoincrement_from_dump.rake).
So the AUTO_INCREMENT part is removed from it.

Rake::Task['db:schema:dump'].enhance do
  structure_sql_path = Rails.root.join("db/structure.sql")
  if File.exist?(structure_sql_path)
    sql = File.read(structure_sql_path)
    File.write(structure_sql_path, sql.gsub(/AUTO_INCREMENT=[0-9]+/, ""))
  end
end

References:

Rails generate both structure.sql and schema.rb

Default behaviour for rails is to generate a db/schema.rb when running migrations or dumping the database.

Schema.rb is great because it can be used for populating other database types and is used to populate the test database.
And I personally think it's great becaus of the vscode plugin Rails Schema which shows the database structure of your application in a sidebar (TIP, move this bar to the right panel).

The rake task db:migrate default behaviour is to invoke a db:schema:dump when migrating is done. By default this generates the schema.rb file.
This output of the generated file is used to popuplate the test-database..

But somethimes the db/schema.rb isn't good enough for popuplating the test database or using the file to generate the basic structure. Contraints are quirky.
(For example see previous article mysql json constraints).
To generate a native sql dump of the database structure, you can change the default structure to sql. Place the following line in your config/application.rb.

    config.active_record.schema_format = :sql

When enabling this dumps are generated to db/structure.sql.
But you loose the schema.rb dumps.

Here's a tip to work with structure.sql and just generate the schema.rb (for your lovely sidebar).

Create a file in lib/tasks/schema_dump.rb and enhance the dump task, so it also creates the schema.rb file.

Rake::Task['db:schema:dump'].enhance do
  File.open(Rails.root.join('db/schema.rb'), 'w') do |stream|
    ActiveRecord::SchemaDumper.dump(ActiveRecord::Base.connection, stream)
  end
end

NOTE: structure.sql contains yet another MySQL bug. (See next article for workaround)
Tip: use postgresql !

Rails incorrect constraints in schema.rb mysql dumps (for json)

The default dumping strategy for rails is to generate a ruby schema.rb file.
Having a table created by the following migration. (simplified version)

class CreateOrganisation < ActiveRecord::Migration[7.0]
  def change
    create_table :organisations do |t|
      t.string :name
      t.json :settings
    end
  end
end

It generates the following dump

ActiveRecord::Schema[7.0].define(version: 2022_12_29_082458) do
  create_table "organisations", charset: "utf8mb4", collation: "utf8mb4_0900_ai_ci", force: :cascade do |t|
    t.string "name"
    t.text "settings", size: :long, collation: "utf8mb4_bin"
    t.check_constraint "son_valid(`settings`", name: "organisations_chk_1"
  end

Trying to use this dump results in an error. Which isn't strange when you look at the generated constraint code. The MySQL adapter doesn't extract the constraints correctly

    t.check_constraint "son_valid(`settings`", name: "organisations_chk_1"

TIP: Don't use the 'json' datatype when MySQL. Event better use Postgres when possible, with it's jsonb column.

(See the next post for a good workaround to use structure.sql and schema.rb)

Ruby check if gem is loadable

Sometimes you want to only execute a given piece of code if a specific gem has is available. Testing if a constant is defined doesn't aslways work when using autoloading.

Gem.loaded_specs.has_key?('gemname')