[SPBS]

> This has saved me from a lot of errors

Are your projects in a dynamically typed language? I struggle to think why a statically typed language would have this problem, because the data being transferred at the boundary between application and database is already well-defined and strictly typed.

[lmz]

What if you manipulate the data using SQL eg. UPDATE t SET col1 = SomeFunc(col2). That would expose you to possible type issues too.

[OskarS]

Say you’re using the C API (not really relevant which language, any statically typed language would work) and had the line

    sqlite3_bind_int(stmt, 3, 45);

to bind the value 45 to the third item in whatever INSERT/UPDATE statement you’re doing. But that was actually wrong: the third item is supposed to be a string, you meant to bind the int to item 4.

A strictly typed database would error out here, a dynamically typed one will just assign the int to the string column, potentially corrupting data.

This is exactly equivalent to messing up types for variables in a dynamically typed language, all pros and cons of typechecking apply equally for SQL. The fact that the host language C is statically typed is irrelevant.

[masklinn]

> A strictly typed database would error out here, a dynamically typed one will just assign the int to the string column, potentially corrupting data.

I would assume posgres is considered "strictly typed", yet it will never error on the correct version of this: all parameters are necessarily passed as `char*` equivalent to "normal" untyped literals/strings.

The best you can do is provide an explicit type (via `paramTypes`), which skips inference and is equivalent to typed constants[0].

[0] https://www.postgresql.org/docs/current/sql-syntax-lexical.h...

[OskarS]

If you used SQLite like the grandparent described (adding check constraints to the CREATE TABLE statement), it would error out when running the statement with the incorrectly bound types. The proposal here is basically to (if you opt in to it) automate that process so it just happens automatically.

I have no idea how the postgres C API looks, but the SQLite API has different functions for binding different kinds of data types to prepared statements, like `sqlite3_bind_int` for ints, `sqlite3_bind_blob` for binary blobs, `sqlite3_bind_text` for strings, and so forth. So SQLite "knows" the source type you're binding.

[asdfasgasdgasdg]

I don't know much about SQLite, but I have used other relational DB's client libraries. And I don't see how static typing in the programming language helps here? The DB's type system and the language's type system are not hooked up to each other. So there is still effective dynamism here even with a statically typed client language. If the SQLite library has various sorts of built-in content coercions, it would still be a problem.

[IshKebab]

This problem can definitely happen in statically typed languages too. E.g. if you write one data type in one place and read a different type in another place.

You need more than just a statically typed language to prevent it. Either you need something like an ORM or you need some clever hackery like sqlx that interrogates your schema at compile time.

[justsomeuser]

It is possible that the db is shared between many processes with potentially different types