6 ms·

> SQLite is serverless. Maybe if you use SQLite as a file format. But if you use it like an actual database (e.g. in a web application), I find that one is bes

> SQLite is serverless. Maybe if you use SQLite as a file format. But if you use it like an actual database (e.g. in a web application), I find that one is best off setting up a daemon thread to queue/batch transactions.

code-scope 6y ago
I use a different pattern. A lot sqlite files for diff purposes (UserSession, User Files, etc each store in separate files) This way diff threads of webserver can open/query/read/write a lot of files concurrently without any issue.
- saalweachter 6y ago
  I, too, enjoy creating deadlocks. [I assume if you are using this pattern successfully in production you are already aware of and taking proper steps to avoid them, but the pedant in me takes issue with "without any issue", since once you have multiple resources being locked in multiple threads, you need to be careful to acquire and release locks in such a way that deadlocks do not occur, either never acquiring more than one lock at a time, acquiring locks in specific orders, or acquiring batches of locks at a time.]
- snicker7 6y ago
  > without any issue It's easier than you think to corrupt SQLite if you access from multiple threads and especially from multiple processes (yup, I've done this before). Also, there are no concurrent transactions in SQLite. The entire db file gets locked (using POSIX locking, which is known to be broken [0]). Better to queue/batch transactions on a single connection. If your web server consists of multiple processes, then this requires a separate daemon. [0]: http://0pointer.de/blog/projects/locking.html http://0pointer.de/blog/projects/locking.html
II2II 6y ago
One of the disappointing developments of the past quarter century is the near demise of general purpose databases as an end user application. Yes, client/server models are useful when dealing with a large number of transactions. On the other hand, it is usually too complex to justify for personal or small office use. I miss the days when databases were included in office suites or could be purchased as relatively inexpensive standalone applications since it was easy to create a database and associated forms, rather than depending upon domain specific applications that use a database as a file format but may be poorly suited for a particular scenario. (Yes, the phrasing "file format" verses "actual database" rubbed me the wrong way.) Edit: I realized that my post comes off as dismissive of SQLite, which is not entirely true. I use it as a database and appreciate it's role, but embedding it in an application is frequently more than a given application requires.
- fpoling 6y ago
  Access DB is still included in Microsoft Office
  - OldHand2018 6y ago
    Only some versions of Office, mainly the non-cheapest business versions.
  - zokier 6y ago
    And LibreOffice has Base, a sadly often neglected component.
    - II2II 6y ago
      Another issue with Base is the required JRE. Installing the JRE may be trivial, but it means that an error message is the first experience most people will have with Base. As for Access, it's access is limited due to the much higher price point of Business/Professional versions of Office. In terms of office suites, that version is about two to three times more expensive than an equivalent office suite from the mid-1990's (adjusted to 2021 dollars). In other words, people will only have Access if they feel that the additional cost is justified. That additional cost may be fine for business use, yet it also means that people have less exposure to databases to start with. With respect to exposure, there also appears to be an absence of general purpose databases for home users these days. (By that I mean in terms of cost and ease of use.)
      - zokier 6y ago
        > Another issue with Base is the required JRE I thought that they dropped JRE requirement when the engine was switched to Firebird? Googling around apparently the transition wasn't quite successful :( https://ask.libreoffice.org/en/question/279711/firebird-dead/ https://ask.libreoffice.org/en/question/279711/firebird-dead...
        II2II 6y ago

code-scope 6y ago

I use a different pattern. A lot sqlite files for diff purposes (UserSession, User Files, etc each store in separate files) This way diff threads of webserver can open/query/read/write a lot of files concurrently without any issue.

saalweachter 6y ago
I, too, enjoy creating deadlocks. [I assume if you are using this pattern successfully in production you are already aware of and taking proper steps to avoid them, but the pedant in me takes issue with "without any issue", since once you have multiple resources being locked in multiple threads, you need to be careful to acquire and release locks in such a way that deadlocks do not occur, either never acquiring more than one lock at a time, acquiring locks in specific orders, or acquiring batches of locks at a time.]
snicker7 6y ago
> without any issue It's easier than you think to corrupt SQLite if you access from multiple threads and especially from multiple processes (yup, I've done this before). Also, there are no concurrent transactions in SQLite. The entire db file gets locked (using POSIX locking, which is known to be broken [0]). Better to queue/batch transactions on a single connection. If your web server consists of multiple processes, then this requires a separate daemon. [0]: http://0pointer.de/blog/projects/locking.html http://0pointer.de/blog/projects/locking.html

II2II 6y ago

One of the disappointing developments of the past quarter century is the near demise of general purpose databases as an end user application. Yes, client/server models are useful when dealing with a large number of transactions. On the other hand, it is usually too complex to justify for personal or small office use. I miss the days when databases were included in office suites or could be purchased as relatively inexpensive standalone applications since it was easy to create a database and associated forms, rather than depending upon domain specific applications that use a database as a file format but may be poorly suited for a particular scenario. (Yes, the phrasing "file format" verses "actual database" rubbed me the wrong way.) Edit: I realized that my post comes off as dismissive of SQLite, which is not entirely true. I use it as a database and appreciate it's role, but embedding it in an application is frequently more than a given application requires.

fpoling 6y ago
Access DB is still included in Microsoft Office
- OldHand2018 6y ago
  Only some versions of Office, mainly the non-cheapest business versions.
- zokier 6y ago
  And LibreOffice has Base, a sadly often neglected component.
  - II2II 6y ago
    Another issue with Base is the required JRE. Installing the JRE may be trivial, but it means that an error message is the first experience most people will have with Base. As for Access, it's access is limited due to the much higher price point of Business/Professional versions of Office. In terms of office suites, that version is about two to three times more expensive than an equivalent office suite from the mid-1990's (adjusted to 2021 dollars). In other words, people will only have Access if they feel that the additional cost is justified. That additional cost may be fine for business use, yet it also means that people have less exposure to databases to start with. With respect to exposure, there also appears to be an absence of general purpose databases for home users these days. (By that I mean in terms of cost and ease of use.)
    - zokier 6y ago
      > Another issue with Base is the required JRE I thought that they dropped JRE requirement when the engine was switched to Firebird? Googling around apparently the transition wasn't quite successful :( https://ask.libreoffice.org/en/question/279711/firebird-dead/ https://ask.libreoffice.org/en/question/279711/firebird-dead...
      - II2II 6y ago