You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Andreas Hartmann <an...@apache.org> on 2009/07/26 15:10:34 UTC
PostgreSQL schemas for multi-tenant application
Hi Cayenne users & devs,
I'm currently designing the DB + ORM layer for a multi-tenant
application, using PostgreSQL 8.3 and Cayenne 2.0.4.
After some research it looks like PostgreSQL schemas are a pretty decent
way to achieve the separation of the tenant data on the DB level.
Unfortunately, it looks like multiple schemas are not supported by Cayenne.
Is this correct?
If yes, what strategy would you recommend for the multi-tenancy? Do I
have to resort to using a tenant ID column in the affected tables?
Thanks a lot for any hints!
-- Andreas
--
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01
Re: PostgreSQL schemas for multi-tenant application
Posted by Øyvind Harboe <oy...@zylin.com>.
> thanks for your reply! Do you think a final release of Cayenne 3.0 will be
> available this year? Our application shall go live in January, and since I'm
> not familiar with the Cayenne project yet I'm not sure whether the
> milestones should be used in production.
We've been using Cayenne 3.0 in production for a year or so now. Very
happy with it.
What I would advise is that you test your application against a specific
version of Cayenne and that version may well be a 3.0 release candidate
or even some svn version. We upgrade to svn head/release candidate
whenever we run into a problem or need some new feature.
The advantage of using the latest version of Cayenne is that any
problem you do run into is more likely to have a fix that is relatively
easy to apply to the particular version of Cayenne you're using.
"A problem" covers adding new features to Cayenne to cover some
off the beaten path usage... You'll probably find that there is a
greater interest in the community to work on the latest stuff than
working on older versions.
--
Øyvind Harboe
Embedded software and hardware consulting services
http://www.zylin.com
Re: PostgreSQL schemas for multi-tenant application
Posted by Andreas Hartmann <an...@apache.org>.
Hi Michael,
Michael Gentry schrieb:
> I think multiple DataDomains might be what you want, but I'm not 100%
> certain everything you are trying to do.
> Also, if you are just starting out, it might be worthwhile to use Cayenne
> 3.0M6.
thanks for your reply! Do you think a final release of Cayenne 3.0 will
be available this year? Our application shall go live in January, and
since I'm not familiar with the Cayenne project yet I'm not sure whether
the milestones should be used in production.
-- Andreas
>
> mrg
>
>
> On Sun, Jul 26, 2009 at 10:51 AM, Andreas Hartmann <an...@apache.org>wrote:
>
>> Andreas Hartmann schrieb:
>>
>>> Andreas Hartmann schrieb:
>>>
>>>> Hi Cayenne users & devs,
>>>>
>>>> I'm currently designing the DB + ORM layer for a multi-tenant
>>>> application, using PostgreSQL 8.3 and Cayenne 2.0.4.
>>>>
>>>> After some research it looks like PostgreSQL schemas are a pretty decent
>>>> way to achieve the separation of the tenant data on the DB level.
>>>> Unfortunately, it looks like multiple schemas are not supported by Cayenne.
>>>>
>>> I just noticed that the DbEntity class has a schema property. Would the
>>> following approach work?
>>>
>>> Create a DB entity for each table in each schema, e.g. users_schema1,
>>> users_schema2 etc.
>>>
>>> To run a query:
>>>
>>> DbEntity entity = dataMap.getDbEntity("users_" + schemaName);
>>> SelectQuery query = new SelectQuery(entity, expression);
>>>
>> It seems like this won't work because for each DB entity an object entity
>> (i.e. class) would be required.
>>
>> But I found the section on multiple DataDomains in the documentation. I
>> guess this is the way to go?
>>
>>
>> -- Andreas
>>
>>
>> --
>> Andreas Hartmann, CTO
>> BeCompany GmbH
>> http://www.becompany.ch
>> Tel.: +41 (0) 43 818 57 01
>>
>>
>
--
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01
Re: PostgreSQL schemas for multi-tenant application
Posted by Michael Gentry <mg...@masslight.net>.
I think multiple DataDomains might be what you want, but I'm not 100%
certain everything you are trying to do.
Also, if you are just starting out, it might be worthwhile to use Cayenne
3.0M6.
mrg
On Sun, Jul 26, 2009 at 10:51 AM, Andreas Hartmann <an...@apache.org>wrote:
> Andreas Hartmann schrieb:
>
>> Andreas Hartmann schrieb:
>>
>>> Hi Cayenne users & devs,
>>>
>>> I'm currently designing the DB + ORM layer for a multi-tenant
>>> application, using PostgreSQL 8.3 and Cayenne 2.0.4.
>>>
>>> After some research it looks like PostgreSQL schemas are a pretty decent
>>> way to achieve the separation of the tenant data on the DB level.
>>> Unfortunately, it looks like multiple schemas are not supported by Cayenne.
>>>
>>
>> I just noticed that the DbEntity class has a schema property. Would the
>> following approach work?
>>
>> Create a DB entity for each table in each schema, e.g. users_schema1,
>> users_schema2 etc.
>>
>> To run a query:
>>
>> DbEntity entity = dataMap.getDbEntity("users_" + schemaName);
>> SelectQuery query = new SelectQuery(entity, expression);
>>
>
> It seems like this won't work because for each DB entity an object entity
> (i.e. class) would be required.
>
> But I found the section on multiple DataDomains in the documentation. I
> guess this is the way to go?
>
>
> -- Andreas
>
>
> --
> Andreas Hartmann, CTO
> BeCompany GmbH
> http://www.becompany.ch
> Tel.: +41 (0) 43 818 57 01
>
>
Re: PostgreSQL schemas for multi-tenant application
Posted by Andreas Hartmann <an...@apache.org>.
Andreas Hartmann schrieb:
> Andreas Hartmann schrieb:
>> Hi Cayenne users & devs,
>>
>> I'm currently designing the DB + ORM layer for a multi-tenant
>> application, using PostgreSQL 8.3 and Cayenne 2.0.4.
>>
>> After some research it looks like PostgreSQL schemas are a pretty
>> decent way to achieve the separation of the tenant data on the DB
>> level. Unfortunately, it looks like multiple schemas are not supported
>> by Cayenne.
>
> I just noticed that the DbEntity class has a schema property. Would the
> following approach work?
>
> Create a DB entity for each table in each schema, e.g. users_schema1,
> users_schema2 etc.
>
> To run a query:
>
> DbEntity entity = dataMap.getDbEntity("users_" + schemaName);
> SelectQuery query = new SelectQuery(entity, expression);
It seems like this won't work because for each DB entity an object
entity (i.e. class) would be required.
But I found the section on multiple DataDomains in the documentation. I
guess this is the way to go?
-- Andreas
--
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01
Re: PostgreSQL schemas for multi-tenant application
Posted by Andreas Hartmann <an...@apache.org>.
Andreas Hartmann schrieb:
> Hi Cayenne users & devs,
>
> I'm currently designing the DB + ORM layer for a multi-tenant
> application, using PostgreSQL 8.3 and Cayenne 2.0.4.
>
> After some research it looks like PostgreSQL schemas are a pretty decent
> way to achieve the separation of the tenant data on the DB level.
> Unfortunately, it looks like multiple schemas are not supported by Cayenne.
I just noticed that the DbEntity class has a schema property. Would the
following approach work?
Create a DB entity for each table in each schema, e.g. users_schema1,
users_schema2 etc.
To run a query:
DbEntity entity = dataMap.getDbEntity("users_" + schemaName);
SelectQuery query = new SelectQuery(entity, expression);
TIA!
-- Andreas
--
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01