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.

[0xgeert]

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 notifications@github.com
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.

[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 notifications@github.com
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

[TimofeyBiryukov]

It's 7 years. This would still be a good feature to add.

[vinilneves]

Just short of 11! 🤡