9 ms·
At some point, don't you just end up making a low-quality, poorly-tested reinvention of SQLite by doing this and adding features?
by z3ugma 6mo ago
At some point, don't you just end up making a low-quality, poorly-tested reinvention of SQLite by doing this and adding features?
- gorjusborg 6mo agoOnly if you get there and need it.
- upmostly 6mo agoExactly. And most apps don't get there and therefore don't need it.
- evanelias 6mo agoYour article completely ignores operational considerations: backups, schema changes, replication/HA. As well as security, i.e. your application has full permissions to completely destroy your data file. Regardless of whether most apps have enough requests per second to "need" a database for performance reasons, these are extremely important topics for any app used by a real business.
- z3ugma 6mo agobut it's so trivial to implement SQLite, in almost any app or language...there are sufficient ORMs to do the joins if you don't like working with SQL directly...the B-trees are built in and you don't need to reason about binary search, and your app doesn't have 300% test coverage with fuzzing like SQLite does you should be squashing bugs related to your business logic, not core data storage. Local data storage on your one horizontally-scaling box is a solved problem using SQLite. Not to mention atomic backups?
- 9rx 6mo ago> and your app doesn't have 300% test coverage with fuzzing like SQLite does Surely it does? Otherwise you cannot trust the interface point with SQLite and you're no further ahead. SQLite being flawless doesn't mean much if you screw things up before getting to it.
- RL2024 6mo agoThat's true but relying on a highly tested component like SQLite means that you can focus your tests on the interface and your business logic, i.e. you can test that you are persisting to the your datastore rather than testing that your datastore implementation is valid.
- 9rx 6mo agoYour business logic tests will already, by osmosis, exercise the backing data store in every conceivable way to the fundamental extent that is possible with testing given finite time. If that's not the case, your business logic tests have cases that have been overlooked. Choosing SQLite does mean that it will also be tested for code paths that your application will never touch, but who cares about that? It makes no difference if code that is never executed is theoretically buggy.
- wmanley 6mo agoBusiness logic tests will rarely test what happens to your data if a machine loses power.
- 9rx 6mo agoThen your business logic contains unspecified behaviour. Maybe you have a business situation where power loss conditions being unspecified is perfectly acceptable, but if that is so it doesn't really matter what happens to your backing data store either.
- moron4hire 6mo agoCame here to also throw in a vote for it being so much easier to just use SQLite. You get so much for so very little. There might be a one-time up-front learning effort for tweaking settings, but that is a lot less effort than what you're going to spend on fiddling with stupid issues with data files all day, every day, for the rest of the life of your project.
- tracker1 6mo agoEven then... I'd argue for at least LevelDB over raw jsonl files... and I say this as someone who would regularly do ETL and backups to jsonl file formats in prior jobs.
- gorjusborg 6mo agoHonestly, there is zero chance you will implement anything close to sqlite. What is more likely, if you are making good decisions, is that you'll reach a point where the simple approach will fail to meet your needs. If you use the same attitude again and choose the simplest solution based on your _need_, you'll have concrete knowledge and constraints that you can redesign for.
- z3ugma 6mo agonot re-implement SQLite, I mean "use SQLite as your persistence layer in your program" e.g. worry about what makes your app unique. Data storage is not what makes your app unique. Outsource thinking about that to SQLite
- hirvi74 6mo agoSqlite is also the only major database to receive DO-178B certification, which allows Sqlite to legally operate in avionic environments and roles.
- freedomben 6mo agoSometimes yes, I've seen it. It even tends to happen on NoSQL databases as well. Three times I've seen apps start on top of Dynamo DB, and then end up re-implementing relational databases at the application level anyway. Starting with postgres would have been the right answer for all three of those. Initial dev went faster, but tech debt and complexity quickly started soaking up all those gains and left a hard-to-maintain mess.
- leafarlua 6mo agoThis always confuses me because we have decades of SQL and all its issues as well. Hundreds of experienced devs talking about all the issues in SQL and the quirks of queries when your data is not trivial. One would think that for a startup of sorts, where things changes fast and are unpredictable, NoSQL is the correct answer. And when things are stable and the shape of entities are known, going for SQL becomes a natural path. There is also cases for having both, and there is cases for graph-oriented databases or even columnar-oriented ones such as duckdb. Seems to me, with my very limited experience of course, everything leads to same boring fundamental issue: Rarely the issue lays on infrastructure, and is mostly bad design decisions and poor domain knowledge. Realistic, how many times the bottleneck is indeed the type of database versus the quality of the code and the imlementation of the system design?
- dalenw 6mo agoIt's almost always a system design issue. Outside of a few specific use cases with big data, I struggle to imagine when I'd use NoSQL, especially in an application or data analytics scenario. At the end of the data, your data should be structured in a predictable manner, and it most likely relates to other data. So just use SQL.
- greenavocado 6mo agoSystem design issues are a product of culture, capabilities, and prototyping speed of the dev team
- mike_hearn 6mo ago
- noveltyaccount 6mo agoAs soon as you need to do a JOIN, you're either rewriting a database or replatforming on Sqlite.
- pgtan 6mo agoHere are two checks using joins, one with sqlite, one with the join builtin of ksh93: check_empty_vhosts () { # Check which vhost adapter doesn't have any VTD mapped start_sqlite tosql "SELECT l.vios_name,l.vadapter_name FROM vios_vadapter AS l LEFT OUTER JOIN vios_wwn_disk_vadapter_vtd AS r USING (vadapter_name,vios_name) WHERE r.vadapter_name IS NULL AND r.vios_name IS NULL AND l.vadapter_name LIKE 'vhost%';" endsql getsql stop_sqlite } check_empty_vhosts_sh () { # same as above, but on the shell join -v 1 -t , -1 1 -2 1 \ <(while IFS=, read vio host slot; do if [[ $host == vhost* ]]; then print ${vio}_$host,$slot fi done < $VIO_ADAPTER_SLOT | sort -t , -k 1)\ <(while IFS=, read vio vhost vtd disk; do if [[ $vhost == vhost* ]]; then print ${vio}_$vhost fi done < $VIO_VHOST_VTD_DISK | sort -t , -k 1) }
- goerch 6mo agoa) Just heard today: JOINs are bad for performance b) How many columns can (an Excel) table have: no need for JOINs
- datadrivenangel 6mo agovlookups are bad for performance. recursive vlookups even more so.
- hunterpayne 6mo agoWow, I'm sorry you have to work with such coworkers. For reference, joins are just an expensive use case. DBs do them about 10x faster that you can do them by hand. But if you need a join, you probably should either a) do it periodically and cache the result (making your data inconsistent) or b) just do it in a DB. Confusing caching the result with doing the join efficiently is an amazing misunderstanding of basic Computer Science.
- bachmeier 6mo agoBased on what's in the article, it wouldn't take much to move these files to SQLite or any other database in the future. Edit: I just submitted a link to Joe Armstrong's Minimum Viable Programs article from 2014. If the response to my comment is about the enterprise and imaginary scaling problems, realize that those situations don't apply to some programming problems.
- locknitpicker 6mo ago> Based on what's in the article, it wouldn't take much to move these files to SQLite or any other database in the future. Why waste time screwing around with ad-hoc file reads, then? I mean, what exactly are you buying by rolling your own?
- bachmeier 6mo agoYou can avoid the overhead of working with the database. If you want to work with json data and prefer the advantages of text files, this solution will be better when you're starting out. I'm not going to argue in favor of a particular solution because that depends on what you're doing. One could turn the question around and ask what's special about SQLite.
- pythonaut_16 6mo agoIf your language supports it, what is the overhead of working with SQLite? What's special about SQLite is that it already solves most of the things you need for data persistence without adding the same kind of overhead or trade offs as Postgres or other persistence layers, and that it saves you from solving those problems yourself in your json text files... Like by all means don't use SQLite in every project. I have projects where I just use files on the disk too. But it's kinda inane to pretend it's some kind of burdensome tool that adds so much overhead it's not worth it.
- locknitpicker 6mo ago> You can avoid the overhead of working with the database. What overhead? SQLite is literally more performant than fread/fwrite.
- whalesalad 6mo agoReminds me of the infamous Robert Virding quote: “Virding's First Rule of Programming: Any sufficiently complicated concurrent program in another language contains an ad hoc informally-specified bug-ridden slow implementation of half of Erlang.”
- mrec 6mo agoIn case you weren't aware, that in itself is riffing on Greenspun's tenth rule: https://en.wikipedia.org/wiki/Greenspun%27s_tenth_rule https://en.wikipedia.org/wiki/Greenspun%27s_tenth_rule
- randyrand 6mo ago“You Aren’t Gonna Need It” - one of the most important software principles. Wait until you actually need it.
- upmostly 6mo ago100%. Premature optimisation I believe that's called. I've seen it play out many times in engineering over the years.
- hunterpayne 6mo agoCounterpoint, Meta is currently (and for the last decade) trying to rewrite MySQL so it is basically Postgres. They could just change their code so it works with Postgres and retrain their ops on Postgres. But for some reason they think its easier to just rewrite MySQL. Now, that is almost certainly more about office politics than technical matters...but it could also be the case that they have so much code that only works with MySQL that it is true (seriously doubtful). You are just mislabling good architecture as 'premature optimization'. So I will give you another platitude... "There is nothing so permanent as a temporary software solution"
- dkarl 6mo agoI interpret YAGNI to mean that you shouldn't invest extra work and extra code complexity to create capabilities that you don't need. In this case, I feel like using the filesystem directly is the opposite: doing much more difficult programming and creating more complex code, in order to do less. It depends on how you weigh the cost of the additional dependency that lets you write simpler code, of course, but I think in this case adding a SQLite dependency is a lower long-term maintenance burden than writing code to make atomic file writes. The original post isn't about simplicity, though. It's about performance. They claim they achieved better performance by using the filesystem directly, which could (if they really need the extra performance) justify the extra challenge and code complexity.
- goerch 6mo agoIs this what we do with education in general?
- hackingonempty 6mo agoProbably more like a low-quality, poorly-tested reinvention of BerkeleyDB.
- trgn 6mo agoim sure, but honestly, i would love to have a db engine that just writes/reads csv or json. does it exist?
- herpdyderp 6mo agoI wrote a CSV DB engine once! I can't remember why. For fun?
- zabzonk 6mo agoMicrosoft actually provide an ODBC CSV data source out of the box.
- banana_giraffe 6mo agoDuckDB can do exactly this, once you get the API working in your system, it becomes something simple like SELECT \* from read_csv('example.csv'); Writing generally involves reading to an in-memory database, making whatever changes you want, then something like COPY new_table TO 'example.csv' (HEADER true, DELIMITER ',');
- akdev1l 6mo agoSQLite can do it
- trgn 6mo agoit's storage file is a csv? or do you mean import/export to csv?
- akdev1l 6mo agoYou can import csv files into in memory tables and query them or you can use the csv extensions $ sqlite3 :memory: .import myfile.csv mytable SELECT * FROM mytable; $ sqlite3 :memory: SELECT * FROM csv_read('myfile.csv');
- hunterpayne 6mo ago