Comparison of managed sqlite services

Let me introduce you to the characters of this story, sqlite, cloudflare d1 and turso.

• Sqlite: Sqlite is a battle tested sql database used on millions of devices.
• Cloudflare D1: D1 is a managed sqlite database service from cloudflare.
• Turso: Turso is a company that also provides managed sqlite database service it is based on forked sqlite called libsql.

I will not go over when to choose sqlite, you can refer to this sqlite choosing guide for it. However when it comes to choosing hosted sqlite solutions most of the reasons stated in blogs go away, after all what difference does it makes if the network call is made to postgres/sqlite one can even argue that choosing hosted sqlite solution is actually worse than using managed Postgres or some other managed DB as we lose all the benefits of local database while keeping all the drawbacks of limited datatypes, battle tested extensions and in general wider availability and support of other db solutions. My main reason for choosing it is flexibility to move to self-hosting if required. I definitely don't want to manage postgres and if the need for it arises in future i will go for some other managed service for it.

I chose D1 early on and was happy with the solution, there were minor problems but nothing deal breaking, this however changed with beta and eventually GA of D1.

• Export DB issues with D1 beta: There was a nice feature with D1 to download the DB locally as single sqlite file. This was great because i was able to work with production data without interfering with production database. With beta this feature was removed without any recourse. I had to write custom script to sync the data locally(you can read more about it here D1 script story).
• Export DB issues with D1 GA: With GA we were able to export the database but as a sqlite dump not as a file, that too was buggy so you can't just recreate db from the exported dump. I eventually have to create another script to recreate db from the dump. Last but not the least while database is being exported db would be unavailable ! I don't know how this is acceptable solution. There are similar problems with importing your existing sqlite db too, regular dump will not work out of the box, no support for sqlite file, no support for seeding new db from old db hosted in their own service etc.
• Inconsistent Latencies : In my app i was seeing inconsistent delays while querying the data. This is kind of expected in managed database that have replication since service may decide to replicate the db to a server near to you or may serve it from the master which is far away but i was getting unusual delays during certain time of the day and my hunch is it has less to do with the location and more to do with traffic load where requests to D1 would be rationed.
• Extension support: D1 supports fts and json out of the box but there is no support for any other extension. This is not a major inconvenience but it still makes life difficult especially for statistical operations that you have to do on application side to overcome this limitation.

In short i was spending too much time working around CF's tooling with fragile custom made scripts that i decided to look for other options

I looked for other hosted sqlite solutions since i was not ready to give up serverless altogether and i found out about turso though they have done a good job at seo to hide from google for the keywords i was searching for, i was still able to find them!

• Location support: Turso lets you choose location of creating and serving your database. This is great because most of my users come from a specific location and i can place the db close to them.
• Importing DB to turso: Turso allows you to use sqlite file as it is while importing. It is quite simple and it is not limited to sqlite file alone you can also use sqlite dump as well. It also allows you to seed new db from existing db in turso service. All i can say is turso has done a good job in this department and has covered most of the options that a user is likely to take for importing db.
• Exporting DB out of turso: You can take a regular sqlite dump to take the db out of turso in case you need to recreate it locally or migrate to other service. I was not able to find out the option to download sqlitee file, that would have made it even easier but i don't have any complaints sqlite dump is good enough for me. But the real game changer for me was embedded sync.
• Embedded Sync to local db: Remember i had to write custom scripts to sync database regularly with D1, it turns out turso has inbuilt feature for this called embedded syncs. It allows you to sync local and remote db with each other with few lines of change. It also allows for periodic syncs with set periods of time but i did not explored it as i was happy with manual syncing. It also opens up door for some interesting use cases for local apps but i will not get into it. One thing that you should be careful about embedded sync is that it replicates local writes to remote db as well, this may not be what you want if you just want a copy of remote db for your local development as you don't want to replicate writes to your production database. For me solution was to simply make a copy of embedded replica making sure that i don't use replica and therefore never run a risk of writing data back to the remote. I wish there was an option to indicate read only syncs but its a minor inconvenience.
• Latencies: Latencies are higher in case of turso but that is because i have fixed the location of master db and i am not utilizing turso's edge replica(different from embedded replica discussed above) feature which would have improved it. Moreover i have found that latencies are higher in case of turso because edge feature of CF is indirectly making latencies worse. CF serves the worker from the location that is closest to the user which can be far from db(since i am using single location for db) now if you have multiple trips to db it would end up affecting final response. CF knows about this and has smart placement feature just for this kind of situation. I have enabled it and hopefully latencies will improve with this change.
• Accounting sqlite reads: One good feature of D1 was that every sql request also came up with nice number of rows read information. This helps in optimizing the queries since managed DB's generally set price based on number of rows read/written. While turso allows querying remote db with its shell command it is missing this information. You can still see it on their dashboard for the past queries or using their CLI but it is not available as part of regular queries in shell. You can still get rough estimates in sqlite so its not a major inconvenience but it is still the only feature where D1 is better. By the way let me know if you are interested in knowing how to get read counts in sqlite, here is the article Counting row reads in sqlite.
• Extension support: By default turso supports FTS and json but in addition to it you can also enable support for other extensions. This makes it much closer to your regular sqlite experience where you can augment its shortcomings with extensions. This feature is experimental by the way.
• LibSQlite: Libsqlite is a fork of sqlite so its not exactly the same db that is tested on millions of device and turso is based on this. While it maintains same interfaces you should be aware of this fact before blindly choosing it thinking its your regular old sqlite.

I am wondering if i should write "in conclusion" that would make this entire article to be dismissed as written by chatgpt 😀 but here it is in conclusion in my few days of testing i am quite happy with turso. I am also hopeful that turso is likely improve over time as compared to D1 as its main product is turso db while D1 is among the many products of CF.

I also came across sqlitecloud but it seems they are not out of beta yet and also have their own sdk. I am using drizzle orm so i am not sure how much effort it would require in changing to their sdk so i decided to drop it. I might give them a try once their orm integration story is clear.

For those of you brave souls who are planning to write their own cloud based sqlite service like D1 here is a nice guide on it sqlite cloud guide . Hopefully it would lead to more options in managed sqlite space.