Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite the only database you will ever need in most cases (2021)

unixsheikh.com

Post reply on HN
Print view View original

81–90 of 378 posts

Re: SQLite the only database you will ever need in most cases (2021)

#81

Post by bagels »

adamkf wrote: post #10

>The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. This caveat covers "most cases". If there's only a single machine, then any data stored is not durable. Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchr…

My standard for any serious service is at least minimal redundancy for improved availability during failures. At least two webservers.

Re: SQLite the only database you will ever need in most cases (2021)

#82

Post by jameshart »

Back in 1999 or so, before SQLite was a thing, I used to throw little ASP websites together with an MS access .mdb file as the backend connected up through ODBC.

It was neat and quick and easy to get running right on your regular windows desktop.

By the criteria of this article, that was apparently the only database I ever needed. It could handle the multiple reads and occasional write of a small scale website. Backing it up consisted of copying the file. It was accessed through a simple standard library (ODBC) and supported SQL.

Since that was the only database we needed, what does SQLite bring to the table?

Re: SQLite the only database you will ever need in most cases (2021)

#83

Post by jll29 »

hammock wrote: post #49

I used to use SQL Server on a PC to deal with tables over 1 million rows. I'm on a Mac now... can I use SQL Lite? What is my best option? I am not a coder, I just know enough how to query SQL databases.

The Mac OS download is also available from https://www.sqlite.org/download.html - and in any case SQLite is a single-file C program, so you could easily compile your own (but no need for the Mac).

Re: SQLite the only database you will ever need in most cases (2021)

#84

Post by xenator »

deathclassic wrote: post #65
xwdv wrote: post #37

Why learn SQLite when you could just learn Postgres and have a database that is virtually guaranteed to be enough in almost all cases?

SQLite has other advantages over larger db systems. - By far easiest db to install. - Really go to learn database fundamentals with. If you have no experience with databases and are just starting out programming, PG is going to steepen the learning curve substantially.

> By far easiest db to install.

The difference between installation for sqlite and postgres is the difference between typing `apt install sqlite3` and `apt install postgres`.

The actual learning curve for sqlite is more complex because picking random sql queries on the internet will end in misunderstanding in underlying types in sqlite and broken data. Postgres tools ecosystem is much more expressive, because more people think about Postgres as about "real" database.

Re: SQLite the only database you will ever need in most cases (2021)

#85

Post by christophilus »

onlypositive wrote: post #3

> I have run SQLite as a web application database with thousands concurrent writes every second, coming from different HTTP requests, without any delays or issues. Is this with nodejs or something single threaded as the webserver? I would kind of assume you'd run into issues with something like PHP.

In production, Node is generally run in clustered mode (multiple threads per instance). SQLite works fine with multithreaded applications, if you set a longish timeout for acquiring a file lock.

It does work better when all of your writes are on a single thread, though. I used Node’s IPC to accomplish this, and was able to get it up to 10k or so writes per second while still having it do tens of thousands of simple reads per second.

Re: SQLite the only database you will ever need in most cases (2021)

#86

Post by bogwog »

irskep wrote: post #29

This sentiment pops up regularly on HN, and I've seen at least one article per month for the past few months, but the trouble is, none of them seem to help you actually deploy it. They assume you're comfortable spinning up public web servers. If you want to use a PaaS to deploy an app, because you don't want to spend your time learning to be a sysadmin, then all the tutorials are going to put you on the Postgres path…

> I'm an application developer and I do not want to become a release engineer. I resent even having to learn Docker. :-)

Sorry, but that's a terrible attitude to have. You don't need to be a full-blown sysadmin to know how to do basic deployments, and learning these things will make you a better developer.

Re: SQLite the only database you will ever need in most cases (2021)

#87

Post by chillfox »

margorczynski wrote: post #21

Yeah, it works when it works. Just that in many cases you'll run into scenarios with it when it completely doesn't or is missing something crucial and we'll get another prodigal son story about going back to Postgres.

Having learnt SQLite before PostgreSQL I get that experience every so often with PostgreSQL... Both have got nice features that the other does not and what you got used to is going to determine what you miss when picking up the other.

Re: SQLite the only database you will ever need in most cases (2021)

#88

Post by irskep »

cldellow wrote: post #75
bravura wrote: post #61

Earlier quoted context omitted.

Thank you for the thoughtful response. I was looking at https://github.com/irskep/cheapo_website from commenter irskep above, and they make a nice point that render.com has automatic daily backups, solving 4) However, in another comment they mention "You can't(?) run migrations from another process" and that "people don't talk about the completely ordinary need to run migrations on a database". I guess this is also t…

You can run migrations from another process. Migrations are just writes, and SQLite supports writes from multiple processes. A single transaction that does a very large write will likely impact the reader -- the reader will be blocked while the write finishes. I use SQLite in a web scraper on my laptop. The scraper runs as 16 processes hammering the database, doing about 5,000 write transactions/sec. Occasionally, si…

> You can run migrations from another process. Migrations are just writes, and SQLite supports writes from multiple processes.

Thank you for explicitly saying this! I'll see if I can update my repo to allow for online migrations. I was trying to be as conservative as possible based on what I knew at the time. It'll be nice to update the migration steps to be simpler.

Re: SQLite the only database you will ever need in most cases (2021)

#89

Post by canadiantim »

irskep wrote: post #29

This sentiment pops up regularly on HN, and I've seen at least one article per month for the past few months, but the trouble is, none of them seem to help you actually deploy it. They assume you're comfortable spinning up public web servers. If you want to use a PaaS to deploy an app, because you don't want to spend your time learning to be a sysadmin, then all the tutorials are going to put you on the Postgres path…

Note with Neon now you don't need to spend $15+/mo for Postgres because they separate compute from storage. So compute can scale down to 0 and storage is cheap.

Re: SQLite the only database you will ever need in most cases (2021)

#90

Post by guluarte »

RcouF1uZ4gsC wrote: post #12
adamkf wrote: post #10

>The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. This caveat covers "most cases". If there's only a single machine, then any data stored is not durable. Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchr…

https://litestream.io/ does streaming replication to S3 (or similar service). With this, you probably have better data durability than a small database cluster.

litestream is more for data recovery, for replication LiteFS is better.
Post reply on HN