News for hackers. Stuff that matters.
Re:SQLite is not a toy database by doomleika on Thursday March 25, 2021 @01:16PM (#26582824)
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

Re:SQLite is not a toy database by haberman on Thursday March 25, 2021 @02:05PM (#26583422)
> 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

Re:SQLite is not a toy database by setr on Thursday March 25, 2021 @03:31PM (#26584392)
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
Re:SQLite is not a toy database by le-mark on Thursday March 25, 2021 @08:16PM (#26587383)
WAL is journaling mode set on the connection. It’s not a db level setting.
Re:SQLite is not a toy database by doomleika on Thursday March 25, 2021 @09:26PM (#26587859)
WAL mode will stick after subsequent "connections".
Re:SQLite is not a toy database by pdimitar on Thursday March 25, 2021 @07:09PM (#26586802)
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.

Re:SQLite is not a toy database by mikedilger on Friday March 26, 2021 @01:24AM (#26588992)
Maybe there could be a fork called SQLITE, an acronym for SQLite Including Type Enforcement
Re:SQLite is not a toy database by trissylegs on Friday March 26, 2021 @02:40AM (#26589375)
I think that would win the prize for most ungooglable name. (As Google is not very cases sensitive)
Re:SQLite is not a toy database by doomleika on Thursday March 25, 2021 @02:25PM (#26583622)
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.

Re:SQLite is not a toy database by dfox on Thursday March 25, 2021 @07:31PM (#26586987)
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.
Re:SQLite is not a toy database by mastre_ on Thursday March 25, 2021 @02:31PM (#26583692)
> 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.).

Re:SQLite is not a toy database by isoprophlex on Thursday March 25, 2021 @02:43PM (#26583827)
Considering that we're on sqlite3 already, it'd probably be something for the v4 ;)
Re:SQLite is not a toy database by warmwaffles on Thursday March 25, 2021 @11:44PM (#26588547)
There is already an sqlite4 but they haven't done any work with it for a long time.
Re:SQLite is not a toy database by mbreese on Thursday March 25, 2021 @02:51PM (#26583915)
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.
Re:SQLite is not a toy database by samatman on Thursday March 25, 2021 @07:10PM (#26586812)
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.

Re:SQLite is not a toy database by le-mark on Thursday March 25, 2021 @08:09PM (#26587329)
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.
Re:SQLite is not a toy database by zie on Thursday March 25, 2021 @03:23PM (#26584312)
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.

Re:SQLite is not a toy database by dnautics on Thursday March 25, 2021 @05:58PM (#26586017)
> 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?

Re:SQLite is not a toy database by Sohcahtoa82 on Thursday March 25, 2021 @06:22PM (#26586332)
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.
Re:SQLite is not a toy database by dnautics on Thursday March 25, 2021 @06:29PM (#26586416)
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.
Re:SQLite is not a toy database by petters on Thursday March 25, 2021 @02:54PM (#26583961)
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

Re:SQLite is not a toy database by edwinyzh on Friday March 26, 2021 @03:03AM (#26589494)
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 ;)
Re:SQLite is not a toy database by lsb on Thursday March 25, 2021 @01:38PM (#26583116)
If you are in WAL mode, you can have unlimited readers as one writer is writing.
Re:SQLite is not a toy database by mastre_ on Thursday March 25, 2021 @03:05PM (#26584091)
Also, another issue when not in WAL mode is that a long read will actually have a write lock and block writes.
Re:SQLite is not a toy database by HelloNurse on Thursday March 25, 2021 @04:59PM (#26585294)
> 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.