Topic: Same Rails app accessing separate database for each user?

I am trying to figure out how I can write a Rails web enabled appication that accesses separate and distinct databases based on user. Obviously, the program would have to login to the common database to access the user-privileges. Along with this user information would be the name of the database to use in the MySQL volume (ex:application_User001). From then on, each controller request for the logged in user would be processed against their distinct database.
I see how we set the database addresses in our \config\database.yml and normally the database is application_production. My objective is to have many databases inside MySQL that are accessible based on user privileges.
Thank you for any ideas you might offer.
David

Re: Same Rails app accessing separate database for each user?

This is also a question I had.  Do you believe this method could help scaling a site that receives a lot of traffic and users?

If so, would this method be a good approach?

Re: Same Rails app accessing separate database for each user?

It seems to me that having multiple databases complicates the problem rather than simplifying it.  Why not have one database with the tables you need with each user's rows in each table identified by their user identifier.

One reason to do this is maintenance or changes later on.  Let's say you have 100 users with 100 databases.  To add a field to any table you would have to it 100 times, one for each database.  On the other hand if all user's were in  one database, you make only 1 change.

Last edited by willirl (2007-03-07 10:55:26)

Re: Same Rails app accessing separate database for each user?

Richard,
I looked at your websites..way cool! It's great to meet someone who builds their own stuff.
I know a very talented man from my ASP.NET days who is the leader of a team building an online web based accounting program. He describes three nightmares of having a single database for many users;
1. If a user say's he "messed up" and wants to restore his data from 2 days ago, his only option is to restore the ENTIRE database or write "in his words", VERY complicated SQL commands". For this guy, VERY COMPLICATED means it.
2. He worries about the LIABILITY of messing someone's data up.
3. His database calls must always work down a chain starting at the users-privileges for each DB call he makes. Because of this he said that by bringing people onto the project the QA overhead is immense to make sure each programmer makes the correct DB calls.
I've sent out many emails on this topic in the last week and am trying to learn more how Basecamp does their user login architecture and maintenance of a large database that is 'forked' with many separate projects.
I would be grateful for any insight into this topic.
David Kennedy
I sent you a private email, also.

Re: Same Rails app accessing separate database for each user?

BraveDave wrote:

I've sent out many emails on this topic in the last week and am trying to learn more how Basecamp does their user login architecture and maintenance of a large database that is 'forked' with many separate projects.
I would be grateful for any insight into this topic.

I'm almost 100% certain Basecamp does not split up the databases based on users, and I recommend you do the same. Although, yes, it will be a pain to restore data for just one user it will be more difficult to manage a duplicate database structure and changing of databases based on the logged in user.

Of course there are exceptions, but I can't think of many reasons it would be better to split up the databases based on the user who accesses it.

Railscasts - Free Ruby on Rails Screencasts

Re: Same Rails app accessing separate database for each user?

What kills me about this whole Ruby on Rails movement is that no one seems to understand the Basecamp database design..at least no one who's willing to admit it. This application is like the Bible or Koran for this new religion but no one seems to have read it?
    If I could find someone, or figure out how Basecamp creates a user-safe security envelope to get into the database, I could run with this and prosper. I have nothing but praise and respect for Basecamp and would never try to compete with it.
    In other words, I just need to know the 'front end' database structure for maintaining this separation of users in a common database.
    All of my applications are going to run the same program logic against 'user-project' distinct data, so I've got to keep pounding on the front door hoping to get in.
    I've been reading as many posts as I can find on this thread and the only answers I can find are "Read a book on Relational Databases" (translated="don't bother me sonny").
    I really think getting this topic out into the open would serve many in the RonR community.
David

Re: Same Rails app accessing separate database for each user?

It would be nice if we knew how Basecamp was done, but it's closed source. There may be a few topics on the mailing list or in DHH's blog which reveals some stuff, but I haven't looked.

The reason I highly doubt Basecamp split the databases by users is because DHH likes to keep constraints out of the database (permission handling, etc.) and he doesn't like duplication. Rails is also built with the single database mentality. Considering it was extracted from Basecamp, it would be easier to change the database on a per-request basis if Basecamp did this. Right now it's rather a hack.

Railscasts - Free Ruby on Rails Screencasts

Re: Same Rails app accessing separate database for each user?

I think it was DHH who said "I basically use databases like hashes". I don't think he'd do a per-user database like what you're asking for Dave. Yes each account has its own subdomain, but that's easily done with virtual hosting and some Rails magic, and can be done while sharing one common database.

vinnie - rails forum admin

Re: Same Rails app accessing separate database for each user?

Vinnie,
I don't mean to beat this topic up, but could you tell me more about how "virtual hosting" applies to the idea of many users accessing DISTINCT data out of one common (monolithic) database? If you wanted to describe a little more about the "Rails Magic" I'm a receptive audience.
I think I've got a reasonable design now to accomplish this but am trying to figure out how one would backup / restore a separate users data in this scheme?
Thank you,
David

10

Re: Same Rails app accessing separate database for each user?

BraveDave wrote:

Vinnie,
I don't mean to beat this topic up, but could you tell me more about how "virtual hosting" applies to the idea of many users accessing DISTINCT data out of one common (monolithic) database? If you wanted to describe a little more about the "Rails Magic" I'm a receptive audience.
I think I've got a reasonable design now to accomplish this but am trying to figure out how one would backup / restore a separate users data in this scheme?
Thank you,
David

The Rails magic I meant was the account_location plugin, which will allow a username.domain.com subdomain per account. I wasn't really talking about the data itself.

And as far as backing up/restoring one user's data, I guess I don't see why it's an issue to back up/restore everyone's data if we're talking about real database administration here. If it's just a matter of one of your users screwing things up in their account, maybe use versioning (i.e.  acts_as_versioned) on any items they create so you can roll back to a previous version (or even better, provide an interface to let them do the rollback themselves).

vinnie - rails forum admin

Re: Same Rails app accessing separate database for each user?

In the comments for this post DHH said he uses only one database per application:

DHH wrote:

Warren, we run free and pay accounts on the same database. It

Railscasts - Free Ruby on Rails Screencasts

Re: Same Rails app accessing separate database for each user?

BraveDave wrote:

1. If a user say's he "messed up" and wants to restore his data from 2 days ago, his only option is to restore the ENTIRE database or write "in his words", VERY complicated SQL commands". For this guy, VERY COMPLICATED means it.

It can be complicated if you don't design your application to account for the possibility that somebody might goof up and break something.  I've built an application that faced the exact same problem and we solved that by allowing for per user backups.  Still only one database.

BraveDave wrote:

2. He worries about the LIABILITY of messing someone's data up.

This is only a problem if you're not keeping security in mind when developing things.  What's to keep me from messing up someone's data if their info is stored in another database? Moot point.

BraveDave wrote:

3. His database calls must always work down a chain starting at the users-privileges for each DB call he makes. Because of this he said that by bringing people onto the project the QA overhead is immense to make sure each programmer makes the correct DB calls.

This is just poor application design if anything.  If your application relies on some fragile way of doing things that can't be broken and requires a bunch of training for a developer to join the team and begin working there is a bigger problem somewhere.

Re: Same Rails app accessing separate database for each user?

I think this might help you out Dave, http://wiki.rubyonrails.org/rails/pages … ccountKeys It's an explanation of one of the links vin gave you.

Posts [ 13 ]

Pages 1