8 ms·

SQLite is such a fantastic database. I've always wondered, is anyone using it at scale in a client server application? How do people handle syncing online/offli

SQLite is such a fantastic database. I've always wondered, is anyone using it at scale in a client server application? How do people handle syncing online/offline in mobile apps?

bluedino 9y ago
One problem we've ran into with it, is tables with > 400 columns.
- Diederich 9y ago
  Can you expand on that?
  - andrewguenther 9y ago
    Isn't 400 columns expanded enough?
  - bluedino 9y ago
    It's simply creating a local cache of a remote database that runs on Oracle.
    - Diederich 9y ago
      I mean...what kinds of problems did you run into? Thanks.
- revelation 9y ago
  Well, don't do that, normalize your scheme.
  - phn 9y ago
    When your application grows, eventually you need to de-normalize data if you want to keep having reasonable performance for some operations. You can reach a point where it's not feasible to have everything done by joins. EDIT: I mean in relational DBs in general. In this case, 400+ columns probably mean SQLite may not be the engine to use, regardless of schema :)
- striking 9y ago
  Sounds to me like you're using RDBMS wrong.
  - tyfon 9y ago
    Not that it necessarily relates to SQLlite, but I've seen this _many many_ times in real world databases in the banking industry. Personally I don't like it and I am luckily not in IT, but as an analyst I have seen many weird database structures. One can only imagine the business requirements that gave birth to some of them. To quote a solutions architect to the head of marketing in a previous job: "You asked for a monster, you got a monster."
- danso 9y ago
  Is there something about SQLite's architecture that would make such a schema less problematic in MySQL/PgSQL/etc?
alttab 9y ago
Spiceworks has a network management app that runs on a rails server on your network and uses sqlite to store everything and run the multi-tenant help desk. I was always surprised how well it worked.
dhd415 9y ago
I have but I'd say that SQLite is not intended for use in that scenario. It's an in-process library for persisting data to disk in a generally relational format with a SQL interface. It starts to degrade under highly concurrent read and write workloads that you can experience in client-server applications. At that point, a typical RDBMS with more robust concurrency support starts to be a better choice. I experienced that when using SQLite and we eventually moved the application to a full RDBMS which was more complex but also performed and scaled much better. Note that this is very much in line with their recommendations from the docs (http://sqlite.org/whentouse.html http://sqlite.org/whentouse.html): >>SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem. Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity. SQLite does not compete with client/server databases. SQLite competes with fopen().<< It's worth noting that when considered as competition to fopen(), SQLite is quite good.
- tiffanyh 9y ago
  Fossil-SCM. I've always wondered why the creator of SQLite (who's also the creator of Fossil SCM) will state that SQLite is not meant for client/server scenarios ... yet his Fossil SCM (which is client/server) uses SQLite. https://www.fossil-scm.org https://www.fossil-scm.org
  - bensummers 9y ago
    fossil on the server gets low numbers of writes, and not many reads. And being a distributed system, most of the SQLite action is on the client.
  - mritun 9y ago
    SQLite does not have a client-server interface. That’s ok because the creator says it is supposed to be an embedded library that gives a better fopen interface. That is, you don’t need to invent file formats (think JSON, YAML, etc). SQLite is not just a database, it’s a persistence layer that happens to have SQL interface. It places no restrictions whatsoever on the applications that uses it. That will be pointless. Fossil-SCM uses SQLite and is itself client server.
russell_h 9y ago
OpenStack Swift (it's S3-like service) uses SQLite to store account and bucket metadata.
hasenj 9y ago
I really don't understand why majority of websites use mysql or even postgres for that matter. The vast majority of websites would run perfectly fine on sqlite. Not only would you get rid of network latency, you would also vastly reduce your dependencies. I have a side project to try to see if I can build a minimal micro-blogging type server application on sqlite and try to test how many requests it can handle per second (reads and writes). I have a hunch it will be able to handle quite a lot. Specially if the server code is written in a fast language that compiles to native code (e.g. D) with some caching of the most commonly requested items in memory.
- akvadrako 9y ago
  SQLite does not work smoothly in many languages (like python) in regards to concurrent access. Without a controlling process (or thread) it's synchronisation possibilities are limited, so it can never scale too far. Nonetheless I think it's a great idea to build your own single-threaded layer over SQLite and scale that, using a message bus or whatever. But it's _not_ a drop-in replacement for MySQL based apps.
  - ageofwant 9y ago
    SQLAlchemy with transactions on SQLite should get you very close, probably close enough.
    - infecto 9y ago
      Why get very close when I can just spin up a single micro RDS instance of MySQL or Postgres and not have to worry about it?
      - marktangotango 9y ago
        Whoa! That's nearly $20 a month, if you're past the free tier, which you will be if you're not already. Edit that's t1.micro, t2 is $13.
alvil 9y ago
Here are some comments from SQLite itself: https://www.reddit.com/r/PHP/comments/59na74/sqlite_as_the_only_database_for_website/d99us21/ https://www.reddit.com/r/PHP/comments/59na74/sqlite_as_the_o...
evanspa 9y ago
In my strength-tracking iOS app Riker, I use SQLite directly (not CoreData) as my local data store, and support full offline-mode. The backend is Postgres. In the app, for each relation (e.g., a "workout set"), I have 2 tables: a master and a scratchpad. When a user saves a set, a row is written to the scratchpad table. When the user syncs it with the server, a row is written to the master table and deleted from the scratchpad table. When the user wants to edit the record, I first copy it down from the master table to the scratchpad table. All local editing impacts the scratchpad row. When the user wants to sync, only if a 200 response is returned will I copy-up the scratchpad row to the master row. If the set was edited on another device and the local copy is out-of-sync, the server would have responded with a 409 (http conflict code), and the body would contain the server copy, which is then written to the master table. The user can then figure how they want to merge the scratchpad row and the master row. Anyway...trying to do all this with CoreData would have been a pain, so I use SQLite directly, and works great. Or to summarize, I handle offline mode, syncing and conflict detection using "updated_at" timestamp columns along with logic in my REST API to returned appropriate HTTP status codes, interpret "if-unmodified-since" headers, etc. https://itunes.apple.com/us/app/riker/id1196920730?mt=8 https://itunes.apple.com/us/app/riker/id1196920730?mt=8 Riker on Android is currently in-progress...
- lungureanu 9y ago
  I find it difficult to get full offline-mode + sync working correctly. Don't know if you have duplicates in the remote db but I think your approach has a problem: the app send the data (from scratchpad table) to server, the server saves it in DB but the connection drops on device (ex bad connection, user turn off WIFI/3G while syncing) so the app will never get a 200 response. In this case your new data was saved on server but it remains on scratchpad table. On next sync the data will be uploaded as being new: this will cause duplicates.
  - evanspa 9y ago
    You are right. The way I solved for this scenario is that when a record is first created on the device, a GUID is created for it (and stored using another column of course). When POSTing new records to the server for syncing, the server will check the GUID and see if the record already exists in its database, and if so, can ignore it (so the duplicate isn't written). But yes, you're right overall - full offline mode w/syncing, etc is a big pain :)
edwinyzh 9y ago
For question #1, an approach is to build a full-fledged Web/REST server wrapping around SQLite: https://github.com/synopse/mORMot https://github.com/synopse/mORMot The performance is high!
pier25 9y ago
Check out Realm: https://realm.io/ https://realm.io/

SQLite is such a fantastic database. I've always wondered, is anyone using it at scale in a client server application? How do people handle syncing online/offline in mobile apps?

bluedino 9y ago

One problem we've ran into with it, is tables with > 400 columns.

Diederich 9y ago
Can you expand on that?
- andrewguenther 9y ago
  Isn't 400 columns expanded enough?
- bluedino 9y ago
  It's simply creating a local cache of a remote database that runs on Oracle.
  - Diederich 9y ago
    I mean...what kinds of problems did you run into? Thanks.
revelation 9y ago
Well, don't do that, normalize your scheme.
- phn 9y ago
  When your application grows, eventually you need to de-normalize data if you want to keep having reasonable performance for some operations. You can reach a point where it's not feasible to have everything done by joins. EDIT: I mean in relational DBs in general. In this case, 400+ columns probably mean SQLite may not be the engine to use, regardless of schema :)
striking 9y ago
Sounds to me like you're using RDBMS wrong.
- tyfon 9y ago
  Not that it necessarily relates to SQLlite, but I've seen this _many many_ times in real world databases in the banking industry. Personally I don't like it and I am luckily not in IT, but as an analyst I have seen many weird database structures. One can only imagine the business requirements that gave birth to some of them. To quote a solutions architect to the head of marketing in a previous job: "You asked for a monster, you got a monster."
danso 9y ago
Is there something about SQLite's architecture that would make such a schema less problematic in MySQL/PgSQL/etc?

alttab 9y ago

Spiceworks has a network management app that runs on a rails server on your network and uses sqlite to store everything and run the multi-tenant help desk. I was always surprised how well it worked.

dhd415 9y ago

I have but I'd say that SQLite is not intended for use in that scenario. It's an in-process library for persisting data to disk in a generally relational format with a SQL interface. It starts to degrade under highly concurrent read and write workloads that you can experience in client-server applications. At that point, a typical RDBMS with more robust concurrency support starts to be a better choice. I experienced that when using SQLite and we eventually moved the application to a full RDBMS which was more complex but also performed and scaled much better. Note that this is very much in line with their recommendations from the docs (http://sqlite.org/whentouse.html http://sqlite.org/whentouse.html): >>SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem. Client/server SQL database engines strive to implement a shared repository of enterprise data. They emphasize scalability, concurrency, centralization, and control. SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity. SQLite does not compete with client/server databases. SQLite competes with fopen().<< It's worth noting that when considered as competition to fopen(), SQLite is quite good.

tiffanyh 9y ago
Fossil-SCM. I've always wondered why the creator of SQLite (who's also the creator of Fossil SCM) will state that SQLite is not meant for client/server scenarios ... yet his Fossil SCM (which is client/server) uses SQLite. https://www.fossil-scm.org https://www.fossil-scm.org
- bensummers 9y ago
  fossil on the server gets low numbers of writes, and not many reads. And being a distributed system, most of the SQLite action is on the client.
- mritun 9y ago
  SQLite does not have a client-server interface. That’s ok because the creator says it is supposed to be an embedded library that gives a better fopen interface. That is, you don’t need to invent file formats (think JSON, YAML, etc). SQLite is not just a database, it’s a persistence layer that happens to have SQL interface. It places no restrictions whatsoever on the applications that uses it. That will be pointless. Fossil-SCM uses SQLite and is itself client server.

russell_h 9y ago

OpenStack Swift (it's S3-like service) uses SQLite to store account and bucket metadata.

hasenj 9y ago

I really don't understand why majority of websites use mysql or even postgres for that matter. The vast majority of websites would run perfectly fine on sqlite. Not only would you get rid of network latency, you would also vastly reduce your dependencies. I have a side project to try to see if I can build a minimal micro-blogging type server application on sqlite and try to test how many requests it can handle per second (reads and writes). I have a hunch it will be able to handle quite a lot. Specially if the server code is written in a fast language that compiles to native code (e.g. D) with some caching of the most commonly requested items in memory.

akvadrako 9y ago
SQLite does not work smoothly in many languages (like python) in regards to concurrent access. Without a controlling process (or thread) it's synchronisation possibilities are limited, so it can never scale too far. Nonetheless I think it's a great idea to build your own single-threaded layer over SQLite and scale that, using a message bus or whatever. But it's _not_ a drop-in replacement for MySQL based apps.
- ageofwant 9y ago
  SQLAlchemy with transactions on SQLite should get you very close, probably close enough.
  - infecto 9y ago
    Why get very close when I can just spin up a single micro RDS instance of MySQL or Postgres and not have to worry about it?
    - marktangotango 9y ago
      Whoa! That's nearly $20 a month, if you're past the free tier, which you will be if you're not already. Edit that's t1.micro, t2 is $13.

alvil 9y ago

Here are some comments from SQLite itself: https://www.reddit.com/r/PHP/comments/59na74/sqlite_as_the_only_database_for_website/d99us21/ https://www.reddit.com/r/PHP/comments/59na74/sqlite_as_the_o...

evanspa 9y ago

In my strength-tracking iOS app Riker, I use SQLite directly (not CoreData) as my local data store, and support full offline-mode. The backend is Postgres. In the app, for each relation (e.g., a "workout set"), I have 2 tables: a master and a scratchpad. When a user saves a set, a row is written to the scratchpad table. When the user syncs it with the server, a row is written to the master table and deleted from the scratchpad table. When the user wants to edit the record, I first copy it down from the master table to the scratchpad table. All local editing impacts the scratchpad row. When the user wants to sync, only if a 200 response is returned will I copy-up the scratchpad row to the master row. If the set was edited on another device and the local copy is out-of-sync, the server would have responded with a 409 (http conflict code), and the body would contain the server copy, which is then written to the master table. The user can then figure how they want to merge the scratchpad row and the master row. Anyway...trying to do all this with CoreData would have been a pain, so I use SQLite directly, and works great. Or to summarize, I handle offline mode, syncing and conflict detection using "updated_at" timestamp columns along with logic in my REST API to returned appropriate HTTP status codes, interpret "if-unmodified-since" headers, etc. https://itunes.apple.com/us/app/riker/id1196920730?mt=8 https://itunes.apple.com/us/app/riker/id1196920730?mt=8 Riker on Android is currently in-progress...

lungureanu 9y ago
I find it difficult to get full offline-mode + sync working correctly. Don't know if you have duplicates in the remote db but I think your approach has a problem: the app send the data (from scratchpad table) to server, the server saves it in DB but the connection drops on device (ex bad connection, user turn off WIFI/3G while syncing) so the app will never get a 200 response. In this case your new data was saved on server but it remains on scratchpad table. On next sync the data will be uploaded as being new: this will cause duplicates.
- evanspa 9y ago
  You are right. The way I solved for this scenario is that when a record is first created on the device, a GUID is created for it (and stored using another column of course). When POSTing new records to the server for syncing, the server will check the GUID and see if the record already exists in its database, and if so, can ignore it (so the duplicate isn't written). But yes, you're right overall - full offline mode w/syncing, etc is a big pain :)

edwinyzh 9y ago

For question #1, an approach is to build a full-fledged Web/REST server wrapping around SQLite: https://github.com/synopse/mORMot https://github.com/synopse/mORMot The performance is high!

pier25 9y ago

Check out Realm: https://realm.io/ https://realm.io/