MySQL compatibility for esignet-service

[anushasunkada]

Context

esignet-service (the Go rewrite under /opt/mosip/github/idp/esignet-service, not the Java module) is currently hardwired to PostgreSQL end-to-end:

• Connection layer (internal/config/db.go): builds a Postgres keyword-value DSN and configures a pgxpool.Pool directly; cmd/esignet/main.go hardcodes sqlx.NewDb(pgConn, "pgx").
• Query layer: clientmgmt/db and consentmgmt/db are sqlc-generated (sqlc.yaml hardcodes engine: "postgresql") from query.sql files using $1 placeholders, ON CONFLICT ... DO UPDATE ... EXCLUDED, RETURNING *, and IS NOT DISTINCT FROM. keymanager/db/db.go is hand-written with sqlx, also using $1-style placeholders built via fmt.Sprintf with schema-qualified table names.
• Error classification: clientmgmt/service.go (isDuplicateClientID, isDuplicatePublicKeyHash) and keymanager/rotation.go (IsDuplicateUniIdent) detect unique-constraint violations by type-asserting *pgconn.PgError and checking SQLSTATE 23505 + ConstraintName — a third, independently-discovered Postgres-only dependency beyond the SQL text itself.
• Provisioning: db_scripts/mosip_esignet/ and db_upgrade_script/mosip_esignet/ are psql-driven (schema/search_path, CREATE ROLE, GRANT ... ON ALL TABLES IN SCHEMA, ALTER DEFAULT PRIVILEGES). docker-compose/docker-compose.yaml only provisions postgres:bookworm.
• The actual table DDL (ddl/*.sql) is already portable — plain varchar/timestamp/boolean, no jsonb, arrays, or Postgres-only types — and the sqlc-generated Go models use plain database/sql types (sql.NullString, sql.NullTime), not pgx-specific ones.
• All three DB packages' callers (clientmgmt/service.go, consentmgmt/service.go, cryptomanager via keymanager/db) depend only on each package's Querier interface and db.New(...)/model/*Params structs — never on the sqlc-generated implementation directly. This is what makes an internal rewrite safe: the public surface (interface method signatures, struct field names/types) must stay identical so calling code needs no changes.

Goal: add MySQL as a second supported database without duplicating business logic, and without hard-wiring the solution to exactly two databases — the user wants a design that keeps adding a third RDBMS cheap. Chosen direction: drop sqlc for query execution, converge clientmgmt/db and consentmgmt/db onto the same hand-written sqlx pattern keymanager/db already uses, and eliminate dialect divergence at the SQL-text and error-handling level wherever possible instead of maintaining parallel implementations per database.

Approach: five portability rules, two isolated exceptions

Rule A — Placeholders are free. Write every query with ?, never $N. Convert once per query string, at construction time, via sqlx.Rebind(sqlx.BindType(driverName), query) — a standalone function, so the DBTX/TX interfaces in all three db packages stay plain database/sql-shaped (no need to switch clientmgmt/consentmgmt onto sqlx.DB/sqlx.Tx at all). Adding a third RDBMS needs no query-text changes, only a sqlx.BindType mapping sqlx mostly already knows.

Rule B — Never use RETURNING. Replace clientmgmt's INSERT ... RETURNING * / UPDATE ... RETURNING * (CreateClient, UpdateClient, PatchClient) with the write followed by a SELECT ... WHERE id = ? in Go. Written once, works on every RDBMS.

Rule C — No dialect-specific null-safe operators. Replace PatchClient's upd_dtimes IS NOT DISTINCT FROM sqlc.arg(expected_upd_dtimes) with a WHERE clause built conditionally in Go: upd_dtimes IS NULL when the expected value is nil, else upd_dtimes = ?. Fully portable.

Rule D (isolated exception #1) — the upsert. consentmgmt's UpsertConsent (ON CONFLICT ... DO UPDATE ... EXCLUDED) has no unified SQL syntax across RDBMS. Fix: an application-level transactional check-then-write (SELECT ... FOR UPDATE on (client_id, psu_token), then INSERT or UPDATE) — standard across Postgres and MySQL/InnoDB, so it needs zero per-dialect SQL.

Rule E (isolated exception #2) — unique-constraint error classification. errors.As against *pgconn.PgError only works for Postgres. Add one small shared helper (e.g. internal/dbutil/uniqueviolation.go) exposing IsUniqueViolation(err error, names ...string) bool that type-switches on *pgconn.PgError (SQLSTATE 23505, match ConstraintName) or *mysql.MySQLError (Number == 1062, match by substring against the index name in Message, since MySQL doesn't expose a separate constraint-name field). All three existing call sites (isDuplicateClientID, isDuplicatePublicKeyHash, IsDuplicateUniIdent) switch to calling this helper instead of hand-rolling the type assertion. This is the only other place a future RDBMS needs one new case added.

Net effect: adding a future RDBMS touches only the connection layer (Step 1) and the two isolated helpers (Rules D and E) — no other query text or business logic changes.

Detailed implementation steps

Step 1 — internal/config/db.go: driver-aware connection layer

1. Add DB_DRIVER to the env-var list (envOrDefault("DB_DRIVER", "postgres")), validate it's "postgres" or "mysql".
2. Split resolveDBDSN/loadDB/buildPoolConfig/Open into a postgres path (existing code, unchanged) and a new mysql path:
   • resolveMySQLDSN: build user:password@tcp(host:port)/dbname?parseTime=true&loc=UTC from DATABASE_HOST/PORT/NAME/USERNAME/PASSWORD (reuse envOrDefault/DB_DBUSER_PASSWORD fallback exactly as today), or pass DATABASE_URL straight through if it already looks like a mysql:///DSN string for that driver.
   • openMySQL(dsn string, pool DBPool) (*sql.DB, func() error, error): sql.Open("mysql", dsn), then conn.SetMaxOpenConns(pool.MaxOpenConns), SetMaxIdleConns(pool.MaxIdleConns), SetConnMaxLifetime(effectiveMaxConnLifetime(...)), SetConnMaxIdleTime(...), then PingContext with the same dbPingTimeout. No pgxpool-style eager MinConns — add a doc comment noting MaxIdleConns here is the passive database/sql ceiling, not an eager floor (unlike the Postgres path).
   • Add _ "github.com/go-sql-driver/mysql" blank import to register the driver.
3. Change DB.Open()'s signature to also return the resolved driver name: func (d DB) Open() (conn *sql.DB, driverName string, closeFn func() error, err error). Dispatch to the postgres or mysql path based on DB_DRIVER, defaulting to "postgres" (preserves current behavior when unset).
4. Update internal/config/db_test.go: add TestResolveMySQLDSN_* cases mirroring the existing TestResolveDBDSN_* table-driven style; keep all existing Postgres tests unchanged.

Step 2 — internal/dbutil (new package): shared portability helpers

Create esignet-service/internal/dbutil/ with two files, importable by all three db packages and by clientmgmt/keymanager:

• rebind.go: func Rebind(driverName, query string) string { return sqlx.Rebind(sqlx.BindType(driverName), query) } — thin wrapper so call sites don't import sqlx directly just for this.
• uniqueviolation.go: func IsUniqueViolation(err error, names ...string) bool implementing Rule E — errors.As against *pgconn.PgError (code 23505, ConstraintName in names) first, then *mysql.MySQLError (Number == 1062, Message contains any of names) as a second branch. Keep the existing string-match fallback from rotation.go (strings.Contains(msg, "23505")) as a third, driver-agnostic-error branch for tests that construct plain errors.

Step 3 — internal/clientmgmt/db/: convert to hand-written sqlx

1. Delete query.sql, query.sql.go's sqlc-generated body, and this package's entry in sqlc.yaml. Keep schema.sql as living documentation (or delete it too — no longer feeds codegen).
2. Rewrite db.go: keep DBTX interface exactly as-is (still plain database/sql shaped — ExecContext/PrepareContext/QueryContext/QueryRowContext). Change New(db DBTX) *Queries to New(db DBTX, driverName string) *Queries, pre-building each rebound query string once via dbutil.Rebind (mirrors keymanager/db.New's pattern of building strings once in the constructor).
3. Keep models.go (ClientDetail) and querier.go (Querier interface) byte-for-byte identical — no caller-visible change.
4. Port each query, preserving the exact *Params struct shapes from today's query.sql.go:
   • GetClient, GetActiveClient: trivial — $1 → ?, everything else unchanged.
   • CreateClient: INSERT INTO client_detail (...) VALUES (?, ?, ...) (17 placeholders, no RETURNING); on success, run SELECT * FROM client_detail WHERE id = ? (reuse the same query string as GetClient) to build the returned ClientDetail (Rule B).
   • UpdateClient: UPDATE client_detail SET ... WHERE id = ? (no RETURNING), then the same follow-up SELECT (Rule B).
   • PatchClient: same UPDATE ... WHERE id = ? pattern, but build the trailing AND upd_dtimes = ? / AND upd_dtimes IS NULL clause in Go from arg.ExpectedUpdDtimes.Valid (Rule C) instead of IS NOT DISTINCT FROM $15; check RowsAffected() — 0 rows means either "not found" or "concurrent modification", matching the current sql.ErrNoRows-from-RETURNING semantics the caller (clientmgmt/service.go) already expects for ErrClientConflict; then the same follow-up SELECT (Rule B).
5. In clientmgmt/service.go: replace isDuplicateClientID/isDuplicatePublicKeyHash's hand-rolled pgconn.PgError assertions with calls to dbutil.IsUniqueViolation(err, "pk_clntdtl_id", "client_detail_pkey") and dbutil.IsUniqueViolation(err, "uk_clntdtl_public_key_hash") respectively (Rule E); drop the now-unused pgconn import.
6. NewService(conn *sql.DB, ...) → thread a driverName string parameter through to db.New(conn, driverName).

Step 4 — internal/consentmgmt/db/: convert to hand-written sqlx

1. Delete query.sql/generated query.sql.go body and this package's sqlc.yaml entry; keep or drop schema.sql as documentation.
2. Rewrite db.go the same way as Step 3.2 (New(db DBTX, driverName string) *Queries, pre-rebound query strings).
3. Keep models.go (ConsentDetail, ConsentHistory) and querier.go (Querier) identical.
4. Port each query, preserving *Params struct shapes:
   • GetConsent, InsertConsentHistory, DeleteConsent: trivial $N → ? swap.
   • UpsertConsent (Rule D): replace the single ON CONFLICT ... EXCLUDED statement with:

     func (q *Queries) UpsertConsent(ctx context.Context, arg UpsertConsentParams) error {
         if beginner, ok := q.db.(txBeginner); ok {           // q.db is a plain *sql.DB — no caller-managed tx
             tx, err := beginner.BeginTx(ctx, nil)
             if err != nil { return fmt.Errorf("begin upsert tx: %w", err) }
             defer func() { _ = tx.Rollback() }()
             if err := upsertConsentTx(ctx, tx, q.selectForUpdate, q.insertConsent, q.updateConsent, arg); err != nil {
                 return err
             }
             return tx.Commit()
         }
         // q.db is already a *sql.Tx (caller opened one, e.g. consentmgmt.Service.SaveRecord) — reuse it, no nested tx.
         return upsertConsentTx(ctx, q.db, q.selectForUpdate, q.insertConsent, q.updateConsent, arg)
     }

     where txBeginner is a small local interface (BeginTx(ctx, *sql.TxOptions) (*sql.Tx, error)) that only *sql.DB satisfies (not *sql.Tx), and upsertConsentTx runs SELECT id FROM consent_detail WHERE client_id = ? AND psu_token = ? FOR UPDATE, then either the existing INSERT INTO consent_detail (...) (no row found) or an UPDATE consent_detail SET claims=?, ... WHERE client_id = ? AND psu_token = ? (row found) using arg's fields.
   • Note found during investigation: consentmgmt.Service.SaveRecord already opens a *sql.Tx and does q = db.New(tx) to wrap InsertConsentHistory + UpsertConsent atomically — but only when s.db != nil, and NewService(conn *sql.DB) (the only constructor main.go actually calls) never sets s.db, so today that branch is dead and both statements run as separate, non-transactional writes in production. This pre-existing gap is orthogonal to MySQL support, but the txBeginner design above happens to make UpsertConsent safe either way; flag it to the user as a separate finding rather than silently changing SaveRecord's behavior as a side effect of this work.
5. NewService(conn *sql.DB) → thread driverName string through to db.New(conn, driverName).

Step 5 — internal/keymanager/db/db.go: placeholder portability

1. Change every hardcoded $1/$2/... literal in the fmt.Sprintf(...)-built query strings to ?.
2. Change New(conn TX, schema string) *Queries to New(conn TX, schema string, driverName string) *Queries; after building each %s.key_alias/%s.key_policy_def/%s.key_store-qualified string, pass it through dbutil.Rebind(driverName, ...) before storing it on q.
3. In rotation.go: replace IsDuplicateUniIdent's pgconn.PgError assertion with dbutil.IsUniqueViolation(err, "uni_ident_const") (Rule E), keeping the existing string-match fallback folded into the shared helper (see Step 2). Drop the now-unused pgconn import if nothing else in the file needs it.
4. Document (doc comment on New) that the schema parameter must be a MySQL database name when driverName == "mysql" (MySQL has no schema-within-database concept; CREATE SCHEMA is a synonym for CREATE DATABASE there), and that the connecting user needs privileges on that database.

Step 6 — cmd/esignet/main.go: wire the driver name through

1. pgConn, closeDB, err := appCfg.DB.Open() → conn, driverName, closeDB, err := appCfg.DB.Open().
2. sqlxConn := sqlx.NewDb(pgConn, "pgx") → sqlxConn := sqlx.NewDb(conn, driverName) (only correct when driverName is a name sqlx recognizes for bind-type purposes — confirm "mysql" and "postgres"/"pgx" both resolve correctly via sqlx.BindType, registering a custom bind type via sqlx.BindDriver if needed for "pgx" specifically, mirroring whatever mapping already makes today's "pgx" call work).
3. Update the clientmgmt.NewService(conn, ...), consentmgmt.NewService(conn), and db.New(conn, kmCfg.DBSchema) (keymanager) call sites to also pass driverName.

Step 7 — esignet-service/go.mod

Add github.com/go-sql-driver/mysql as a direct dependency (go get github.com/go-sql-driver/mysql@<version already pinned in go.sum> then go mod tidy); remove the github.com/mosip/esignet/... sqlc toolchain dependency if sqlc.yaml/codegen is dropped entirely (check for a //go:generate sqlc generate directive or Makefile target referencing it first).

Step 8 — Provisioning scripts (Out of Scope)

1. Create db_scripts/mysql/mosip_esignet/ mirroring the Postgres tree file-by-file:
   • db.sql: CREATE DATABASE mosip_esignet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; — no CREATE SCHEMA/ALTER DATABASE ... SET search_path (the database itself is the equivalent unit).
   • role_dbuser.sql: CREATE USER 'dbuser'@'%' IDENTIFIED BY '...'; in place of CREATE ROLE ... WITH INHERIT LOGIN PASSWORD.
   • grants.sql: GRANT ALL PRIVILEGES ON mosip_esignet.* TO 'dbuser'@'%'; — no ALTER DEFAULT PRIVILEGES equivalent; note in a comment that this must be re-run after adding new tables if using least-privilege grants.
   • ddl/*.sql, dml/*.sql: copy the existing files; spot-check every column/table name against MySQL's reserved-word list (none of the current ones — key, status, etc. as table names would collide, but check column names too) and adjust varchar/timestamp lengths only if MySQL's row-size limits are exceeded (unlikely given current sizes).
   • deploy.sh/deploy.properties, drop_db.sql/drop_role.sql: mirror using the mysql CLI instead of psql (mysql -u ... -p... < file.sql, -e for inline commands, no \c/psql-variable substitution — use shell variable substitution or envsubst instead).
2. Create db_upgrade_script/mysql/mosip_esignet/sql/ mirroring each versioned *_upgrade.sql/*_rollback.sql pair (1.0.0 → 2.0.0) and upgrade.sh/upgrade.properties, translated the same way.

Step 9 — docker-compose/docker-compose.yaml

Add a mysql:8 service under a compose profile (e.g. mysql), with MYSQL_DATABASE/MYSQL_USER/MYSQL_PASSWORD/MYSQL_ROOT_PASSWORD env vars and a mysqladmin ping healthcheck (mirroring the existing postgres service's pg_isready healthcheck). Leave the esignet service's env vars as-is except documenting that setting DB_DRIVER=mysql + pointing DATABASE_* at this service switches it over; don't change the default profile (Postgres stays the default local-dev path).

Step 10 — Tests

1. internal/config/db_test.go: add the MySQL DSN/pool-builder unit tests (Step 1.4).
2. internal/dbutil/uniqueviolation_test.go: table-driven tests covering *pgconn.PgError, *mysql.MySQLError, and the plain-string fallback.
3. Existing clientmgmt/consentmgmt/keymanager unit tests that use NewServiceWithQuerier/hand-written fakes need no changes (they target the unchanged Querier interfaces) — just confirm go test ./... still passes after the rewrite.
4. Add a Makefile/CI target that runs the clientmgmt/consentmgmt/keymanager db package tests against both a real Postgres and a real MySQL instance (e.g. via docker-compose --profile mysql up -d + the new deploy script, or testcontainers-go if introducing a test dependency is acceptable) to catch any query that type-checks but behaves differently per dialect.

Critical files

• esignet-service/internal/config/db.go, db_test.go
• esignet-service/internal/dbutil/rebind.go, uniqueviolation.go (new package)
• esignet-service/cmd/esignet/main.go
• esignet-service/internal/clientmgmt/db/{db,models,querier}.go (rewritten), service.go (error-classification + driver-name wiring)
• esignet-service/internal/consentmgmt/db/{db,models,querier}.go (rewritten), service.go (driver-name wiring)
• esignet-service/internal/keymanager/db/db.go, rotation.go
• esignet-service/sqlc.yaml — trim or remove
• esignet-service/go.mod
• db_scripts/mosip_esignet/** → new db_scripts/mysql/mosip_esignet/** sibling tree
• db_upgrade_script/mosip_esignet/** → new sibling tree per version
• docker-compose/docker-compose.yaml

Verification

• go build ./... && go test ./... in esignet-service — should pass unchanged for callers (they depend on Querier interfaces, not implementations).
• Bring up docker-compose --profile mysql up -d mysql; run the new MySQL deploy script against it; start the service with DB_DRIVER=mysql and the MySQL DATABASE_* values; smoke-test: create an OIDC client (exercises Rule B + Rule E via a deliberate duplicate-ID attempt), patch a client (Rule C, optimistic concurrency), save/overwrite a consent record twice (Rule D upsert path), key generation and lookup via keymanager.
• Repeat the same smoke test against the existing Postgres path (DB_DRIVER=postgres, the default) to confirm no regression.
• go vet/gofmt clean; confirm removing/trimming sqlc.yaml doesn't break any remaining go generate/Makefile step.

[zesu22]

@anushasunkada can we check on this package GORM, it can resolve multiple issue stated here.

• We still have to create the db connection according to the db used.
• We still have to map the error code
• We still have to use transaction for the upsert statement
• But it will help to reduce the query building part, that will become universal

Edited:

I have found another package Ent. It has upsert statement support for all rdbms except oracle/ms sql.