[PR #6109] [management] migration: convert SQLite-imported bool columns to native Postgres boolean (fixes #4326) #27557

Open
opened 2026-08-05 07:08:53 -04:00 by saavagebueno · 0 comments
Owner

📋 Pull Request Information

Original PR: https://github.com/netbirdio/netbird/pull/6109
Author: @MichaelUray
Created: 5/8/2026
Status: 🔄 Open

Base: mainHead: fix/postgres-bool-column-migration


📝 Commits (1)

  • d28e75f [management] migration: convert SQLite-imported bool columns to native Postgres boolean

📊 Changes

3 files changed (+325 additions, -0 deletions)

View changed files

📝 management/server/migration/migration.go (+110 -0)
📝 management/server/migration/migration_test.go (+206 -0)
📝 management/server/store/store.go (+9 -0)

📄 Description

Summary

Closes #4326.

When a self-hosted NetBird management server is migrated from SQLite to Postgres with external tooling (e.g. pgloader), SQLite's INTEGER 0/1 representation of bool fields lands in Postgres as numeric rather than boolean. The Go struct fields are typed bool, so the very first attempt to update any account setting from the dashboard fails with:

failed to encode args[N]: unable to encode true into binary format for numeric (OID 1700): cannot find encode plan

Multiple operators have hit this on different bool settings fields (reporter's original report, confirmation that "this behavior occurs on basically every setting related to the account settings"). Until now the only fix was running manual ALTER TABLE statements per affected column.

Change

New migration helper FixPostgresBoolColumns[T] that walks every gorm-mapped bool field on a model, inspects information_schema.columns for the actual SQL type, and runs an idempotent ALTER COLUMN ... TYPE boolean USING ... when the column is anything other than boolean. Conversion semantics:

  • NULLfalse (matches Go bool zero value)
  • 0false
  • non-zero numeric → true
  • NOT NULL + DEFAULT false are re-applied so the post-migration schema matches what gorm.AutoMigrate would produce on a fresh install

The migration is registered for types.Account in getMigrationsPreAuto. Pre-AutoMigrate placement is required so AutoMigrate's own type-mismatch check does not trip on the still-numeric columns. It is a strict no-op on non-Postgres engines (SQLite stores bools natively as INTEGER, MySQL as TINYINT(1) — neither needs conversion).

Tests

All tests run against a real postgres:16-alpine test container via the existing testcontainers-go scaffolding:

Test Coverage
TestFixPostgresBoolColumns_NoOpOnSqlite SQLite path is unchanged
TestFixPostgresBoolColumns_TableMissingIsNoOp absent table → return nil, no error
TestFixPostgresBoolColumns_AlreadyBooleanIsNoOp already-boolean columns are skipped (fresh-install idempotency)
TestFixPostgresBoolColumns_ConvertsNumericColumnsAndPreservesData core regression test for #4326: synthesises the broken-schema state (numeric columns where bool is expected), seeds NULL/0/1 rows, runs the migration, asserts data_type is now boolean, NULL→false, 0→false, 1→true, and the user-reported failure mode (writing Go bool=true via gorm) succeeds afterwards
TestFixPostgresBoolColumns_IsIdempotent running the migration twice in a row is safe
$ NETBIRD_STORE_ENGINE=postgres go test ./management/server/migration/ -run TestFixPostgresBoolColumns -v -count=1
=== RUN   TestFixPostgresBoolColumns_NoOpOnSqlite
--- PASS: TestFixPostgresBoolColumns_NoOpOnSqlite (0.00s)
=== RUN   TestFixPostgresBoolColumns_TableMissingIsNoOp
--- PASS: TestFixPostgresBoolColumns_TableMissingIsNoOp (1.66s)
=== RUN   TestFixPostgresBoolColumns_AlreadyBooleanIsNoOp
--- PASS: TestFixPostgresBoolColumns_AlreadyBooleanIsNoOp (1.66s)
=== RUN   TestFixPostgresBoolColumns_ConvertsNumericColumnsAndPreservesData
time="2026-05-08T09:47:27Z" level=info msg="converting Postgres column bool_col_test_models.top_level_bool from numeric to boolean (issue #4326)"
time="2026-05-08T09:47:27Z" level=info msg="converting Postgres column bool_col_test_models.settings_lazy_connection_enabled from numeric to boolean (issue #4326)"
time="2026-05-08T09:47:27Z" level=info msg="converting Postgres column bool_col_test_models.settings_jwt_groups_enabled from numeric to boolean (issue #4326)"
--- PASS: TestFixPostgresBoolColumns_ConvertsNumericColumnsAndPreservesData (1.68s)
=== RUN   TestFixPostgresBoolColumns_IsIdempotent
--- PASS: TestFixPostgresBoolColumns_IsIdempotent (1.62s)
PASS
ok      github.com/netbirdio/netbird/management/server/migration        7.065s

Test plan

  • Unit + integration tests cover SQLite no-op, missing-table no-op, already-boolean no-op, the actual conversion + data preservation, and idempotency
  • Existing migration test suite still green (no regression)
  • go build ./management/... clean

Affected columns (concrete)

For types.Account the migration covers the gorm-embedded Settings + ExtraSettings bool fields. At time of writing those are:

settings_peer_login_expiration_enabled
settings_peer_inactivity_expiration_enabled
settings_regular_users_view_blocked
settings_groups_propagation_enabled
settings_jwt_groups_enabled
settings_routing_peer_dns_resolution_enabled
settings_peer_expose_enabled
settings_lazy_connection_enabled
settings_auto_update_always
settings_extra_peer_approval_enabled
settings_extra_user_approval_required

The list is derived automatically from gorm metadata, so any future bool field added to Settings/ExtraSettings is covered without code changes.

Use case

Real-world: any operator who migrated their self-hosted NetBird from SQLite to Postgres using a tool that maps SQLite's INTEGER to Postgres numeric rather than boolean. That includes pgloader with default settings (the most common path), but also other custom dump/restore scripts. With this PR the management server self-heals on first start instead of returning HTTP 500 on every settings update.

  • I added/updated documentation
  • Documentation is not needed

Summary by CodeRabbit

Release Notes

  • Bug Fixes
    • Fixed PostgreSQL database schema issues where boolean columns were not using the proper native type. The correction automatically applies during database migration and is safe to run multiple times.

🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.

## 📋 Pull Request Information **Original PR:** https://github.com/netbirdio/netbird/pull/6109 **Author:** [@MichaelUray](https://github.com/MichaelUray) **Created:** 5/8/2026 **Status:** 🔄 Open **Base:** `main` ← **Head:** `fix/postgres-bool-column-migration` --- ### 📝 Commits (1) - [`d28e75f`](https://github.com/netbirdio/netbird/commit/d28e75f9dd4545db09e72f90b86174b24b200c81) [management] migration: convert SQLite-imported bool columns to native Postgres boolean ### 📊 Changes **3 files changed** (+325 additions, -0 deletions) <details> <summary>View changed files</summary> 📝 `management/server/migration/migration.go` (+110 -0) 📝 `management/server/migration/migration_test.go` (+206 -0) 📝 `management/server/store/store.go` (+9 -0) </details> ### 📄 Description ## Summary Closes #4326. When a self-hosted NetBird management server is migrated from SQLite to Postgres with external tooling (e.g. `pgloader`), SQLite's INTEGER 0/1 representation of `bool` fields lands in Postgres as `numeric` rather than `boolean`. The Go struct fields are typed `bool`, so the very first attempt to update any account setting from the dashboard fails with: ``` failed to encode args[N]: unable to encode true into binary format for numeric (OID 1700): cannot find encode plan ``` Multiple operators have hit this on different bool settings fields ([reporter's original report](https://github.com/netbirdio/netbird/issues/4326), [confirmation](https://github.com/netbirdio/netbird/issues/4326#issuecomment-3611776843) that *"this behavior occurs on basically every setting related to the account settings"*). Until now the only fix was running manual `ALTER TABLE` statements per affected column. ## Change New migration helper [`FixPostgresBoolColumns[T]`](management/server/migration/migration.go) that walks every gorm-mapped bool field on a model, inspects `information_schema.columns` for the actual SQL type, and runs an idempotent `ALTER COLUMN ... TYPE boolean USING ...` when the column is anything other than `boolean`. Conversion semantics: - `NULL` → `false` (matches Go bool zero value) - `0` → `false` - non-zero numeric → `true` - `NOT NULL` + `DEFAULT false` are re-applied so the post-migration schema matches what `gorm.AutoMigrate` would produce on a fresh install The migration is registered for `types.Account` in `getMigrationsPreAuto`. Pre-AutoMigrate placement is required so `AutoMigrate`'s own type-mismatch check does not trip on the still-numeric columns. It is a strict no-op on non-Postgres engines (SQLite stores bools natively as INTEGER, MySQL as TINYINT(1) — neither needs conversion). ## Tests All tests run against a real `postgres:16-alpine` test container via the existing `testcontainers-go` scaffolding: | Test | Coverage | |---|---| | `TestFixPostgresBoolColumns_NoOpOnSqlite` | SQLite path is unchanged | | `TestFixPostgresBoolColumns_TableMissingIsNoOp` | absent table → return nil, no error | | `TestFixPostgresBoolColumns_AlreadyBooleanIsNoOp` | already-boolean columns are skipped (fresh-install idempotency) | | `TestFixPostgresBoolColumns_ConvertsNumericColumnsAndPreservesData` | **core regression test for #4326**: synthesises the broken-schema state (numeric columns where bool is expected), seeds NULL/0/1 rows, runs the migration, asserts data_type is now `boolean`, NULL→false, 0→false, 1→true, and the user-reported failure mode (writing Go `bool=true` via gorm) succeeds afterwards | | `TestFixPostgresBoolColumns_IsIdempotent` | running the migration twice in a row is safe | ``` $ NETBIRD_STORE_ENGINE=postgres go test ./management/server/migration/ -run TestFixPostgresBoolColumns -v -count=1 === RUN TestFixPostgresBoolColumns_NoOpOnSqlite --- PASS: TestFixPostgresBoolColumns_NoOpOnSqlite (0.00s) === RUN TestFixPostgresBoolColumns_TableMissingIsNoOp --- PASS: TestFixPostgresBoolColumns_TableMissingIsNoOp (1.66s) === RUN TestFixPostgresBoolColumns_AlreadyBooleanIsNoOp --- PASS: TestFixPostgresBoolColumns_AlreadyBooleanIsNoOp (1.66s) === RUN TestFixPostgresBoolColumns_ConvertsNumericColumnsAndPreservesData time="2026-05-08T09:47:27Z" level=info msg="converting Postgres column bool_col_test_models.top_level_bool from numeric to boolean (issue #4326)" time="2026-05-08T09:47:27Z" level=info msg="converting Postgres column bool_col_test_models.settings_lazy_connection_enabled from numeric to boolean (issue #4326)" time="2026-05-08T09:47:27Z" level=info msg="converting Postgres column bool_col_test_models.settings_jwt_groups_enabled from numeric to boolean (issue #4326)" --- PASS: TestFixPostgresBoolColumns_ConvertsNumericColumnsAndPreservesData (1.68s) === RUN TestFixPostgresBoolColumns_IsIdempotent --- PASS: TestFixPostgresBoolColumns_IsIdempotent (1.62s) PASS ok github.com/netbirdio/netbird/management/server/migration 7.065s ``` ## Test plan - [x] Unit + integration tests cover SQLite no-op, missing-table no-op, already-boolean no-op, the actual conversion + data preservation, and idempotency - [x] Existing migration test suite still green (no regression) - [x] `go build ./management/...` clean ## Affected columns (concrete) For `types.Account` the migration covers the gorm-embedded Settings + ExtraSettings bool fields. At time of writing those are: ``` settings_peer_login_expiration_enabled settings_peer_inactivity_expiration_enabled settings_regular_users_view_blocked settings_groups_propagation_enabled settings_jwt_groups_enabled settings_routing_peer_dns_resolution_enabled settings_peer_expose_enabled settings_lazy_connection_enabled settings_auto_update_always settings_extra_peer_approval_enabled settings_extra_user_approval_required ``` The list is derived automatically from gorm metadata, so any future bool field added to `Settings`/`ExtraSettings` is covered without code changes. ## Use case Real-world: any operator who migrated their self-hosted NetBird from SQLite to Postgres using a tool that maps SQLite's `INTEGER` to Postgres `numeric` rather than `boolean`. That includes `pgloader` with default settings (the most common path), but also other custom dump/restore scripts. With this PR the management server self-heals on first start instead of returning HTTP 500 on every settings update. <!-- Docs acknowledgement --> - [ ] I added/updated documentation - [x] Documentation is **not needed** <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit ## Release Notes * **Bug Fixes** * Fixed PostgreSQL database schema issues where boolean columns were not using the proper native type. The correction automatically applies during database migration and is safe to run multiple times. <!-- end of auto-generated comment: release notes by coderabbit.ai --> --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
saavagebueno added the pull-request label 2026-08-05 07:08:53 -04:00
Sign in to join this conversation.
No Label pull-request
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: DYNR/netbird#27557