5 ms·
> Yeah my DB is the one place I want strict types. Well also RPCs. Which is why in most cases you are going to show at compile time that your code adheres to t
by win311fwg 4mo ago
> Yeah my DB is the one place I want strict types. Well also RPCs.
Which is why in most cases you are going to show at compile time that your code adheres to the typed structure. The SQLite schema you are developing alongside provides the type information for static analysis. There is no real benefit in also double checking again at runtime. Your code isn't going to magically mutate in a way that it starts inserting integers where your static analysis showed that it inserts strings.
SQLite is not like Postgres, which is designed for many different applications all sharing the same data, where you have to place trust in third-parties to also do the right thing. Runtime validation is critical in that environment. SQLite is designed for one application, one database. While it technically can support multiple applications sharing the same file, support is poor and it is not really designed for that. In the typical case, the only trust you need is your code, which you can evaluate at compile time. For the atypical cases you can enable strict tables.
- rjrjthtrjrj 4mo ago2004 called and they want their ignorant bad takes back
- coldtea 4mo agoThey also want this type of joke and misunderstanding SQLite purpose to get "macho programmer" points back.
- frollogaston 4mo agoReddit called and said they're missing a guy
- frollogaston 4mo agoBut one DB one app is fairly common with Postgres too, particularly if you're adhering to a services deliniation. Guess if your code enforces types at DB insert time, the DB doesn't need to, but one of those is more likely to change than the other.
- win311fwg 3mo ago> But one DB one app is fairly common with Postgres too That is common today, but remember that Postgres is now 40 years old. It is so old that it was originally based on QUEL rather than SQL. Back then database servers were designed to be what we now think of as the "API server". That necessitates runtime input validation same as your "API server" needs input validation today. If you were designing Postgres from scratch now you would do a lot of things differently, but it was built for its time. > Guess if your code enforces types at DB insert time It would be unusual for your programming language to magically turn strings into ints, or the like, so you can prove statically that your code won't insert the wrong thing. Duplicating the same thing at runtime doesn't buy you anything. Maybe if you are still trying to futz around with Javascript, but SQLite was designed for statically-typed programming languages.
- frollogaston 3mo agoEven with a statically typed language, two separate codepaths or code versions could write to the same tables and disagree on the types.
- deleted 3mo ago[deleted]
- win311fwg 3mo agoHow? Consider the following pseudocode: Schema: CREATE TABLE foo ( bar INTEGER ); Program: fn main() { stmt := "INSERT INTO foo (bar) VALUES (?)" if (now() % 2 == 0) { db.exec(stmt, [1]); } else { db.exec(stmt, ["Baz"]); // Static analysis fails here. Input is not an integer. } } If a different code version comes along and, say, changes the schema then: Schema: CREATE TABLE foo ( bar TEXT ); Program: fn main() { stmt := "INSERT INTO foo (bar) VALUES (?)" if (now() % 2 == 0) { db.exec(stmt, [1]); // Static analysis fails here. Input is not text. } else { db.exec(stmt, ["Baz"]); } } Perhaps what you are imagining is when the data is provided externally, where the target schema isn't known at compile time? That is a possible use-case, but not what SQLite was primarily designed for and not what we are talking about. If that is what you need that is what strict tables are there for. Different tools for different jobs.