As a postgresql nut, the people suggesting those other platforms (incl. pgsql) are idiots. Postgres in particular would be a bad idea unless he wanted to incorporate VACUUM() into his loading screen
Related note: in the last year I started using SQLite with my Clojure scripts instead of using a remote database, and I'm storing everything in git. Works like a charm for single-user tasks.
The .db file can change, even if the data/records don't.
One approach is to dump the sql to text and store that in git. (e.g. https://stackoverflow.com/a/846665)
Can confirm. SQLite is great. I've written an e-commerce website for a customers a few years ago. Most of the data was static and only updated a couple times per day in bulk. It was easy to do the main import/data update completely offline, then copying over the db file to multiple servers and doing mostly read-only queries on the DB. SQLite was more than fast enough and not having to manage another service was totally worth it. It's good having SQLite in your toolbox.
That seems pretty easy. It's much harder to try to understand where someone is coming from and engage with them in a positive way, even if you're rejecting their idea in the end.
MySQL Embedded[1] is a thing; used by some KDE apps. IIRC, it insists on using multiple files rather than a single file like SQLite does, but otherwise is a pretty much a turn-key component. No such luck with PostgreSQL so far.
Not only desktop, but most mobile platforms as well. Beeing coded in a single C file makes it portable as hell. Their documentation is also a prime example, with great insight.
Not only desktop and mobile, but it can be used as the storage format for larger scale distribued systems. For example it's the on disk format for FoundationDB.
>Beeing coded in a single C file makes it portable as hell.
It kind of is... the SQLite "amalgamation" process takes all of SQLite's source code (across 100+ files) and concatenates it together into a single sqlite3.c file: https://www.sqlite.org/amalgamation.html
A fun trick is to ask a colleague at work how many SQL databases they think are in the room. They almost never bear in mind SQLite is heavily used on phones, not even just smartphones.
Abstract the persistence layer, document the contract, build a conformance suite, and write your one impl that uses sqlite. I know sometimes there seems to be an impedance mismatch between callers and databases, but often it's not that bad. And no, don't use an ORM or existing DB abstraction to do this. Sure it takes work, but pluggable storage is nice on some of these types of things.
Sorry, put in my no-ORM edit as you may have been writing this. No, I mean higher level than that. ORMs suck as solving your problems specifically. Think more like an API than an ORM. I just started a side project and here's the beginning of an example of the iface I mean [0] and here's the beginning of an example of the sqlite impl I mean [1] (all untested and really early stage of course, but the idea is there).
I am admittedly unfamiliar with this specific case, but even a cursory glance showed me some sqlite-specific code embedded in the main library portion [0].
Hence "base". Good orm like sqlalchemy have several layers, each of them building on each others, and you can use each if them independantly depending how much abstraction you want.
I am admittedly unfamiliar with this specific situation, but if this is a common request it can alleviate the requests by allowing others to write their own impls without decoupling what exists. Again though, maybe that's already present here and I am ignorant of the details.
I know this is kind of a snarky response, but seriously... why? This would take developer resources away from real feature work, introduce boatloads of complexity, add more points of failure, make testing and validation harder and make it harder to reason about the entire system end-to-end. Not that that isn't sometimes necessary, but you need a compelling reason, not just jumping into it because it sounds like a good idea.
Programmers have this strange compulsion to design swappable interfaces even when they're totally unnecessary and more often than not just break everything while– ironically– only one implementation is ever used.
To allow others to impl the data store they want and to avoid having to write blog posts like these.
It's not that much real work to quickly pull out an iface on a stable system. Assuming it would be just mostly pass-through to your main impl anyways and this is a common approach in refactorization. You don't need some huge system, just drop it back a layer and abstract it.
Often, what you end up learning about your system when you do this minimal work (again, nothing big) is that you screwed up and buried SQL and string concatenation and a bunch of other hard-to-review pieces all throughout your app because you keep a stranglehold on terms like YAGNI when separation of concerns has real value. Sometimes you also end up learning that your tests don't cover what you thought they did. Nobody wants bloat, but this adds very little and helps devs clamoring for alternative stores.
> To allow others to impl the data store they want and to avoid having to write blog posts like these.
But why?
For the longest time MySQL didn't support window function or CTEs. What do you do if you're using those? (I'm not sure of SQLite's window support, but I'm pretty sure it has CTEs.)
If you're using postgres, should you not use pg_trgm (trigram indexer to use an index for regex searches on text), postgis (GIS tools), pg-routing (routing tools), ossp_uuid (uuid support), better types (boolean, range, inet, geometric, &c), or features such as RETURNING just because someone doesn't like my choice of database? No, I'm going to do what makes my life easier and my application faster and more featureful.
The way you persist has consequences to what types of features you'll build, and it'll end up being the least common denominator, not allowing you to use any advanced or efficient features of your store.
> To allow others to impl the data store they want and to avoid having to write blog posts like these.
That's not a good enough reason. Those others need to first justify what the specific benefits of switching would be. I have a strong feeling that a lot of these people don't actually know why this change should be made beyond "I heard that SQLite 'isn't a real DB' but I don't know what that means". The customer is not always right; sometimes people making feature requests need to be told "No".
> It's not that much real work to quickly pull out an iface on a stable system
Maybe we've worked on different things, but this has not been my experience. IME, replacing a DBMS is agony, and adding an abstraction layer so you can use multiple DBMS's is agony exponentially magnified. Some of that is due to bad choices you made in the original design, but a lot of it is just because these systems will never behave identically, no matter what promises they make about SQL standards. Don't forget, also, that when you target multiple DBMS's, you're locking yourself into hitting the lowest common denominator of the intersection between their feature sets. You might end up making performance worse because you can no longer use DBMS-specific optimizations!
But this is all immaterial: no matter how much or how little work it is, it's all unnecessary work. If there's no benefit to switching, why do any work at all, especially when it has all the downsides I mentioned above.
> Often, what you end up learning about your system when you do this minimal work (again, nothing big) is that you screwed up and buried SQL and string concatenation and a bunch of other hard-to-review pieces....
I agree! So go through the app and fix those and then see if you still need to swap out the storage layer (which is actually what the author is recommending). Don't just do it because it's faddish before you've even established that it would actually add value.
> Imagine you work on Java. Do you build a DSL/Transpiler to Java so you MAYBE could change later to C#?
Why would I do such a thing? I rarely ever promote abstraction for abstraction's sake and will in almost all cases disagree with premature abstraction. There are levels and for simple CRUD things, a thin contract over your DB is a reasonable tradeoff.
> A RDBMS is even more important that the glue code. Why hide it, why think is something "easy" to trow away?
I don't think you even need glue code. You just need a well-specified contract. You don't want to throw away anything of course.
> Why think is nut to code most or all the code on it?
I don't think that. Often it can be prudent to use plsql/triggers for things. Doesn't mean the frontend of your web app needs to build the SQL in the JS though, right? Just a high level "DoThing" contract with a description of what it should do is fine.
In many projects the database is way more important than the "backend" or what have you. Business logic in SQL (DB table schemas is 80% of business logic anyway...); Java or Python or whatever is just some glue to connect user interface or API to database.
(I think "glue code" in parent referred to "what is not the UI or DB" BTW, i.e. the "Java app".)
When the database is the most fundamental thing, then trying to abstract away the database can be a bit like trying to abstract away the programming language you are using.
I have code where I would much rather rewrite the "backend" in another language than swap out the database.
I think it was a very good analogy to say that abstracting over database is a bit like abstracting over what programming language you are using.
Unless for example, your run a background process which periodically imports data from an external source and inserts into sqlite database, while your web application continues to render pages for visitors based on existing old data.
Your web page will fail, as it will time out on database lock because of that background process.
> The idea is that a more complicated DBMS should be faster, especially for huge music libraries.
How "huge" are they talking about? I built a tool that imports Apache logs into sqlite for quick analysis and that easily handles several million records.
Just a ballpark: my beets DB is 15 MB for ~1000 albums (well, folders). This post[0] claims 10M albums ever released. So maybe 150 GB upper bound? Assuming my db has been vacuumed recently (~~probably not true~~ Edit: nope, still 15M after vacuum) and ignoring that indexes will scale slightly non-linearly.
I think 10m is far too low to be the total number of albums. But it would be impossible to actually calculate that figure given the number of producers that have released content outside the scope of any particular authority (eg NIN has released stuff to download only from their site. Aphex Twin uploaded a load of stuff to SoundCloud. Etc). But we digress.
My music collection is massive. It's got 30 years of singles and albums in there. And several years of DJing too. Plus a massive amount of DJ sets (like several hundred gig of DJ sets alone). Yet XBMC / Kodi handles it fine "despite" being sqlite backed. So does Subsonic and that just uses some Java equivalent. And as I've said already, I can throw several million records of Apache logs into sqlite without any issue.
The performance of sqlite is actually really good. It's super fast. Which is why the author can make those boasts on its website. Plus the compactness of the db (ie one file produced) and the ease to create a db makes it a perfect choice for desktop and mobile applications.
I think some people read that WordPress runs on MySQL or spend 10 minutes building their first CMS in PostgreSQL and then think the world and their mother should be using this miraculous new RDBMS they've just discovered themselves. (I'm probably being too harsh there)
Even as a heavy PostgreSQL fan and promoter I don't see why you would choose anything BUT sqlite for single-user applications unless you needed something that only other databases provide (richer procedural language support or other extensions that the fairly basic but still adequate featureset SQLite provides) - size of the dataset is only one factor to consider when choosing the storage layer for your application, and there's a lot of valid solutions for handling 1TB or less of data easily.
For single-threaded access, its possible that a more complicated DBMS will actually be slower than SQLite. (Or similar in performance.)
Of course once your application needs to do multithreaded DB access (especially if writes are involved), then you're far better off ditching SQLite. Then again, this is probably not a very common use case for a typical desktop application.
Single process applications if I’m not mistaken. And somewhere in the docs it says: most writes take a few milliseconds at most, so even in a multi-process environment (say 5-10 PHP processes), it should be fine. Haven’t tried this though.
Are you aware that's unsafe? To make a safe backup use sqlite3's ".dump" command (or filesystem snapshotting, but I've had bad experiences with that, at least on btrfs).
cp does not make atomic snapshots. It copies by reading (usually sequentially) chunk by chunk from the source file, and writing these chunks to the destination file. This takes time. If the database has writes at the time of backup, the backup might be invalid (it contains some old parts and some new parts).
(Unless you use e.g. the --reflink option of GNU cp, in which case it makes atomic snapshots on filesystems that support it).
Isn’t one of the more important points of POSIX that reading sequentially results in an atomic copy ? As long as you keep the file handle open and the fs supports it.
I don't know which point you mean, but that would mean that any writer would be blocked indefinitely by any other reader. That amounts to a read lock. You don't get a read lock just by opening a file for reading. You can test that with a simple shell script
This script does a concurrent write while the reader is in the "sleep 2" phase. The output should be
got line1
got line1
(given that the sleeps do the expected thing).
POSIX might contain something that requires aligned blocks of 512 bytes or so to be read or written atomically. But only if you do that in a single system call, of course.
This is just a thought: SQLite is a really good file format. Why aren't we replacing CSV with it, especially for big data applications?
CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality.
SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.
Universality is key. Everything reads it, everything writes it, everyone can read or edit it by virtually any piece of software. Compare that with SQLite.
I used it. SQLite can even import directly from CSV (as long as you're only have ASCII characters). Joining several CSVs and the first data cleaning steps took a second instead of 30 minutes with pandas.
I'm a coder and I still find the ability to eyeball a file useful, also csv can be munged about with the shell easily (cut, grep, sed etc) which can be surprisingly useful.
But both XML and SQLite have the same issue: They give you just enough rope to hang yourself with. While SQLite is a fantastic micro-database engine and a file format, it isn't a very good universal B2B format because there are too many features you'd have to support to interoperate.
I'd argue CSV's biggest strength is that it is easy to parse correctly, because the format is so simple and the standard is largely the superset. The biggest problem for CSV is that the most popular desktop application for view CSV (Microsoft Excel) sucks at it, and causes data corruption on save (and has for at least twenty years).
If people wanted a relational database as a file, I can think of nothing better than SQLite, but in B2B you want automated tooling that can parse the format without human involvement. If you need some structure there are some simpler XML-based formats which accommodate that, but still offer far fewer features you'd have to support than SQLite does.
Ajax might become popular in this space, but I'm yet to see it.
I'm talking about business to business transactions, as in automated transactions on VANs and similar data interchanges.
The industry doesn't really use XLSX or XLS because they're unreliable to parse without Microsoft's Office libraries which require Windows licenses, and the two formats have a huge amount of overhead compared to XML formats and CSV (which costs money on VANs).
This is a space where plain text files and binary is still king. Formats like EDI are still massive. In many ways CSV and XML are the new kids on the block.
Only end users are sending Excel formatted files (and PDFs) to one another.
> The industry doesn't really use XLSX or XLS because they're unreliable to parse without Microsoft's Office libraries
XLS yes, however XLSX is just easy to parse if you document only contains simple types. of course with embedded mathml/wordml it gets a little bit wierd.
XLSX is actually a decent format for business users. The only trouble is it has tons of limits [1], most restrictive being the limit of 1,048,576 rows by 16,384 columns.
I'm working with a client who had to split up their data into several XLSX files because they hit a row limit.
They can of course ETL the data into a real SQL database, but ETL tools for end users are still not a common thing plus not all end users have access to a SQL database.
> I'd argue CSV's biggest strength is that it is easy to parse correctly
Not sure I resonate with that. I work with large CSVs of varying provenance every day (I work in big data) and there's always some CSV edge case that stymies my analysis pipeline.
Timestamp parsing is extremely hard if it's not ISO-8601, as well as handling of unicode encoding, missing data, type inference, European usage of , as a decimal point, hidden characters, etc. One of the costs of almost complete freedom in input is the "interesting" possibilities people come up with to stymie your code.
> Excel's CSV parsing isn't the greatest, but does a surprisingly decent job considering how ill-defined CSV is.
No, it really doesn't. Save this as a CSV, open it in Excel, hit save, and then review the raw CSV:
"1000000000012345", "Hello",
"2000000000067890", "World",
Here's what Excel (O365) does to it for me:
1E+15, Hello,
2E+15, World,
That data is now permanently lost. And this corruption occurs for almost all EAN/UPCs. There are many ways to get around this in Excel, but the default experience is data corruption and has been for most of my lifetime. It is a terrible application for CSV. If they followed the standard every column would be what it is: Text.
> XML isn't really on anyone's radar in the big data world.
But is a central theme in the business to business back-end systems world. I'm talking about ordering, invoicing, remittances, hospital records, hospital billing, and so on. No clue what "big data" is, we deal with billions of transactions a year, but that's likely not what you're referring to.
1. I think in the first instance, Excel performs type-inference and coerces it into a numeric type and writes a truncated precision version of numeric fields in CSV, which is an abominable sin. There are workarounds for this [1], but agree this is terrible behavior due to CSV's lack of types.
Arguably, there should have been an explicit Excel switch which forces all CSV data to be parsed as raw strings.
I've seen problems with Excel CSVs that are worse than that: I have serial numbers that have leading 0's that have semantic meaning, like 000002324122323. Most CSV parsers cannot tell that this isn't a numeric type and so handle it wrongly. So I resort to [1].
2. Big data is of course somewhat ambiguous nomenclature as well, but is nowadays typically understood to mean the Hadoop ecosystem or similar. Data is typically ingested into a distributed file system (HDFS, S3, etc.) in formats such as Parquet, Avro, JSON and often CSV. A schema-on-read database like Hive sits on top of this layer and presents a SQL interface to the user. Tools like Apache Spark provide programmatic transformations that operate on the data on the large.
CSV is often promoted as a format for storing structured data due to ease of ingestion and inspection (all you need is a text editor for troubleshooting). However, you pay a performance penalty every time an analytic query is run because CSV doesn't support indexes, predicate pushdowns, compression, etc. and records can sometimes be uninterpretable under certain schemas (in which case they are simply excluded or dropped).
Sometimes having one too many commas in a row can completely mess things up (all the columns get shifted in a record) -- I found this out the hard way when Spark exported malformed CSVs with un-escaped commas.
sqlite wouldn't solve any of those issues because it's dynamically typed and what you're asking for is something which is strictly typed. In that regard you're better off with a data format like YAML.
I would also disagree that Excel does a good job with CSV. Well, maybe opening files but certainly not saving them as it rewrites values in weird ways. Back when I used to have to handle CSVs regularly (which admittedly was a while ago now) I found OpenOffice Calc to be far superior in terms of CSV support. I'd assume the same is true for LibreOffice Calc but honestly I've not done a huge amount with CSV since.
While that's not exactly correct - CSV can be quite hard to parse if you want to cover each and every variation and edge case - for all practical intents and purposes that statement is true in my opinion.
CSV is something of a lowest common denominator, a compromise between well-defined, structured data and portability / accessibility.
CSV could be much easier to parse if people use the ASCII codes meant to be used as field and line separators, thus avoiding having problems with commas.
Your costumer just sent you a slightly malformed cab, there’s a couple of column names that need to change and some of the rows need to be excluded. 10min tops. Vs your consumer just sent you a slightly mallformed sqlite dB... which tables need to be merged with which tables? What values are actually indexes into other tables? What assumptions are there on the id’s of this table compared to this table? 4days and 20 emails later you still haven’t fed the data into your model.
csv has problems that are trivial to solve live. SQLite is just way overkill to be handed over from party to party lightly.
Parquet is a great columnar format, and Avro is great for schema evolution. But they're not that easy to work with directly. To this day there isn't a decent Parquet viewer to do adhoc data viewing.
SQLite on the other hand has a lightweight SQL REPL that can be invoked from the command line.
Spark can work with SQLite via JDBC, though obviously it isn't as native as Parquet. Between SQLite and Parquet, I might pick Parquet under most circumstances.
But it seems to me SQLite ought to at least be a better option than CSV for Spark jobs (less work needed to do type inference, predicate pushdowns are trivial, etc.)
csv is notoriously bad with spark - in fact you couldn't write to csv for the longest time without external libraries like the databricks one, so I don't contest your last point.
SQlite is usually a single file representing a database though, I don't know how it would work with partitioning and stuff, and then how to handle the schema evolving across sqlite files.
Speaking for an Engineer unfortunately often data is simple CSV. Parsing this data robustly and visualizing it without scripting is still something that hasn't been solved.
If you have any idea how to do it, I'd be more than glad to hear about it.
I agree that SQLite-as-file-format is a great idea. But CSV still has some significant advantages for certain use cases. One big one that I don't think others have mentioned is that CSVs are human readable with any old text editor. That's a big help when working with unfamiliar datasets, debugging, etc.
SQLite has no type safety on its fields; CSV is just text. You can create an integer field and save the value "wenc" in a SQLite record without it complaining, something it calls "dynamic typing."
CSV is fine for tabular data which is by far the biggest use case. When the data gets even slightly more complicated, people are not importing and exporting it all that much.
You've posted countless comments like this to promote your database. Often you include a lot of low-substance, barely relevant verbiage as packing, if not camouflage, for the promotion. That doesn't help.
I don't say it lightly, but you're basically spamming HN, as users have been complaining for a long time (e.g. [1], [2]). Even when you don't appear to be doing it, you're doing it [3]. It's past time for this to stop; please stop.
We use sqlite in data-heavy visualization website (http://atlas.cid.harvard.edu/): We currently have an approximately 22GB sqlite file with the two largest tables going up to 150 million rows each. It still works just fine. The limiting factor seems to be the I/O throughput of the instance and disk of the server (EBS volumes).
Read-only workloads, most of which eventually get cached by the webserver, but still. Each visualization ends up pulling a large amount of data (sometimes in the 10 thousands of rows) so the network / serde overhead and the administration costs of an external database server adds up, though things have changed recently so perhaps a revisit is in order.
Where it hurts is: The query optimizer sometimes stumbles and does silly things (e.g. not always very smart about column / index selectivity statistics). The data import takes a long time (compared to e.g. postgres' COPY), so that's another pain point.
You can help the optimizer stumbling by running ANALYZE [1] with representative data present, and turning off auto-analyze. This was intentionally designed as a feature so you could set up the statistics for a db, empty it, deploy it out to the end user and know that the optimizer isn't gonna go off and do something funky.
I'm actually working on a project that's in the process of migrating from SQLite to MySQL as the preferred DB. I'll admit that the reasons we're doing it are probably not applicable to a desktop application like this.
The real wins for us are multithreaded and multiprocess DB I/O. This is something MySQL can handle, but SQLite really isn't designed to do well or efficiently.
I am on the train that the idea of using SQLite for a webapp, even one where SQLite can handle the traffic just fine, isn't nearly as good of an idea as some would have you think. Nevertheless, the idea of switching a local end-user application off of SQLite in favor of a client-server RDBMS is bonkers IMHO. You would be making everything about the application dramatically more complex for no real benefit. This blog post is considerably nicer than the idea deserved.
124 comments
[ 4.6 ms ] story [ 219 ms ] thread* No external services required * Backups, when the application isn't run, is just a copy * Good SQL support
I'm not even sure it's possible to get to the point where SQLite is going to fall over in this context (music collection metadata managment).
I totally wish for a fancier DBMS that can be embebed (firebird fit here!) and work on mobile (but not here yet).
And not only "fancier" in the limited sense that most believe. I truly mean A LOT FANCIER.
[1] https://www.oracle.com/mysql/embedded.html
>Beeing coded in a single C file makes it portable as hell.
It's not coded in a single C file...
I wonder if the alleged 5-10% performance gains are compared with usage of LTO.
0 - https://github.com/cretz/yukup/blob/eecb3b24b33f40e6a1eefaa5... 1 - https://github.com/cretz/yukup/tree/eecb3b24b33f40e6a1eefaa5...
It has drawbacks but if accepting several db is an important goal, you don't want to handcraft this unless you can measure inaceptable perfs.
I am admittedly unfamiliar with this specific case, but even a cursory glance showed me some sqlite-specific code embedded in the main library portion [0].
0 - https://github.com/beetbox/beets/blob/4f7c1c9beda6c2a5b0705e...
I know this is kind of a snarky response, but seriously... why? This would take developer resources away from real feature work, introduce boatloads of complexity, add more points of failure, make testing and validation harder and make it harder to reason about the entire system end-to-end. Not that that isn't sometimes necessary, but you need a compelling reason, not just jumping into it because it sounds like a good idea.
Programmers have this strange compulsion to design swappable interfaces even when they're totally unnecessary and more often than not just break everything while– ironically– only one implementation is ever used.
YAGNI.
To allow others to impl the data store they want and to avoid having to write blog posts like these.
It's not that much real work to quickly pull out an iface on a stable system. Assuming it would be just mostly pass-through to your main impl anyways and this is a common approach in refactorization. You don't need some huge system, just drop it back a layer and abstract it.
Often, what you end up learning about your system when you do this minimal work (again, nothing big) is that you screwed up and buried SQL and string concatenation and a bunch of other hard-to-review pieces all throughout your app because you keep a stranglehold on terms like YAGNI when separation of concerns has real value. Sometimes you also end up learning that your tests don't cover what you thought they did. Nobody wants bloat, but this adds very little and helps devs clamoring for alternative stores.
But why?
For the longest time MySQL didn't support window function or CTEs. What do you do if you're using those? (I'm not sure of SQLite's window support, but I'm pretty sure it has CTEs.)
If you're using postgres, should you not use pg_trgm (trigram indexer to use an index for regex searches on text), postgis (GIS tools), pg-routing (routing tools), ossp_uuid (uuid support), better types (boolean, range, inet, geometric, &c), or features such as RETURNING just because someone doesn't like my choice of database? No, I'm going to do what makes my life easier and my application faster and more featureful.
That's not a good enough reason. Those others need to first justify what the specific benefits of switching would be. I have a strong feeling that a lot of these people don't actually know why this change should be made beyond "I heard that SQLite 'isn't a real DB' but I don't know what that means". The customer is not always right; sometimes people making feature requests need to be told "No".
> It's not that much real work to quickly pull out an iface on a stable system
Maybe we've worked on different things, but this has not been my experience. IME, replacing a DBMS is agony, and adding an abstraction layer so you can use multiple DBMS's is agony exponentially magnified. Some of that is due to bad choices you made in the original design, but a lot of it is just because these systems will never behave identically, no matter what promises they make about SQL standards. Don't forget, also, that when you target multiple DBMS's, you're locking yourself into hitting the lowest common denominator of the intersection between their feature sets. You might end up making performance worse because you can no longer use DBMS-specific optimizations!
But this is all immaterial: no matter how much or how little work it is, it's all unnecessary work. If there's no benefit to switching, why do any work at all, especially when it has all the downsides I mentioned above.
> Often, what you end up learning about your system when you do this minimal work (again, nothing big) is that you screwed up and buried SQL and string concatenation and a bunch of other hard-to-review pieces....
I agree! So go through the app and fix those and then see if you still need to swap out the storage layer (which is actually what the author is recommending). Don't just do it because it's faddish before you've even established that it would actually add value.
Let me show you why is insane.
Imagine you work on Java. Do you build a DSL/Transpiler to Java so you MAYBE could change later to C#?
A RDBMS is even more important that the glue code. Why hide it, why think is something "easy" to trow away?
Why think is nut to code most or all the code on it?
Why would I do such a thing? I rarely ever promote abstraction for abstraction's sake and will in almost all cases disagree with premature abstraction. There are levels and for simple CRUD things, a thin contract over your DB is a reasonable tradeoff.
> A RDBMS is even more important that the glue code. Why hide it, why think is something "easy" to trow away?
I don't think you even need glue code. You just need a well-specified contract. You don't want to throw away anything of course.
> Why think is nut to code most or all the code on it?
I don't think that. Often it can be prudent to use plsql/triggers for things. Doesn't mean the frontend of your web app needs to build the SQL in the JS though, right? Just a high level "DoThing" contract with a description of what it should do is fine.
(I think "glue code" in parent referred to "what is not the UI or DB" BTW, i.e. the "Java app".)
When the database is the most fundamental thing, then trying to abstract away the database can be a bit like trying to abstract away the programming language you are using.
I have code where I would much rather rewrite the "backend" in another language than swap out the database.
I think it was a very good analogy to say that abstracting over database is a bit like abstracting over what programming language you are using.
Your web page will fail, as it will time out on database lock because of that background process.
[1]: https://www.sqlite.org/wal.html
How "huge" are they talking about? I built a tool that imports Apache logs into sqlite for quick analysis and that easily handles several million records.
[0]: https://www.quora.com/How-many-music-albums-are-available-in...
My music collection is massive. It's got 30 years of singles and albums in there. And several years of DJing too. Plus a massive amount of DJ sets (like several hundred gig of DJ sets alone). Yet XBMC / Kodi handles it fine "despite" being sqlite backed. So does Subsonic and that just uses some Java equivalent. And as I've said already, I can throw several million records of Apache logs into sqlite without any issue.
The performance of sqlite is actually really good. It's super fast. Which is why the author can make those boasts on its website. Plus the compactness of the db (ie one file produced) and the ease to create a db makes it a perfect choice for desktop and mobile applications.
I think some people read that WordPress runs on MySQL or spend 10 minutes building their first CMS in PostgreSQL and then think the world and their mother should be using this miraculous new RDBMS they've just discovered themselves. (I'm probably being too harsh there)
Of course once your application needs to do multithreaded DB access (especially if writes are involved), then you're far better off ditching SQLite. Then again, this is probably not a very common use case for a typical desktop application.
- 0h to manage backups ("cp"),
- 0h to manage seeds and tests fixtures ("cp"),
- 0h to configure and secure (void),
- 0h to write the deployment scripts (void),
- 0h monitoring/watchdog jobs (void),
- 1h to rsync for failover ("rsync")
My last projects always spent at least a good 100h to do all of this the right way. Then, if it is not good enough, we'll move to RDS or equivalent.
Do you have multiple servers? How do you handle real-time filesystem sync? If you only have one server, how do you handle its inevitable failure?
Do you loose any data collected between last backup and failure?
(I'm not saying SQLite isn't good, because it's f-ing amazing. I'm just not sold on it as a multi-process, multi-user database.)
(If you have a filesystem capable of atomic snapshots, then you can simply snapshot any database and treat the backup as a single file.)
I'd also be interested in that. Last time I wanted to use Sqlite opening twice the same file for writing either would not succeed or could time out.
That's fine. It was never designed to be a multi-process, multi-user database. Even the authors don't pretend it works in that use case:
https://www.sqlite.org/whentouse.html
Are you aware that's unsafe? To make a safe backup use sqlite3's ".dump" command (or filesystem snapshotting, but I've had bad experiences with that, at least on btrfs).
(Unless you use e.g. the --reflink option of GNU cp, in which case it makes atomic snapshots on filesystems that support it).
POSIX might contain something that requires aligned blocks of 512 bytes or so to be read or written atomically. But only if you do that in a single system call, of course.
https://www.sqlite.org/howtocorrupt.html#_backup_or_restore_...
> > 0h to manage backups ("cp")
not if you are on btrfs and use a snapshot (same for xfs or even lvm)
CSV can be difficult and ambiguous to parse correctly (because there's no real standard) and isn't extremely performant. The only thing it has going for it is its universality.
SQLite is lightweight, structured, supports indexing for performance and is extremely easy to use.
I'm inclined to say we are. R, for instance, includes an embedded sqlite, and it seems to be used a lot.
Related link: https://sqlite.org/appfileformat.html
Non-coders technical users
I find this very useful to quickly summarize, filter, reorder/rename columns etc. in csv files.
[1] https://harelba.github.io/q/
Simple, I can edit this with only a text editor if need be.
But both XML and SQLite have the same issue: They give you just enough rope to hang yourself with. While SQLite is a fantastic micro-database engine and a file format, it isn't a very good universal B2B format because there are too many features you'd have to support to interoperate.
I'd argue CSV's biggest strength is that it is easy to parse correctly, because the format is so simple and the standard is largely the superset. The biggest problem for CSV is that the most popular desktop application for view CSV (Microsoft Excel) sucks at it, and causes data corruption on save (and has for at least twenty years).
If people wanted a relational database as a file, I can think of nothing better than SQLite, but in B2B you want automated tooling that can parse the format without human involvement. If you need some structure there are some simpler XML-based formats which accommodate that, but still offer far fewer features you'd have to support than SQLite does.
Ajax might become popular in this space, but I'm yet to see it.
The industry doesn't really use XLSX or XLS because they're unreliable to parse without Microsoft's Office libraries which require Windows licenses, and the two formats have a huge amount of overhead compared to XML formats and CSV (which costs money on VANs).
This is a space where plain text files and binary is still king. Formats like EDI are still massive. In many ways CSV and XML are the new kids on the block.
Only end users are sending Excel formatted files (and PDFs) to one another.
XLS yes, however XLSX is just easy to parse if you document only contains simple types. of course with embedded mathml/wordml it gets a little bit wierd.
I'm working with a client who had to split up their data into several XLSX files because they hit a row limit.
They can of course ETL the data into a real SQL database, but ETL tools for end users are still not a common thing plus not all end users have access to a SQL database.
[1] https://support.office.com/en-us/article/excel-specification...
Not sure I resonate with that. I work with large CSVs of varying provenance every day (I work in big data) and there's always some CSV edge case that stymies my analysis pipeline.
Timestamp parsing is extremely hard if it's not ISO-8601, as well as handling of unicode encoding, missing data, type inference, European usage of , as a decimal point, hidden characters, etc. One of the costs of almost complete freedom in input is the "interesting" possibilities people come up with to stymie your code.
The Pandas read_csv method has tons of switches to deal with all kinds of CSV parsing definitions precisely because there is no standard. Fortunately this covers 80% of the use-cases. https://pandas.pydata.org/pandas-docs/stable/generated/panda...
Excel's CSV parsing isn't the greatest, but does a surprisingly decent job considering how ill-defined CSV is.
XML isn't really on anyone's radar in the big data world. It's an interchange format yes but is hugely inefficient for dataframe-type data.
No, it really doesn't. Save this as a CSV, open it in Excel, hit save, and then review the raw CSV:
"1000000000012345", "Hello",
"2000000000067890", "World",
Here's what Excel (O365) does to it for me:
1E+15, Hello,
2E+15, World,
That data is now permanently lost. And this corruption occurs for almost all EAN/UPCs. There are many ways to get around this in Excel, but the default experience is data corruption and has been for most of my lifetime. It is a terrible application for CSV. If they followed the standard every column would be what it is: Text.
> XML isn't really on anyone's radar in the big data world.
But is a central theme in the business to business back-end systems world. I'm talking about ordering, invoicing, remittances, hospital records, hospital billing, and so on. No clue what "big data" is, we deal with billions of transactions a year, but that's likely not what you're referring to.
Arguably, there should have been an explicit Excel switch which forces all CSV data to be parsed as raw strings.
I've seen problems with Excel CSVs that are worse than that: I have serial numbers that have leading 0's that have semantic meaning, like 000002324122323. Most CSV parsers cannot tell that this isn't a numeric type and so handle it wrongly. So I resort to [1].
2. Big data is of course somewhat ambiguous nomenclature as well, but is nowadays typically understood to mean the Hadoop ecosystem or similar. Data is typically ingested into a distributed file system (HDFS, S3, etc.) in formats such as Parquet, Avro, JSON and often CSV. A schema-on-read database like Hive sits on top of this layer and presents a SQL interface to the user. Tools like Apache Spark provide programmatic transformations that operate on the data on the large.
CSV is often promoted as a format for storing structured data due to ease of ingestion and inspection (all you need is a text editor for troubleshooting). However, you pay a performance penalty every time an analytic query is run because CSV doesn't support indexes, predicate pushdowns, compression, etc. and records can sometimes be uninterpretable under certain schemas (in which case they are simply excluded or dropped).
Sometimes having one too many commas in a row can completely mess things up (all the columns get shifted in a record) -- I found this out the hard way when Spark exported malformed CSVs with un-escaped commas.
[1] http://support.pitneybowes.com/SearchArticles/VFP06_Knowledg...
I would also disagree that Excel does a good job with CSV. Well, maybe opening files but certainly not saving them as it rewrites values in weird ways. Back when I used to have to handle CSVs regularly (which admittedly was a while ago now) I found OpenOffice Calc to be far superior in terms of CSV support. I'd assume the same is true for LibreOffice Calc but honestly I've not done a huge amount with CSV since.
While that's not exactly correct - CSV can be quite hard to parse if you want to cover each and every variation and edge case - for all practical intents and purposes that statement is true in my opinion.
CSV is something of a lowest common denominator, a compromise between well-defined, structured data and portability / accessibility.
See https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...
(goes on to say a bunch of applications don't parse it correctly; lets add mssql to the pile)
https://news.ycombinator.com/newsguidelines.html
[1] https://www.geopackage.org/
Big data applications tend to use other structured binary formats like parquet and avro, which any big data tool can typically parse.
SQLite on the other hand has a lightweight SQL REPL that can be invoked from the command line.
Spark can work with SQLite via JDBC, though obviously it isn't as native as Parquet. Between SQLite and Parquet, I might pick Parquet under most circumstances.
But it seems to me SQLite ought to at least be a better option than CSV for Spark jobs (less work needed to do type inference, predicate pushdowns are trivial, etc.)
SQlite is usually a single file representing a database though, I don't know how it would work with partitioning and stuff, and then how to handle the schema evolving across sqlite files.
If you have any idea how to do it, I'd be more than glad to hear about it.
There are free alternatives, but many require programming.
[1] https://www.absentdata.com/tableau-alternatives/
[1] https://www.sqlite.org/faq.html#q3
There's your reason.
SQLite is good enough for pretty much everything, just use it!!!
I don't say it lightly, but you're basically spamming HN, as users have been complaining for a long time (e.g. [1], [2]). Even when you don't appear to be doing it, you're doing it [3]. It's past time for this to stop; please stop.
1. https://news.ycombinator.com/item?id=16893429
2. https://news.ycombinator.com/item?id=16677493
3. https://news.ycombinator.com/item?id=16749503
Read-only workloads, most of which eventually get cached by the webserver, but still. Each visualization ends up pulling a large amount of data (sometimes in the 10 thousands of rows) so the network / serde overhead and the administration costs of an external database server adds up, though things have changed recently so perhaps a revisit is in order.
Where it hurts is: The query optimizer sometimes stumbles and does silly things (e.g. not always very smart about column / index selectivity statistics). The data import takes a long time (compared to e.g. postgres' COPY), so that's another pain point.
[1]: https://www.sqlite.org/lang_analyze.html
The real wins for us are multithreaded and multiprocess DB I/O. This is something MySQL can handle, but SQLite really isn't designed to do well or efficiently.