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

SQLite is not a toy database

antonz.org

Post reply on HN
Print view View original

71–80 of 364 posts

Re: SQLite is not a toy database

#71

Post by hexmiles »

ben509 wrote: post #16

> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a si…

> "No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc."

I think it has concurrent backups via the backup api: https://www.sqlite.org/backup.html

Re: SQLite is not a toy database

#72

Post by smilliken »

candiddevmike wrote: post #41

I wish SQLite had a PostgreSQL compatibility layer. Right now, to add SQLite support to a Go project (or anything without an ORM), you have to rework all your queries and migrations. It's probably an impossible ask, but having a compatibility flag within SQLite so it would accept PostgreSQL formatted queries would be extremely helpful.

Abstracting over two complex and evolving systems is a bottomless well of bugs. Contributers would shy away from this sort of compatibility because it would encourage users to ignore the differences at first, and then complain loudly when they are discovered.

Re: SQLite is not a toy database

#73

Post by duskwuff »

eingaeKaiy8ujie wrote: post #66
gwd wrote: post #46

Earlier quoted context omitted.

The article addresses this. Basically, you can have any number of concurrent readers , but only a single writer . Writing and reading can happen concurrently just fine. So the question is -- how many users does a website need before having only a single concurrent writer becomes a bottleneck? That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where [ED…

>That number will obviously depend on the read/write ratio of any given website; but it's hard to imagine any website where that number is actually "1". I can imagine a static website where the content is read-only for users and is only editable by admins/developers/content managers through some CMS.

That'd be a near-infinite ratio. The parent is discussing a site where the ratio is near 1 (i.e. roughly as many reads as writes).

Re: SQLite is not a toy database

#74

Post by mywittyname »

Thaxll wrote: post #28
busterarm wrote: post #25

Earlier quoted context omitted.

Most websites/frameworks access their database through a singleton pattern/single-connection anyway. Edit: Sometimes you have to lie and lead people down the wrong path to enlightenment... ;)

That's not the case no, you usually access your database with thread pool. Otherwise everyone would wait until the single connection is free. Once you have a bit more users that tries to write everything will fall appart.

One could implement a write queue and caching for the front end. That might sound like a lot of work to go through to avoid moving to a proper multi-user database. But given that writes are probably done through a handful of API endpoints, it's probably not really that much work to implement.

Re: SQLite is not a toy database

#75

Post by gwd »

nalgeon wrote: post #51
ben509 wrote: post #16

> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a si…

I think that the absence of 'high availability' is not an issue for small websites or web apps. Transactions are ACID, concurrent readers are fully supported. Backups and administrative tasks are super-easy.

I just looked, and the entire database for my webapp (which typically serves around ~50 people) is 139k. As you say, you can do a data dump quickly and safely; at that size, you could afford to just dump the entire DB every hour and keep the last year's worth of snapshots if you wanted.

Re: SQLite is not a toy database

#77

Post by II2II »

hackeraccount wrote: post #35

Is it the unix version of MS Access? It's sort of interesting to compare the two.

SQLite is a database that is intended to be embedded in applications rather than run as a separate server. You could make an Access-like application from it, but it cannot replace Access on its own.

It's commonly used in applications that use it as a convenient container to store data (the "file format" scenario) or as a domain specific database. One of the better examples of the latter is Calibre, an e-book library manager, since it exposes some of the database functionality to the end user. For example: the end user can add columns to store custom data for each book.

Re: SQLite is not a toy database

#78

Post by quesera »

OldHand2018 wrote: post #58
Thaxll wrote: post #28

Earlier quoted context omitted.

That's not the case no, you usually access your database with thread pool. Otherwise everyone would wait until the single connection is free. Once you have a bit more users that tries to write everything will fall appart.

Well, let me throw out a crazy idea: Nobody said you have to use a single database/file. Obviously, you are going to want to spend a couple minutes thinking about referential integrity. But how often do you delete records in your web app?

UPDATE is also a write, and references crossing files sounds like a nightmare.

If your deployment environment has serious constraints, I'm sure you could make it work. The product you deliver would be SQLite + custom DBI layer to hide SQLite's limitations.

It would be a lot more work, and not be as robust or scalable, compared to a more traditional selection. But I can imagine cases where it would be appropriate.

Re: SQLite is not a toy database

#79

Post by TeMPOraL »

bob1029 wrote: post #4

Don't forget about User-Defined Functions. https://www.sqlite.org/appfunc.html We just started enhancing our SQL dialect with new functions which are implemented in C# code. One of them is an aggregate and it is really incredible to see how it simplifies projections involving multiple rows. One huge benefit of SQLite's idea of UDFs is that you can actually set breakpoints and debug them as SQL is executing.

They're awesome!

One common use case is REGEXP. SQLite has a keyword for regular expression matching, but it has no implementation for it. What your application needs to do is to take whatever regex library it's using, make (through whatever FFI method it's using) a C function that interfaces with it, and register it with SQLite.

A more advanced feature of this binding mechanism is that, if you provide a bunch of specific callbacks to SQLite, you can expose anything you like as a virtual table, that can be queried and operated on as if it was just another SQLite table.

See: https://www.sqlite.org/vtab.html. The plugin for full-text search is implemented in terms of this mechanism.

Re: SQLite is not a toy database

#80

Post by vbsteven »

Toutouxc wrote: post #45

> not bothering with optimization (≈200 requests per page) What amount of SQL queries per page render is considered sensible? When I run more than 20 queries per request in my Rails apps (smallish internal tools for different companies) I get uneasy. I usually deploy the app on the same machine where the DB (not SQLite) runs, but I imagine if that weren't the case the app-DB roundtrips could soon dominate the whole t…

When using an external db every query needs to cross the connection boundary and thus perform network IO and deal with the threading implications for that IO. With SQLite running in-process a round trip just means a function call, and when data is available in the cache it won’t even hit the disk.
Post reply on HN