Hacker News Reader

Comment by doomleika | Hacker News Reader

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

[1]: https://www.sqlite.org/whentouse.html

View on HN

> 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

Yeah, 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.

> 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.).

Considering that we're on sqlite3 already, it'd probably be something for the v4 ;)

There is already an sqlite4 but they haven't done any work with it for a long time.

This 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.

SQLite 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

WAL is journaling mode set on the connection. It’s not a db level setting.

WAL mode will stick after subsequent "connections".

Something like "PRAGMA STRICT = ON;" should be trivial to add after the strict mode is actually implemented.

But IMO that would just warrant an entirely separate software package. Shoving it inside sqlite3 is likely to make it much more complex.

I would not say that it is acknowledged as a mistake. Sqlite originally comes from Tcl world and thus it is somewhat natural that it follows Tcl's stringly-typed object model.

Maybe there could be a fork called SQLITE, an acronym for SQLite Including Type Enforcement

I think that would win the prize for most ungooglable name. (As Google is not very cases sensitive)

It'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.

Using 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.

1: 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-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.

> 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?

Data 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.

huh? 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.

The 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

I 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 ;)

If you are in WAL mode, you can have unlimited readers as one writer is writing.

Also, another issue when not in WAL mode is that a long read will actually have a write lock and block writes.

> 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.

Reply on news.ycombinator.com