But then you have to implement all the SELECT and DML logic yourself. SQL makes this a breeze with JOIN, ON UPDATE CASCADE, etc. And being SQL, it is very easy to maintain, even by the PFY that replaces you.
SQLite (also H2 and some other embedded SQL databases) can be used entirely in-memory, one can also drop an SQLite file on a RAM-hosted filesystem (tmpfs/ramdrive). You really can put everything into RAM (and still enjoy SQL) if you have enough, don't mind long cold-load and potential data loss.
It looked to me that the GP was suggesting to keep the data in the application instead of in a DB. But yeah, I suppose he might have meant a HEAP table instead of MyISAM.
If your RAM exceeds the size of your tables and indexes, that data will be served from RAM in any modern relational database system. No special config usually necessary for the speed but you don't lose everything when the power goes out, unlike tmpfs/ramdrive option.
That would depend on the DB server settings. Such a config might be found on a dedicated database server, but I doubt such settings would make sense on a machine running e.g. an application server together with the database.
I'd argue adding SQL into the mix makes it difficult to maintain, mixed-language codebases are almost by definition complex, and you get significant chafing when mixing a declarative language like SQL and OOP.
Since this is a no-update and no live-insert scenario we're talking about, it's fairly easy to produce code that is an order of magnitude faster than a DBMS, since they're not only primarily optimized for efficiently reading off disk (an in-memory hash table beats a B-tree every day of the week), they've got really unfortunate CPU cache characteristics, and additionally need to acquire read locks.
Maybe this is a failure of imagination on my part, but won't most people be using ORMs? Again, talking about the use case of the average application that's light enough to get away with SQLite, it doesn't seem like you would need to be hand writing queries.
In my experience ORMs add a layer of complexity, instead of removing one. It's nice to e.g. have a "Pythonic" interface in Python, but when working close to the data I far prefer to write a concise, clear query instead of trying to remember some ORM syntax or what they're calling VARCHARS in this particular ORM, or how they're representing JOINS, or if the condition will be on the ON clause or the WHERE clause, or how they're representing GROUP BY, etc etc.
Two features I enjoy in ActiveRecord and other ORMs, and why I would consider them a good standard practice for most things that aren't "toy" projects.
1. Easy chainability. In ActiveRecord you can have scopes like `User#older_than_65` and `User.lives_in_utah` and easily chain them: `User.older_than_65.lives_in_utah` which is occasionally very useful and way more sane than dynamically building up SQL queries "by hand."
2. Standardization. Maintenance and ongoing development (usually the biggest part of the software lifecycle) tend to get absolutely insane when you have N different coders doing things N different ways. I don't love everything ActiveRecord does, but it's generally quite sane and you can drop a new coder into a standard Rails project and they can understand it quickly. On a large team/codebase that can equate to many thousands or even millions of dollars worth of productivity.
I far prefer to write a concise, clear query instead
of trying to remember some ORM syntax
100% agree.
ActiveRecord strikes a good balance here IMO. An explicit goal of ActiveRecord is to make it painless to use "raw" SQL when desired.
On non-toy projects, I think a policy of "use the ORM by default, and use raw SQL for the other N% of the time when it makes sense" is very very sane.
ORMs integrate poorly in many languages, and perform strictly worse than hand-written SQL.
If you're just using the database for object persistence, which is common, it doesn't matter all too much. But that's not really the scenario we're discussing here, since the data is by the original problem statement, immutable.
