4 ms·

Possibly the wrong place to ask this, but: I've played with SQLite when it was still available in-browser, and I felt that was on the brink of being a game-cha

Possibly the wrong place to ask this, but: I've played with SQLite when it was still available in-browser, and I felt that was on the brink of being a game-changer. If it was still supported in-browser and we had replication from the browser, peer-to-peer, I think we'd be living in a much more useful world. It's a lovely tech, but I never built anything serious around it. At this point, as a front-end web technology that seems to be gone. I know I could conceivably use it to back a NodeJS server, keep all the data in memory and local files, but I don't see a great use case for that. I do lots of small projects that could use SQLite, but I usually scaffold them around a single shot Mysql DB for testing, which is easy to spin up and easy to detach from any given back-end instance. So I'm not sure what I'd gain by trying to make a tiny databse on the back-end live in Sqlite files. I'm totally enchanted by stuff like Litestream, and I'm actually dying to find a reason to try it. But every good use case for Sqlite that I could think of sort of died when it stopped being a viable client-side store. TL;DR, what are people using SQLite for? What's the advantage over spinning up a tiny MySQL instance in a cloud somewhere, where you don't have to deal with managing replication and failover by yourself?

nicoburns 1y ago
Most uses of SQLite are client-side apps. Basically everything that's not web: from mobile apps and desktop apps to embedded software in things like cars, tvs, kiosks, etc. There are probably more apps using SQLite than not using SQLite for these kind of apps.
- noduerme 1y ago
  Are these things just running sql.js? Doesn't that use a kind of unstable webstorage instead of writing client-side files? I don't have a good handle on the state of SQLite these days as a way to store semi-permanent data on the client. In a locked-in environment or a backend I feel like it might make sense, but... isn't there like a 50Mb limit on localStorage, and how does that play nicely with a potentially larger DB...?
  - nicoburns 1y ago
    "client" doesn't mean web. Mobile apps, desktop apps, etc are all client-side apps that can run regular SQLite. Think of apps Spotify, WhatsApp, AirBnB, Uber, etc. Not to mention mail clients, web browsers, etc. Probably 90% of non-web clients are using SQLite.
    - noduerme 1y ago
      I'm not sure about this, I may be exaggerating, but aren't all four apps you mentioned (Spotify, WhatsApp, AirBnB, Uber) built on Electron? So they'd be using SQLite in the Node portion as their storage. That's their "server side", not client side. For that portion (the locally-run mobile backend - the middleware) I guess it would make more sense... so I see what you're saying. [Edit: Of all 4 things - Maybe only Spotify is actually an Electron app...? Although I'm confused as to how the rest could leverage NodeJS locally]
      - lights0123 1y ago
        I would consider the entirety of one of those Electron apps to be a client since their main purpose is to interface with an external server—even if a small part of them internally is itself a server.
      - beejiu 1y ago
        Servers are things on other sides of networks. An electron app running locally is all client, whether it contains a database or not.
      - jitl 1y ago
        Spotify used Chrome Embedded Framework (CEF) not Electron, but it’s similar in that it bundles Chrome and uses webviews to draw UI
  - _joel 1y ago
    There are bindings to sqlite for pretty much most languages out there. Not just webapps https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html https://www.sqlite.org/cintro.html https://www.sqlite.org/cintro.html https://docs.rs/sqlite/latest/sqlite/ https://docs.rs/sqlite/latest/sqlite/ etc :)
striking 1y ago
Definitely can still be used on a client, you just have to be creative with running it. https://github.com/orbitinghail/sqlsync https://github.com/orbitinghail/sqlsync uses rusqlite compiled to WASM within a Web Worker, for instance.
_joel 1y ago
Chances are you have an installation or several running in your pocket right now. It's one of the most widely deployed pieces of software in existence. It's not supposed to be a 'traditional' DB in that running a webapp for many users sense (although it can do that), but to back client based software that need a data store/query tool and don't want to implement their own.
- softfalcon 1y ago
  Agree with all of this. Also want to add that SQLite is a perfect jumping off point for full stack web-devs doing react-native (or similar) and want a familiar data query pattern they were already used to from MySQL, Postgres, etc. Having a consistency of SQL everywhere is really appealing for data management.
incomingpain 1y ago
Most of my python projects use SQLite; 1 exception where i need multiprocessing access to the database and no locking problems and speed so i need to run the entire db in memory. https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html The built in library makes it really quick and easy to use it. Whereas mysql or in my case id use postgresql if i needed a full db. You're looking for a third party library? I have used Psycopg before but its just not needed. Yes, ive come up against the sqlite locked database performance troubles; and failed to actually get the multi user thing working properly. But I moreso just needed to reapproach the issue. My new startup http://mapleintel.ca http://mapleintel.ca is db.sqlite3 based. thousands of lines in it so far and growing every day.
- noduerme 1y ago
  I've used sqlite3 in node, and it's nice and performant for small cases, yes. Mostly I've used it for things small enough where a user could download an entire sqlite file of their data, and then re-upload it in case their data got lost. But ultimately this data gets stored in a true MySQL DB. I don't think I'd trust it to run a whole system with thousands of users and millions of entries... honestly, maybe my issue is that I don't trust NodeJS enough...
  - hruk 1y ago
    FWIW, I've been running a system with roughly 100K users, about 25 qps on average, with a single SQLite file for several years. No issues with data.
    - noduerme 1y ago
      That's... pretty amazing. It sounds crazy to me, I'm obsessive about hourly backups, but do you use something like Litestream to keep copies?
      - arkh 1y ago
        From some months ago: https://news.ycombinator.com/item?id=43076785 https://news.ycombinator.com/item?id=43076785 > searchcode.com’s SQLite database is probably one of the largest in the world, at least for a public facing website. It’s actual size is 6.4 TB.
chris_st 1y ago
Two things: 1. There's almost certainly a port of Sqlite3 to WASM that would be more than glad to run in your browser. 2. I'd really love to know what applications fit in the "we had replication from the browser, peer-to-peer, I think we'd be living in a much more useful world" situation. We've had GunDB, IPFS, etc. that live in the browser for decades (and projects like Urbit), and the killer app just... doesn't seem to exist? Let alone anything useful as just a basic demo? Anyone have anything to point to? I just don't see it, personally.
- noduerme 1y ago
  Heh. Well, #2, brilliant question. But no, I'm not thinking of anything as sexy as totally distributed filesystems. 15 years ago when I was into crypto and ran a bitcoin casino I would have had much bigger ideas for fully distributed DBs (which surely would have tanked and caused me ruin). Currently, I deal with a lot of site-specific software installations that run their own MySQL servers, some of which have unexpected downtime or go offline. I have a lot of custom code to align them with a master source of truth when they come back up. At least a few times a year, one of them gets so corrupted that I have to just login remotely and rebuild its database by hand. If I could have designed them to share a single database peer to peer, it would have saved me a lot of personal time. There are probably a lot of hub-and-spoke systems like this flying way under the radar that would be a lot better if there were a reliable technology to keep them synchronized. I keep looking at Litestream and thinking about it.
  - RUnconcerned 1y ago
    Your best answer for "a much more useful world" is... easier development of crypto gambling? That sounds like an actively worse world to live in to me, to be honest.
  - chris_st 1y ago
    Wow, that sounds like a really difficult situation! I think your idea of using Litestream is definitely worth a try. Good luck (but not with crypto gambling :-).
- 1y ago
jurip 1y ago
One thing that's always brought up in these discussions, because it's worth bringing up, is the file format of the macOS image editor Acorn: https://flyingmeat.com/acorn/docs/technotes/ACTN002.html https://flyingmeat.com/acorn/docs/technotes/ACTN002.html Personally I use it a bunch in mobile and desktop apps.
- noduerme 1y ago
  That's quite fun to know! Although, it's also funny that I downloaded the demo .acorn files on a Mac and the OS has no idea how to open them without searching the App Store. I feel like a JSON file would be more compact and easier to read, but wtf do I know. Harder to query, I guess?
  - gbalduzzi 1y ago
    JSON is just terrible size-wise, it can't efficiently store binary data
- criddell 1y ago
  SQLite is bundled with Windows as well. > All supported versions of Windows support SQLite, so your app does not have to package SQLite libraries. Instead, your app can use the version of SQLite that comes installed with Windows. https://learn.microsoft.com/en-us/windows/apps/develop/data-access/sqlite-data-access https://learn.microsoft.com/en-us/windows/apps/develop/data-...
arkh 1y ago
> what are people using SQLite for? Managing profiles and inventory in a solo game where crafting results are random and I don't like limited inventories.
dist-epoch 1y ago
> single shot Mysql DB for testing, which is easy to spin up and easy to detach from any given back-end instance you're doing something wrong if that is easier than using sqlite > What's the advantage over spinning up a tiny MySQL instance in a cloud somewhere one advantage is your thing will work without needing network access

nicoburns 1y ago

Most uses of SQLite are client-side apps. Basically everything that's not web: from mobile apps and desktop apps to embedded software in things like cars, tvs, kiosks, etc. There are probably more apps using SQLite than not using SQLite for these kind of apps.

noduerme 1y ago
Are these things just running sql.js? Doesn't that use a kind of unstable webstorage instead of writing client-side files? I don't have a good handle on the state of SQLite these days as a way to store semi-permanent data on the client. In a locked-in environment or a backend I feel like it might make sense, but... isn't there like a 50Mb limit on localStorage, and how does that play nicely with a potentially larger DB...?
- nicoburns 1y ago
  "client" doesn't mean web. Mobile apps, desktop apps, etc are all client-side apps that can run regular SQLite. Think of apps Spotify, WhatsApp, AirBnB, Uber, etc. Not to mention mail clients, web browsers, etc. Probably 90% of non-web clients are using SQLite.
  - noduerme 1y ago
    I'm not sure about this, I may be exaggerating, but aren't all four apps you mentioned (Spotify, WhatsApp, AirBnB, Uber) built on Electron? So they'd be using SQLite in the Node portion as their storage. That's their "server side", not client side. For that portion (the locally-run mobile backend - the middleware) I guess it would make more sense... so I see what you're saying. [Edit: Of all 4 things - Maybe only Spotify is actually an Electron app...? Although I'm confused as to how the rest could leverage NodeJS locally]
    - lights0123 1y ago
      I would consider the entirety of one of those Electron apps to be a client since their main purpose is to interface with an external server—even if a small part of them internally is itself a server.
    - beejiu 1y ago
      Servers are things on other sides of networks. An electron app running locally is all client, whether it contains a database or not.
    - jitl 1y ago
      Spotify used Chrome Embedded Framework (CEF) not Electron, but it’s similar in that it bundles Chrome and uses webviews to draw UI
- _joel 1y ago
  There are bindings to sqlite for pretty much most languages out there. Not just webapps https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html https://www.sqlite.org/cintro.html https://www.sqlite.org/cintro.html https://docs.rs/sqlite/latest/sqlite/ https://docs.rs/sqlite/latest/sqlite/ etc :)

striking 1y ago

Definitely can still be used on a client, you just have to be creative with running it. https://github.com/orbitinghail/sqlsync https://github.com/orbitinghail/sqlsync uses rusqlite compiled to WASM within a Web Worker, for instance.

_joel 1y ago

Chances are you have an installation or several running in your pocket right now. It's one of the most widely deployed pieces of software in existence. It's not supposed to be a 'traditional' DB in that running a webapp for many users sense (although it can do that), but to back client based software that need a data store/query tool and don't want to implement their own.

softfalcon 1y ago
Agree with all of this. Also want to add that SQLite is a perfect jumping off point for full stack web-devs doing react-native (or similar) and want a familiar data query pattern they were already used to from MySQL, Postgres, etc. Having a consistency of SQL everywhere is really appealing for data management.

incomingpain 1y ago

Most of my python projects use SQLite; 1 exception where i need multiprocessing access to the database and no locking problems and speed so i need to run the entire db in memory. https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html The built in library makes it really quick and easy to use it. Whereas mysql or in my case id use postgresql if i needed a full db. You're looking for a third party library? I have used Psycopg before but its just not needed. Yes, ive come up against the sqlite locked database performance troubles; and failed to actually get the multi user thing working properly. But I moreso just needed to reapproach the issue. My new startup http://mapleintel.ca http://mapleintel.ca is db.sqlite3 based. thousands of lines in it so far and growing every day.

noduerme 1y ago
I've used sqlite3 in node, and it's nice and performant for small cases, yes. Mostly I've used it for things small enough where a user could download an entire sqlite file of their data, and then re-upload it in case their data got lost. But ultimately this data gets stored in a true MySQL DB. I don't think I'd trust it to run a whole system with thousands of users and millions of entries... honestly, maybe my issue is that I don't trust NodeJS enough...
- hruk 1y ago
  FWIW, I've been running a system with roughly 100K users, about 25 qps on average, with a single SQLite file for several years. No issues with data.
  - noduerme 1y ago
    That's... pretty amazing. It sounds crazy to me, I'm obsessive about hourly backups, but do you use something like Litestream to keep copies?
    - arkh 1y ago
      From some months ago: https://news.ycombinator.com/item?id=43076785 https://news.ycombinator.com/item?id=43076785 > searchcode.com’s SQLite database is probably one of the largest in the world, at least for a public facing website. It’s actual size is 6.4 TB.

chris_st 1y ago

Two things: 1. There's almost certainly a port of Sqlite3 to WASM that would be more than glad to run in your browser. 2. I'd really love to know what applications fit in the "we had replication from the browser, peer-to-peer, I think we'd be living in a much more useful world" situation. We've had GunDB, IPFS, etc. that live in the browser for decades (and projects like Urbit), and the killer app just... doesn't seem to exist? Let alone anything useful as just a basic demo? Anyone have anything to point to? I just don't see it, personally.

noduerme 1y ago
Heh. Well, #2, brilliant question. But no, I'm not thinking of anything as sexy as totally distributed filesystems. 15 years ago when I was into crypto and ran a bitcoin casino I would have had much bigger ideas for fully distributed DBs (which surely would have tanked and caused me ruin). Currently, I deal with a lot of site-specific software installations that run their own MySQL servers, some of which have unexpected downtime or go offline. I have a lot of custom code to align them with a master source of truth when they come back up. At least a few times a year, one of them gets so corrupted that I have to just login remotely and rebuild its database by hand. If I could have designed them to share a single database peer to peer, it would have saved me a lot of personal time. There are probably a lot of hub-and-spoke systems like this flying way under the radar that would be a lot better if there were a reliable technology to keep them synchronized. I keep looking at Litestream and thinking about it.
- RUnconcerned 1y ago
  Your best answer for "a much more useful world" is... easier development of crypto gambling? That sounds like an actively worse world to live in to me, to be honest.
- chris_st 1y ago
  Wow, that sounds like a really difficult situation! I think your idea of using Litestream is definitely worth a try. Good luck (but not with crypto gambling :-).
1y ago

jurip 1y ago

One thing that's always brought up in these discussions, because it's worth bringing up, is the file format of the macOS image editor Acorn: https://flyingmeat.com/acorn/docs/technotes/ACTN002.html https://flyingmeat.com/acorn/docs/technotes/ACTN002.html Personally I use it a bunch in mobile and desktop apps.

noduerme 1y ago
That's quite fun to know! Although, it's also funny that I downloaded the demo .acorn files on a Mac and the OS has no idea how to open them without searching the App Store. I feel like a JSON file would be more compact and easier to read, but wtf do I know. Harder to query, I guess?
- gbalduzzi 1y ago
  JSON is just terrible size-wise, it can't efficiently store binary data
criddell 1y ago
SQLite is bundled with Windows as well. > All supported versions of Windows support SQLite, so your app does not have to package SQLite libraries. Instead, your app can use the version of SQLite that comes installed with Windows. https://learn.microsoft.com/en-us/windows/apps/develop/data-access/sqlite-data-access https://learn.microsoft.com/en-us/windows/apps/develop/data-...

arkh 1y ago

> what are people using SQLite for? Managing profiles and inventory in a solo game where crafting results are random and I don't like limited inventories.

dist-epoch 1y ago

> single shot Mysql DB for testing, which is easy to spin up and easy to detach from any given back-end instance you're doing something wrong if that is easier than using sqlite > What's the advantage over spinning up a tiny MySQL instance in a cloud somewhere one advantage is your thing will work without needing network access