Deprecate query builder

[mbrandenburger]

Based on the discussion #1122 (comment) we remove the internal query builder (platform/view/services/storage/driver/sql/query) and replace it with an external dependency.

This will lower the maintenance burden on FSC and will allow applications to write flexible queries.

Action plan:

• No new query builder (qb) features
• Evaluate existing qb frameworks
• Create a migration plan and deprecate

[sid200727]

Hi @mbrandenburger, I am interested in helping with this task.

I have been contributing to fabric-token-sdk (PR #1431) and am
familiar with the current query builder usage across both repos.

I can start by evaluating existing Go SQL query builder frameworks
and proposing a migration plan. Would that be a good first step?

[mbrandenburger]

Hi @sid200727 Thank you for you help! It would be wonderful if you could have a look into this.

[sid200727]

Hi @mbrandenburger, I looked into the current query builder and evaluated
a few external alternatives. Here's what I found:

Current State

The internal QB (platform/view/services/storage/driver/sql/query) is about
~1,800 lines across 26 files. The main operations used across the repo are:

• q.Select() — 31 times
• q.Table() — 32 times
• q.InsertInto() — 10 times
• q.Update() / q.Delete() — 2 times each

The main consumers are platform/view/services/storage/driver/sql/common/
and platform/fabric/services/db/driver/sql/common/. Worth noting that
fabric-token-sdk also uses this transitively, so any migration will
affect both repos.

Frameworks I looked at

1. squirrel (github.com/Masterminds/squirrel) — my top pick

The API is very close to what we already have, so the migration would be
relatively straightforward. It handles both postgres ($1) and sqlite (?)
placeholder formats out of the box via PlaceholderFormat, which is exactly
what FSC needs. The only downside is no built-in pagination support, but we
can keep our custom pagination logic as-is.

// Current internal QB
q.Select().FieldsByName("id").From(q.Table("users")).
    Where(cond.Eq("id", val)).Format(ci)

// Squirrel equivalent
sq.Select("id").From("users").
    Where(sq.Eq{"id": val}).ToSql()

2. goqu (github.com/doug-martin/goqu) — solid but overkill

Has great dialect support and is more type-safe, but it's a heavier dependency
and the API is more complex than what we need here. Migration effort would be
noticeably higher.

3. go-sqlbuilder (github.com/huandu/go-sqlbuilder) — decent but niche

Has explicit postgres/sqlite flavor support which is nice, but the API is more
verbose and community adoption is much lower than squirrel. Didn't feel like
the right fit.

What I'd suggest

Given how close squirrel's API is to ours, I think the migration can be done
in phases:

Phase 1: Freeze
Add //Deprecated comments to all internal QB packages — no new features
from this point.

Phase 2 — Add squirrel

go get github.com/Masterminds/squirrel

Set up dialect-aware builders for postgres and sqlite:

// postgres
sq.StatementBuilder.PlaceholderFormat(sq.Dollar)

// sqlite
sq.StatementBuilder.PlaceholderFormat(sq.Question)

Phase 3 — Migrate file by file

Priority order:

1. platform/view/services/storage/driver/sql/common/ (5 files)
2. platform/fabric/services/db/driver/sql/common/ (5 files)

Phase 4 — Cleanup

Once all consumers are migrated, delete
platform/view/services/storage/driver/sql/query/ and clean up
go.mod/go.sum.

Does this approach sound reasonable? Happy to start with Phase 1 and the
squirrel integration if you're aligned on the direction.

[mbrandenburger]

Hi @sid200727 - the proposal sounds fantastic. squirrel looks like a solid choice.

I suggest that we change your plan slightly and do not "deprecate" our query builder code - as this would cause the tokenSDK to get many linter errors.

Therefore I suggest the following:

We start with Phase 2 + 3. We use this feature branch to validate the approach with the Token SDK. If this validation is successful, we can deprecate our query builder and remove it :)

WDYT?

[sid200727]

Hi @mbrandenburger, thanks for the direction!
I'll start with Phase 3 on a fabric-token-sdk branch to validate the squirrel migration approach first, before touching the
query builder deprecation.

I'll begin with the highest priority files:

• platform/fabric/services/storage/driver/sql/query/
  (5 files)
• platform/fabric/services/storage/driver/sql/common/
  (5 files)

Will open a draft PR once I have the first few files migrated so you can review the approach early.
Does that sound good?

[sid200727]

Hi @mbrandenburger, opened draft PR #1199 for the squirrel migration starting with SignerInfoStore as a proof of concept:
#1199
DCO is green. CI workflows are awaiting maintainer approval to run.

Would appreciate early feedback on the approach before proceeding to the remaining files (binding.go, auditinfo.go, simplekeydata.go, unversioned.go).