# Syncthing on SQLite -- help test!
**URL:** https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981
**Category:** Development
**Created:** [March 13, 2025, 9:26am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981 "2025-03-13T09:26:51Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 13, 2025, 9:26am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/1 "2025-03-13T09:26:51Z")
I’ve been working on migrating the Syncthing database from leveldb to SQLite, and it’s reached the point where I would like to ask for help in testing.
### Why
The database layer has been a murky corner for a long time, a source of real or potential bugs both in our code and the now unmaintained leveldb database code. Leveldb lacks things like actual transactions and our maintaining indexes ourselves has been a source of inconsistencies. We see a lot of odd crashes in the database layer.
The new database layer uses a SQLite database, with tables for files, folders, size accounting, etc. These are relational and indexed with proper keys and foreign keys. Metadata is maintained by triggers that execute in the same transaction as the change itself. There are a lot of changes all over the place to accommodate this.
### How you can help
Run the new testing release on one or more of your devices. See how it works and which new problems show up. When you do see problems, be prepared to share logs, screenshots and a full copy of the database – with me, if not with the entire world.
I’ve run it in my home setup for a couple of weeks (one Mac, one amd64 Linux server and a Raspberry Pi). It hasn’t eaten any of my data as far as I can tell and I don’t think it will eat your data either, but I can’t guarantee it. I don’t use all of Syncthing’s features and haven’t tested everything. Make sure you have a backup if you run it on important data.
### What you can expect
It’s best to do the migration when everything is in sync and quiescent.
On first startup Syncthing will convert the existing database to SQLite. This can take quite a while. The old database is renamed to `index-v0.14.0.db.migrated`. When it next connects to devices it will exchange full indexes with everyone. This can also take quite a while and things will possibly look out of sync for the duration. This will happen for every migrated device.
Some things are slower in the new system. One advantage of a simple key-value database like leveldb is that inserts and changes are incredibly fast. Now, changes entail triggers, multiple indexes to update, and transactions.
Some other things are quite a lot faster, for example listing the out of sync files for a remote device, which now uses indexes instead of a full database traversal in leveldb.
### How to revert to the old system
Make sure your files are in sync, as far as possible. Shut down the SQLite version, rename `index-v0.14.0.db.migrated` back to `index-v0.14.0.db`, start again. You might get a few sync conflicts but hopefully it’s not too bad.
### Let’s go!
Here are builds: [Release SQLite preview 1 · calmh/syncthing · GitHub](https://github.com/calmh/syncthing/releases/tag/v2.0.0-beta.1). There is also a Docker image at `ghcr.io/calmh/syncthing:v2.0.0-beta.1`.
The Windows and macOS binaries are not code signed unfortunately, so may be a little more annoying than usual to get to run.
“2.0.0?!” Yes, for now at least. It prevents auto upgrade back to non-SQLite versions, and if/when this gets into mainline, it prevents auto upgrade from previous versions into SQLite.
There are several reasons we might want to not do auto upgrades from previous versions. [This table](https://github.com/syncthing/syncthing/wiki/SQLite-build-notes) is one of them:
Building Go with SQLite is nontrivial, as SQLite is not written in Go. We can build and include the C library, which is what happens in the green checkbox scenarios. That gives us a binary that now depends on libc, which we didn’t use to. That means it may not run on some systems that could previously run Syncthing. There is also a translation of the C SQLite source into Go, along with a significant part of the C runtime libraries, which is what you get in the yellow circle scenarios. That’s a marvelous machine that I’m amazed it works at all, but it actually does seem to. It doesn’t support all platforms though, and there is a third alternative, a WASM compilation of SQLite…
Currently 95-something percent of users are covered by the green builds. Adding windows/386, which is possible with some effort, would bring that up to 99%. For the other platforms I think it’s good that we provide _a_ functioning binary, but ideally they would get platform native builds from their package managers. It’s not that it’s impossible to build SQLite with C for NetBSD for example, it’s just that it’s tricky to do without a NetBSD build machine.
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 13, 2025, 1:37pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/2 "2025-03-13T13:37:00Z")
---
### Author:  [@239](https://forum.syncthing.net/u/239)
#### Post date: [March 13, 2025, 8:23pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/3 "2025-03-13T20:23:09Z")
I have hardly any experience with Go, but after my own unsuccessful attempts to statically compile Go programs that use SQLite, I stumbled across [Building SQLite with CGo for (almost) every OS](https://zig.news/kristoff/building-sqlite-with-cgo-for-every-os-4cic). However I’m not sure if this would tick off more boxes.
---
### Author:  [@bt90](https://forum.syncthing.net/u/bt90)
#### Post date: [March 13, 2025, 9:01pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/4 "2025-03-13T21:01:32Z")
> [@calmh](#):
>
> Adding windows/386, which is possible with some effort, would bring that up to 99%
The last bastion of 32-bit Windows hosts is set to lose support with the end of Windows 10 in October.
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 13, 2025, 9:06pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/5 "2025-03-13T21:06:19Z")
> [@239](#):
>
> I stumbled across [Building SQLite with CGo for (almost) every OS](https://zig.news/kristoff/building-sqlite-with-cgo-for-every-os-4cic). However I’m not sure if this would tick off more boxes.
That blog article is literally the three green ticks at the top left in my table. 🙂
> [@bt90](#):
>
> The last bastion of 32-bit Windows hosts is set to lose support with the end of Windows 10 in October.
---
### Author:  [@bt90](https://forum.syncthing.net/u/bt90)
#### Post date: [March 13, 2025, 9:13pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/6 "2025-03-13T21:13:15Z")
> [@calmh](#):
>
> That blog article is literally the three green ticks at the top left in my table. 🙂
It’s easy to miss but the article author is using `zig cc` as the compiler for CGO. If that is useful 🤷♂️
---
### Author:  [@bt90](https://forum.syncthing.net/u/bt90)
#### Post date: [March 13, 2025, 9:23pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/7 "2025-03-13T21:23:44Z")
Neat. This can be paired with musl to create static binaries:
> **[Static-linked CGO binaries using musl and Zig](https://flowerinthenight.com/blog/2025-02-15-cgo-static-linked-bin-musl/)**
>
> 2025-02-15
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 14, 2025, 5:58am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/8 "2025-03-14T05:58:08Z")
Compilation aside, did any of you try it out? 😉
---
### Author:  [@Martchus](https://forum.syncthing.net/u/Martchus)
#### Post date: [March 14, 2025, 9:02am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/9 "2025-03-14T09:02:30Z")
I’ll try it this weekend with my test setup on GNU/Linux and Android and perhaps Windows.
Note that for Linux package maintainers the question how to build this against the system sqlite3 library will arise as this is often the preference/policy. So I’ll also try that. (I suppose one has to specify some tag for that.)
---
### Author:  [@239](https://forum.syncthing.net/u/239)
#### Post date: [March 14, 2025, 1:58pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/10 "2025-03-14T13:58:36Z")
Only with test data, no problems so far. My main setup utilizes containers on some nodes, but there are no images available?! Will _syncthing/syncthing:edge_ be updated to 2.0 after the beta?
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 14, 2025, 2:11pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/11 "2025-03-14T14:11:51Z")
There’s a container image on `ghcr.io/calmh/syncthing:v2.0.0-beta.1`
The regular container images will indeed be updated if and when this reaches `main` etc.
---
### Author:  [@239](https://forum.syncthing.net/u/239)
#### Post date: [March 14, 2025, 2:47pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/12 "2025-03-14T14:47:57Z")
Obvious disadvantage of limited portability with this build:
> Could not start dynamically linked executable: ./syncthing2 NixOS cannot run dynamically linked executables intended for generic linux environments out of the box.
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 14, 2025, 3:13pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/13 "2025-03-14T15:13:38Z")
😅 I mean that applies to most binaries in the world so I think that’s mostly on you. But it is interesting, and validates that this is not a straight upgrade.
---
### Author:  [@239](https://forum.syncthing.net/u/239)
#### Post date: [March 14, 2025, 4:14pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/14 "2025-03-14T16:14:07Z")
Almost all (statically linked) binaries I’m syncing across different systems do run everywhere (including Syncthing on NixOS). But most users just use packages so this is irrelevant.
---
### Author:  [@bt90](https://forum.syncthing.net/u/bt90)
#### Post date: [March 14, 2025, 4:41pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/15 "2025-03-14T16:41:31Z")
Sqlite on Linux can be statically built using musl-gcc:
>
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 14, 2025, 7:43pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/16 "2025-03-14T19:43:30Z")
Can we for the moment leave the compilation details to the side and focus on the functionality or lack thereof?
---
### Author:  [@gadget](https://forum.syncthing.net/u/gadget)
#### Post date: [March 14, 2025, 9:04pm UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/17 "2025-03-14T21:04:02Z")
It’s been over 24 hours since switching to v2.0.0-beta.1, and so far no data has been eaten (yet)… 😀
Pair of Linux file servers at work. Syncthing has read-only access on the production system plus there are automated backups and its warm standby twin isn’t mission critical so using a beta release on both servers isn’t an issue:
- Quad-core KVMs with 8GB RAM (host has an AMD EPYC CPU; hashing performance is 1103.89 MB/s).
- Production is send-only while standby is receive-only. Vanilla sync without versioning, untrusted devices, etc.
- One of the Syncthing folders is ~83GB consisting of \>1.1 million files. Files are constantly being added/removed 24/7 (btrfs filesystem on SSD).
- LevelDB to SQLite migration took ~15 minutes.
As expected, average CPU usage is up a bit, but nothing shocking:
_(Area in the yellow box is during the conversion to SQLite along with followup rescans and while I tested a few things.)_
After a few hours, a dry-run of rsync looked normal.
I tested reverting back to LevelDB on the standby server. Other than a bit of time out-of-sync, it caught up and was working fine.
Launching the beta version without an existing database, Syncthing promptly said no database was available to migrate and exited. I’m assuming that the mainline release will just start with a fresh SQLite database.
---
### Author:  [@Martchus](https://forum.syncthing.net/u/Martchus)
#### Post date: [March 15, 2025, 1:56am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/18 "2025-03-15T01:56:43Z")
I tested it on GNU/Linux and Android and it generally works as far as I can tell. (I only tested limited features on a test setup.)
* * *
About performance: I also tested with a folder that has many files (262226 files, 75485 dirs, 6.93 GiB) and it took a notable while until these statistics showed fully up on the new test device with SQLite3 (as global statistics). Maybe the is an effect of the slower insertions. It was still acceptable in this case. Unfortunately I didn’t measure the time (but it would maybe be useful to do that and compare against the current release on the same system/hardware).
I also ran into the following errors when trying to fully sync this folder on GNU/Linux:
```plaintext
Listen (BEP/tcp): Accepting connection: accept tcp [::]:34861: accept4: too many open files
Error on folder projects: global: unable to open database file: too many open files
```
I haven’t run into these error message when trying to sync this and similarly big folders before on this setup. So it could be a regression. Maybe this is also unrelated, though. (Despite seeing these error messages Syncthing was able to continue its operation.)
* * *
After running out of disk space in my Android VM I noticed that the database now needs quite a lot of disk space. With the mentioned folder (262226 files, 75485 dirs, 6.93 GiB) I ended up with a DB file that is almost 700 MiB - just for that folder. I compared this with the size of the leveldb files of my regular setup. It is only a little bit more than 800 MiB but this setup has many more (big) folders.
* * *
Browsing the global tree doesn’t seem to work; one gets a 500 response:
```plaintext
[2025-03-15T01:27:31] Unable to browse "projects": Error transferring http://127.0.0.1:8384/rest/db/browse?folder=projects&levels=1 - server replied: Internal Server Error
Request URL: http://127.0.0.1:8384/rest/db/browse?folder=projects&levels=1
```
If you cannot reproduce this yourself I can try to get more debug output. (With default log verbosity and no additional debugging setup there were no error messages on the Syncthing log.)
* * *
On Android SQLite seems to have one advantage over leveldb: When moving the Syncthing database to the SD-card one so far wasn’t able to start Syncthing because it couldn’t create the database lock file (even when staying within the private app folder within the SD-card). In both cases (SD-card and internal storage) Android uses FUSE. So I guess the problem is that the actual underlying filesystem on the SD-card (e.g. exFAT) doesn’t support locking in the way leveldb requires it. With SQLite3 this problem seems no longer apparent. (I’m only wondering whether concurrent accesses are handles as well by SQLite then.)
* * *
I also rebased my C-bindings for these tests. This wasn’t a big deal but it would be nice if the `migrateDatabase` function was part of a library module (instead of `main.go`) so I could just reuse it. (At this point I simply copied it over from `main.go` into my bindings module and replaced all `os.Exit(…)` calls with `return …` which works but is not nice.)
* * *
Building against the system-provided SQLite3 library was very easy. One just had to add `-tags libsqlite3` to the list of arguments when invoking the Go build system. I tested this when compiling for GNU/Linux with a shared and static build of SQLite3 (x86-64). I also tested it when building for Android with a shared build of SQLite3 (aarch64 and x86-64). It worked when compiling for these targets and seem to behave correctly at runtime.
I still have to figure out Windows because there it cannot find the symbols `sqlite3_enable_load_extension` and `sqlite3_load_extension` despite using `-DSQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION=1` when compiling SQLite3. ~~If I cannot figure this out I can supposedly still just fallback to the bundled SQLite3 build which makes not a big difference on Windows anyway.~~ It is possible to workaround it by adding the tag `sqlite_omit_load_extension`. With this I was able to build for Windows using static and shared system SQLite3 (i686, x86-64, aarch64).
* * *
Note that I still provide i686 Windows builds of Syncthing Tray that also include Syncthing itself. So users could still use those builds on 32-bit Windows 10.
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 15, 2025, 6:34am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/19 "2025-03-15T06:34:22Z")
Thanks for the testing!
> [@Martchus](#):
>
> `unable to open database file: too many open files`
Interesting. I ran into this also in my setup, and then set the max number of database connections (i.e., open file descriptors on the database file) to 128. It would be interesting to know if you are running with a fairly low limit, or if I’m leaking database connections/FDs somewhere. Syncthing should try to increase the rlimit to the hard max on startup.
I also noticed the database is bigger, about twice the size on my systems. I attributed this to indexes and stuff. I experimented with using lz4 on stored FileInfos and block lists, but it made no relevant difference. (The block lists are mostly random data anyhow.)
> [@Martchus](#):
>
> `Unable to browse "projects"`
~~I’ll look into it!~~ Found and fixed, needed an `ORDER BY` to be compatible with the old setup.
---
### Author:  [@calmh](https://forum.syncthing.net/u/calmh)
#### Post date: [March 15, 2025, 7:38am UTC](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981/20 "2025-03-15T07:38:09Z")
> [@gadget](#):
>
> Launching the beta version without an existing database, Syncthing promptly said no database was available to migrate and exited.
That’s not what I coded 😃 It should just not migrate anything and carry on as usual.
[Next page](https://forum.syncthing.net/t/syncthing-on-sqlite-help-test/23981.md?page=2)