Sails: support for custom indexes

[wanderer]

It would be nice to be able to specify custom indexes. so for example index: "customIndex" the string customIndex would get passed along to the adapter.

If the index was a bool then the current behavior would hold.

[particlebanana]

I'm cool with that. Any thoughts on how we can do composite indexes? I struggled with that in the initial build and couldn't think of a nice way to express it in a collection so I left it out.

[wanderer]

yeah composites are tricky. I would be happy a new option indexes which could be an array of objects. Maybe like so..

indexes:[
//a composite index
{
   attributes:  ['attribute1', 'attribute2' ...]
   unique: boolean
},

But then what if we needed a composite index With custom indexes?

indexes:[
//a composite index
{
   attributes:  [
        {
            name: 'attribute1',
            type: '2dsphere'
        },
        {
            name: 'attribute1',
            type: 'hash'
        }
   ]
   unique: boolean
}
]

A bit messy but i think anyway you do its going to get a bit ugly. What do you think?

[sschepens]

what about having support for concurrent indexes (postgres) or background (mongo), etc?

[CWyrtzen]

Contribution Guide | Stackoverflow | Google Group | Trello | IRC | Build Status

[RWOverdijk]

What happened with this? It's been closed but I have no idea if this got implemented or not.

Update: If not, I'd like to propose allowing unique to be a string. The string will be used for the index key, making it possible to group them. This would apply to both index and unique.

[CWyrtzen]

It will eventually be pushed to Trello as a FR.

[gebrits]

I've seen multiple issues closed like this the past days.
I realize getting a low 'open issue count' is something to strive for, but
closing without any back-and-forth really isn't the solution.

May I suggest to at least leave a note as to why an issue is going to be
closed?
Moreover, if the issue is moved to Trello as a FR, how about linking to the
Trello Card of the FR _before_ closing, so the issue isn't lost in void?

On Tue, Jun 17, 2014 at 9:45 PM, Courtney Wyrtzen [email protected]
wrote:

It will eventually be pushed to Trello as a FR.

—
Reply to this email directly or view it on GitHub
https://github.com/balderdashy/waterline/issues/109#issuecomment-46355686
.

[CWyrtzen]

@gebrits we understand your concern and possible frustration. Thanks for your participation in the support community! This particular issue did have some discussion but, as you see was referenced in January and then trailed off. As you probably already know, closed issues are still searchable. Also, you are welcome to reopen ANY issue and restart a discussion if you feel we have closed it prematurely. That gets the conversation going again. On other issues you have seen, we do our best to reach out to the OP before we close up but that doesn't mean it can't be reopened.

https://trello.com/c/4ApPbu5C

Contribution Guide | Stackoverflow | Google Group | Trello | IRC | Build Status

[CWyrtzen]

Also, the FR milestone stays intact so they can be searched that way, too. (If you find one where this is not the case it was just a clerical error)~

[tjwebb]

When I google for waterline issues or features, I never see Trello in the search results. I always see github. It's annoying and confusing to have two places to discuss and report issues and features.

Morever, the link you posted is returning 404 for me: https://trello.com/c/4ApPbu5C

[RWOverdijk]

The link isn't working indeed, verified. It's probably a private board, or it has been moved by now.

@tjwebb Cards on Trello can be moved / renamed etc. So it doesn't make sense to show up in google. I do agree that it's annoying to keep track of these things in two different places.

[CWyrtzen]

@tjwebb and @RWOverdijk YES, good catch. We recently stopped using the Trello board and my new signature as of a few days ago removes this as an option. We are still honing the best way to respond to the needs of the community. Right now IRC Channel and Stack for support questions and GitHub for FR and Bugs are our main forums.

I know it can be frustrating when you need immediate help. I can tell you that we work as diligently as possible to respond in a timely manner. The needs of the community are important to us~

Sails v0.10 is here: Upgrade | Contribution Guide | Stackoverflow | Google Group | IRC | Build Status

[RWOverdijk]

To anyone googling and ending up here, I recommend IRC. I'm active at least 10 hours a day (work week, weekends a lot, too) and there are others that are really actively there as well.

I've decided to start blogging about FAQ in the channel as an archive.

[tjwebb]

IRC communication is ephemeral, which has the effect of making an ostensibly "public" conversation inaccessible to everyone who doesn't happen to be right there on that IRC channel at that time. This is especially problematic for a global community spanning many time zones. It leaves people out of the conversation.

This issue right here is certainly a feature request. Continuing the discussion here ensures that Googlers who end up here end up in the discussion, which is probably what they wanted anyway.

[CWyrtzen]

@tjwebb this is marked as a FR for all posterity (truly, I'm not just being snarky). Closed issues show up in search results. Once a member of the core team has marked it as a FR, we close it so that we can see that someone has addressed and discussed it (see @particlebanana comments above, plus he marked it as a FR so that helps me know what to do with it). The discussion can always continue.

We are working out a permanent system to log FR. In fact, some of them may have been implemented in the new release so we now have that as part of our plan, to see what's been implemented and what lies ahead.

The FR system is in place but we are smoothing out the kinks and eventually we will ask people to use it to log their own FR.

Is there anything else I can do for you? That's what I am here for.

[tjwebb]

Nope, I'm just following this issue. Thanks

[CWyrtzen]

Just FYI, this Trello card was in the archive - it may have been moved and that's why the link was broken but we did have it logged at one point (that system didn't develop - we're working on logging them another way)

just curious to see if you can access this: https://trello.com/c/lLXdNXtm (pretty sure it wasn't made public yet or it has been made private since it was created)

[tjwebb]

Yea, in postgres at least, there's no possible way to re-create this:

  CONSTRAINT mytable_pkey PRIMARY KEY (mytable_id, mytable_foo, mytable_bar),

It's kind of a problem.

[radkodinev]

Guys, is there any progress on this?

[CWyrtzen]

The current system for handling FR is here: https://github.com/balderdashy/sails/blob/master/ROADMAP.md

[leejt489]

+1, would like to see this opened back up.

If it were implemented for primary keys as well as any index, how should the model be referenced in an association?

[ArdentZeal]

+1 for composite keys

[lebarde]

To add my word, I currently want to inherit a model. I assume it is not possible for the moment, but raw SQL in the model definition would do it, see this post on stackoverflow.

I am therefore following the evolution of this thread!

[m-bodmer]

+1

[devinivy]

+1

[vinaymayar]

+1

[CWyrtzen]

Noted~

[CWyrtzen]

I did some hunting for you all and this is the word: for now that is something you can't model in the model definition. You can always add it manually or in your bootstrap. We would accept a pull request for it though.

[devinivy]

Thanks, @CWyrtzen . As part of the API change for this, perhaps the dominant option should have a particular effect; pivot tables typically ought to have a compound index (in the case of a SQL table) where one side is considered dominant. The first column in a compound key governs which join direction is more performant. Maybe this should go in sails-mysql. I guess the main point is that these compound indices should be implemented as ordered lists of columns rather than unordered sets of columns.

[arcseldon]

+1 Workarounds and hacks aside, there is a sizeable gap when implementing non-trivial domain models with waterline without this feature. Using sails-mysql adapter, and must have guarantees around combinations of attributes being unique composite keys. Don't wish to debate where the responsibility for this feature lies, only that it is being implemented. @CWyrtzen - Thanks for the great work to date on waterline, mostly a pleasure to work with. Please can you clarify your last comment - a solution is still definitely being provided? The community seems pretty adamant this a must have feature from a requirements perspective. Are you suggesting a technical issue preventing its provision?

[albertpeiro]

+1 I'd love to see this in waterline and use it from sails. Thank you for outstanding work.

[MarijnMensinga]

+1

[rmbagel]

+1

Is there a bounty for this? I'd like for composite key support to be made a priority.

[albertpeiro]

@arcseldon agree with the size of the gap. A simple constraint on a key pair turns into a series of checks in the shape of promises throughout the entire logic of the api in the best case. The only other way I could think of working around this would be: add the ability to run additional low-level/wrapped database commands after sails magic "alters" the database on lift. This approach could be connected too with how we solve data migrations for production - which by the way I think it's the biggest off-putter when you're new to sails.

[dzcpy]

+1 for composite keys

[dmarcelino]

+1 for composite keys and I'm OK with @wanderer's suggestion above.

[calidion]

+1 for composite keys

[franc23]

+1 for composite keys

[byCedric]

+1 for the ability to use composite keys

[cyberwookiee]

Data integrity should be maintained at the lowest possible level

+1 for composite keys

[franc23]

@particlebanana or any collaborator: I have hacked into the mysql adapter (sails v 0.11.0) to get composite keys to work with get/put calls (post not being an issue). I make use of the unique attribute to modify the adapter.js methods and a few other methods in 6 files total.
Maybe totally wrong in my approach, but thought I'd share this with the team to see if it helps. Would be happy to help as much as I can if someone can guide me on any waterline standards, etc.

Uploaded the files with appropriate folder paths here -http://www.megafileupload.com/K1/node_modules.zip

[albertkim]

I've hacked around this issue by having big beforeCreate functions in my models involving composite keys. Thankfully none of my tables require more than 2 composites, but if there are any more I don't think this method will hold up. Here's a +1 from me.

I look forward to seeing how this gets implemented.

[elexx]

I am also struggling with not having composite keys available. +1 from me.

[Salakar]

I created a sails hook to give advanced indexing options for models that use the sails-mongo adapter.

Supports all mongo indexing options. Just have to add an 'indexes' property to your model!

https://www.npmjs.com/package/sails-hook-mongoat

[rafacapa]

+1

[BLamy]

+1 This is really throwing a huge wrench in my database design.

[devinivy]

@BLamy you can always manually create indexes or use migration tools to do so.

[BLamy]

@devinivy It's not just the indexes. It's the primary keys. Is it possible to create a composite primary key using migration tools or do it behind the scenes and not break sails-mysql?

[devinivy]

Yes– if migrate is set to safe or create then you should be fine. alter and drop are not suitable for production anyway :+1:

[sailsbot]

Thanks for posting, @wanderer. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

• review our contribution guide to make sure this submission meets our criteria (only _verified bugs_ with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
• create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

[dmarcelino]

Reopening as this one has been picked for WL_Next, likely to be renamed 1.2.

[sailsbot]

Thanks for posting, @wanderer. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

• review our contribution guide to make sure this submission meets our criteria (only _verified bugs_ with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
• create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

[LeonardoGentile]

+1
So being closed by sailsbot means it is the end of this FR?
Any news on this from human beings?

[RWOverdijk]

I wish I could reopen but I can't. @devinivy

[devinivy]

Reopening, just until someone PRs this into the roadmap.

@LeonardoGentile mind making a PR to add this feature into the roadmap?

[LeonardoGentile]

@devinivy :+1: done

[calidion]

:+1:

[arcseldon]

@particlebanana, @CWyrtzen, @albertpeiro, @RWOverdijk - does anyone have any further information regarding this feature request? It continues to generate frequent votes, and it is unclear (to me at least) whether any of the core committers / sails / waterline community are working on a solution. Has the conversation moved elsewhere (IRC channels etc) or is this still the correct issue to be expressing opinions / providing updates etc? Thanks for any clarifications, and please keep up the good work on these excellent libraries which is much appreciated.

[devinivy]

@arcseldon this is now in the roadmap. I don't think it's anyone's duty right _now_ to add the feature, but it's scheduled for the future. In the meantime you can support custom indices on the adapter level by exploiting the permissiveness of waterline models– see https://github.com/gadelkareem/sails-dynamodb for an example. If you'd like to help implement a solution, feel free to reach out!

[particlebanana]

@arcseldon it's on the roadmap. No work has started on it but a PR implementing it would be accepted!

Ideally we could move all the migrations and schema building to a standalone library eventually.

[calidion]

seems you are eventually encountered this problem:)

[arcseldon]

@devinivy, @particlebanana - thanks for your prompt replies. Good to know it's on the roadmap! Working overtime on other projects right now (who isn't right?) but will surely take a look if time permits next year.

[icodeforlove]

:+1:

[gadidev]

I want this. This discussion is over 2 years old. I hope its coming soon.

[tjwebb]

This discussion is over 2 years old. I hope its coming soon.

This statement is inconsistent with itself :) If it hasn't happened in 2 years, it'll probably never happen. See also: https://github.com/balderdashy/sails/issues/3429#issuecomment-165004024

[gadidev]

yeah. I switched my project from Laravel to Sails about 2 months ago. I'm starting to wonder if I made a mistake.

[RWOverdijk]

@gadidev I have to admit it has been quiet for a bit. But rest assured, things are being picked up again. I for one am still here :) I too think this is a useful feature (which I'll be needing really soon).

I think we have two options here:

Option 1: Extend the behavior of index. Allow for an index identifier to be set, and group indexes by that identifier. E.g.

{
  gender: {type: 'string', index: {name: 'idx_gender_age'}}, // Object could also just be a string
  age: {type: 'integer', index: {name: 'idx_gender_age'}} // Object could also just be a string
};

Option 2: Add a indexes property, holding an object, where you specify the index name as key, and an array or string value as the columns to index, like so:

{
  indexes: {
    idx_gender_age: ['gender', 'age']
  },
  attributes: {
    gender: 'string',
    age: 'integer'
  }
}

I prefer option 2.

Implementing support for this shouldn't be too tricky, but isn't BC either way (if someone is using the properties right now, or incorrectly using index). Also this would have to be implemented on the adapter level. Thoughts?

[gadidev]

I too prefer #2. #1 seems clunky index: {name: 'idx_gender_age'} is repeated.
Option #2 seems more natural, since you could have multiple indexes with the same attributes.
I was planning to use this prevent the same combination of fields (ie gener+age) from being inserted. ie, unique property. Thanks for taking this on.

[RWOverdijk]

@gadidev Good one! We'd also need support for index type.

[Tronix117]

+1

[vladfr]

+1

Was this added? because all comments point to here, but this issue is closed.

[Tronix117]

I agree this is a really important feature, which, among others, Waterline doesn't support yet but should. On our projects, we are currently discussing about replacing Waterline with Knex or Sequelize.

[devinivy]

It's perfectly reasonable to use Waterline as an ORM and Knex to help
manage migrations.

On Thu, Jul 21, 2016 at 11:16 AM, Jeremy TRUFIER [email protected]
wrote:

I agree this is a really important feature, which, among others, Waterline
doesn't support yet but should. On our projects, we are currently
discussing about replacing Waterline with Knex or Sequelize.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/balderdashy/waterline/issues/109#issuecomment-234286066,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ADARmeSo4-WoiJlcmFuC94dpVICF13Reks5qX40zgaJpZM4A90rE
.

[Tronix117]

@devinivy Totally using Knex for migrations right now, when I said Knex, I wanted to say Bookshelfjs, the ORM based on it. We didn't dig too much, but it seems worth discussions.
However Sequelize totaly handle indexes or composite primary keys, which Waterline does not.

[sio-iago]

Feels pretty bad when you start working with JavaScript, get crazy excited and bump into an issue like this in the middle of a project.

I'm not here to be a fanboy, but I'd suggest you guys to use Hibernate/Doctrine as a benchmark to Waterline. Some really important features (and composite key is just one of many I needed) were not developed.

The framework is well developed and easy to use, but it's pending on some really important features guys.

[RWOverdijk]

@sio-iago Perhaps not the place to mention this, but you might want to star wetland. We're currently actively developing it so it should come out soon(ish). It'll first be a drop in replacement (more or less) for waterline. The reason I mention it is because it's closely modeled to the jpa-spec (570 pages of goodness), borrowing principles from doctrine, and that you mentioned doctrine. I thought, perhaps you might want to weigh in on the development (or brainstorm a bit). If so, you can find me here.

[jvanalst]

Ran into this trying to hook sails up to a legacy database. Not sure what I'm going to do now.

[sio-iago]

@jvanalst either cry or use knex/bookshelf.

[arunramachandran15]

Is this issue fixed ?

[Tronix117]

@arunramachandran15 Like previous answer, it's not fixed and I don't think it will be before a long time. Anyway it is not a major issue, since this notion of indexes is only used to automaticaly change the schemas of your database in your DEV environment.

If you need good indexes, it means you expect to have a lot of data, thus you can not rely on waterline mecanism to upgrade your database (I explain why at the end of this message).

You should use sails-migration (which uses Knex) to create a file each time you need to change the schema of your database. You can do pretty much what you want, as well as multi key indexes. It's the recommended solution, and plus it allow you a great control over your schema by giving you the possibility to rollback to a previous migration.

---

You should always have migrate: "safe", otherwise each time you modify a column or change something in the model sails will have the following behavior (even with migrate: alter on mysql) :

1. Dump the corresponding table in memory
2. Drop the table
3. Recreate the table following the specs in the model file
4. Restore the dump from memory in the newly created table (datas that does not fit anymore are lost)

It may be great in development, but totaly not viable once you have a lot of datas in production.

[jvanalst]

The solution we ended up settling on for composite primary keys was to add new id row to the database that was a combination of id1 & id2 (e.g. id = id1*10000 + id2). It's hackney'd and very specific to our situation (since id2 will never realistically be above 9999). But it gets us up and running quickly with legacy data.

[AjkP]

+1 on adding support for this...
3.5 years of conversation on this and no definite answer...

[rvssvl]

+1. I am building a real time chat app and store all the messages of the users in database. So my db has a table "Conversations" with two fields userA and userB(users having conversation with each other) and this pair should be unique, pls could anyone advice how to solve this in Waterline(the solution with direct constraints to db isn't so elegant)?

[Tronix117]

@rassulcrowe Like I said before, the solution using directly the DB with migrations (with knee) is elegant and very clean because waterline handles migrations really poorly and should only be used in safe mode. You can eventually add a custom validation to check the DB for duplicate on inserting.

[omidh28]

+1

[stokesbga]

If you're not using legacy db, the solution is to abandon REST and just use RPC. Who cares. Not me!

[maniflames]

So I just read the whole thread. I wanna +1 but I'm gonna check out the other suggested ORM/ODM because it's kinda clear this functionality is not going to be implemented

[yanlinaung30]

Conversation started 4 years and 8 months ago and no definite answer yet.

[arcseldon]

That's about how long ago i last used Sails.js or Waterline. They were decent open source projects back in the day, but getting traction on maintenance & support for valuable issues / feature gaps was never a good story. Just setting expectations.

[mj-mehdizadeh]

+1

[zhaoyao91]

5 years, wow