How to avoid table aliases?

[JasonTheAdams]

I have a model:

var name = "churches";

exports['definition'] = function(sequelize, Datatypes) {
    return sequelize.define(name, {
        name:       {type: Datatypes.TEXT, allowNull: false, validate: { len: [2,50] } },
        address:    {type: Datatypes.TEXT, allowNull: true},
        colors: {type: Datatypes.TEXT, allowNull: true,  validate: { 'isJson': textIsJson } },
        baseUrl:    {type: Datatypes.TEXT, allowNull: true,  validate: { isUrl: true } },
        logo:       {type: Datatypes.TEXT, allowNull: true},
        phone:  {type: Datatypes.TEXT, allowNull: true, validate: { len: [7] } },
        email:  {type: Datatypes.TEXT, allowNull: true, validate: { isEmail: true } }
    }, {
        freezeTableName: true,
        tableName: name,
        updatedAt: 'modified',
        createdAt: 'created'
    });
}

I need the word table "churches" to be exactly that, always. The problem I'm running happens when I do this (Churches hasMany Forms):

    Forms
      .find({
        where: {
          id: request.params.id
        },
        include: [Churches]
      })

Strangely, the SQL it produces renames "churches" to "church". I tried adding the freezeTableName property to the churches config and synced, but no dice. Why is it applying an alias? And where is it even getting it from?

[mickhansen]

And you have Forms.belongsTo(Churches)? Currently we will singularize the result in that scenario.

You might be able to cancel it out by using Forms.belongsTo(Churches, {as: 'churches'})

[JasonTheAdams]

@mickhansen I think a better option, rather than fighting your methods, would be to expose the singular and plural names from the Model object. I see there's a getTableName, which I assume is the provided plural name. Perhaps a parameter or another function?

[mickhansen]

@JasonTheAdams We have actually previously discussed the possibility of defining the result names for associations, but for belongsTo you can actually do that with as.

The problem is that the associations are in charge of generating the names themselves, the model name is just used as a parameter, so the responsibility is not actually with the model. Although i suppose it could be.

[JasonTheAdams]

I see. It just makes it a challenge to write generic code if elements are being hidden. The option seem to be to add complexity to the configuration of the models/associations. But I don't have a problem with singular/plural aliases, so long as they're accessible.

[mickhansen]

I'm not a huge fan of adding more complexity - But i don't see a good alternative, the singularization/pluralization makes a lot of sense for a lot of cases generally.
I'm more than ammendable to other suggestions for handling this issue.

For the 1:1 case i reckon Forms.belongsTo(Churches, {as: 'churches'}) would do about the same as Forms.belongsTo(Churches, {singular: 'churches', plural: 'churches'})

[JasonTheAdams]

It seems to me that it'd simplify the situation to include the names as part of the model definition. At that point, the names are accessible from the model itself and things likes associations can pull from that based on which makes the most sense (e.g. plural for hasMany and singular for belongsTo).

[mickhansen]

@JasonTheAdams i suppose you are right. I was just thinking of codebases with lots of associations where people might use aliases. But i guess in general you don't use aliases, or must of your associations will be without an alias.

@janmeier what do you think about being able to configure signular and plural name at the model level, and then the association would use that assuming there's no alias provided?

[janmeier]

That doesn't sound like a bad idea. What about capitalization? Should it always be kept. So plural: 'users' mean getusers as a getter and othermodel.users when doing includes? I guess people might want something like getUsers and othermodel.users, but we also have users who don't want that :) (#920 etc.)

[JasonTheAdams]

Having now finished a small-medium API with Sequelize and HAPI, one bit of feedback I would give for Sequelize is that I feel like it's trying too hard to meet every scenario cleverly (specifically nomenclature). Like that case offers, there's more and more configuration, adding complexity for niche situations.

I'd suggest adding a name: {singular, plural} property to the model definition. If the user wants capitalization, they'll add it there. Simply use model[get + this.name.plural/singular] for the function naming conventions. Keep the "underscored" configuration to permit db-side naming conventions — consider adding dbSingular and dbPlural to the name object to make the tentative distinction available (added optionally by the user; otherwise derived by the framework). But leave it up to the immediate definition of the user so they can always derive the names themselves.

Thoughts? :)

[mickhansen]

We try to meet every scenario cleverly, and it oftens annoys me when we have to cover niche situations, but there are often a lot of niche situations.

Another solution to the getusers issue could be to provide instance.[assoc].get aswell. so instance.users.get, instance.users.add, etc.

Except that would clash with prefetching most likely, so would have to be instance.associations.users.add or something :/

[JasonTheAdams]

In my experience, when I whine at a framework to cleverly fix my niche issue, it really should be me coming up with a clever solution. Otherwise, frameworks that try to meet every niche become a beehive of functionality and configuration. Anyway, I'll get off the soapbox.

Why is "getusers" an issue? Sure it's not general practice, but if the user wants to do that, then I'd let them. All they have to do is capitalize the words themselves. I feel like it's easy for them to fix, and a mess for the framework to try and take on.

What do you mean by prefetching? I'm familiar with that in hardware, but not sure how it fits this context.

[mickhansen]

Prefetching, our "include" option which provides fetching models and their associations in one call/query.

But yeah i think there's general concensus that we need to move towards a place where things are used litteraly from the config, and then we provide a couple of sane defaults.

[janmeier]

What I meant with getusers is that most users probably want getUsers as a getter method, but othermodel.users when using includes. So it should be capitalized in one case but not the other. But then again some people might not want that :). And in general, I think if the user provides singular and plural options, sequelize should not mess with those

[JasonTheAdams]

Just can't please 'em all. :)

The way I look at it is that on the one hand we have general practice standards (e.g. camel-case), wherein the framework tries to adjust capitalization, pluralization, etc.. But, on the other hand, is the ability to take framework and build generically on it against another framework — which becomes considerably more complex if the developer has to figure out the rhyme and reason behind how to expect a name to turn up somewhere.

Anyway, it does seem like we agree: Add a singular/plural name object to the model object, access it from there, and leave the capitalization alone.

Feel free to close this if you guys feel we've arrived at an amiable conclusion.

[mickhansen]

Yea i think that's the way to go. Sane defaults, and if the user provides overrides, he gets exactly those overrides.

I'm closing the issue, please do open a FR if you haven't already for the options discussed here @JasonTheAdams

[AndrewRayCode]

Please aliasing my table names! My tables get randomly capitalized, quoted, and pluralized. It makes debugging queries confusing and feels like bad magic.

[janmeier]

@delvarworld You'll have to be more specific than that - we have options to control most of these things - what specifically do you want?

Plural table names - yes by default the name of the table is the plural of the model - use freezeTableName either on the model or in the sequelize constructor to use the model name verbatim. Or use tableName in the model definition

Capitalized - No they don't - if your model has a capitalized name, then yes your table will be so too

Quoted - yes, unless you set quoteIdentifiers to false. But as you found out, that probably breaks some other scenarios

[bcmhkf]

When Sequelize is executing SQL statements, it's for some reason capitalizing my foreignkey col names...so, from 'chatId' to 'ChatId' thus causing an error.

What's a possible reason this is happening? And any solution?

[janmeier]

@brianchristophermendozahuey Very hard to say without any code - But if your model or association alias is capitaled (sequelize.define('Chat') || { as: 'Chat' }), so will the foreign key be

[bcmhkf]

Ok, it might be that the model is Capitalized. BUT, I thought by adding 'field: 'chatId' in the Message model, it should override it.

Code below is in the Message model:

chatId: { type: DataTypes.INTEGER, references: { model: "Chats", key: "id" }, field: "chatId" }

[VikR0001]

Why does Sequelize change table names and aliases at all? It causes an enormous number of anomalies -- especially when it happens despite freezeTableName being set to true. I just lost a day dealing with this. I love Sequelize, but will you please turn this behavior off?

[webmobiles]

i'm starting with sequelize, and i'm lost a lof of time with sequelize changing names... there are plan to turn off this function ?

[kmannislands]

This 'feature' must have cost developers collective lifetimes in wasted debugging. Honestly astounding that this is intentional.

[smithaitufe]

Please I have a challenge. How do I stop sequelize from using this -> in table aliases?

For example, I don't want this UserRoyaltyPurchase->UserRoyaltyPurchaseLineItem. I want only UserRoyaltyPurchaseLineItem

How can I get this?

[fsevenm]

Is there any progress to this issue?

[jorgeLopezClikalia]

Please I have a challenge. How do I stop sequelize from using this -> in table aliases?

For example, I don't want this UserRoyaltyPurchase->UserRoyaltyPurchaseLineItem. I want only UserRoyaltyPurchaseLineItem

How can I get this?

and now?