[PR #1030] [CLOSED] Implement SQLite Store using gorm and lookup table #14730

Open
opened 2026-08-05 03:06:13 -04:00 by saavagebueno · 0 comments
Owner

📋 Pull Request Information

Original PR: https://github.com/netbirdio/netbird/pull/1030
Author: @surik
Created: 7/25/2023
Status: Closed

Base: mainHead: add-sqlite


📝 Commits (1)

  • eb8fe61 Implement SQLite Store using gorm and lookup table

📊 Changes

19 files changed (+645 additions, -31 deletions)

View changed files

📝 .gitignore (+1 -0)
📝 client/cmd/testutil.go (+5 -1)
📝 client/internal/engine_test.go (+7 -2)
📝 go.mod (+5 -1)
📝 go.sum (+10 -2)
📝 management/client/client_test.go (+5 -1)
📝 management/cmd/management.go (+1 -1)
📝 management/server/account.go (+14 -13)
📝 management/server/account_test.go (+1 -1)
📝 management/server/dns.go (+1 -1)
📝 management/server/dns_test.go (+1 -1)
📝 management/server/management_proto_test.go (+5 -1)
📝 management/server/management_test.go (+7 -2)
📝 management/server/nameserver_test.go (+1 -1)
📝 management/server/network.go (+2 -2)
📝 management/server/route_test.go (+1 -1)
management/server/sqlite_store.go (+296 -0)
management/server/sqlite_store_test.go (+194 -0)
management/server/store_test.go (+88 -0)

📄 Description

Describe your changes

This pull request is currently in draft mode, and my aim is to experimentally implement a Store interface using SQLite and Gorm. For this implementation, I have chosen to follow a document-oriented approach instead of utilizing the full power of relational databases. The primary objective of this pull request is to showcase my approach and draw attention to any potential weak points. Additionally, I want to test the existing Store Interface with this implementation and identify areas where improvements can be made.

Goals:

  1. Test the Store interface using SQLite and Gorm to gauge its effectiveness and identify strengths and weaknesses.
  2. Evaluate whether certain methods, such as GetAllAccount, can be removed to avoid loading the entire database into memory, considering it is only used in two places.
  3. Address concurrent modification concerns by exploring the use of locks, specifically focusing on Global and Account locks. I want to assess whether using transactions can help alleviate potential concurrency issues.
    Benchmark Results:
$ go test  ./management/server -run Benchmark_ -bench=.  -benchmem
goos: darwin
goarch: amd64
pkg: github.com/netbirdio/netbird/management/server
cpu: Intel(R) Core(TM) i5-8259U CPU @ 2.30GHz
BenchmarkTest_StoreWrite/FileStore_Write_100-8               163          13118187 ns/op         5638586 B/op       8789 allocs/op
BenchmarkTest_StoreWrite/SqliteStore_Write_100-8             199           5097194 ns/op           47591 B/op        773 allocs/op
BenchmarkTest_StoreWrite/FileStore_Write_500-8                50          23935032 ns/op        12289436 B/op      15859 allocs/op
BenchmarkTest_StoreWrite/SqliteStore_Write_500-8             100          11405159 ns/op           47724 B/op        777 allocs/op
BenchmarkTest_StoreWrite/FileStore_Write_1000-8               27          41880172 ns/op        25661102 B/op      30031 allocs/op
BenchmarkTest_StoreWrite/SqliteStore_Write_1000-8            100          17335683 ns/op           47777 B/op        777 allocs/op
BenchmarkTest_StoreWrite/FileStore_Write_2000-8               13          89936823 ns/op        45821132 B/op      58825 allocs/op
BenchmarkTest_StoreWrite/SqliteStore_Write_2000-8             55          30195228 ns/op           47928 B/op        782 allocs/op
BenchmarkTest_StoreRead/FileStore_Read_100-8              437596              2821 ns/op            2856 B/op         26 allocs/op
BenchmarkTest_StoreRead/SqliteStore_Read_100-8             18478             62006 ns/op           26040 B/op        604 allocs/op
BenchmarkTest_StoreRead/FileStore_Read_500-8              431912              3105 ns/op            2856 B/op         26 allocs/op
BenchmarkTest_StoreRead/SqliteStore_Read_500-8             19396             57730 ns/op           26081 B/op        604 allocs/op
BenchmarkTest_StoreRead/FileStore_Read_1000-8             451514              2504 ns/op            2856 B/op         26 allocs/op
BenchmarkTest_StoreRead/SqliteStore_Read_1000-8            20233             56607 ns/op           26066 B/op        604 allocs/op

The benchmark results demonstrate that the SQLite implementation shows better write speed compared to the FileStore implementation, especially with the growth of accounts.

While my current implementation showcases the document-oriented approach with a lookup table, I recognize that there may be opportunities for further optimization. As the next step, I plan to explore another implementation, either with SQLite or PostgreSQL, utilizing a relational approach. Under this approach, resources such as accounts, users, and peers would have references and can be modified separately.

Please keep in mind that this is an experimental PR, and the focus is on gathering feedback and assessing the feasibility of the approach. The code is not yet intended for production use.

Checklist

  • Is it a bug fix
  • Is a typo/documentation fix
  • Is a feature enhancement
  • It is a refactor
  • Created tests that fail without the change (if possible)
  • Extended the README / documentation, if necessary

🔄 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/1030 **Author:** [@surik](https://github.com/surik) **Created:** 7/25/2023 **Status:** ❌ Closed **Base:** `main` ← **Head:** `add-sqlite` --- ### 📝 Commits (1) - [`eb8fe61`](https://github.com/netbirdio/netbird/commit/eb8fe61afc7cd3b4a28c1e809269014bd2f4d3c4) Implement SQLite Store using gorm and lookup table ### 📊 Changes **19 files changed** (+645 additions, -31 deletions) <details> <summary>View changed files</summary> 📝 `.gitignore` (+1 -0) 📝 `client/cmd/testutil.go` (+5 -1) 📝 `client/internal/engine_test.go` (+7 -2) 📝 `go.mod` (+5 -1) 📝 `go.sum` (+10 -2) 📝 `management/client/client_test.go` (+5 -1) 📝 `management/cmd/management.go` (+1 -1) 📝 `management/server/account.go` (+14 -13) 📝 `management/server/account_test.go` (+1 -1) 📝 `management/server/dns.go` (+1 -1) 📝 `management/server/dns_test.go` (+1 -1) 📝 `management/server/management_proto_test.go` (+5 -1) 📝 `management/server/management_test.go` (+7 -2) 📝 `management/server/nameserver_test.go` (+1 -1) 📝 `management/server/network.go` (+2 -2) 📝 `management/server/route_test.go` (+1 -1) ➕ `management/server/sqlite_store.go` (+296 -0) ➕ `management/server/sqlite_store_test.go` (+194 -0) ➕ `management/server/store_test.go` (+88 -0) </details> ### 📄 Description ## Describe your changes This pull request is currently in draft mode, and my aim is to experimentally implement a Store interface using SQLite and Gorm. For this implementation, I have chosen to follow a document-oriented approach instead of utilizing the full power of relational databases. The primary objective of this pull request is to showcase my approach and draw attention to any potential weak points. Additionally, I want to test the existing Store Interface with this implementation and identify areas where improvements can be made. Goals: 1. Test the Store interface using SQLite and Gorm to gauge its effectiveness and identify strengths and weaknesses. 1. Evaluate whether certain methods, such as GetAllAccount, can be removed to avoid loading the entire database into memory, considering it is only used in two places. 1. Address concurrent modification concerns by exploring the use of locks, specifically focusing on Global and Account locks. I want to assess whether using transactions can help alleviate potential concurrency issues. Benchmark Results: ``` $ go test ./management/server -run Benchmark_ -bench=. -benchmem goos: darwin goarch: amd64 pkg: github.com/netbirdio/netbird/management/server cpu: Intel(R) Core(TM) i5-8259U CPU @ 2.30GHz BenchmarkTest_StoreWrite/FileStore_Write_100-8 163 13118187 ns/op 5638586 B/op 8789 allocs/op BenchmarkTest_StoreWrite/SqliteStore_Write_100-8 199 5097194 ns/op 47591 B/op 773 allocs/op BenchmarkTest_StoreWrite/FileStore_Write_500-8 50 23935032 ns/op 12289436 B/op 15859 allocs/op BenchmarkTest_StoreWrite/SqliteStore_Write_500-8 100 11405159 ns/op 47724 B/op 777 allocs/op BenchmarkTest_StoreWrite/FileStore_Write_1000-8 27 41880172 ns/op 25661102 B/op 30031 allocs/op BenchmarkTest_StoreWrite/SqliteStore_Write_1000-8 100 17335683 ns/op 47777 B/op 777 allocs/op BenchmarkTest_StoreWrite/FileStore_Write_2000-8 13 89936823 ns/op 45821132 B/op 58825 allocs/op BenchmarkTest_StoreWrite/SqliteStore_Write_2000-8 55 30195228 ns/op 47928 B/op 782 allocs/op BenchmarkTest_StoreRead/FileStore_Read_100-8 437596 2821 ns/op 2856 B/op 26 allocs/op BenchmarkTest_StoreRead/SqliteStore_Read_100-8 18478 62006 ns/op 26040 B/op 604 allocs/op BenchmarkTest_StoreRead/FileStore_Read_500-8 431912 3105 ns/op 2856 B/op 26 allocs/op BenchmarkTest_StoreRead/SqliteStore_Read_500-8 19396 57730 ns/op 26081 B/op 604 allocs/op BenchmarkTest_StoreRead/FileStore_Read_1000-8 451514 2504 ns/op 2856 B/op 26 allocs/op BenchmarkTest_StoreRead/SqliteStore_Read_1000-8 20233 56607 ns/op 26066 B/op 604 allocs/op ``` The benchmark results demonstrate that the SQLite implementation shows better write speed compared to the FileStore implementation, especially with the growth of accounts. While my current implementation showcases the document-oriented approach with a lookup table, I recognize that there may be opportunities for further optimization. As the next step, I plan to explore another implementation, either with SQLite or PostgreSQL, utilizing a relational approach. Under this approach, resources such as accounts, users, and peers would have references and can be modified separately. Please keep in mind that this is an experimental PR, and the focus is on gathering feedback and assessing the feasibility of the approach. The code is not yet intended for production use. ## Issue ticket number and link ### Checklist - [ ] Is it a bug fix - [ ] Is a typo/documentation fix - [x] Is a feature enhancement - [ ] It is a refactor - [ ] Created tests that fail without the change (if possible) - [ ] Extended the README / documentation, if necessary --- <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 03:06:13 -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#14730