Using Squeryl with multiple database connections/schemas

[Dick Wall]

Hi Max and everyone,

I am wondering if I can do the following simply, it feels like I am
fighting squeryl too hard right now, and there has to be a simpler
way, so I thought I would check before digging in any deeper.

We have several different databases that we are using for accessing
data with Squeryl. For the first time we need to use code that
accesses both databases at the same time, and I am getting confused
over the transaction and concreteFactory behavior. Let me explain
quickly what I want to do:

I have a dependency injection mechanism which can, for any of my
classes, inject the session I want to be able to use for a particular
query, let's say I have curationSession and variantSession injected

val curationSession = inject[Session]('curation)
val variantSession = inject[Session]('variant)

These are now completely different sessions, with even different
database connections and schemas. What I want to be able to do is
something like this:

curationSession.transaction {
// query against the curation DB
}

variantSession.transaction {
// query against the variant DB
}

and have these behave as for transaction itself (e.g. rollback on
exception, etc.) but for the specific sessions rather than the one
returned by concreteFactory. In fact, I would rather not have
concreteFactory defined, since I would like to know that a specific
session is always being used, rather than it defaulting to possibly
the wrong one.

I would even like to be able to nest these if needed:

curationSession.transaction {
// do some stuff with the curation session

val variantMatch = variantSession.transaction {
// query to look up a variant
}

// do some more stuff in curationSession based on the variantMatch
field
}

Is this possible? It looks like I might want something like

using(curationSession) {
// do stuff in curation
val variantMatch = using(variation) {
// query to look up a variant
}

// do more stuff in curation using the variantMatch
}

however it is not clear to me if this is the right approach, nor what
is managing the transaction in this case to make sure a rollback
occurs in the case of an exception.

I could also be barking up the wrong tree entirely. Hopefully this
post makes my intention clear, what's the best way to achieve this in
Squeryl?

Thanks

Dick

[Maxime Lévesque]

In your case, don't initialize any session factory, create your two sessions and do :

using(sessionA) {
  ...query against sessionA
}

using(sessionB) {
  ...query against sessionA 
}

Nesting should also work, i.e :

using(sessionA) {
  ...query against sessionA
  using(sessionB) {
    ...query against sessionB
  }
}

If you want commits or rollback at the end of the block, you can do :

using(sessionA) inTransaction {
  ...query against sessionA
}

using(sessionB) inTransaction {
  ...query against sessionA
}

(Notice that it's inTransaction, not transaction, because
the later will make use of Session.concreteFactory which
you'll purposely leave unassigned.


Under the hood, using(s) assigns s as the 'currentSession'
on the thread local storage, so inTransaction will to a commit/rollback
with this 's'. It also puts back the previous 'curent' session  on the TLS
if there was one, to ensure that nesting of 'using' is possible

ML

[Dick Wall]

Thanks Max - that confirms I was going along the right lines. I didn't
get it working yet but our system is fiddly, so I will track down the
issues now I know this is the way it is supposed to work.

[Ján Raška]

Wow, that's what I've been trying to find out :)

Just to clarify, using(sessionA) { } will give me a praticular transaction block, same as transaction{}? So instead of transaction{} I use using(session){} and instead of inTransaction{} I use using(session) inTransacion{}?

Also nesting is really cool, as I mostly need to work with one session, so I'd put it in a loanWrapper in Lift, and use the other session only when necessary.

[Maxime Lévesque]

I just noticed I said something false in my reply to Dick :

in the combination of using and inTransaction :

using(sessionA) inTransaction { } 

the inTransaction has no effect at all...

because the inTransaction "sees" that there is a current session, so it doesn't create a new one.

to get the behavior that I described, a method like :

  def transaction[A](s: Session)(a: =>A) =
    _executeTransactionWithin(s, a _)

could be added here :

  https://github.com/max-l/Squeryl/blob/master/src/main/scala/org/squeryl/dsl/QueryDsl.scala#L90

Then one would do :
 
transaction(sessionA) {
  ...query against sessionA
  transaction(sessionB) {
    ...query against sessionB
  }
}


ML

[Idan Waisman]

Hi Max. You have suggested adding the methods:

 def transaction[A](s: Session)(a: =>A)

 def inTransaction[A](s: Session)(a: =>A)

but as of yet they don't exist and they would have to make direct use of private methods in QueryDsl. Is there a preferred way to submit a patch (which I would be happy to do)? I would rather not rely on my own fork for obvious reasons...

Or maybe since this post is a bit dated at this point you have other mechanisms for handling use of Squeryl on multiple DBs?

Thanks,

Idan

[Maxime Lévesque]

I experimented with adding

  def transaction[A](s: Session)(a: =>A)

and it creates an ambiguity with calls to transaction that return the Nothing type, ex.:

 transaction {
  .....
   sys.error("!")
 }

I reflected on it, and left it there ever since...

Now that I rethink of it, I thinkg that the above example is pretty extreem (as in rare)
the test suite does it in order to cause a rollback, which is not the cleanest thing,

So I'll go ahead and add

  def transaction[A](s: Session)(a: =>A)

and cases of
 transaction {
  .....
   sys.error("!")
 }

should they occur, will have to be rewritten as

 transaction {
  .....
  fail
 }

 def fail: Unit = sys.error(....)

it's a slight backward incompatibility, shich is what make me hesitate, but
I think no one should get affected, cause they should probably be doing this instead :

 transaction {
  .....
  Session.currentSession.connection.rollback
 }

Watch the master branch, it should get there once I'm done
running the test suite.


Now, for  def inTransaction[A](s: Session)(a: =>A)

I'm a bit skeptical because it means :
use this session if none is already in scope....

Is there a compeling use case for it ?


ML

2011/10/12 Idan Waisman <iwai...@manaproducts.com>

[Idan Waisman]

Well, I am considering two slightly separate issues at the moment.

1) Using multiple databases with Squeryl (the original use case presented in this thread)

And now that you present the question it does seem strange to have inTransaction[A](s: Session)(a: => A). Perhaps it makes more sense to have something like:

def transaction[A](factory: Some[() => Session])(a: => A) 

def inTransaction[A](factory: Some[() => Session])(a: => A) 

where the given factory is also used as a key to a Map version of Session.currentSession or some such... basically some mechanism to have multiple currentSessions one for each given data source. Said another way, it seems like it would be more appropriate to deal with transactional blocks upon a (factory, currentSession) pair (which one might be tempted to call a "context"). I hope that makes sense.

2) I am attempting to use Squeryl from within Akka Actors and I am having trouble determining a good place to set the SessionFactory.concreteFactory

Because akka actors share threads (though presumably not in between receive() calls; for the uninitiated think of the receive() call as a unit of work) I would need to reset the SessionFactory.concreteFactory at the beginning of each receive which is... problematic. It would be more convenient to be able to specify something like the "context" from #1 above when necessary. This would be especially convenient if such contexts were weakly referenced so that they could be cheaply produced without having to worry about explicitly cleaning them up.

Again, I hope this makes sense and that I am not sounding like a crazy person. I am not experienced in the design of frameworks requiring funky session management so forgive me if this is newb-ish.

I definitely appreciate any help with these issues!

-Idan

[Maxime Lévesque]

Why the option in :

 def transaction[A](factory: Some[() => Session])(a: => A) 

 def inTransaction[A](factory: Some[() => Session])(a: => A) 

I.e. why not :

  def transaction[A](factory: () => Session)(a: => A) 

  def inTransaction[A](factory: () => Session)(a: => A)

The later would make much sense indeed. I think that given you not so uncommon
use case, it's the right thing to have.

Session.currentSession would refer to the correct one, (currentSession behave like a stack, it returns
the innermost session when there is nesting)

If there were no worries of backward compatibility, these signatures would probably be ideal :

 def transaction[A](implicit s: Session)(a: => A) 

 def transaction[A](implicit factory: () => Session)(a: => A) 

 def inTransaction[A](implicit factory: () => Session)(a: => A)

and there could be a default implicit factory defined that would be the
thread local based one that is in use today.

ML

2011/10/12 Idan Waisman <iwai...@manaproducts.com>

Well, I am considering two slightly separate issues at the moment.

[Idan Waisman]

I like your formulation better. There is no compelling reason for the Some - I was just pasting from the concreteSession type.

You mention concerns for backwards compatibility. How will you handle that? Do you actually intend to implement this new idiom? I would be eager to make use of it, of course.

-Idan

[Maxime Lévesque]

These :

1)

  def transaction[A](factory: () => Session)(a: => A) 

  def inTransaction[A](factory: () => Session)(a: => A)

can be added without breaking anything, however these :

2)

  def transaction[A](implicit factory: () => Session)(a: => A)
  def inTransaction[A](implicit factory: () => Session)(a: => A)

  def transaction[A](implicit s: Session)(a: => A) 

could break backward compatibility, for (2), it has to be experimented.

I have no problems adding (1), you can add these in QueryDsl :

  def transaction[A](factory: () => Session)(a: => A): A = transaction(factory()) {a}
 
  def inTransaction[A](factory: () => Session)(a: => A) =
    if(Session.hasCurrentSession)
      transaction { a }
    else
      transaction(factory()) { a }

I'll eventually add them to the master banch

ML

2011/10/12 Idan Waisman <iwai...@manaproducts.com>

I like your formulation better. There is no compelling reason for the Some - I was just pasting from the concreteSession type.

[Idan Waisman]

That's excellent! I will make sure to watch the master branch.

-Idan

[ido]

On Friday, June 15, 2012 10:58:03 PM UTC+2, Sebastian wrote:

Is this feature already implemented? I need it for a project and didn't find it in squeryl 0.9.5-2.

yes, in 0.9.5 I use

transaction(session) {
   //something     
}

and it works with 2 databases connected with 2 connection pools.

The linked API docs is out of date (0.9.5 RC1).

https://github.com/max-l/Squeryl/blob/master/src/main/scala/org/squeryl/dsl/QueryDsl.scala

 def transaction[A](s: Session)(a: =>A) =

     _executeTransactionWithin(s, a _)

HTH,
ido