8 ms·
Two 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 on
by doomleika 6y ago
Two 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 solution eventually.
Just like the offical FAQ said, SQLite competes with fopen[1] instead of RDBMS systems.
--
[0]: https://sqlite.org/datatype3.html https://sqlite.org/datatype3.html
[1]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- lsb 6y agoIf you are in WAL mode, you can have unlimited readers as one writer is writing.
- mastre_ 6y agoAlso, another issue when not in WAL mode is that a long read will actually have a write lock and block writes.
- haberman 6y ago> SQLite doesn't really enforce column types[0], the choice is really puzzling to me. This is acknowledged as a likely mistake, but one that will never be fixed due to backward compatibility: > Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more judgmental with regard to data types. In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature. https://sqlite.org/quirks.html https://sqlite.org/quirks.html
- doomleika 6y agoYeah, still I would really wish SQLite is a feather weight RDBM system than almost-but-not-quite-your-typical-RDBMS the better `fopen`. Having a tool like this would made my life whole lot easier, well, one can dream.
- mastre_ 6y ago> but one that will never be fixed due to backward compatibility I wonder if a fork/"new version" could address this. Like, sqlite2 (v1.0, etc.).
- isoprophlex 6y agoConsidering that we're on sqlite3 already, it'd probably be something for the v4 ;)
- deleted 6y ago[deleted]
- warmwaffles 6y agoThere is already an sqlite4 but they haven't done any work with it for a long time.
- mbreese 6y agoThis would be something on the order of the python2 -> python3 transition. Meaning, it would likely take a decade. After going though that, I'm not sure it would be worth it to just change from (default) flexible data types.
- deleted 6y ago[deleted]
- setr 6y agoSQLite already has different operating modes, right? e.g. WAL is turned on and stays on, I think; It seems like you could at least make type-checking an opt-in mode
- petters 6y agoThe expensify blog linked from a comment here claims: > But lesser known is that there is a branch of SQLite that has page locking, which enables for fantastic concurrent write performance. Reach out to the SQLite folks and I’m sure they’ll tell you more
- zie 6y ago1: Actually this is a feature, It's awesome and easy to map typing to your language types. In python see: https://docs.python.org/3/library/sqlite3.html#using-adapters-to-store-additional-python-types-in-sqlite-databases https://docs.python.org/3/library/sqlite3.html#using-adapter... specifically the DECLTYPES option. Other language bindings do things like this also, and makes it pretty idiot proof. you `create table test (mydict dict);` so your tables know their types, and then at bind time you say a sqlite column type of dict == a python dictionary. Obviously python is sort of a terrible example, because python typing is somewhat non-existent in many ways, but you see the point here. 2: There are definitely cases where it won't work out well, high-concurrent write load is definitely it's big weak spot, but those are usually fairly rare use cases.
- HelloNurse 6y ago> 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.
- dnautics 6y ago> SQLite doesn't really enforce column types[0], the choice is really puzzling to me. It's not the worst thing in the world; you're validating on data ingest anyways to prevent sqli, for example, right?
- Sohcahtoa82 6y agoData validation is a dangerous method to try to prevent SQL injection. The only surefire method is to used parameterized queries, which you should be doing anyways.
- dnautics 6y agohuh? By data validation I mean a validation library in your surrounding PL, it's flowing through the types of that language, and at no point is unprepared SQL entering your system.
- samatman 6y agoIt's more accurate to phrase point 1. as SQLite column constraints are opt-in. If you explicitly add a check constraint on a column schema, SQLite will dutifully perform it for you. It's a little extra work, and as siblings have pointed out, a greenfield SQLite would probably not have done things this way. But it's also easy, I have check constraints on many columns and they serve the purpose. Think of SQLie as having a weird dialect where `Col1 INTEGER` is spelled `Col1 INTEGER CHECK (typeof(Col1) IN ('integer', 'null'))`. Ideal? No, but also, not a showstopper. There are a few good reasons not to use SQLite. Your point 2 is one of them, running the client on one machine and accessing the database file via a network file system is another. Although I've pushed write-heavy workloads pretty hard with some care, it's easy to create a situation where contention becomes untenable. You can really pound on it with one client, but with several it gets dicey.
- le-mark 6y agoUsing a single write thread and multiple readers gives perfectly sound and high performance concurrency in SQLite. Of course the particulars of how one actually does that depend on the language in use.
- edwinyzh 6y agoI love that I don't have to define a length for the 'varchar' columns and I can store strings of any length to those columns ;)