3 ms·

I also found out the hard way -- I was initially excited to adopt SQLite everywhere because I had heard so many good things about it (which are in fact true), b

by wenc 5y ago

I also found out the hard way -- I was initially excited to adopt SQLite everywhere because I had heard so many good things about it (which are in fact true), but its dynamic typing/type-affinity based design made it difficult to use as a data store especially for analytics work. I mean, you can still use it, but you have to do your own type checking/coercion in code -- basically you have to re-implement a subset of a type handling system -- which you'd get for free in a full SQL db. You also have to standardize certain types like DATEs because SQLite has no native date type -- the 3 ways to implement dates in SQLite are ISO 8601 strings, Julian and UNIX epochs, and you can convert between them, but you lose some performance on joins and aggregations. It's best to think of SQLite as its own data structure/file format which happens to have a SQL engine on top of it, rather than an actual database format. In fact the docs makes a case for SQLite as an application file format [1]. For analytics work however, I'm finding DuckDB (https://duckdb.org https://duckdb.org) to be quite amazing. It is SQLite-ish (i.e. also an embedded database), but has a columnar query engine, can read parquet files, and has native types (including date!). It also runs in-process in Python and queries Pandas dataframes using a more optimized query engine than Pandas itself. Side: Many of the main contributors work at CWI Amsterdam, the Dutch institute where Guido van Rossum conceived Python. [1] https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html

clnhlzmn 5y ago
> I mean, you can still use it, but you have to do your own type checking/coercion in code Can you explain why you wouldn't have to check what you're putting into the database? If you're just stuffing values of unknown type into a statically typed DB you're going to get errors up front and if you stuff values of unknown type into SQLite you might get errors down the road. Either way you probably should know what you're putting into the DB in the first place.
- deleted 5y ago
  [deleted]
- mumblemumble 5y ago
  SQLite doesn't fit perfectly cleanly into this, but, for me, the big distinction is between schema-on-read and schema-on-write. This captures the fact that you always need to have some sort of schema constraint when you're working with data. The question is, do you apply those constraints when you're writing the data, or when you're reading it? Schema-on-write is useful when you know exactly what the schema should be ahead of time, and it's static. That happens quite often in OLTP and business intelligence applications, but that's not always what you're doing. Schema-on-read is very useful in those situations where the schema constraints you need can't be known ahead of time, or might vary from situation to situation. At that point you're kind of stuck delaying the schema bits (including making sure everything is an appropriate type) until the last minute. This comes up in, for example, big data applications such as data lakes.
  - wenc 5y ago
    Schema-on-read vs Schema-on-write are definitely relevant concepts. I think SQLite sort of straddles the two -- it's sort of a loose schema-on-write (with type affinity to its base types), and schema-on-read (with respect to more complex types which is inferred from what is stored in base types)
    - mumblemumble 5y ago
      Agreed. Which kind of makes it even more complicated to talk about. Whether its approach offers the best of both worlds, or the worst of both words, depends on your application, and the dividing line doesn't cleanly follow any of the distinctions we normally make among types of application.
- wenc 5y ago
  It's not so much that one is stuffing random/unknown types into the database without type-checking. Programming language types do not map exactly to database types even in statically typed DBs. ORMs manage this translation for you (imperfectly at times), but for those of those of us work with directly with SQL, we do typecheck, otherwise the INSERTs will fail. It's more that static database types provide a standard contractual interface that is enforced (agnostic of application and programming language) and has reproducible behavior upon retrieval. The advantage of static types is the guarantee that if a data point is successfully INSERTed, it can be successfully retrieved in the future. In a dynamically typed DB you have a problem of standardization across codebases -- every new program/microservice that is written will have to use the exact same typechecking code, or else risk future retrieval issues. Those that do type coercion on the other end are essentially guessing and hoping that the original type was successfully reproduced upon retrieval. Plus if you work with different programming languages, that same code has to be ported to all the different languages. You also find yourselves having to reinvent the wheel a lot -- for instance the SQL DECIMAL type. In SQLite you can either store it as an INTEGER or REAL, and then either store metadata in another field or create a specific function to retrieve the INTEGER and recreate the specific DECIMAL type with the right number of digits (say DECIMAL(18,0)). On the other hand you can rely on SQL types and get all this for free and have the assurance that it will work impeccably.
- CRConrad 5y ago
  > Either way you probably should know what you're putting into the DB in the first place. Yeah, well, one good way of finding that out in practice is that reasonable RDBMS systems tell you that when you try to stuff the stuff in.

by wenc 5y ago

clnhlzmn 5y ago

> I mean, you can still use it, but you have to do your own type checking/coercion in code Can you explain why you wouldn't have to check what you're putting into the database? If you're just stuffing values of unknown type into a statically typed DB you're going to get errors up front and if you stuff values of unknown type into SQLite you might get errors down the road. Either way you probably should know what you're putting into the DB in the first place.

deleted 5y ago
[deleted]
mumblemumble 5y ago
SQLite doesn't fit perfectly cleanly into this, but, for me, the big distinction is between schema-on-read and schema-on-write. This captures the fact that you always need to have some sort of schema constraint when you're working with data. The question is, do you apply those constraints when you're writing the data, or when you're reading it? Schema-on-write is useful when you know exactly what the schema should be ahead of time, and it's static. That happens quite often in OLTP and business intelligence applications, but that's not always what you're doing. Schema-on-read is very useful in those situations where the schema constraints you need can't be known ahead of time, or might vary from situation to situation. At that point you're kind of stuck delaying the schema bits (including making sure everything is an appropriate type) until the last minute. This comes up in, for example, big data applications such as data lakes.
- wenc 5y ago
  Schema-on-read vs Schema-on-write are definitely relevant concepts. I think SQLite sort of straddles the two -- it's sort of a loose schema-on-write (with type affinity to its base types), and schema-on-read (with respect to more complex types which is inferred from what is stored in base types)
  - mumblemumble 5y ago
    Agreed. Which kind of makes it even more complicated to talk about. Whether its approach offers the best of both worlds, or the worst of both words, depends on your application, and the dividing line doesn't cleanly follow any of the distinctions we normally make among types of application.
wenc 5y ago
It's not so much that one is stuffing random/unknown types into the database without type-checking. Programming language types do not map exactly to database types even in statically typed DBs. ORMs manage this translation for you (imperfectly at times), but for those of those of us work with directly with SQL, we do typecheck, otherwise the INSERTs will fail. It's more that static database types provide a standard contractual interface that is enforced (agnostic of application and programming language) and has reproducible behavior upon retrieval. The advantage of static types is the guarantee that if a data point is successfully INSERTed, it can be successfully retrieved in the future. In a dynamically typed DB you have a problem of standardization across codebases -- every new program/microservice that is written will have to use the exact same typechecking code, or else risk future retrieval issues. Those that do type coercion on the other end are essentially guessing and hoping that the original type was successfully reproduced upon retrieval. Plus if you work with different programming languages, that same code has to be ported to all the different languages. You also find yourselves having to reinvent the wheel a lot -- for instance the SQL DECIMAL type. In SQLite you can either store it as an INTEGER or REAL, and then either store metadata in another field or create a specific function to retrieve the INTEGER and recreate the specific DECIMAL type with the right number of digits (say DECIMAL(18,0)). On the other hand you can rely on SQL types and get all this for free and have the assurance that it will work impeccably.
CRConrad 5y ago
> Either way you probably should know what you're putting into the DB in the first place. Yeah, well, one good way of finding that out in practice is that reasonable RDBMS systems tell you that when you try to stuff the stuff in.