8 ms·

I think it's not used widely enough yet. For example, 99% of websites could benefit from using SQLite instead of MySql or (god forbid) PostgreSQL. I mean Postg

by hasenj 9y ago

I think it's not used widely enough yet. For example, 99% of websites could benefit from using SQLite instead of MySql or (god forbid) PostgreSQL. I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite.

deleted 9y ago
[deleted]
hardwaresofton 9y ago
So I'm actually doing this right now, I write about it on my blog. None of the apps I've written have hitten any kind of crazy traffic peak, so I started wondering why the hell I was using postgres or other database types after reading https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html. Seeing how simple SQLite has been for me to use has inspired me to write a bunch of dead-simple good-enough approximations for other tools that exist. High up on my list is a Graylog competitor that just uses SQLITE FTS (https://sqlite.org/fts3.html/https://sqlite.org/fts5.html https://sqlite.org/fts3.html/https://sqlite.org/fts5.html), that just is super easy to start and handles that micro-to-mid-size case before you really need something like graylog or ELK. Blog post if anyone's interested: https://vadosware.io/post/adding-sqlite-powered-fts-search-to-a-servant-powered-haskell-app/ https://vadosware.io/post/adding-sqlite-powered-fts-search-t...
- xellisx 9y ago
  How does SQLite FTS compare to things like Sphinx and Elastic Search?
  - hardwaresofton 9y ago
    Probably not well, in the long run? FTS is just a plugin to SQLite where as Sphinx and ES are purpose-built software. It all comes down to what your actual need is though. I think for most smal projects SQLite + FTS offers a pretty simple easy to setup solution that doesn't require much compute power, either. The simplicity is also a bonus.
- the_duke 9y ago
  FTFY: https://sqlite.org/fts5.html https://sqlite.org/fts5.html
  - hardwaresofton 9y ago
    Unfortunately I can't edit the comment anymore, but I meant to put both links there, and made the sily mistake of putting a "/" between them...
flatline 9y ago
Sqlite is great but it is a poor substitute for a "real" database where you need any serious constraint enforcement at the database level. Yes, you can enable RI, but it's off by default. There is no field length constraint on text values, no native datetime, boolean, or guid types. You can't structurally alter tables without building a new one and copying the old. If you actually need any of these things you can enforce them in code, by convention, but you're better off installing Postgres. Also writes are serialized. That one really can't be worked around.
- akira2501 9y ago
  > There is no field length constraint on text values SQLite has supported CHECK() for a while. So, for example, you can do: CHECK(LENGTH(col) IS BETWEEN 1 AND 16) > Also writes are serialized. Across multiple processes, but there is a shared cache and read uncommitted mode available otherwise.
- zackelan 9y ago
  > Sqlite is great but it is a poor substitute for a "real" database This is true, and explicitly acknowledged by the author: > SQLite does not compete with client/server databases. SQLite competes with fopen(). https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html If you're deciding between Postgres and SQLite, in most cases you're doing it wrong (there are a few exceptions, such as serving a low-traffic almost-entirely-read dynamic website can be accomplished easily with either). Usually you want to choose between SQLite and "anything else that involves writing directly to the local filesystem".
  - mwexler 9y ago
    I would disagree. The proper question is: "do I need a structured RDBMS or not?" Then if yes, which should I use? Just saying "SQLite!" starts to sound like the Regex quote from 1997: https://blog.codinghorror.com/regular-expressions-now-you-have-two-problems/ https://blog.codinghorror.com/regular-expressions-now-you-ha... A SQL system can do many wonderous things, but there's a wide range of pain and benefit across the different systems. Think about requirements first, then tech/implementation approach. But yeah, like everyone else, I often just start with SQLite anyway!
xellisx 9y ago
Of course you have sites built with bloated software, like Wordpress...
- h1d 9y ago
  Only if it were just bloated... Global variables to this day.
  - xellisx 9y ago
    That too, and no code/"view" separation.
danielvf 9y ago
SQLite is absolutely fantastic, but I’ve twice used on sites where I would have used a real DB and twice needed to switch back to a real DB after a few months. The concurrency model just doesn’t match up well with multiple web processes doing work. If you are writing on every request, Murphy’s law says that too many of those 500 visitors will be loading pages at exactly the same time...
- hasenj 9y ago
  It's really weird to have to write on every request. If you're logging user visits for example, you can queue those up in a list (in the code) and flush it to the database every few seconds instead of flushing it on every visit.
  - dotancohen 9y ago
    > queue those up in a list (in the code) That won't work in the language that most server-side website code is written in. PHP processes are created and destroyed per HTTP request, and even the FastCGI implementation won't let one share data across requests. PHP does have a sessions feature, but that data is written to either the filesystem (by default) or a database on every request!
    - hasenj 9y ago
      Yes but if you use SQLite because you value minimalism you would also choose a language that compiles the server to a statically linked binary, e.g. Go, in which case this is a trivial "optimization" to do (if you would even call it that).
      - Can_Not 9y ago
        I've ran sites in the ~500 users per day category where MySQL and postgre use nearly nothing in resources while user interaction causes complex transactions and competing row-level locks. Sounds pretty minimal to me. Odd though that someone who values golang would want to use DB with a poor reputation for concurrency.
interfixus 9y ago
Despite a lot of habitual nay-sayers, this is absolutely correct. The vast majority of sites simply do not need the black-boxery of handing stuff off to external storage. SQLite will carry the load just fine, and not only be as good as, but an actual improvement. I have written fairly large web-apps taking fairly decent traffic, and relying solely on SQLite, unless really pressing circumstances required otherwise. Healthy exercise too: Sharpens your focus, "Do I absolutely need to do a write here?".
wongarsu 9y ago
For me, using PostgreSQL is like using static typing: a bit more work, but it catches a lot of bugs. SQLite favours a very permissive approach, while PostgreSQL favours locking everything down with strict types and strong integrity checks, and it's very easy to verify lots of details of your data before it's accepted into the database. That takes work to set up properly and maintain, but it catches bugs early and reduces the number of headaches you get when trying to use your data in new code. Scalability is a very different problem that happens to favour PostgreSQL, but most projects indeed never get far enough that this matters.
- hasenj 9y ago
  Strictly speaking, SQLite is dynamically typed in a sense, because the database does not enforce the values in the rows match what the columns claim about the type. But from a developer experience point of view, the main difference between dynamic and static typing is the following: Dynamic typing requires a lot of extra checks to ensure everything is what you expect it to be, and a lot of annotations to document what everything is supposed to be. In other words, dynamic typing has a high cognitive tax. With this in mind, I find that PostgreSQL imposes a cognitive tax in terms of maintaining a separate process (potentially on a different machine) that a lot of times is outside your direct control. Not to mention all the strange ways in which it can break. SQLite just works. There's nothing to configure. So there's a lot less cognitive tax. There is still of course the tax of having to ensure that all your sql statements are valid and that they return what you expect them to return, but this is also the case with PostgreSQL.
  - TeMPOraL 9y ago
    I agree with your point, but disagree with the example. RE static vs dynamic typing, I'd say that it's dynamic typing that has higher cognitive tax - you as a programmer are fully responsible for ensuring types agree up everywhere, whereas with static typing, all of that job is done by compiler. It's easier to change stuff in the code when you know the compiler will catch your dumb mistakes. And I say that as someone who loves writing in Common Lisp. But the point about SQLite vs a typical RDBMS is spot-on, IMO. SQLite is a library. It operates on files. That's it. It's entirely local. You can use it without having to become a sysadmin, without having to set up a whole service on the OS on which you might not even have root privileges anyway. You don't have to make global changes to the system just for your program. Moreover, if your product is of the distributable kind (desktop apps, self-hosting web stuff) you don't need to make your users become sysadmins, manage a system-wide service, acquire root rights they might not have, etc. SQLite is local. Its data is local. That's, IMO, its strongest benefit.
bschwindHN 9y ago
Why a (god forbid) for PostgreSQL but not MySQL? I've used both and compared to SQLite they have around the same setup complexity.
- hasenj 9y ago
  Last time I used MySQL was more than 10 years ago, but my impression from back then is that it's more beginner friendly and easy to setup and use than Postgres.
  - dragonwriter 9y ago
    More than 10 years ago, that was true. Not so much for quite a while, though.
daveguy 9y ago
> I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite. On the flip side, setting up a PostgreSQL instance is so easy now with docker that you could go ahead and use it with a fraction of the admin overhead that it used to require: https://hub.docker.com/_/postgres/ https://hub.docker.com/_/postgres/
- bane 9y ago
  The running docker image will still need many more resources than opening up a SQLite dB and running a few queries.
  - daveguy 9y ago
    True, it's not as low resource as a single file. But with the official Alpine based images it's not too bad. For self contained app storage definitely would go with sqlite, but any database that needs multiple users (like a service website even if it doesn't have many users yet) I would go with postgres.
- hasenj 9y ago
  Docker is its own nightmare :/ I just find it weird and confusing. Also, how do you setup a database in docker? Docker does not support persisting data as far as I know. So if you need to restart the thing, all the data will be wiped. At least that's how it seems to be.
  - echlebek 9y ago
    You are meant to use a storage driver such as aufs or overlay with an attached volume to persist information between container lifetimes. There are a variety of opinions on the goodness of using docker for running a database.
    - hasenj 9y ago
      Which translates to: I basically don't want to use it. At least not for development. Not as a means to abstract away the setup of something like Postgres; Docker has its own complications when it comes to setting things up, which I must learn _in addition_ to learning everything related to setting up Postgres.
golergka 9y ago
I'm trying to follow current news and knowledge on SQL databases, but 99% of my career was on the client-side, so I lack practical knowledge. How easy it is, in practice, to switch from one SQL database to another?

by hasenj 9y ago

deleted 9y ago

[deleted]

hardwaresofton 9y ago

So I'm actually doing this right now, I write about it on my blog. None of the apps I've written have hitten any kind of crazy traffic peak, so I started wondering why the hell I was using postgres or other database types after reading https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html. Seeing how simple SQLite has been for me to use has inspired me to write a bunch of dead-simple good-enough approximations for other tools that exist. High up on my list is a Graylog competitor that just uses SQLITE FTS (https://sqlite.org/fts3.html/https://sqlite.org/fts5.html https://sqlite.org/fts3.html/https://sqlite.org/fts5.html), that just is super easy to start and handles that micro-to-mid-size case before you really need something like graylog or ELK. Blog post if anyone's interested: https://vadosware.io/post/adding-sqlite-powered-fts-search-to-a-servant-powered-haskell-app/ https://vadosware.io/post/adding-sqlite-powered-fts-search-t...

xellisx 9y ago
How does SQLite FTS compare to things like Sphinx and Elastic Search?
- hardwaresofton 9y ago
  Probably not well, in the long run? FTS is just a plugin to SQLite where as Sphinx and ES are purpose-built software. It all comes down to what your actual need is though. I think for most smal projects SQLite + FTS offers a pretty simple easy to setup solution that doesn't require much compute power, either. The simplicity is also a bonus.
the_duke 9y ago
FTFY: https://sqlite.org/fts5.html https://sqlite.org/fts5.html
- hardwaresofton 9y ago
  Unfortunately I can't edit the comment anymore, but I meant to put both links there, and made the sily mistake of putting a "/" between them...

flatline 9y ago

Sqlite is great but it is a poor substitute for a "real" database where you need any serious constraint enforcement at the database level. Yes, you can enable RI, but it's off by default. There is no field length constraint on text values, no native datetime, boolean, or guid types. You can't structurally alter tables without building a new one and copying the old. If you actually need any of these things you can enforce them in code, by convention, but you're better off installing Postgres. Also writes are serialized. That one really can't be worked around.

akira2501 9y ago
> There is no field length constraint on text values SQLite has supported CHECK() for a while. So, for example, you can do: CHECK(LENGTH(col) IS BETWEEN 1 AND 16) > Also writes are serialized. Across multiple processes, but there is a shared cache and read uncommitted mode available otherwise.
zackelan 9y ago
> Sqlite is great but it is a poor substitute for a "real" database This is true, and explicitly acknowledged by the author: > SQLite does not compete with client/server databases. SQLite competes with fopen(). https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html If you're deciding between Postgres and SQLite, in most cases you're doing it wrong (there are a few exceptions, such as serving a low-traffic almost-entirely-read dynamic website can be accomplished easily with either). Usually you want to choose between SQLite and "anything else that involves writing directly to the local filesystem".
- mwexler 9y ago
  I would disagree. The proper question is: "do I need a structured RDBMS or not?" Then if yes, which should I use? Just saying "SQLite!" starts to sound like the Regex quote from 1997: https://blog.codinghorror.com/regular-expressions-now-you-have-two-problems/ https://blog.codinghorror.com/regular-expressions-now-you-ha... A SQL system can do many wonderous things, but there's a wide range of pain and benefit across the different systems. Think about requirements first, then tech/implementation approach. But yeah, like everyone else, I often just start with SQLite anyway!

xellisx 9y ago

Of course you have sites built with bloated software, like Wordpress...

h1d 9y ago
Only if it were just bloated... Global variables to this day.
- xellisx 9y ago
  That too, and no code/"view" separation.

danielvf 9y ago

SQLite is absolutely fantastic, but I’ve twice used on sites where I would have used a real DB and twice needed to switch back to a real DB after a few months. The concurrency model just doesn’t match up well with multiple web processes doing work. If you are writing on every request, Murphy’s law says that too many of those 500 visitors will be loading pages at exactly the same time...

hasenj 9y ago
It's really weird to have to write on every request. If you're logging user visits for example, you can queue those up in a list (in the code) and flush it to the database every few seconds instead of flushing it on every visit.
- dotancohen 9y ago
  > queue those up in a list (in the code) That won't work in the language that most server-side website code is written in. PHP processes are created and destroyed per HTTP request, and even the FastCGI implementation won't let one share data across requests. PHP does have a sessions feature, but that data is written to either the filesystem (by default) or a database on every request!
  - hasenj 9y ago
    Yes but if you use SQLite because you value minimalism you would also choose a language that compiles the server to a statically linked binary, e.g. Go, in which case this is a trivial "optimization" to do (if you would even call it that).
    - Can_Not 9y ago
      I've ran sites in the ~500 users per day category where MySQL and postgre use nearly nothing in resources while user interaction causes complex transactions and competing row-level locks. Sounds pretty minimal to me. Odd though that someone who values golang would want to use DB with a poor reputation for concurrency.

interfixus 9y ago

Despite a lot of habitual nay-sayers, this is absolutely correct. The vast majority of sites simply do not need the black-boxery of handing stuff off to external storage. SQLite will carry the load just fine, and not only be as good as, but an actual improvement. I have written fairly large web-apps taking fairly decent traffic, and relying solely on SQLite, unless really pressing circumstances required otherwise. Healthy exercise too: Sharpens your focus, "Do I absolutely need to do a write here?".

wongarsu 9y ago

For me, using PostgreSQL is like using static typing: a bit more work, but it catches a lot of bugs. SQLite favours a very permissive approach, while PostgreSQL favours locking everything down with strict types and strong integrity checks, and it's very easy to verify lots of details of your data before it's accepted into the database. That takes work to set up properly and maintain, but it catches bugs early and reduces the number of headaches you get when trying to use your data in new code. Scalability is a very different problem that happens to favour PostgreSQL, but most projects indeed never get far enough that this matters.

hasenj 9y ago
Strictly speaking, SQLite is dynamically typed in a sense, because the database does not enforce the values in the rows match what the columns claim about the type. But from a developer experience point of view, the main difference between dynamic and static typing is the following: Dynamic typing requires a lot of extra checks to ensure everything is what you expect it to be, and a lot of annotations to document what everything is supposed to be. In other words, dynamic typing has a high cognitive tax. With this in mind, I find that PostgreSQL imposes a cognitive tax in terms of maintaining a separate process (potentially on a different machine) that a lot of times is outside your direct control. Not to mention all the strange ways in which it can break. SQLite just works. There's nothing to configure. So there's a lot less cognitive tax. There is still of course the tax of having to ensure that all your sql statements are valid and that they return what you expect them to return, but this is also the case with PostgreSQL.
- TeMPOraL 9y ago
  I agree with your point, but disagree with the example. RE static vs dynamic typing, I'd say that it's dynamic typing that has higher cognitive tax - you as a programmer are fully responsible for ensuring types agree up everywhere, whereas with static typing, all of that job is done by compiler. It's easier to change stuff in the code when you know the compiler will catch your dumb mistakes. And I say that as someone who loves writing in Common Lisp. But the point about SQLite vs a typical RDBMS is spot-on, IMO. SQLite is a library. It operates on files. That's it. It's entirely local. You can use it without having to become a sysadmin, without having to set up a whole service on the OS on which you might not even have root privileges anyway. You don't have to make global changes to the system just for your program. Moreover, if your product is of the distributable kind (desktop apps, self-hosting web stuff) you don't need to make your users become sysadmins, manage a system-wide service, acquire root rights they might not have, etc. SQLite is local. Its data is local. That's, IMO, its strongest benefit.

bschwindHN 9y ago

Why a (god forbid) for PostgreSQL but not MySQL? I've used both and compared to SQLite they have around the same setup complexity.

hasenj 9y ago
Last time I used MySQL was more than 10 years ago, but my impression from back then is that it's more beginner friendly and easy to setup and use than Postgres.
- dragonwriter 9y ago
  More than 10 years ago, that was true. Not so much for quite a while, though.

daveguy 9y ago

> I mean Postgres is a fine piece of software but if your website gets 500 visits a day, you don't need Postgres; just use SQLite. On the flip side, setting up a PostgreSQL instance is so easy now with docker that you could go ahead and use it with a fraction of the admin overhead that it used to require: https://hub.docker.com/_/postgres/ https://hub.docker.com/_/postgres/

bane 9y ago
The running docker image will still need many more resources than opening up a SQLite dB and running a few queries.
- daveguy 9y ago
  True, it's not as low resource as a single file. But with the official Alpine based images it's not too bad. For self contained app storage definitely would go with sqlite, but any database that needs multiple users (like a service website even if it doesn't have many users yet) I would go with postgres.
hasenj 9y ago
Docker is its own nightmare :/ I just find it weird and confusing. Also, how do you setup a database in docker? Docker does not support persisting data as far as I know. So if you need to restart the thing, all the data will be wiped. At least that's how it seems to be.
- echlebek 9y ago
  You are meant to use a storage driver such as aufs or overlay with an attached volume to persist information between container lifetimes. There are a variety of opinions on the goodness of using docker for running a database.
  - hasenj 9y ago
    Which translates to: I basically don't want to use it. At least not for development. Not as a means to abstract away the setup of something like Postgres; Docker has its own complications when it comes to setting things up, which I must learn _in addition_ to learning everything related to setting up Postgres.

golergka 9y ago

I'm trying to follow current news and knowledge on SQL databases, but 99% of my career was on the client-side, so I lack practical knowledge. How easy it is, in practice, to switch from one SQL database to another?