14130607387

Committed 28 Mar 2025 01:59PM UTC coverage: 69.03% (-0.006%) from 69.036%

Build # 14130607387

Build Type

push

github

Committed by

web-flow

Commit Message

Merge pull request #9647 from bhandras/sqldb-migration-base-version

sqldb: establish a base DB version even if it's not yet tracked

Run Details

48 of 75 new or added lines in 3 files covered. (64.0%)

77 existing lines in 18 files now uncovered.

133408 of 193262 relevant lines covered (69.03%)

22159.27 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

69.72

/sqldb/postgres.go

package sqldb

import (
        "context"
        "database/sql"
        "fmt"
        "net/url"
        "path"
        "strings"
        "time"

        pgx_migrate "github.com/golang-migrate/migrate/v4/database/pgx/v5"
        _ "github.com/golang-migrate/migrate/v4/source/file" // Read migrations from files. // nolint:ll
        _ "github.com/jackc/pgx/v5"
        "github.com/lightningnetwork/lnd/sqldb/sqlc"
)

var (
        // DefaultPostgresFixtureLifetime is the default maximum time a Postgres
        // test fixture is being kept alive. After that time the docker
        // container will be terminated forcefully, even if the tests aren't
        // fully executed yet. So this time needs to be chosen correctly to be
        // longer than the longest expected individual test run time.
        DefaultPostgresFixtureLifetime = 10 * time.Minute

        // postgresSchemaReplacements is a map of schema strings that need to be
        // replaced for postgres. This is needed because we write the schemas to
        // work with sqlite primarily but in sqlc's own dialect, and postgres
        // has some differences.
        postgresSchemaReplacements = map[string]string{
                "BLOB":                "BYTEA",
                "INTEGER PRIMARY KEY": "BIGSERIAL PRIMARY KEY",
                "TIMESTAMP":           "TIMESTAMP WITHOUT TIME ZONE",
        }

        // Make sure PostgresStore implements the MigrationExecutor interface.
        _ MigrationExecutor = (*PostgresStore)(nil)

        // Make sure PostgresStore implements the DB interface.
        _ DB = (*PostgresStore)(nil)
)

// replacePasswordInDSN takes a DSN string and returns it with the password
// replaced by "***".
func replacePasswordInDSN(dsn string) (string, error) {
        // Parse the DSN as a URL
        u, err := url.Parse(dsn)
        if err != nil {
                return "", err
        }

        // Check if the URL has a user info part
        if u.User != nil {
                username := u.User.Username()

                // Reconstruct user info with "***" as password
                userInfo := username + ":***@"

                // Rebuild the DSN with the modified user info
                sanitizeDSN := strings.Replace(
                        dsn, u.User.String()+"@", userInfo, 1,
                )

                return sanitizeDSN, nil
        }

        // Return the original DSN if no user info is present
        return dsn, nil
}

// getDatabaseNameFromDSN extracts the database name from a DSN string.
func getDatabaseNameFromDSN(dsn string) (string, error) {
        // Parse the DSN as a URL
        u, err := url.Parse(dsn)
        if err != nil {
                return "", err
        }

        // The database name is the last segment of the path. Trim leading slash
        // and return the last segment.
        return path.Base(u.Path), nil
}

// PostgresStore is a database store implementation that uses a Postgres
// backend.
type PostgresStore struct {
        cfg *PostgresConfig

        *BaseDB
}

// NewPostgresStore creates a new store that is backed by a Postgres database
// backend.
func NewPostgresStore(cfg *PostgresConfig) (*PostgresStore, error) {
        sanitizedDSN, err := replacePasswordInDSN(cfg.Dsn)
        if err != nil {
                return nil, err
        }
        log.Infof("Using SQL database '%s'", sanitizedDSN)

        db, err := sql.Open("pgx", cfg.Dsn)
        if err != nil {
                return nil, err
        }

        // Ensure the migration tracker table exists before running migrations.
        // This table tracks migration progress and ensures compatibility with
        // SQLC query generation. If the table is already created by an SQLC
        // migration, this operation becomes a no-op.
        migrationTrackerSQL := `
        CREATE TABLE IF NOT EXISTS migration_tracker (
                version INTEGER UNIQUE NOT NULL,
                migration_time TIMESTAMP NOT NULL
        );`

        _, err = db.Exec(migrationTrackerSQL)
        if err != nil {
                return nil, fmt.Errorf("error creating migration tracker: %w",
                        err)
        }

        maxConns := defaultMaxConns
        if cfg.MaxConnections > 0 {
                maxConns = cfg.MaxConnections
        }

        db.SetMaxOpenConns(maxConns)
        db.SetMaxIdleConns(maxConns)
        db.SetConnMaxLifetime(connIdleLifetime)

        queries := sqlc.New(db)

        return &PostgresStore{
                cfg: cfg,
                BaseDB: &BaseDB{
                        DB:      db,
                        Queries: queries,
                },
        }, nil
}

// GetBaseDB returns the underlying BaseDB instance for the Postgres store.
// It is a trivial helper method to comply with the sqldb.DB interface.
func (s *PostgresStore) GetBaseDB() *BaseDB {
        return s.BaseDB
}

// ApplyAllMigrations applies both the SQLC and custom in-code migrations to the
// Postgres database.
func (s *PostgresStore) ApplyAllMigrations(ctx context.Context,
        migrations []MigrationConfig) error {

        // Execute migrations unless configured to skip them.
        if s.cfg.SkipMigrations {
                return nil
        }

        return ApplyMigrations(ctx, s.BaseDB, s, migrations)
}

func errPostgresMigration(err error) error {
        return fmt.Errorf("error creating postgres migration: %w", err)
}

// ExecuteMigrations runs migrations for the Postgres database, depending on the
// target given, either all migrations or up to a given version.
func (s *PostgresStore) ExecuteMigrations(target MigrationTarget) error {
        dbName, err := getDatabaseNameFromDSN(s.cfg.Dsn)
        if err != nil {
                return err
        }

        driver, err := pgx_migrate.WithInstance(s.DB, &pgx_migrate.Config{})
        if err != nil {
                return errPostgresMigration(err)
        }

        // Populate the database with our set of schemas based on our embedded
        // in-memory file system.
        postgresFS := newReplacerFS(sqlSchemas, postgresSchemaReplacements)
        return applyMigrations(
                postgresFS, driver, "sqlc/migrations", dbName, target,
        )
}

// GetSchemaVersion returns the current schema version of the Postgres database.
func (s *PostgresStore) GetSchemaVersion() (int, bool, error) {
        driver, err := pgx_migrate.WithInstance(s.DB, &pgx_migrate.Config{})
        if err != nil {
                return 0, false, errPostgresMigration(err)

        }

        version, dirty, err := driver.Version()
        if err != nil {
                return 0, false, err
        }

        return version, dirty, nil
}

// SetSchemaVersion sets the schema version of the Postgres database.
//
// NOTE: This alters the internal database schema tracker. USE WITH CAUTION!!!
func (s *PostgresStore) SetSchemaVersion(version int, dirty bool) error {
        driver, err := pgx_migrate.WithInstance(s.DB, &pgx_migrate.Config{})
        if err != nil {
                return errPostgresMigration(err)
        }

        return driver.SetVersion(version, dirty)
}

1	package sqldb
2
3	import (
4	"context"
5	"database/sql"
6	"fmt"
7	"net/url"
8	"path"
9	"strings"
10	"time"
11
12	pgx_migrate "github.com/golang-migrate/migrate/v4/database/pgx/v5"
13	_ "github.com/golang-migrate/migrate/v4/source/file" // Read migrations from files. // nolint:ll
14	_ "github.com/jackc/pgx/v5"
15	"github.com/lightningnetwork/lnd/sqldb/sqlc"
16	)
17
18	var (
19	// DefaultPostgresFixtureLifetime is the default maximum time a Postgres
20	// test fixture is being kept alive. After that time the docker
21	// container will be terminated forcefully, even if the tests aren't
22	// fully executed yet. So this time needs to be chosen correctly to be
23	// longer than the longest expected individual test run time.
24	DefaultPostgresFixtureLifetime = 10 * time.Minute
25
26	// postgresSchemaReplacements is a map of schema strings that need to be
27	// replaced for postgres. This is needed because we write the schemas to
28	// work with sqlite primarily but in sqlc's own dialect, and postgres
29	// has some differences.
30	postgresSchemaReplacements = map[string]string{
31	"BLOB": "BYTEA",
32	"INTEGER PRIMARY KEY": "BIGSERIAL PRIMARY KEY",
33	"TIMESTAMP": "TIMESTAMP WITHOUT TIME ZONE",
34	}
35
36	// Make sure PostgresStore implements the MigrationExecutor interface.
37	_ MigrationExecutor = (*PostgresStore)(nil)
38
39	// Make sure PostgresStore implements the DB interface.
40	_ DB = (*PostgresStore)(nil)
41	)
42
43	// replacePasswordInDSN takes a DSN string and returns it with the password
44	// replaced by "***".
45	func replacePasswordInDSN(dsn string) (string, error) {	280✔
46	// Parse the DSN as a URL	280✔
47	u, err := url.Parse(dsn)	280✔
48	if err != nil {	280✔
49	return "", err	×
50	}	×
51
52	// Check if the URL has a user info part
53	if u.User != nil {	560✔
54	username := u.User.Username()	280✔
55		280✔
56	// Reconstruct user info with "***" as password	280✔
57	userInfo := username + ":***@"	280✔
58		280✔
59	// Rebuild the DSN with the modified user info	280✔
60	sanitizeDSN := strings.Replace(	280✔
61	dsn, u.User.String()+"@", userInfo, 1,	280✔
62	)	280✔
63		280✔
64	return sanitizeDSN, nil	280✔
65	}	280✔
66
67	// Return the original DSN if no user info is present
68	return dsn, nil	×
69	}
70
71	// getDatabaseNameFromDSN extracts the database name from a DSN string.
72	func getDatabaseNameFromDSN(dsn string) (string, error) {	1,834✔
73	// Parse the DSN as a URL	1,834✔
74	u, err := url.Parse(dsn)	1,834✔
75	if err != nil {	1,834✔
76	return "", err	×
77	}	×
78
79	// The database name is the last segment of the path. Trim leading slash
80	// and return the last segment.
81	return path.Base(u.Path), nil	1,834✔
82	}
83
84	// PostgresStore is a database store implementation that uses a Postgres
85	// backend.
86	type PostgresStore struct {
87	cfg *PostgresConfig
88
89	*BaseDB
90	}
91
92	// NewPostgresStore creates a new store that is backed by a Postgres database
93	// backend.
94	func NewPostgresStore(cfg PostgresConfig) (PostgresStore, error) {	280✔
95	sanitizedDSN, err := replacePasswordInDSN(cfg.Dsn)	280✔
96	if err != nil {	280✔
97	return nil, err	×
98	}	×
99	log.Infof("Using SQL database '%s'", sanitizedDSN)	280✔
100		280✔
101	db, err := sql.Open("pgx", cfg.Dsn)	280✔
102	if err != nil {	280✔
103	return nil, err	×
104	}	×
105
106	// Ensure the migration tracker table exists before running migrations.
107	// This table tracks migration progress and ensures compatibility with
108	// SQLC query generation. If the table is already created by an SQLC
109	// migration, this operation becomes a no-op.
110	migrationTrackerSQL := `	280✔
111	CREATE TABLE IF NOT EXISTS migration_tracker (	280✔
112	version INTEGER UNIQUE NOT NULL,	280✔
113	migration_time TIMESTAMP NOT NULL	280✔
114	);`	280✔
115		280✔
116	_, err = db.Exec(migrationTrackerSQL)	280✔
117	if err != nil {	280✔
118	return nil, fmt.Errorf("error creating migration tracker: %w",	×
119	err)	×
120	}	×
121
122	maxConns := defaultMaxConns	280✔
123	if cfg.MaxConnections > 0 {	280✔
124	maxConns = cfg.MaxConnections	×
125	}	×
126
127	db.SetMaxOpenConns(maxConns)	280✔
128	db.SetMaxIdleConns(maxConns)	280✔
129	db.SetConnMaxLifetime(connIdleLifetime)	280✔
130		280✔
131	queries := sqlc.New(db)	280✔
132		280✔
133	return &PostgresStore{	280✔
134	cfg: cfg,	280✔
135	BaseDB: &BaseDB{	280✔
136	DB: db,	280✔
137	Queries: queries,	280✔
138	},	280✔
139	}, nil	280✔
140	}
141
142	// GetBaseDB returns the underlying BaseDB instance for the Postgres store.
143	// It is a trivial helper method to comply with the sqldb.DB interface.
144	func (s PostgresStore) GetBaseDB() BaseDB {	×
145	return s.BaseDB	×
146	}	×
147
148	// ApplyAllMigrations applies both the SQLC and custom in-code migrations to the
149	// Postgres database.
150	func (s *PostgresStore) ApplyAllMigrations(ctx context.Context,
151	migrations []MigrationConfig) error {	273✔
152		273✔
153	// Execute migrations unless configured to skip them.	273✔
154	if s.cfg.SkipMigrations {	273✔
155	return nil	×
156	}	×
157
158	return ApplyMigrations(ctx, s.BaseDB, s, migrations)	273✔
159	}
160
NEW 161	func errPostgresMigration(err error) error {	×
NEW 162	return fmt.Errorf("error creating postgres migration: %w", err)	×
NEW 163	}	×
164
165	// ExecuteMigrations runs migrations for the Postgres database, depending on the
166	// target given, either all migrations or up to a given version.
167	func (s *PostgresStore) ExecuteMigrations(target MigrationTarget) error {	1,834✔
168	dbName, err := getDatabaseNameFromDSN(s.cfg.Dsn)	1,834✔
169	if err != nil {	1,834✔
170	return err	×
171	}	×
172
173	driver, err := pgx_migrate.WithInstance(s.DB, &pgx_migrate.Config{})	1,834✔
174	if err != nil {	1,834✔
NEW 175	return errPostgresMigration(err)	×
176	}	×
177
178	// Populate the database with our set of schemas based on our embedded
179	// in-memory file system.
180	postgresFS := newReplacerFS(sqlSchemas, postgresSchemaReplacements)	1,834✔
181	return applyMigrations(	1,834✔
182	postgresFS, driver, "sqlc/migrations", dbName, target,	1,834✔
183	)	1,834✔
184	}
185
186	// GetSchemaVersion returns the current schema version of the Postgres database.
187	func (s *PostgresStore) GetSchemaVersion() (int, bool, error) {	272✔
188	driver, err := pgx_migrate.WithInstance(s.DB, &pgx_migrate.Config{})	272✔
189	if err != nil {	272✔
NEW 190	return 0, false, errPostgresMigration(err)	×
NEW 191		×
NEW 192	}	×
193
194	version, dirty, err := driver.Version()	272✔
195	if err != nil {	272✔
NEW 196	return 0, false, err	×
NEW 197	}	×
198
199	return version, dirty, nil	272✔
200	}
201
202	// SetSchemaVersion sets the schema version of the Postgres database.
203	//
204	// NOTE: This alters the internal database schema tracker. USE WITH CAUTION!!!
205	func (s *PostgresStore) SetSchemaVersion(version int, dirty bool) error {	5✔
206	driver, err := pgx_migrate.WithInstance(s.DB, &pgx_migrate.Config{})	5✔
207	if err != nil {	5✔
NEW 208	return errPostgresMigration(err)	×
NEW 209	}	×
210
211	return driver.SetVersion(version, dirty)	5✔
212	}

lightningnetwork / lnd / 14130607387

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous