3 ms·

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

> 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 ago
Even 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 ago
  How? 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.
  - deleted 3mo ago
    [deleted]
  - frollogaston 3mo ago
    One schema, defined by you, but your codebase is big and you forget what type something is. You're also updating the code while keeping the same SQLite file, as many apps would do, so compatibility across versions is important. CREATE TABLE foo ( bar INTEGER ); money_adder.c inserts string: sqlite3_stmt *stmt; const char *sql = "INSERT INTO foo (bar) VALUES (?);"; ... sqlite3_bind_text(stmt, 1, "4.5", -1, SQLITE_STATIC); money_viewer.c selects int: sqlite3_stmt *stmt; const char *sql = "SELECT bar FROM foo;"; ... int bar_int = sqlite3_column_int(stmt, 0); // bar_int = 4
    - win311fwg 3mo ago
      > but your codebase is big and you forget what type something is. Why does that matter? You don't have to rely on human memory. You went to all the trouble to define the types so you don't have to remember. Your static analysis will tell you that the types are incompatible. > money_adder.c inserts string This fails analysis. `bar` is defined as an integer. money_adder.c will not ever get the point of inserting a string as your infrastructure will halt the build pipeline long before you ever get to the point of running the program. You must have accidentally replied to the wrong comment at some point? It is technically true that you can write software without type analysis, but that's clearly not applicable to our discussion about using type analysis. > You're also updating the code while keeping the same SQLite file In the real world where migrations are necessary it is prudent to validate that any already persisted data is structured as expected, applying any necessary migrations if there is a mismatch, but when used as SQLite was primarily designed you only need to do that once at initialization, not every single time you touch the data. Once you have validated that the file's schema matches the schema defined at compile time then the static truths hold.

frollogaston 3mo ago

Even 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 ago
How? 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.
- deleted 3mo ago
  [deleted]
- frollogaston 3mo ago
  One schema, defined by you, but your codebase is big and you forget what type something is. You're also updating the code while keeping the same SQLite file, as many apps would do, so compatibility across versions is important. CREATE TABLE foo ( bar INTEGER ); money_adder.c inserts string: sqlite3_stmt *stmt; const char *sql = "INSERT INTO foo (bar) VALUES (?);"; ... sqlite3_bind_text(stmt, 1, "4.5", -1, SQLITE_STATIC); money_viewer.c selects int: sqlite3_stmt *stmt; const char *sql = "SELECT bar FROM foo;"; ... int bar_int = sqlite3_column_int(stmt, 0); // bar_int = 4
  - win311fwg 3mo ago
    > but your codebase is big and you forget what type something is. Why does that matter? You don't have to rely on human memory. You went to all the trouble to define the types so you don't have to remember. Your static analysis will tell you that the types are incompatible. > money_adder.c inserts string This fails analysis. `bar` is defined as an integer. money_adder.c will not ever get the point of inserting a string as your infrastructure will halt the build pipeline long before you ever get to the point of running the program. You must have accidentally replied to the wrong comment at some point? It is technically true that you can write software without type analysis, but that's clearly not applicable to our discussion about using type analysis. > You're also updating the code while keeping the same SQLite file In the real world where migrations are necessary it is prudent to validate that any already persisted data is structured as expected, applying any necessary migrations if there is a mismatch, but when used as SQLite was primarily designed you only need to do that once at initialization, not every single time you touch the data. Once you have validated that the file's schema matches the schema defined at compile time then the static truths hold.