How to properly alter the database?

faq-material

(Ryan C. Gordon) #1

This is probably easy, but I see conflicting information in various topics, so I’ll ask:

If I wanted to add a field to a database table for a new Discourse feature, where is the schema defined? And how does one tell existing instances to alter their existing tables when upgrading if something is added/changed?

I see topics that say db:migrate handles this, but others that say Discourse doesn’t use the standard Rails mechanism. Is there a simple “you write the new field in here, and add a note to alter the table here, done!” explanation to this?

Thanks!


(Matt Palmer) #2

Step 1: don’t. Most of the common objects in Discourse have the ability to store “custom fields”, and typically you’ll do a whole lot better if you use those.

Step 2: no, really, don’t. If per-object custom fields won’t do it for you, there’s also PluginStore that maintains per-plugin data seamlessly.

Step 3: if you absolutely, positively need your own database tables, you can change the schema by creating your own ActiveRecord migrations in db/migrate in your plugin. They get included by core if they exist.

The caveats to modifying the schema are many, and include, but are not limited to:

  • Cleaning up your schema changes if your plugin is uninstalled is an unsolved problem.
  • If your schema change conflicts with that of another plugin, or core, you’ve just broken the install of everyone who uses your plugin.
  • The combination of the above two points means that you could, at some unknown point in the future, break the install of everyone who ever used your plugin, even if they uninstalled it a long time ago.
  • Once you’ve broken things, it will require SQL command line experience from everyone who has ever used your plugin to fix.

Please, just use PluginStore. Think of the kittens.


(Allen - Watchman Monitoring) #3

I was going to reference my last run in with modifications of the database (topic 58905 appears deleted to me, so I can’t) . but the short story was that a database modification I did 2+ years ago bit me when it prevented a restore to a new host.

It took about 2 hours of figuring out what happened, altering the backup to remove the edit, & recompressing it so it would restore.

So yeah, I’m with Matt, think of the kittens, probably best not to mess with the database.


(Ryan C. Gordon) #4

Please, just use PluginStore. Think of the kittens.

Ok, this is good advice, but I wasn’t clear. I meant for something intended to be sent in as a PR for the core system, not a site-specific thing; for those we’d want to alter the database, right?

(Specifically, I was thinking of adding a means to specify an “email subject slug” per category, so emails can be sent with that instead of a category’s full title, but I’m sure I’ll eventually have 12 other unrelated ideas that want to change the schema too, heh)


(Mittineague) #5

No offense intended but this sounds like Core Feature Creep, which IMHO is a slippery slope and better avoided. (I am admittedly biased having seen WordPress forge ahead bloating itself by adding “more” at the neglect of fixing the existing in an apparent desire to become “everything to everyone”)

That said, I would have no problem with a “custom field” being added that plugins could use if none of the currently existing tables provide one.

AFAIK, there are custom fields for

  • category
  • group
  • post
  • topic
  • user

Maybe another eg. notification_custom_fields would be a better idea that adding limited use fields to tables?


(Ryan C. Gordon) #6

No offense intended

None taken! Everyone has to stand on guard against bloat. :slight_smile:

“everything to everyone”

I sympathize with the concern, but this is an actual need on our production server, and as we try to pull more things in from loosely-related mailing lists, we would like to make these look as much like the old lists as possible, and what I’m thinking about should only be a few lines of code and a handful of strings in the average database, if I do it at all.


(Matt Palmer) #7

Aaaaaaaaah, in that case, yeah, a new migration in db/migrate is the correct course of action.


(Ryan C. Gordon) #8

…and now I have done that, in case anyone wants to laugh at my PR.