4 ms·

gh-ost is hardly useful for anyone outside of github. It's predicated on the fact that you don't use foreign keys. Now why would someone use MySQL without FKs.

gh-ost is hardly useful for anyone outside of github. It's predicated on the fact that you don't use foreign keys. Now why would someone use MySQL without FKs... is beyond me, but I'm sure they have their reasons. MySQL has this https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.ht... for Online Schema migration and MariaDB has ALTER ONLINE https://mariadb.com/kb/en/library/alter-table/ https://mariadb.com/kb/en/library/alter-table/ They both work with triggers + ghost tables, so they don't need transactions.

orf 8y ago
Rails does not use foreign keys by default.
- rantanplan 8y ago
  What does "by default" mean? If you want to have referential integrity there's no other choice. Otherwise you create tables that have no relation to each other. No one stops you from doing this, but then you probably don't want an RDBMS in the first place.
  - orf 8y ago
    Rails by default does not add a foreign key constraint at the database level when defining a relationship[1]. And once you have a fairly large rails app that uses this default it's somewhat tricky to migrate. It's madness, I know. 1. https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_reference https://api.rubyonrails.org/classes/ActiveRecord/ConnectionA...
    - rantanplan 8y ago
      WTF! Thanks for that tidbit, I have no XP with Rails, but I would never imagine they do something so atrocious.
      - lloeki 8y ago
        The rationale is that non-trivial conditions on ActiveRecord model associations can be quite dynamic (possibly implying ruby code evaluation), therefore constraints are best handled at the model level, or else the constraints would only be partially checked at the db level, or even conflicting with the models (e.g if a constraint is to be enforced based on some condition evaluated at runtime, like a simple if clause, or when using STI or polymorphism).
        rantanplan 8y ago
        I have no clue what you're talking about. That's an honest statement, not trying to be confrontational. If you have constraints that can be enforced by the DB, you simply use the DB's constraints, because the DB guarantees they're gonna work 100% of the times and your data will be correct. If they are more dynamic or require custom business logic... well you do it on the application layer. That's what everyone does. I mean you can probably implement your own transactions, that doesn't mean that you should. And if you do, then just admit that there's no point in using an off-the-shelf RDBMS.
        matthewmacleod 8y ago
        Well, Rails is kind of an off-the-shelf solution to building web apps, so it makes some compromises to present a uniform process and API for some actions. One of those trade-offs is that the relationship between database tables in ActiveRecord is handled at the model/application layer instead of the database layer. This has some benefits - it allows for a uniform definition of relationships regardless of database backend, allows for constraints that can’t be expressed by the database itself, and allows constraints to be used as a first-class concept for things like presenting error messages to users. But it also means that data integrity is not guaranteed - modifying records concurrently or outside of the application can result in a data model that’s valid according to the database schema but not according to the application model. FWIW I exclusively use Postgres as my Rails database backend these days, and foreign key relationships are extremely easy to include in migrations. This still requires a companion definition in application code so that good error messages can be presented, but that seems acceptable to me. I’d hope that this eventually becomes the default for databases that support these keys.
- pqdbr 8y ago
  You can create your models anyway you want, but the “default” is by doing something like this: rails g model Post user:references
- nicolasMLV 8y ago
  Rails uses foreign keys by default, usually I generate a migration file with `rails generate migration AddThisToThing this:references` Then this line is generated `add_reference :things, :this, foreign_key: true` and then we call the migration, and there is the foreign key.
  - orf 8y ago
    Is it not more accurate to say "by default rails generates migrations with the foreign key argument set to a non default value of true"? By default the relationship constructors do not create foreign keys, if they did it would be redundant to include it in the generated output surely? At least that's what the docs seem to say.
sciurus 8y ago
It's not just gh-ost. pt-osc is much safer to use when you don't have to worry about updating foreign keys. Here are some of Github's reasons for not using them: https://github.com/github/gh-ost/issues/331 https://github.com/github/gh-ost/issues/331 I think this is pretty normal for heavy MySQL users, honestly. When I was at Eventbrite we didn't use them for the same reasons.
- rantanplan 8y ago
  I've read about their reasons, I just didn't want to open a discussion about it :D Deciding to implement constraints on the application/business layer is something that github can probably do, in the same vein that Facebook can create their own PHP compiler. I just think that you can't use these examples as effective arguments on whether someone should use FKs or not. I guess whatever brute-force solutions these companies would apply, on any given problem, would probably work one way or the other.

orf 8y ago

Rails does not use foreign keys by default.

rantanplan 8y ago
What does "by default" mean? If you want to have referential integrity there's no other choice. Otherwise you create tables that have no relation to each other. No one stops you from doing this, but then you probably don't want an RDBMS in the first place.
- orf 8y ago
  Rails by default does not add a foreign key constraint at the database level when defining a relationship[1]. And once you have a fairly large rails app that uses this default it's somewhat tricky to migrate. It's madness, I know. 1. https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_reference https://api.rubyonrails.org/classes/ActiveRecord/ConnectionA...
  - rantanplan 8y ago
    WTF! Thanks for that tidbit, I have no XP with Rails, but I would never imagine they do something so atrocious.
    - lloeki 8y ago
      The rationale is that non-trivial conditions on ActiveRecord model associations can be quite dynamic (possibly implying ruby code evaluation), therefore constraints are best handled at the model level, or else the constraints would only be partially checked at the db level, or even conflicting with the models (e.g if a constraint is to be enforced based on some condition evaluated at runtime, like a simple if clause, or when using STI or polymorphism).
      - rantanplan 8y ago
        I have no clue what you're talking about. That's an honest statement, not trying to be confrontational. If you have constraints that can be enforced by the DB, you simply use the DB's constraints, because the DB guarantees they're gonna work 100% of the times and your data will be correct. If they are more dynamic or require custom business logic... well you do it on the application layer. That's what everyone does. I mean you can probably implement your own transactions, that doesn't mean that you should. And if you do, then just admit that there's no point in using an off-the-shelf RDBMS.
        matthewmacleod 8y ago
        Well, Rails is kind of an off-the-shelf solution to building web apps, so it makes some compromises to present a uniform process and API for some actions. One of those trade-offs is that the relationship between database tables in ActiveRecord is handled at the model/application layer instead of the database layer. This has some benefits - it allows for a uniform definition of relationships regardless of database backend, allows for constraints that can’t be expressed by the database itself, and allows constraints to be used as a first-class concept for things like presenting error messages to users. But it also means that data integrity is not guaranteed - modifying records concurrently or outside of the application can result in a data model that’s valid according to the database schema but not according to the application model. FWIW I exclusively use Postgres as my Rails database backend these days, and foreign key relationships are extremely easy to include in migrations. This still requires a companion definition in application code so that good error messages can be presented, but that seems acceptable to me. I’d hope that this eventually becomes the default for databases that support these keys.
pqdbr 8y ago
You can create your models anyway you want, but the “default” is by doing something like this: rails g model Post user:references
nicolasMLV 8y ago
Rails uses foreign keys by default, usually I generate a migration file with `rails generate migration AddThisToThing this:references` Then this line is generated `add_reference :things, :this, foreign_key: true` and then we call the migration, and there is the foreign key.
- orf 8y ago
  Is it not more accurate to say "by default rails generates migrations with the foreign key argument set to a non default value of true"? By default the relationship constructors do not create foreign keys, if they did it would be redundant to include it in the generated output surely? At least that's what the docs seem to say.

sciurus 8y ago

It's not just gh-ost. pt-osc is much safer to use when you don't have to worry about updating foreign keys. Here are some of Github's reasons for not using them: https://github.com/github/gh-ost/issues/331 https://github.com/github/gh-ost/issues/331 I think this is pretty normal for heavy MySQL users, honestly. When I was at Eventbrite we didn't use them for the same reasons.

rantanplan 8y ago
I've read about their reasons, I just didn't want to open a discussion about it :D Deciding to implement constraints on the application/business layer is something that github can probably do, in the same vein that Facebook can create their own PHP compiler. I just think that you can't use these examples as effective arguments on whether someone should use FKs or not. I guess whatever brute-force solutions these companies would apply, on any given problem, would probably work one way or the other.