5 ms·

My main concern with application-defined schemas is that this schema is validated by the wrong system. The database is the authority on what the schema is; all

by levkk 2y ago

My main concern with application-defined schemas is that this schema is validated by the wrong system. The database is the authority on what the schema is; all other layers in your application make assumptions based on effectively hearsay. The closest we came so far to bridging this gap in strictly typed language like Rust is SQLx, which creates a struct based on the database types returned by a query. This is validated at compile time against a database, which is good, but of course there is no guarantee that the production database will have the same types. Easiest mistake to make is to design a query against your local Postgres v15 and hit a runtime error in production running Postgres v12, e.g. a function like gen_ramdom_uuid() doesn't exist. Another is to assume a migration in production was actually executed. In duck-typed languages like Ruby, the application objects are directly created from the database at runtime. They are as accurate as possible, since the schema is directly read at application startup. Then of course you see developers do something like: if respond_to?(:column_x) # do something with column_x end To summarize, I think application-defined schemas provide a false sense of security and add another layer of work for the engineer.

IshKebab 2y ago
This doesn't seem fundamentally different from any schema/API mismatch issue. For example using the wrong header for a C library, or the wrong Protobuf schema. I guess it would be good if it verified it at runtime somehow though. E.g. when you first connect to the database it checks Postgresql is the minimum required version, and the tables match what was used at compile time.
- dietr1ch 2y ago
  It could be verified at runtime, but I haven't seen anyone trying to version/hash schemas and include that in the request. The workaround in practice seems to be to keep the DB behind a server that always™ uses a compatible schema and exposes an API that's either properly versioned or at least safe for slightly older clients. To be fair it's hard to get rid of the middleman and serve straight from the DB, it's always deemed too scary for many reasons, so it's not that bad.
- Someone 2y ago
  It shouldn’t be hard for a database to keep a hash around for each database, update it whenever a DDL (https://en.wikipedia.org/wiki/Data_definition_language https://en.wikipedia.org/wiki/Data_definition_language) command is run, and, optionally, verify that a query is run against the exact database structure. Could be as simple as secure hashing the old hash with the text of the DDL command appended. That would mean two databases can be identical, structure-wise, but have different hashes (for example if tables are created in a different order), but would that matter in practice? Alternatively, they can keep a hash for every table, index, constraint, etc. and XOR them to get the database hash.
  - jeltz 2y ago
    Sounds very hard to me. How do you handle online schema changes with this? Schema changes are trivial to do if you can be down for a long time.
    - Someone 2y ago
      You can only do those if schema changes are transactional. The transaction could update the checksum and return it for the caller to use in subsequent calls. And yes, that would shut off other clients that want to be sure to talk to the correct schema, but if you opt in to such a scheme, that’s what you want.
Hytak 2y ago
rust-query manages migrations and reads the schema from the database to check that it matches what was defined in the application. If at any point the database schema doesn't match the expected schema, then rust-query will panic with an error message explaining the difference (currently this error is not very pretty). Furthermore, at the start of every transaction, rust-query will check that the `schema_version` (sqlite pragma) did not change. (source: I am the author)
- mjr00 2y ago
  > rust-query manages migrations and reads the schema from the database to check that it matches what was defined in the application. If at any point the database schema doesn't match the expected schema, then rust-query will panic with an error message explaining the difference (currently this error is not very pretty). IMO - this sounds like "tell me you've never operated a real production system before without telling me you've never operated a real production system before." Shit happens in real life. Even if you have a great deployment pipeline, at some point, you'll need to add a missing index in production fast because a wave of users came in and revealed a shit query. Or your on-call DBA will need to modify a table over the weekend from i32 -> i64 because you ran out of primary key values, and you can't spend the time updating all your code. (in Rust this is dicier, of course, but with something like Python shouldn't cause issues in general.) Or you'll just need to run some operation out of band -- that is, not relying on a migration -- because it what makes sense. Great example is using something like pt-osc[0] to create a temporary table copy and add temporary triggers to an existing table in order to do a zero-downtime copy. Or maybe you just need to drop and recreate an index because it got corrupted. Shit happens! Anyway, I really wouldn't recommend a design that relies on your database always agreeing with your codebase 100% of the time. What you should strive for is your codebase being compatible with the database 100% of the time -- that means new columns get added with a default value (or NULL) so inserts work, you don't drop or rename columns or tables without a strict deprecation process (i.e. a rename is really add in db -> add writes to code -> backfill values in db -> remove from code -> remove from db), etc... But fundamentally panicking because a table has an extra column is crazy. How else would you add a column to a running production system? [0] https://docs.percona.com/percona-toolkit/pt-online-schema-change.html https://docs.percona.com/percona-toolkit/pt-online-schema-ch...
sobellian 2y ago
Surely it is easier to just check that all migrations have run before you start serving requests? Column existence is insufficient to verify that the database conforms to what the application expects (existence of indices, foreign key relationships with the right delete/update rules, etc).
Kinrany 2y ago
The application is necessarily the authority on its expectations of the database.
- throwawaymaths 2y ago
  You might have more than one application hitting the same database
- mjr00 2y ago
  You can see my sibling comment, but in the real world of operating databases at any sort of scale, you need to have databases in transitory states where the application can continue to function even though the underlying database has changed. The quintessential example is adding a column. If you want to deploy with zero downtime, you have to square with the reality that a database schema change and deployment of application code is not an atomic operation. One must happen before the other. Particularly when you deal with fleets of servers with blue/green deploys where server 1 gets deployed at t=0minutes but server N doesn't get deployed until t=60minutes. Your application code will straight up fail if it tries to insert a column that doesn't exist, so it's necessary to change the database first. This normally means adding a column that's either nullable or has a default value, to allow the application to function as normal, without knowing the column exists. So in a way, yes, the application is still the authority, but it's an authority on the interface it expects from the database. It can define which columns should exist, but not which columns should not exist.
  - deleted 2y ago
    [deleted]
  - Hytak 2y ago
    You (and many other commenters) are right that rust-query currently requires downtime to do migrations. For many applications this is fine, but it would still be nice to support zero-downtime migrations Your argument that the application should be the authority on the interface it expects from the database makes a lot of sense. I will consider changing the schema check to be more flexible as part of support for zero-downtime migrations.
ninetyninenine 2y ago
agreed. Maybe having a schema check on the build step of the application will solve this. If the schema doesn't match then it doesn't compile. Most orms of course do the opposite. They generate a migration for the database from the code.
ris 2y ago
And you end up with no canonical declaration of the schema in your application code, leaving developers to mentally apply potentially tens, hundreds of migrations to build up an idea of what the tables are expected to look like.
- eddd-ddde 2y ago
  No matter how you define your schemas, you still have a series of migrations as data evolves. This is not an issue of schema definition.
ojkelly 2y ago
Would it make more sense to consider the response from the DB, like a response from any other system or user input, and take the parse don’t validate approach? After all, the DB is another system, and its state can be different to what you expected. At compile time we have a best guess. Unless there was a way to tell the DB what version of the schema we think it has, it could always be wrong.
runeks 2y ago
> This is validated at compile time against a database, which is good, but of course there is no guarantee that the production database will have the same types. Easiest mistake to make is to design a query against your local Postgres v15 and hit a runtime error in production running Postgres v12, e.g. a function like gen_ramdom_uuid() doesn't exist. Another is to assume a migration in production was actually executed. One could have the backend fetch DB schema/version info at startup, compare it to its own view of what the schema should look like, and fail if the two disagree. That way, a new deployment would fail before being activated, instead of being deployed successfully and queries failing down the line.
nurettin 2y ago
> schema is validated by the wrong system. The database is the authority on what the schema is What you describe is db-first. This rust library is code-first. In code-first, code is responsible for generating ddl statements using what is called a "migration" where the library detects changes to code and applies them to the schema.
shermantanktop 2y ago
Agree. Mid-tier developers who create queries for a SQL db to execute are doing the equivalent of using Java code to generate HTML. The target is not a programmatic API, it’s a language that was designed for end users, and it is both more expressive and more idiosyncratic than any facade you build in front of it.
cryptonector 2y ago
If the database is external to the host language and DB library, then you have external linkage and you'll have this problem no matter what -- not great. If the database is internal to the host language and DB library then you won't have this problem, but also you'll only be able to interact with the database via your application's code -- also not great. Either way is not great. I think the best bet is to have: - an external RDBMS - a compiler from the RDBMS schema to host languages/libraries - run-time validation that the schema has not changed backwards-incompatibly When does run-time validation take place? When you compile a query: a) the RDBMS will fail if the schema has changed in certain backwards-incompatible ways (e.g., tables or columns dropped or renamed, etc.), b) the library has to check that the types of the resulting rows' columns match expectations.

by levkk 2y ago

IshKebab 2y ago

This doesn't seem fundamentally different from any schema/API mismatch issue. For example using the wrong header for a C library, or the wrong Protobuf schema. I guess it would be good if it verified it at runtime somehow though. E.g. when you first connect to the database it checks Postgresql is the minimum required version, and the tables match what was used at compile time.

dietr1ch 2y ago
It could be verified at runtime, but I haven't seen anyone trying to version/hash schemas and include that in the request. The workaround in practice seems to be to keep the DB behind a server that always™ uses a compatible schema and exposes an API that's either properly versioned or at least safe for slightly older clients. To be fair it's hard to get rid of the middleman and serve straight from the DB, it's always deemed too scary for many reasons, so it's not that bad.
Someone 2y ago
It shouldn’t be hard for a database to keep a hash around for each database, update it whenever a DDL (https://en.wikipedia.org/wiki/Data_definition_language https://en.wikipedia.org/wiki/Data_definition_language) command is run, and, optionally, verify that a query is run against the exact database structure. Could be as simple as secure hashing the old hash with the text of the DDL command appended. That would mean two databases can be identical, structure-wise, but have different hashes (for example if tables are created in a different order), but would that matter in practice? Alternatively, they can keep a hash for every table, index, constraint, etc. and XOR them to get the database hash.
- jeltz 2y ago
  Sounds very hard to me. How do you handle online schema changes with this? Schema changes are trivial to do if you can be down for a long time.
  - Someone 2y ago
    You can only do those if schema changes are transactional. The transaction could update the checksum and return it for the caller to use in subsequent calls. And yes, that would shut off other clients that want to be sure to talk to the correct schema, but if you opt in to such a scheme, that’s what you want.

Hytak 2y ago

rust-query manages migrations and reads the schema from the database to check that it matches what was defined in the application. If at any point the database schema doesn't match the expected schema, then rust-query will panic with an error message explaining the difference (currently this error is not very pretty). Furthermore, at the start of every transaction, rust-query will check that the `schema_version` (sqlite pragma) did not change. (source: I am the author)

mjr00 2y ago
> rust-query manages migrations and reads the schema from the database to check that it matches what was defined in the application. If at any point the database schema doesn't match the expected schema, then rust-query will panic with an error message explaining the difference (currently this error is not very pretty). IMO - this sounds like "tell me you've never operated a real production system before without telling me you've never operated a real production system before." Shit happens in real life. Even if you have a great deployment pipeline, at some point, you'll need to add a missing index in production fast because a wave of users came in and revealed a shit query. Or your on-call DBA will need to modify a table over the weekend from i32 -> i64 because you ran out of primary key values, and you can't spend the time updating all your code. (in Rust this is dicier, of course, but with something like Python shouldn't cause issues in general.) Or you'll just need to run some operation out of band -- that is, not relying on a migration -- because it what makes sense. Great example is using something like pt-osc[0] to create a temporary table copy and add temporary triggers to an existing table in order to do a zero-downtime copy. Or maybe you just need to drop and recreate an index because it got corrupted. Shit happens! Anyway, I really wouldn't recommend a design that relies on your database always agreeing with your codebase 100% of the time. What you should strive for is your codebase being compatible with the database 100% of the time -- that means new columns get added with a default value (or NULL) so inserts work, you don't drop or rename columns or tables without a strict deprecation process (i.e. a rename is really add in db -> add writes to code -> backfill values in db -> remove from code -> remove from db), etc... But fundamentally panicking because a table has an extra column is crazy. How else would you add a column to a running production system? [0] https://docs.percona.com/percona-toolkit/pt-online-schema-change.html https://docs.percona.com/percona-toolkit/pt-online-schema-ch...

sobellian 2y ago

Surely it is easier to just check that all migrations have run before you start serving requests? Column existence is insufficient to verify that the database conforms to what the application expects (existence of indices, foreign key relationships with the right delete/update rules, etc).

Kinrany 2y ago

The application is necessarily the authority on its expectations of the database.

throwawaymaths 2y ago
You might have more than one application hitting the same database
mjr00 2y ago
You can see my sibling comment, but in the real world of operating databases at any sort of scale, you need to have databases in transitory states where the application can continue to function even though the underlying database has changed. The quintessential example is adding a column. If you want to deploy with zero downtime, you have to square with the reality that a database schema change and deployment of application code is not an atomic operation. One must happen before the other. Particularly when you deal with fleets of servers with blue/green deploys where server 1 gets deployed at t=0minutes but server N doesn't get deployed until t=60minutes. Your application code will straight up fail if it tries to insert a column that doesn't exist, so it's necessary to change the database first. This normally means adding a column that's either nullable or has a default value, to allow the application to function as normal, without knowing the column exists. So in a way, yes, the application is still the authority, but it's an authority on the interface it expects from the database. It can define which columns should exist, but not which columns should not exist.
- deleted 2y ago
  [deleted]
- Hytak 2y ago
  You (and many other commenters) are right that rust-query currently requires downtime to do migrations. For many applications this is fine, but it would still be nice to support zero-downtime migrations Your argument that the application should be the authority on the interface it expects from the database makes a lot of sense. I will consider changing the schema check to be more flexible as part of support for zero-downtime migrations.

ninetyninenine 2y ago

agreed. Maybe having a schema check on the build step of the application will solve this. If the schema doesn't match then it doesn't compile. Most orms of course do the opposite. They generate a migration for the database from the code.

ris 2y ago

And you end up with no canonical declaration of the schema in your application code, leaving developers to mentally apply potentially tens, hundreds of migrations to build up an idea of what the tables are expected to look like.

eddd-ddde 2y ago
No matter how you define your schemas, you still have a series of migrations as data evolves. This is not an issue of schema definition.

ojkelly 2y ago

Would it make more sense to consider the response from the DB, like a response from any other system or user input, and take the parse don’t validate approach? After all, the DB is another system, and its state can be different to what you expected. At compile time we have a best guess. Unless there was a way to tell the DB what version of the schema we think it has, it could always be wrong.

runeks 2y ago

> This is validated at compile time against a database, which is good, but of course there is no guarantee that the production database will have the same types. Easiest mistake to make is to design a query against your local Postgres v15 and hit a runtime error in production running Postgres v12, e.g. a function like gen_ramdom_uuid() doesn't exist. Another is to assume a migration in production was actually executed. One could have the backend fetch DB schema/version info at startup, compare it to its own view of what the schema should look like, and fail if the two disagree. That way, a new deployment would fail before being activated, instead of being deployed successfully and queries failing down the line.

nurettin 2y ago

> schema is validated by the wrong system. The database is the authority on what the schema is What you describe is db-first. This rust library is code-first. In code-first, code is responsible for generating ddl statements using what is called a "migration" where the library detects changes to code and applies them to the schema.

shermantanktop 2y ago

Agree. Mid-tier developers who create queries for a SQL db to execute are doing the equivalent of using Java code to generate HTML. The target is not a programmatic API, it’s a language that was designed for end users, and it is both more expressive and more idiosyncratic than any facade you build in front of it.

cryptonector 2y ago

If the database is external to the host language and DB library, then you have external linkage and you'll have this problem no matter what -- not great. If the database is internal to the host language and DB library then you won't have this problem, but also you'll only be able to interact with the database via your application's code -- also not great. Either way is not great. I think the best bet is to have: - an external RDBMS - a compiler from the RDBMS schema to host languages/libraries - run-time validation that the schema has not changed backwards-incompatibly When does run-time validation take place? When you compile a query: a) the RDBMS will fail if the schema has changed in certain backwards-incompatible ways (e.g., tables or columns dropped or renamed, etc.), b) the library has to check that the types of the resulting rows' columns match expectations.