Embedding a fragment into a query?

[adamw]

In my first attempts to constructing dynamic queries, I had something like:

def findFiltered(filter: Frag) = sql"SELECT * FROM table WHERE $filter"

findFiltered(sql"age = 12")

However, it turns out that you can't embed Frags into interpolated sql queries. Is this by design?

I've since discovered Spec, which for now meets my needs, but I suspect that once dynamic queries with JOINs or such come into play, Spec might no longer be enough.

[AugustNagro]

However, it turns out that you can't embed Frags into interpolated sql

queries. Is this by design? It's definitely something we can implement, but hopefully the Spec abstraction will cover your dynamic query use case.

I've since discovered Spec, which for now meets my needs, but I suspect

that once dynamic queries with JOINs or such come into play, Spec might no longer be enough. When joins are required, create a database view, and then build a Spec against the view.

…

On Tue, Aug 6, 2024, 4:33 AM Adam Warski ***@***.***> wrote: In my first attempts to constructing dynamic queries, I had something like: def findFiltered(filter: Frag) = sql"SELECT * FROM table WHERE $filter" findFiltered(sql"age = 12") However, it turns out that you can't embed Frags into interpolated sql queries. Is this by design? I've since discovered Spec, which for now meets my needs, but I suspect that once dynamic queries with JOINs or such come into play, Spec might no longer be enough. — Reply to this email directly, view it on GitHub <#34>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQOKNSQQRA7B67LGCHFGXDZQCYBHAVCNFSM6AAAAABMCELABCVHI2DSMVQWIX3LMV43ASLTON2WKOZSGQ2TANRVGY4DGNQ> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[adamw]

Hm well views seem to be quite a "heavyweight" solution, as that's a database object that you have to first create. So simply "writing a query and getting results" becomes much harder. Views are definitely a solution, but I think it would be great if magnum would be less opinionated here and allow for constructing ad-hoc complex queries as well.

[AugustNagro]

Good point @adamw . Giving this some more thought, I've opened two WIP merge requests:

1. Support Frag parameters in the sql interpolator: Embed frags in sql interpolator #51

  test("embed Frag into Frag"):
    def findPersonCnt(filter: Frag)(using DbCon): Int =
      sql"SELECT count(*) FROM person WHERE $filter".query[Int].run().head
    val isAdminFrag = sql"is_admin = true"
    connect(ds()):
      val johnCnt = findPersonCnt(sql"first_name = 'John' AND $isAdminFrag")
      assertEquals(johnCnt, 2)

Pros

• Simple implementation
• Very general purpose and covers all statement types

Cons:

• Can be abused making the SQL layer a complicated mess. I've seen before in other slick/doobie projects and it was a main rational for the Spec class.

2. Enhance Spec to support a user-supplied 'prefix' Frag that ends before the WHERE clause. (WIP MR: WIP: Support prefixes in Spec #52 )

  test("spec with prefix"):
    val age = 3
    val frag = Spec[User](sql"SELECT * FROM user")
      .where(sql"age > $age")
      .build
    assertEquals(frag.sqlString, "SELECT * FROM user WHERE (age > ?)")
    assertEquals(frag.params, Vector(age))

Pros:

• Allows one to use Specs without database views if joins are required

Cons:

• Still needs some design thought and only covers dynamic queries (which is however the most common case).

Right now I think both would be valuable. LMK your thoughts.

[adamw]

Yeah I think both proposals are supplementary. Combining SQLs from fragments is kind of a last-resort thing - discouraged, dangerous, but good to have that "ultimate flexibility" when possible. So I think I would like to have that option. But I agree that using Specs should be the way to go as long as possible - after all, this gives us type safety. Probably documenting such a "gradual degradation path" of safety (Spec - Spec with prefix - Embedding fragments) will still cause some people to misuse the features, but what can you do ... ;)

[AugustNagro]

Since #51 is merged,

I've set the Spec-refactor MR to close this issue.

Just FYI, there are a few breaking changes introduced (such as removing the Spec.build method, which is now the responsibility of the Repo to implement). This MR will make it into v2.0.0 which I plan to release this month along with the ZIO module.

I will continue to support branch 1.x with any bug-fixes as needed.

[AugustNagro]

Closed by #73

[adamw]

Sounds good, thanks @AugustNagro! Just to make sure - "direct"-style will also still be possible?

[AugustNagro]

Just to make sure - "direct"-style will also still be possible?

100%!

…

On Mon, Dec 2, 2024, 11:49 AM Adam Warski ***@***.***> wrote: Sounds good, thanks @AugustNagro <https://github.com/AugustNagro>! Just to make sure - "direct"-style will also still be possible? — Reply to this email directly, view it on GitHub <#34 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQOKNV63RKA3I5KXKBNFAT2DS2VVAVCNFSM6AAAAABMCELABCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKMJSGYZDGMBQGI> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[AugustNagro]

Please feel free to make feature request issues with changes you'd like to see in v2. One thing I have in mind is refactoring the package structure.. it's not ideal to have everything in the magnum package if using wildcard imports.

…

On Mon, Dec 2, 2024, 4:26 PM August Nagro ***@***.***> wrote: > Just to make sure - "direct"-style will also still be possible? 100%! On Mon, Dec 2, 2024, 11:49 AM Adam Warski ***@***.***> wrote: > Sounds good, thanks @AugustNagro <https://github.com/AugustNagro>! Just > to make sure - "direct"-style will also still be possible? > > — > Reply to this email directly, view it on GitHub > <#34 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ABQOKNV63RKA3I5KXKBNFAT2DS2VVAVCNFSM6AAAAABMCELABCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKMJSGYZDGMBQGI> > . > You are receiving this because you were mentioned.Message ID: > ***@***.***> >

[adamw]

@AugustNagro great to know :) I think I posted all of the issues I had so far ... maye the only thing left would be integration with various "error modes", as exemplified in softwaremill/bootzooka#1364 (thanks for the PR!):

def transactEither[E, T](f: DbTx ?=> Either[E, T])(using IO): Either[E, T] =
     try com.augustnagro.magnum.transact(transactor)(Right(f.fold(e => throw LeftException(e), identity)))
     catch case e: LeftException[?] => Left(e.asInstanceOf[LeftException[E]].left)

this works, but the try-catch is not ideal. Maybe some way of evaluating the transaction's logic into a Commit/Rollback result would work here?