Dynamic schema/namespace

[Ján Raška]

Hi guys,

for a multi-tenant application I want to have per-tenant data separation in place, thus I'm looking for a solution with per-tenant PostgreSQL-like schema. Tthis will work also on MySQL/MariaDB, since there effectively database is just an alias for schema.

I like this approach since I if all schemas are accessibla by 1 user, I can then reuse connections via connection pool as compared to per-tenant database (it'd be quite a problem with hundreds/thousands of tenants), and as compared to TenantID foreign key, I have namespace-level data separation, so it's less likely to be vulnerable to data leak bug on the application layer.

Now I'm a long time Squeryl user and haven't used Slick at all for now, however to achieve this with Squeryl would be a bit problematic, since it was suggested to me that dynamic creation of Table instances is rather heavy and there seems to be no other way to specify schema per-demand on individual queries. I've however noticed that in Slick documentation examples (eg. http://slick.typesafe.com/doc/3.0.1/schemas.html ) all TableQuery instances are being created either as vals or object, similar to the recommended (and supposedly the only correct) way in Squeryl.

So my question is very similar to what I've been asking in Squeryl group: is it a good approach to define table queries as a function returning always new TableQuery instance, where I could provide schema as a parameter? Is there any downside to this, or a performance issue I should be afraid of? To be more precise, I'm talking about doing something like this:

class User(tag: Tag) extends Table[(Int, String, String, String)](tag, "USERS") {

  def id = column[Int]("USER_ID", O.PrimaryKey)

  //...

}

def users(tenantSchema: Option[String]) = new TableQuery(new User(_, tenantSchema, "USERS"))

Given that the above would work just fine and I wouldn't suffer from some performance or memory leak issues, am I on right track to do foreign key definitions the following way?

class User(tag: Tag, tenantSchema: Option[String]) extends Table[(Int, String, String, String)](tag, tenantSchema, "USERS") {

  def id = column[Int]("USER_ID", O.PrimaryKey)

  //...

}

def users(tenantSchema: Option[String]) = new TableQuery(new User(_, tenantSchema))

class Invoice(tag: Tag, tenantSchema: Option[String]) extends Table[(Int, String, String, Double)](tag, tenantSchema, "INVOICES") {

  def issuerID = column[Int]("ISSUER_ID")

  //...

  def issuedBy = foreignKey("ISSUER_FK", issuerID, users(tenantSchema))(_.id, onUpdate=ForeignKeyAction.Restrict, onDelete=ForeignKeyAction.Cascade)

    

}

Please forgive possible mistakes in syntax/concepts, as I've said, I haven't used Slick before, so I might be missing a point here, I'm just trying to find out, if Slick is worthy considering, if Squeryl can't do what I need. And I also don't want to start Slick vs. Squeryl discussion here, I mentioned it just so that you understand where I'm coming from and what motivations I have to consider migrating.

Many thanks

Jan

[Ján Raška]

Just wondering, any input on this? I'll be very thankful.

[Naftoli Gugenheim]

I don't see why not, but I think it's safer to make the schema a parameter or property of the enclosing class/method.

--

---
You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/3a3b891e-d83e-4736-8429-7fc25a3abe29%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Naftoli Gugenheim]

Sorry ... because that way within the scope you know you're using the same schema, and from the outside, instead of threading the schema through lots of calls you just need to hold onto the schema "DAO."