sql: support diesel-rs

[tbg]

The following are known to block supporting diesel-rs.

• sql: support rolling back nested transactions containing schema changes #10735 (SQL savepoints)
• sql: Can't drop and replace a table within a transaction #12123 (can't recreate dropped table within same transaction)
• sql: sequelize support is blocked on stored procedures #12120 (uses a stored procedure to automatically populate an updated_at field; however it seems that this isn't used for anything and was added "just because")
• sql: Complete the information_schema by adding empty table definitions #8675 (need at least referential_constraints, perhaps others)

The list is likely incomplete, but with hacky workarounds to these I was at least able to create a simple table and read from it.

cc @cuongdo

Jira issue: CRDB-6111

[tbg]

I played with this again today and while I don't remember whether I had to add more workarounds originally, it doesn't seem so bad, in fact, it's actually usable:

https://github.com/tschottdorf/cockroachdb-diesel-example

I'll probably explore using the query builder portion more (not so crazy about using ORMs generally).

[rushmorem]

Any update on this? Diesel is now at v1.1.1. As of now, it has 15,918 recent downloads while the postgres driver has 11,114. I would love to see this ORM officially supported.

[benesch]

No updates, I'm afraid. Pull requests welcome, of course!

[benesch]

But looks like we’re making progress with #21615! Thanks, @nvanbenschoten!

[jordanlewis]

I revisited this a bit today. It looks like there are a few test suites within the diesel source code. Here's how I ran them:

1. Clone the diesel repo
2. Update to the latest rust nightly (rustup update, and I think you have to have switched your release to nightly first as well)
3. Put the following text in the .env file:

# The database to use when testing against Postgres.
PG_DATABASE_URL=postgresql://root@localhost:26257/diesel_test
# The database to use when running the Postgres examples during testing.
PG_EXAMPLE_DATABASE_URL=postgresql://root@localhost:26257/diesel_example

4. Run ./bin/test, or for each package (diesel, diesel_cli, diesel_tests, etc) run: cargo test --no-default-features --features "postgres"

Here are the failures in the diesel package:

test result: FAILED. 34 passed; 4 failed; 0 ignored; 0 measured; 0 filtered out
    connection::transaction_manager::test::transaction_depth_is_tracked_properly_on_commit_failure
    pg::connection::tests::queries_with_sql_literal_nodes_are_not_cached
    pg::types::record::tests::record_deserializes_correctly
    pg::types::record::tests::serializing_named_composite_types

Two of them seem to be about default integer size (4 in Postgres, 8 in Cockroach). The other two:

#55233 (transaction state after serializable restart is wrong) #54954 (BEGIN/ROLLBACK when inside/outside of a txn should be a warning)
#55226 (fractional years are parsed wrong in intervals).

diesel_cli

    test result: FAILED. 11 passed; 2 failed; 0 ignored; 0 measured; 0 filtered out
    infer_schema_internals::information_schema::tests::get_foreign_keys_loads_foreign_keys
    infer_schema_internals::information_schema::tests::get_table_data_loads_column_information

The first one is caused by the fact that, in Cockroach, index names are not globally unique: they're instead scoped by table. In particular, more than one table has the index called 'primary': in fact, every table with a primary index has an index called 'primary'. Diesel writes the following query and expects it to produce the output for a single index, but in fact it produces output for all indexes called 'primary' in the database:

SELECT information_schema.key_column_usage.table_name, information_schema.key_column_usage.table_schema, information_schema.key_column_usage.column_name FROM information_schema.key_column_usage WHERE ((information_schema.key_column_usage.constraint_schema = 'test_schema') AND (information_schema.key_column_usage.constraint_name = 'primary')) LIMIT 1;

The second one is (I think) caused by the int4/int8 default int size issue.

diesel_dynamic_schema

running 7 tests
test providing_custom_schema_name ... ok
test dynamic_values::nullable_dynamic_value ... ok
test dynamic_values::dynamic_query ... FAILED
test dynamic_values::mixed_value_query ... FAILED
test querying_multiple_types ... ok
test querying_basic_schemas ... ok
test columns_used_in_where_clause ... ok

failures:

---- dynamic_values::dynamic_query stdout ----
thread 'dynamic_values::dynamic_query' panicked at 'called `Result::unwrap()` on an `Err` value: DeserializationError("Unknown type: 20")', src/libcore/result.rs:1165:5
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace.

---- dynamic_values::mixed_value_query stdout ----
thread 'dynamic_values::mixed_value_query' panicked at 'Received more than 4 bytes decoding i32. Was a BigInt expression accidentally identified as Integer?', diesel/src/type_impls/integers.rs:47:9


failures:
    dynamic_values::dynamic_query
    dynamic_values::mixed_value_query

diesel_tests

This test suite fails to start due to the lack of user-defined functions, as mentioned in the issue subject. Diesel seems to make one to keep track of updated_at.

[zicklag]

So if I want to use CockroachDB with diesel are will I be fine if I don't run into one of the gotchas, or is it essentially not really going to work at all until these are fixed?

I've got an app I'm thinking of building and I'm thinking about using CockroachDB and Diesel. It's a very simple app that will probably only have like two or three tables, mostly storing basic user information necessary to have accounts and also probably some either binary or JSON data. Is it possible that I could use Diesel with Cockroach without problems in this case?

[kernfeld-cockroach]

Hey @zicklag, although no promises because it's unsupported, it's possible that your app will work just fine. Even if you want to query JSON from Diesel, that should work okay because I see that running cargo test --no-default-features --features serde_json json in the diesel package works with CockroachDB. I would say to give it a shot and open an issue in this repo if you see a specific problem. Good luck!! 😄

[kernfeld-cockroach]

I took a whack at this today; here are my notes.

@rafiss and I found what we think is a bug in cockroachdb, #58069

A couple augmentations to @jordanlewis 's instructions above:

1. I don't think that nightly Rust is required; I didn't see any problems on stable Rust 1.40.0.
2. --no-default-features is not necessary. In fact, we can run even more tests by enabling the extras feature.

As a next step, setting up a roachtest for diesel would let us easily triage test failures. By combining this with a command to set the Cockroach default int size to 4 bytes and using something like cargo test --features postgres,extras -- -Z unstable-options --format json to let us programmatically ignore tests that are known to fail, my hope is that we could soon make claims about a whitelist of Cockroach functionality that works correctly with diesel.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!