[sql-63] session: add database benchmarking for session package by ViktorT-11 · Pull Request #1231 · lightninglabs/lightning-terminal

ViktorT-11 · 2026-02-16T20:34:18Z

Based on #1227

Implements part of step 7. of "Phase 3" in #917.

This PR implements benchmarking tests for different database backends in the session package.

gemini-code-assist · 2026-02-16T20:36:36Z

Summary of Changes

Hello @ViktorT-11, I'm Gemini Code Assist¹! I'm currently reviewing this pull request and will post my feedback shortly. In the meantime, here's a summary to help you and other reviewers quickly get up to speed!

This pull request significantly enhances the project's database management and performance evaluation capabilities. It introduces a robust benchmarking suite for core database operations in the accounts and session packages, enabling detailed performance analysis across different storage backends. Concurrently, it updates the Go toolchain to the latest stable version and refactors the database migration system to improve modularity and support future database evolutions, including a new code migration path for development environments.

Highlights

Database Benchmarking: Introduced comprehensive benchmarking tests for the accounts and session packages, allowing performance comparison across KVDB (bbolt), native SQLite, and native PostgreSQL backends.
Go Version Update: Updated the Go version from 1.24.9 to 1.24.11 across the project's Dockerfile, Makefile, and go.mod files.
New Makefile Target: Added a new unit-bench Makefile target to easily run database benchmark tests.
Database Migration Refactoring: Refactored the database migration infrastructure by moving common SQL DB utilities to lnd/sqldb/v2 and introducing a dedicated sqlcmig6 package for migration-specific queries. This includes a new db/migrationstreams package to manage migration logic, with a code migration from KVDB to SQL for dev builds.
Code Migration for Dev Builds: Implemented a programmatic migration for dev builds to transition existing KVDB stores (accounts, sessions, firewall) to SQL databases, ensuring data integrity and validating the migration process.

🧠 New Feature in Public Preview: You can now enable Memory to help Gemini Code Assist learn from your team's feedback. This makes future code reviews more consistent and personalized to your project's style. Click here to enable Memory in your admin console.

Changelog

.golangci.yml
- Added lll linter exclusion for the new db/sqlcmig6 package.
Dockerfile
- Updated the Go base image version from 1.24.9 to 1.24.11.
Makefile
- Updated the GO_VERSION variable from 1.24.9 to 1.24.11.
- Added a new unit-bench target for running benchmark tests.
- Included unit-bench in the .PHONY list.
accounts/benchmark_test.go
- Added new file accounts/benchmark_test.go to benchmark account store operations across KVDB, SQLite, and PostgreSQL.
accounts/service.go
- Updated comments to consistently use 'DB' instead of 'db'.
accounts/service_test.go
- Updated comments to consistently use 'DB' instead of 'db'.
accounts/sql_migration.go
- Updated imports to use github.com/lightninglabs/lightning-terminal/db/sqlcmig6 and github.com/lightningnetwork/lnd/sqldb/v2.
- Modified MigrateAccountStoreToSQL and migrateAccountsToSQL function signatures to accept *sqlcmig6.Queries instead of SQLQueries.
- Renamed getAndMarshalAccount to getAndMarshalMig6Account and introduced marshalDBMig6Account for migration-specific account unmarshaling.
- Updated InsertAccountParams, AddAccountInvoiceParams, UpsertAccountPaymentParams, and SetAccountIndexParams to use sqlcmig6 types.
accounts/sql_migration_test.go
- Updated imports to use github.com/lightninglabs/lightning-terminal/db/sqlcmig6 and github.com/lightningnetwork/lnd/sqldb/v2.
- Modified makeSQLDB to return *sqlcmig6.TxExecutor[*sqlcmig6.Queries].
- Updated NewTestDB and NewTestDBFromPath calls to use sqldb.NewTestSqliteDB with db.MakeTestMigrationStreams().
- Changed kvStore.db.Close() to kvStore.DB.Close().
- Updated txEx.ExecTx calls to use sqldb.NoOpReset.
accounts/store_kvdb.go
- Changed DBFilename constant from accounts.db to accounts.DB.
- Renamed the db field to DB in the BoltStore struct.
- Updated all references to s.db within BoltStore methods to s.DB.
accounts/store_sql.go
- Updated imports to use github.com/lightningnetwork/lnd/sqldb/v2.
- Modified SQLQueries interface to embed sqldb.BaseQuerier.
- Removed UpdateAccountAliasForTests from the SQLQueries interface.
- Changed db.BatchedTx[SQLQueries] to sqldb.BatchedTx[SQLQueries] in BatchedSQLQueries interface.
- Updated BaseDB field in SQLStore from *db.BaseDB to *sqldb.BaseDB.
- Introduced SQLQueriesExecutor struct and NewSQLQueriesExecutor function.
- Updated NewSQLStore to accept *sqlc.Queries and use NewSQLQueriesExecutor.
- Added sqldb.NoOpReset to all ExecTx calls.
accounts/test_kvdb.go
- Changed store.db.Close() to store.DB.Close() in NewTestDBFromPath cleanup.
accounts/test_sql.go
- Updated imports to use github.com/lightninglabs/lightning-terminal/db/sqlc and github.com/lightningnetwork/lnd/sqldb/v2.
- Modified createStore to accept *sqldb.BaseDB and *sqlc.Queries.
accounts/test_sqlite.go
- Updated imports to use github.com/lightningnetwork/lnd/sqldb/v2.
- Modified NewTestDB and NewTestDBFromPath to use sqldb.NewTestSqliteDB and sqldb.NewTestSqliteDBFromPath respectively, incorporating db.MakeTestMigrationStreams().
autopilotserverrpc/go.mod
- Updated Go version from 1.24.9 to 1.24.11.
config_dev.go
- Added imports for context, github.com/lightninglabs/lightning-terminal/db/migrationstreams, github.com/lightninglabs/lightning-terminal/db/sqlc, github.com/lightningnetwork/lnd/lnrpc, and github.com/lightningnetwork/lnd/sqldb/v2.
- Modified NewStores function signature to accept context.Context and lnrpc.LightningClient.
- Refactored SQLite and Postgres store initialization to use sqldb.NewSqliteStore, sqldb.NewPostgresStore, sqldb.ApplyAllMigrations, sqlc.NewForType, and migrationstreams.MakeMigrationStreams.
config_prod.go
- Added imports for context and github.com/lightningnetwork/lnd/lnrpc.
- Modified NewStores function signature to accept context.Context and lnrpc.LightningClient (unused in production).
db/interfaces.go
- Removed the entire db/interfaces.go file, as its contents were moved to lnd/sqldb/v2.
db/migrations.go
- Removed MigrationTarget, MigrateOpt, migrationLogger, applyMigrations, replacerFS, replacerFile structs and functions.
- Updated LatestMigrationVersion to 5 and added LatestDevMigrationVersion to 1.
- Introduced MakeTestMigrationStreams function to provide migration streams for testing environments.
db/migrationstreams/log.go
- Added new file db/migrationstreams/log.go for logging within the migrationstreams package.
db/migrationstreams/post_migration_callbacks_dev.go
- Added new file db/migrationstreams/post_migration_callbacks_dev.go to define dev-specific post-migration callbacks, including the KVDB to SQL migration for all stores.
db/migrationstreams/sql_migrations.go
- Added new file db/migrationstreams/sql_migrations.go to define production migration streams.
db/migrationstreams/sql_migrations_dev.go
- Added new file db/migrationstreams/sql_migrations_dev.go to define dev migration streams, including KVDBtoSQLMigVersion and MakePostStepCallbacksMig6.
db/postgres.go
- Removed PostgresStore struct and its associated methods (NewPostgresStore, ExecuteMigrations).
- Removed postgresSchemaReplacements map.
- Updated NewTestPostgresDB to use sqldb.NewTestPgFixture and sqldb.NewTestPostgresDB with MakeTestMigrationStreams().
db/schemas.go
- Modified SqlSchemas embed path to include sqlc/migration*/*.*.sql to cover both migrations and migrations_dev.
db/sqlc/accounts.sql.go
- Removed the updateAccountAliasForTests constant and its associated UpdateAccountAliasForTestsParams struct and UpdateAccountAliasForTests method.
db/sqlc/db_custom.go
- Updated BackendType to sqldb.BackendType.
- Removed NewSqlite and NewPostgres functions.
- Added NewForType function to create Queries instances based on sqldb.BackendType.
- Updated CustomQueries interface to use sqldb.BackendType for Backend() method.
db/sqlc/migrations_dev/000001_code_migration_kvdb_to_sql.down.sql
- Added new file db/sqlc/migrations_dev/000001_code_migration_kvdb_to_sql.down.sql.
db/sqlc/migrations_dev/000001_code_migration_kvdb_to_sql.up.sql
- Added new file db/sqlc/migrations_dev/000001_code_migration_kvdb_to_sql.up.sql.
db/sqlc/querier.go
- Removed UpdateAccountAliasForTests from the Querier interface.
db/sqlc/queries/accounts.sql
- Removed the UpdateAccountAliasForTests SQL query definition.
db/sqlcmig6/accounts.sql.go
- Added new file db/sqlcmig6/accounts.sql.go containing SQLC-generated queries for accounts, including UpdateAccountAliasForTests.
db/sqlcmig6/actions.sql.go
- Added new file db/sqlcmig6/actions.sql.go containing SQLC-generated queries for actions.
db/sqlcmig6/actions_custom.go
- Added new file db/sqlcmig6/actions_custom.go containing custom SQL queries for actions, including ListActions and CountActions.
db/sqlcmig6/db.go
- Added new file db/sqlcmig6/db.go defining DBTX, Queries, New, WithTx, TxExecutor, and NewTxExecutor for migration-specific SQL operations.
db/sqlcmig6/db_custom.go
- Added new file db/sqlcmig6/db_custom.go defining custom database utilities for sqlcmig6, including wrappedTX, Backend, NewForType, and CustomQueries.
db/sqlcmig6/kvstores.sql.go
- Added new file db/sqlcmig6/kvstores.sql.go containing SQLC-generated queries for KV stores.
db/sqlcmig6/models.go
- Added new file db/sqlcmig6/models.go defining all SQLC models for the sqlcmig6 package.
db/sqlcmig6/privacy_paris.sql.go
- Added new file db/sqlcmig6/privacy_paris.sql.go containing SQLC-generated queries for privacy pairs.
db/sqlcmig6/querier.go
- Added new file db/sqlcmig6/querier.go defining the Querier interface for the sqlcmig6 package.
db/sqlcmig6/sessions.sql.go
- Added new file db/sqlcmig6/sessions.sql.go containing SQLC-generated queries for sessions.
db/sqlite.go
- Removed SqliteStore struct and its associated methods (NewSqliteStore, backupSqliteDatabase, backupAndMigrate, ExecuteMigrations, NewTestSqliteDB, NewTestSqliteDbHandleFromPath, NewTestSqliteDBWithVersion).
- Removed constants and variables related to SQLite configuration and schema replacements.
dev.Dockerfile
- Updated the Go base image version from 1.24.9 to 1.24.11.
docs/benchmark_db_backends.md
- Added new documentation file docs/benchmark_db_backends.md explaining how to run and compare database benchmarks for LiT.
firewalldb/actions_sql.go
- Updated imports to use github.com/lightningnetwork/lnd/sqldb/v2.
- Added sqldb.NoOpReset to all ExecTx calls.
firewalldb/kvstores_sql.go
- Updated imports to use github.com/lightningnetwork/lnd/sqldb/v2.
- Added ListAllKVStoresRecords to the SQLKVStoreQueries interface.
- Added sqldb.NoOpReset to the ExecTx call in DeleteTempKVStores.
firewalldb/sql_migration.go
- Updated imports to use github.com/lightninglabs/lightning-terminal/db/sqlcmig6 and github.com/lightningnetwork/lnd/sqldb/v2.
- Modified MigrateFirewallDBToSQL and related migration functions to accept *sqlcmig6.Queries instead of SQLQueries, and removed session.SQLQueries and accounts.SQLQueries parameters.
- Updated calls to various SQL query methods to use the sqlcmig6.Queries interface and its specific parameter types.
firewalldb/sql_migration_test.go
- Updated imports to use github.com/lightninglabs/lightning-terminal/db/sqlcmig6 and github.com/lightningnetwork/lnd/sqldb/v2.
- Modified makeSQLDB to return *sqlcmig6.TxExecutor[*sqlcmig6.Queries].
- Updated assertKvStoreMigrationResults and assertPrivacyMapperMigrationResults to accept *sqlcmig6.Queries.
- Updated assertActionsMigrationResults to use sqlcmig6.ListActionsParams and marshalDBAction.
- Removed explicit acctSQLStore and sessSQLStore variables in TestFirewallDBMigration setup.
- Updated txEx.ExecTx calls to use sqldb.NoOpReset and perform assertions within the transaction body.
- Added sqlcmig6.NewForType calls to create queries objects in helper functions.
firewalldb/sql_store.go
- Updated imports to use github.com/lightninglabs/lightning-terminal/db/sqlc and github.com/lightningnetwork/lnd/sqldb/v2.
- Modified SQLQueries interface to embed sqldb.BaseQuerier.
- Changed db.BatchedTx[SQLQueries] to sqldb.BatchedTx[SQLQueries] in BatchedSQLQueries interface.
- Updated BaseDB field in SQLDB from *db.BaseDB to *sqldb.BaseDB.
- Introduced SQLQueriesExecutor struct and NewSQLQueriesExecutor function.
- Updated NewSQLDB to accept *sqlc.Queries and use NewSQLQueriesExecutor.
- Added sqldb.NoOpReset to all ExecTx calls.
firewalldb/test_sql.go
- Updated imports to use github.com/lightninglabs/lightning-terminal/db/sqlc and github.com/lightningnetwork/lnd/sqldb/v2.
- Modified createStore to accept *sqldb.BaseDB and *sqlc.Queries.
firewalldb/test_sqlite.go
- Updated imports to use github.com/lightningnetwork/lnd/sqldb/v2.
- Modified NewTestDB and NewTestDBFromPath to use sqldb.NewTestSqliteDB and sqldb.NewTestSqliteDBFromPath respectively, incorporating db.MakeTestMigrationStreams().
go.mod
- Updated Go version from 1.24.9 to 1.24.11.
- Updated github.com/golang-migrate/migrate/v4 to v4.19.0.
- Updated github.com/lightninglabs/lightning-node-connect/gbn and mailbox to newer versions.
- Updated github.com/lightninglabs/taproot-assets and taprpc to newer versions.
- Updated github.com/lightningnetwork/lnd to a newer beta release candidate.
- Added github.com/lightningnetwork/lnd/sqldb/v2 dependency.
- Updated golang.org/x/exp and modernc.org/sqlite versions.
- Added replace directive for github.com/lightningnetwork/lnd/sqldb/v2.
go.sum
- Updated various dependency hashes to reflect changes in go.mod.
itest/litd_node.go
- Added taproot-assets.allow-public-uni-proof-courier flag to default litd arguments.
litrpc/Dockerfile
- Updated the Go base image version from 1.24.9 to 1.24.11.
litrpc/go.mod
- Updated Go version from 1.24.9 to 1.24.11.
log.go
- Added imports for github.com/lightninglabs/lightning-terminal/db/migrationstreams and github.com/lightningnetwork/lnd/sqldb/v2.
- Added sqldb and migrationstreams subsystems to SetupLoggers.
make/builder.Dockerfile
- Updated the Go base image version from 1.24.9 to 1.24.11.
make/testing_flags.mk
- Added UNIT_BENCH variable to define commands for running benchmark tests, including -test.bench=. -test.benchmem -test.run=NONE flags.
perms/go.mod
- Updated Go version from 1.24.9 to 1.24.11.
scripts/gen_sqlc_docker.sh
- Modified restore_files and the patching loop to include db/sqlc/migrations_dev directory.
session/benchmark_test.go
- Added new file session/benchmark_test.go to benchmark session store operations across KVDB, SQLite, and PostgreSQL.
session/sql_migration.go
- Added imports for github.com/btcsuite/btcd/btcec/v2, github.com/lightninglabs/lightning-node-connect/mailbox, gopkg.in/macaroon-bakery.v2/bakery, and gopkg.in/macaroon.v2.
- Updated MigrateSessionStoreToSQL and migrateSessionsToSQLAndValidate function signatures to accept *sqlcmig6.Queries.
- Introduced getAndUnmarshalSession, unmarshalMig6Session, unmarshalMig6MacPerms, unmarshalMig6MacCaveats, unmarshalMig6FeatureConfigs, and unmarshalMig6PrivacyFlags for migration-specific unmarshaling.
- Updated InsertSession, SetSessionRevokedAt, SetSessionGroupID, InsertSessionMacaroonPermission, InsertSessionMacaroonCaveat, InsertSessionFeatureConfig, and InsertSessionPrivacyFlag calls to use sqlcmig6 types.
session/sql_migration_test.go
- Updated imports to use github.com/lightninglabs/lightning-terminal/db/sqlcmig6 and github.com/lightningnetwork/lnd/sqldb/v2.
- Modified makeSQLDB to return *sqlcmig6.TxExecutor[*sqlcmig6.Queries].
- Updated txEx.ExecTx calls to use sqldb.NoOpReset.
session/sql_store.go
- Updated imports to use github.com/lightningnetwork/lnd/sqldb/v2.
- Modified SQLQueries interface to embed sqldb.BaseQuerier.
- Changed db.BatchedTx[SQLQueries] to sqldb.BatchedTx[SQLQueries] in BatchedSQLQueries interface.
- Updated BaseDB field in SQLStore from *db.BaseDB to *sqldb.BaseDB.
- Introduced SQLQueriesExecutor struct and NewSQLQueriesExecutor function.
- Updated NewSQLStore to accept *sqlc.Queries and use NewSQLQueriesExecutor.
- Added sqldb.NoOpReset to all ExecTx calls.
session/test_sql.go
- Updated imports to use github.com/lightninglabs/lightning-terminal/db/sqlc and github.com/lightningnetwork/lnd/sqldb/v2.
- Modified createStore to accept *sqldb.BaseDB and *sqlc.Queries.
session/test_sqlite.go
- Updated imports to use github.com/lightningnetwork/lnd/sqldb/v2.
- Modified NewTestDB and NewTestDBFromPath to use sqldb.NewTestSqliteDB and sqldb.NewTestSqliteDBFromPath respectively, incorporating db.MakeTestMigrationStreams().
terminal.go
- Modified NewStores call to pass context and g.basicClient.
tools/Dockerfile
- Updated the Go base image version from 1.24.9 to 1.24.11.
tools/go.mod
- Updated Go version from 1.24.9 to 1.24.11.
tools/linters/go.mod
- Updated Go version from 1.24.9 to 1.24.11.

Ignored Files

Ignored by pattern: .github/workflows/** (1)
- .github/workflows/main.yml

Using Gemini Code Assist

The full guide for Gemini Code Assist can be found on our documentation page, here are some quick tips.

Invoking Gemini

You can request assistance from Gemini at any point by creating a comment using either /gemini <command> or @gemini-code-assist <command>. Below is a summary of the supported commands on the current page.

Feature	Command	Description
Code Review	`/gemini review`	Performs a code review for the current pull request in its current state.
Pull Request Summary	`/gemini summary`	Provides a summary of the current pull request in its current state.
Comment	@gemini-code-assist	Responds in comments when explicitly tagged, both in pull request comments and review comments.
Help	`/gemini help`	Displays a list of available commands.

Customization

To customize Gemini Code Assist for GitHub experience, repository maintainers can create a configuration file and/or provide a custom code review style guide (such as PEP-8 for Python) by creating and adding files to a .gemini/ folder in the base of the repository. Detailed instructions can be found here.

Limitations & Feedback

Gemini Code Assist may make mistakes. Please leave feedback on any instances where its feedback is incorrect or counter productive. You can react with 👍 and 👎 on @gemini-code-assist comments. If you're interested in giving your feedback about your experience with Gemini Code Assist for Github and other Google products, sign up here.

You can also get AI-powered code generation, chat, as well as code reviews directly in the IDE at no cost with the Gemini Code Assist IDE Extension.

Review the Privacy Notices, Generative AI Prohibited Use Policy, Terms of Service, and learn how to configure Gemini Code Assist in GitHub here. Gemini can make mistakes, so double check it and use code with caution. ↩

gemini-code-assist

Code Review

This pull request introduces benchmarking tests for the accounts and session packages to compare different database backends. The changes include a new unit-bench Makefile target, the benchmark test files, and documentation. The implementation is solid and follows standard Go practices. I have one minor suggestion for the Makefile to improve maintainability.

gemini-code-assist · 2026-02-16T20:37:40Z

Makefile

+#? unit-bench: Run benchmark tests
+unit-bench:
+	@$(call print, "Running benchmark tests.")
+	mkdir -p app/build && touch app/build/index.html


This line is duplicated across several test targets (unit, unit-debug, unit-race, and now unit-bench). While this works, it introduces redundancy. For better maintainability, consider extracting this command into a common prerequisite target in a future refactoring.

ViktorT-11 added 5 commits February 11, 2026 23:46

accounts: add database backend benchmark test

e9742d3

make: add make unit-bench command

34c4e92

docs: add doc for benchmarking database backends

735d6d5

session: add database backend benchmark test

316472d

docs: update benchmarking docs with session pkg

f5e50e2

ViktorT-11 changed the base branch from master to trigger-kvdb-to-sql-mig February 16, 2026 20:34

gemini-code-assist bot reviewed Feb 16, 2026

View reviewed changes

ViktorT-11 mentioned this pull request Feb 18, 2026

[sql-64] firewalldb: add database benchmarking for firewalldb package #1232

Closed

ViktorT-11 deleted the branch lightninglabs:trigger-kvdb-to-sql-mig February 19, 2026 11:11

ViktorT-11 closed this Feb 19, 2026

ViktorT-11 mentioned this pull request Feb 19, 2026

[sql-63] session: add database benchmarking for session package #1233

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Comments

[sql-63] session: add database benchmarking for session package#1231

[sql-63] session: add database benchmarking for session package#1231
ViktorT-11 wants to merge 5 commits intolightninglabs:trigger-kvdb-to-sql-migfrom
ViktorT-11:2026-02-add-session-benchmarking

ViktorT-11 commented Feb 16, 2026

Uh oh!

gemini-code-assist bot commented Feb 16, 2026

Uh oh!

gemini-code-assist bot left a comment

Uh oh!

gemini-code-assist bot Feb 16, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant

Comments

Conversation

ViktorT-11 commented Feb 16, 2026

Uh oh!

gemini-code-assist bot commented Feb 16, 2026

Summary of Changes

Highlights

Footnotes

Uh oh!

gemini-code-assist bot left a comment

Choose a reason for hiding this comment

Code Review

Uh oh!

gemini-code-assist bot Feb 16, 2026

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

1 participant