Error when running tests from local machine

[jacobprall]

Site ID Regeneration Causes Excessive Device Registration in sqlite-sync

Summary

When integrating sqlite-sync with SQLite Cloud, I discovered that Site IDs are regenerated on every session, causing each application restart to register as a new device. This quickly exhausts device limits on free/lower-tier plans and creates confusion for developers.

Environment

• sqlite-sync version: Latest (from releases)
• Platform: macOS (darwin-arm64)
• SQLite Cloud: Free tier
• Integration: Electron app with better-sqlite3
• Node.js: v20.19.4
• better-sqlite3: ^12.2.0
• Electron: ^28.3.3
• macOS: 14.x (Apple Silicon)

Problem Description

Expected Behavior

• Site ID should persist across application sessions when using the same database file
• Each unique database file should have one stable Site ID
• Device registration should happen once per database, not once per session

Actual Behavior

• Site ID changes on every application restart, even with the same database file
• Each session registers as a new device in SQLite Cloud
• Quickly hits device limits (403 errors) making development/testing difficult

Reproduction Steps

1. Create a database and initialize sqlite-sync:

-- Initialize sync for a table
SELECT cloudsync_init('messages', 'cls');
SELECT cloudsync_enable('messages');

-- Initialize network and set credentials
SELECT cloudsync_network_init('sqlitecloud://project.sqlite.cloud:8860/db');
SELECT cloudsync_network_set_apikey('your-api-key');

-- Check site ID
SELECT cloudsync_siteid(); -- Returns: 01987209f7b3747d85bcea0fc4a70045

2. Close the application and database connection

3. Restart application with same database file:

-- Same initialization steps
SELECT cloudsync_network_init('sqlitecloud://project.sqlite.cloud:8860/db');
SELECT cloudsync_network_set_apikey('your-api-key');

-- Check site ID again
SELECT cloudsync_siteid(); -- Returns: 01987209a1b2c3d4e5f6789012345678 (DIFFERENT!)

4. Attempt to sync:

SELECT cloudsync_network_send_changes();
-- Error: 403 Device limit reached: You've already registered the maximum number of devices

Investigation Results

I conducted testing and found:

Site ID Storage

• Site ID is stored in cloudsync_site_id table in the database
• The table persists correctly between sessions
• However, cloudsync_siteid() returns different values each session

Network Cleanup Impact

• Calling cloudsync_network_cleanup() appears to affect Site ID generation
• Avoiding cleanup helps with persistence but doesn't fully solve the issue
• Site ID still changes between cloudsync_network_init() calls

Test Results

Session 1: Site ID = 0198720915de7fc6900d44f54defe8cd
Session 2: Site ID = 0198720915e07ced988af18553193f8a
Session 3: Site ID = 01987209f7b3747d85bcea0fc4a70045

Each unique Site ID registers as a separate device in SQLite Cloud.

Impact on Developers

1. Development Friction: Can't test sync functionality after a few restarts
2. Unclear Error Messages: 403 errors don't clearly indicate the root cause
3. Production Concerns: Applications would register new devices frequently
4. Cost Implications: Forces upgrades to higher-tier plans for basic testing

Workaround (Temporary)

For now, developers can:

1. Avoid calling cloudsync_network_cleanup() unless absolutely necessary
2. Use persistent database files (don't recreate for each test)
3. Implement application-level session management
4. Upgrade SQLite Cloud plan for higher device limits

Code Examples

Problematic Pattern (causes device registration on each run):

// This pattern registers a new device every time
const syncService = new SyncService();
await syncService.initialize(config);
// ... use sync service
await syncService.logout(); // Calls cloudsync_network_cleanup()

Better Pattern (preserves device registration):

// This pattern reuses device registration
const syncService = new SyncService();
await syncService.initialize(config);
// ... use sync service
syncService.stopAutoSync(); // Don't call logout/cleanup
await databaseService.close(); // Just close database

Request

Could you please:

1. Investigate why Site IDs change between sessions despite database persistence
2. Document the relationship between Site ID, device registration, and session management
3. Consider adding functions for explicit device management
4. Improve error messages to help developers understand device limit issues

This issue significantly impacts the developer experience and makes it difficult to build reliable local-first applications with sqlite-sync.

Additional Context

• I'm building a local-first chatbot application
• Successfully integrated sqlite-sync extension loading and CRDT functionality
• Authentication and local data storage work perfectly
• Only blocked by this device registration issue
• Happy to provide more details or test potential fixes

---

[andinux]

Ciao @jacobprall
I'm not able to reproduce the issue. I'm calling sqlite-sync functions directly from the SQLite CLI.
This is the output of my test:

cloudsync % /opt/homebrew/Cellar/sqlite/3.49.1/bin/sqlite3 /Users/andrea/Downloads/issue-11.sqlite       
SQLite version 3.49.1 2025-02-18 13:38:58
Enter ".help" for usage hints.
sqlite> .load /Users/andrea/Documents/GitHub/SQLiteAI/sqlite-sync/dist/cloudsync.dylib
sqlite> CREATE TABLE messages (id TEXT PRIMARY KEY NOT NULL, message TEXT);
sqlite> SELECT cloudsync_init('messages', 'cls');

sqlite> SELECT cloudsync_enable('messages');

sqlite> SELECT hex(cloudsync_siteid());
019875528CA17706B9392A43BA1EADF5
sqlite> SELECT cloudsync_network_init('sqlitecloud://project.sqlite.cloud:8860/db');
0
sqlite> SELECT cloudsync_network_set_apikey('your-api-key');
0
sqlite> SELECT hex(cloudsync_siteid());
019875528CA17706B9392A43BA1EADF5
sqlite> SELECT cloudsync_network_cleanup();
0
sqlite> SELECT hex(cloudsync_siteid());
019875528CA17706B9392A43BA1EADF5
sqlite> SELECT cloudsync_terminate();

sqlite> .quit
andrea@Andreas-MacBook-Pro cloudsync % /opt/homebrew/Cellar/sqlite/3.49.1/bin/sqlite3 /Users/andrea/Downloads/issue-11.sqlite
SQLite version 3.49.1 2025-02-18 13:38:58
Enter ".help" for usage hints.
sqlite> .load /Users/andrea/Documents/GitHub/SQLiteAI/sqlite-sync/dist/cloudsync.dylib
sqlite> SELECT hex(cloudsync_siteid());
019875528CA17706B9392A43BA1EADF5
sqlite> SELECT cloudsync_network_init('sqlitecloud://project.sqlite.cloud:8860/db');
0
sqlite> SELECT cloudsync_network_set_apikey('your-api-key');
0
sqlite> SELECT hex(cloudsync_siteid());
019875528CA17706B9392A43BA1EADF5
sqlite> SELECT cloudsync_init('messages', 'cls');

sqlite> SELECT cloudsync_enable('messages');

sqlite> SELECT hex(cloudsync_siteid());
019875528CA17706B9392A43BA1EADF5

I perform various sqlite-sync functions (including cloudsync_init, cloudsync_enable , cloudsync_network_init , cloudsync_network_set_apikey , cloudsync_network_cleanup , cloudsync_terminate , close the db connection, reopen the db connection and recalled all the previous functions) and the value of SELECT hex(cloudsync_siteid()); didn't change during the whole test.

The only two functions that force the creation of new site ID are cloudsync_network_logout() and cloudsync_cleanup('*'), are you sure that the await syncService.logout(); function is not calling any of these two functions?

[marcobambini]

@jacobprall please let us know what is not clear about the current documentation

[jacobprall]

I think I figured out the issue! Is there a way to unregister site ids? 3 files as a limit is tough! :)

[andinux]

We are implementing an UI to unregister site ids.
I already augmented the limit on your project cnwr... to 100 devices, just let me know if you need to increase the limit further or to raise it in another project.