Re: [rails-oceania] Trying to implement per tenant ID's

[Adam Boas]

Hi Rich,

I have never used this Gem but I did notice it a while ago and it seems to be under active development. It might be of use to you:

https://github.com/drnic/composite_primary_keys

I'm not totally sure that any kind of gem is really required here. One thing I can say is that even if ActiveRecord doesn't support composite primary keys, I would still enforce them at the database level to underpin whatever strategy you use. You can then use the Account class that as the owning parent to be a factory for the Invoices and shell out IDs from there which the database will enforce uniqueness on based on the combination of it and the account_id. If you do that building inside a transaction and increment your sequence in there as well you should be pretty good.

Cheers,

Adam

-- 

Adam Boas

e: adam...@gmail.com

m: +61 457 741 117

On Wednesday, 3 April 2013 at 8:29 PM, Rich Buggy wrote:

Hi everyone,

I'm building a multi-tenant application and I'd like some of the ID's to be per tenant. It's using the path based multi-tenant approach. For example - /:account/invoices/:id

I've got the routing working correctly but the ID's are being generated automatically so they are unique across the application, not just the tenant. This isn't ideal because each tenant should start from 1.

I already store the next id in the tenant table so they can choose to start from a number other than 1. This is important as someone with existing data may decide to not start from 1. I think there are a number of ways I can deal with this.

1. If I was doing this without rails I'd make the tenant_id + id a composite field and set the id manually when creating the record. Is there are an easy way to do this with rails?

2. I could add number field and accept that number != id. This is easy but ugly because /demo/invoices/123456 could be invoice 1

3. I could add a number field and try to muck with the routing so it used number instead of id

Does anyone know of a good guide or blog post on how to do this?

    Rich

--
You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rails-oceani...@googlegroups.com.
To post to this group, send email to rails-...@googlegroups.com.
Visit this group at http://groups.google.com/group/rails-oceania?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Ben Hoskings]

Firstly, I'd keep this display ID (maybe "member ID") separate from users.id. Any non-standard behaviour there is asking for trouble. There's a lot of convention built up in activerecord around having a single artificial integer PK, and veering from this standard isn't worth the trouble it causes. (We used some composite PKs for a time at TC as part of a migration strategy, and they were very inconvenient to work with.)

I'd recommend having a unique users.id column like normal, and then a separate member_id that you use for routing, which is unique within each tenant (enforced in the DB).

I'd add a unique index to the users table on (tenant_id, member_id):

    add_index :users, [:tenant_id, :member_id], unique: true

Then I'd add a validation in the model, for reasonable error messages (the real "validation" is done by the DB's unique index):

    validates :member_id, presence: true, uniqueness: {scope: :tenant_id}

Then I'd auto-populate that field before the model's validation happens.

    before_validation :assign_member_id_if_required

    def assign_member_id_if_required

      self.member_id ||= begin

        max_member_id = tenant.users.order('member_id DESC').limit(1).pluck(:member_id).first

        (max_member_id || 0) + 1

      end

    end

That will query efficiently:

    SELECT "users"."member_id" FROM "users" WHERE "users"."tenant_id" = 12345 ORDER BY member_id DESC LIMIT 1

- Ben

On 3 April 2013 20:29, Rich Buggy <ri...@zoombugmedia.com> wrote:

Hi everyone,

I'm building a multi-tenant application and I'd like some of the ID's to be per tenant. It's using the path based multi-tenant approach. For example - /:account/invoices/:id

I've got the routing working correctly but the ID's are being generated automatically so they are unique across the application, not just the tenant. This isn't ideal because each tenant should start from 1.

I already store the next id in the tenant table so they can choose to start from a number other than 1. This is important as someone with existing data may decide to not start from 1. I think there are a number of ways I can deal with this.

1. If I was doing this without rails I'd make the tenant_id + id a composite field and set the id manually when creating the record. Is there are an easy way to do this with rails?

2. I could add number field and accept that number != id. This is easy but ugly because /demo/invoices/123456 could be invoice 1

3. I could add a number field and try to muck with the routing so it used number instead of id

Does anyone know of a good guide or blog post on how to do this?

    Rich

--
You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rails-oceani...@googlegroups.com.
To post to this group, send email to rails-...@googlegroups.com.
Visit this group at http://groups.google.com/group/rails-oceania?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Cheers

Ben

[Warren Seen]

+1 to all of what Ben says. 

I've used composite keys with the CPK gem in apps with legacy DBs - based on this experience it's not something I would consciously choose when starting a new rails app. As Ben says, there's a lot of conventions that need to be overridden to make it work consistently.

[Tim Uckun]

Does anyone know of a good guide or blog post on how to do this?

I found this http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram interesting. You may want to give it a shot.

In my opinion the more you want to do these kinds of things the less suitable rails is for your app.  Maybe if you swapped out sequel for AR you might have an easier time.    I think that AR works best when you don't buck the system which means using it as a least common denominator thing. If you are using AR you give up all kinds of Postgres awesomeness.

[Clifford Heath]

On 04/04/2013, at 8:29 AM, Tim Uckun <timu...@gmail.com> wrote:
> In my opinion the more you want to do these kinds of things the less suitable rails is for your app. Maybe if you swapped out sequel for AR you might have an easier time. I think that AR works best when you don't buck the system…

It's not just AR. CPK helps overcome AR's difficulties in this regard, but a lot
of other things run into difficulties - form helpers like SimpleForm is one.
My position is that while Rails is wrong to be so committed to the surrogate
key concept, if you opt out, you effectively opt out of the whole ecosystem.

Bottom line: Use a surrogate key *and* a natural primary key almost always.
Join tables with no attributes are the one time you can escape surrogates,
and even those often grow attributes.

Clifford Heath, Data Constellation, http://dataconstellation.com
Agile Information Management and Design

[Simon Russell]

What Ben said is probably the easiest thing to do; generally, if you
care about the number (i.e. a requirement that it start from 1) then
probably don't use the autoincrement primary key as that number.

As an extra bit I usually stick with as well: don't end the column
with "_id", unless it's a foreign key in your system; other devs will
like you more when they're refactoring, and also there are various
spots where the "_id" gets stripped off for display (the assumption
being that it's a foreign key).

[Tim Carey-Smith]

As an extra note, I generally add routing helper methods to the model.
These make the router automatically use the member_id for the models :id segment.

``` ruby
class Account < ActiveRecord::Base
def invoice_from_param(param)
invoices.from_param(param)
end
end

class Invoice < ActiveRecord::Base
def self.from_param(member_id)
where(member_id).first!
end

def to_param
member_id
end
end
```

Sadly, it seems that the `first!` and `take!` methods do not provide any information in their exceptions.
https://github.com/rails/rails/blob/81ed4f5ecbb45e96355a5ff3bd00a2c26ed60503/activerecord/lib/active_record/relation/finder_methods.rb#L94-L98

Be wary of having urls which are scoped to the current tenant.
Preferring /accounts/:account/invoices/:invoice over /account/invoices/:invoice as you have done is best.

Cheers,
Tim

[Rich Buggy]

Thanks to everyone who replied.

@Ben: your approach sounds like what I already have. There is an invoice_number that's manually calculated in addition to the id. What I was hoping to do is use the invoice_number in the route instead of id. For example: My existing route is /:tenant/invoices/:id but I would prefer /:tenant/invoices/:invoice_number. Doing this seems involve fighting the framework so I was hoping there may have been an easier way with AR involving changing how the id was calculated. I'll just live with this for now so I don't slow down development. I can revisit it when I'm further down the track.

   Rich

[Paul Annesley]

Have you looked at #to_param on ActiveModel / ActiveRecord?
Also I recommend an equivalent .from_param(param) class method on such models.

class Something < AR::Base
def to_param
customer_number
end
def self.from_param(param)
where(customer_number: param).first!
end
end

— Paul

[Rich Buggy]

Perfect!! That's solved the routing and now it works exactly the way I wanted. Definitely something that needs a blog post this week.

   Rich

--
You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rails-oceani...@googlegroups.com.
To post to this group, send email to rails-...@googlegroups.com.
Visit this group at http://groups.google.com/group/rails-oceania?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

--
Zoombug Pty Ltd
www.zoombugmedia.com

[Ryan Bigg]

Coming in to the thread a bit late here, but I think you should look into using PostgreSQL's schemas to separate the data for your tenants. The benefit of this is that in order to scope the data so that you're only showing one tenant's data, it's as easy as switching the schema lookup path to being just for that user's schema and the public schema.

You can do this very easily with the Apartment gem: https://github.com/influitive/apartment

Apartment::Database.create(account.database_name)

Where database_name is a unique attribute on your model. That command will create a new schema and run your migrations in that schema. 

In a before_filter in your controller you can look up the account object, and then switch to it using this method:

Apartment::Database.switch(account.database_name)

Subsequent calls to your models will look up data only from the named schema, which will have a completely unique set of IDs from the same table in different schemas.

I know it sounds like you're pretty set on what you're doing now. This PostgreSQL schema usage is just something to consider. I'm using this in the multitenancy book I'm  writing (http://leanpub.com/multi-tenancy-rails), and we've used this at Spree on some of our side projects to great success. Just thought you would like to know an alternative.