search

skeema / skeema

Declarative pure-SQL schema management for MySQL and MariaDB
Apache License 2.0
1.27k stars 99 forks source link

Support for prefixed tables #138

Closed ElMatella closed 4 years ago

ElMatella commented 4 years ago

Hey! Thank you a lot for this great project, I made a ton of google searches to finally find you...

I'm wondering if you support prefixed tables? My project run on multiple environments and my tables should be prefixed (some_prefix_my_table). An option in the .skeema file would be a perfect fit for me.

I stumbled upon https://github.com/skeema/skeema/issues/76 but this seems to be about prefixing databases names.

Do you have or plan any support for prefixing tables?

Thank you very much for your answers and have a nice day

evanelias commented 4 years ago

Skeema doesn't support table name prefixes currently, but we can keep this open as a feature request.

I'd love to learn a bit more about the use-case please, as I'm not really familiar with it. Does each database schema use a different table name prefix for its tables, or do multiple prefixes exist within each database schema? Is there a particular motivation for using table name prefixing, vs using the same table names in different schemas? Thanks!

On a separate topic, re: tons of google searches, I'd like to find ways to make Skeema more discoverable. Are there any particular search keywords you tried first unsuccessfully? Thanks again.

ElMatella commented 4 years ago

Hey, thank you for your answer! If no one ever asked you something like that and I'm the first, it might not be worth it to even keep this open.

The use case is mainly with legacy apps where databases where used by multiple services. Thinking of Wordpress, Prestashop kind of services.

My personal use case that lead me to open this issue is also for a legacy app that use the same schema for multiple instances of the same service. Think of it as Wordpress A with prefix wp1, Wordpress 2 with wp2. In our "deployment process", we need to upgrade those wp1_articles when code is updated etc, wp2_articles some time later when user2 wants to upgrade etc...

Anyways, the more I think about it, the more I think it's bad practice, so we might just focus on migrating to different schemas rather than asking for skeema to do something about it.

For the google search question, here are some keywords I can remind of where I can't find you on the front page:

I finally found you on this github article: https://github.blog/2020-02-14-automating-mysql-schema-migrations-with-github-actions-and-more/

This article must have gave you some reasonable juice because I actually can find you on most of the keywords that come to my mind.

The thing I was really searching for was to get my database schema as versionable code. I come from ORMs such as Doctrine and Spring Data that allowed me to sync my code with my schema. Switching to pure SQL made me loose such tools.. So skeema is great! I just had to enable WSL on my windows to make it work, and hell yeah it works.

evanelias commented 4 years ago

I see, that use-case makes more sense now, thanks! Prefixed tables in the same schema would be tricky to support in the near-term, since currently Skeema assumes the *.sql files in one subdir fully define a single logical schema. To support prefixes within the same schema, this would need to change to allow for the definitions to map to only a subset of the objects in a database schema. It's definitely possible this will get more flexible in the future, but no clear plans yet.

A related future feature idea is the notion of templating: providing the ability to use placeholder variables and conditional logic in the .sql files. This could help with a few other feature requests (e.g. #87 and #21) as well. On its own though it wouldn't fully handle the ability to have different table name prefixes within the same* schema, so it still would require changes in the dir-to-schema mapping flexibility to handle this use-case.

Anyway, we can either keep this open to see if other users have the same requirement, or close if you plan to migrate to separate schemas anyway -- up to you?

re: search terms, that's helpful, much appreciated! Good to know for future SEO efforts. And thank you for the kind words, always glad to hear from a happy user :)

evanelias commented 4 years ago

Closing this for now, but will reopen if other users request similar functionality. Thanks!