The thought that devs believe javascript would ever be fast enough to make sqlite actually need to run in parallel makes me giggle.
SQLite is not a toy database
241–250 of 364 posts
Re: SQLite is not a toy database
#242From the SQLite website:
Situations Where A Client/Server RDBMS May Work Better Client/Server Applications
If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.
A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.
High-volume Websites
SQLite will normally work fine as the database backend to a website. But if the website is write-intensive or is so busy that it requires multiple servers, then consider using an enterprise-class client/server database engine instead of SQLite.
Very large datasets
An SQLite database is limited in size to 281 terabytes (248 bytes, 256 tibibytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.
High Concurrency
SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.
Re: SQLite is not a toy database
#243> 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…
From the dbadmin POV and understandable. From the dev side, if you want to take a stab at your own failover, shards, concurrents and snaps, there may be no better platform to learn on. Writing a custom backend to do tricks with sqlite dlls is extremely satisfying.
Re: SQLite is not a toy database
#244Earlier quoted context omitted.
Clicking the above link takes me to https://imgur.com/32R3qLv . I fail to understand why have a blog at all if its author don't like people linking to it.
How strange, sorry, I didn't know that it did that. Just updated it to an archive link. This is the only source I know of that gives an overview of closures.c so it's worth the read.
Definitely not your fault :)
Re: SQLite is not a toy database
#245It's a shame Nextcloud works terribly with SQLite
Re: SQLite is not a toy database
#246Just went looking, there doesn't seem to be a great actively developed solution for a Java implementation or JNI bridge. The JDBC driver that seemed to have popularity was brought into the SQLite repo, but the documentation is lacking a bit. [1] Even .Net Core has a integrated ADO.NET bridge to SQLite [2]
There are quite a few mature embedded DB options for Java actively developed, for example Apache Derby/HSQLDB/H2. The SQLite fileformat is very portable, which is very handy and useful, the SQLite C Library is very powerful. It'd be nice for the Java community to tap into that power.
1) https://www.sqlite.org/java/file?name=doc/overview.html&ci=t...
2) https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite...
Re: SQLite is not a toy database
#247Earlier quoted context omitted.
SQLite underpins large parts of both iOS and Android, so... all of them? Whether you can serialize "an entire database" just depends on the size of the database and whether it will fit in the local storage. SQLite scales up to a few TB at least, so that is unlikely to be the bottleneck.
I don't fully get it -- this is an SQlite implementation in JavaScript, which is different from what you'd use in a native app. Unless you store it to localStorage or cookies, all variables disappear when you navigate away from the page. Can you write an offline HTML5 webapp with any of these libraries such that it can cerealize the entire database into a string and then store that to localStorage and reload the next…
Really you'd want to do this in to an indexeddb store rather than local storage. Less issues with size limits and string serialisation as you can chuck Blob instances straight into it.
You'd have to handle loading/saving though
Re: SQLite is not a toy database
#248I love SQLite. Am building a webapp + mobile app that needs to sync user created content. Is there an easy way to sync SQLite DBs?
SQLite has a "session" extension which is designed to help handle the sync problem: https://sqlite.org/sessionintro.html
Re: SQLite is not a toy database
#249> 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…
A kludge---I forget from where---for low traffic sites is one SQLite DB per user.
Re: SQLite is not a toy database
#250Two major gripe I had with SQlite 1. SQLite doesn't really enforce column types[0], the choice is really puzzling to me. Since schema enforced type check is one of the strong suit of SQL/RDMBS based data solution. 2. Whole database lock on write, this make it unsuitable to high write usages like logging and metric recording. WAL mode will help but it will only alleviate the issue, you will need row based lock solutio…
> unsuitable to high write usages like logging and metric recording.
If the speed of your write-only workload is limited by whole file locks rather than by raw I/O speed, you can probably consolidate your writes into fewer transactions (i.e. fewer disk accesses, amortizing lock cost over more data) and write to several databases in parallel according to any suitable sharding criteria. Which is what any RDBMS would have to to anyway.