Issue with SELECT 1 query

[oscartbeaumont]

I have been using SeaORM and have found what looks to me like a bug. Please let me know if this is a mistake on my end.

If I run the query SELECT 1 AS 'a' FROM '{table_name}'; I think it should be expected that the result should always be a=1 as long as {table_name} exists in the DB.

When executing this query using SeaORM on a table that contains no rows instead of it returning a Some(QueryResult) containing a=1 it returns None.

Demo. You can enable/disable the commented-out code to show how it acts differently when the table contains data or not.

For context, the reason I am trying to do this query is to detect if a table was created in my unit tests.

Thanks for any help and this awesome project!

[tyt2y3]

I am not entirely sure, but it seems like it is due to the behaviour of the SQL engine, SQLite in this case. More experiments are needed though.
That said, it is not the best way to detect whether a table exists.
The good news is, schema discovery support for SQLite is more or less ready, @billy1624 right?

[billy1624]

The good news is, schema discovery support for SQLite is more or less ready, @billy1624 right?

Yes, please check #386 :)

[oscartbeaumont]

I gave it another look and I think you are correct @tyt2y3 that this is how the SQLite engine is acting and hence not an issue in SeaORM. I must have made a mistake when testing the issue using DBeaver so I think we can close the issue.

About my usecase

I am not exactly sure the schema discovery is what I need for this use case. Essentially I am writing a unit test for the Rust GraphQL API I am building. The function I am testing takes in my custom Configuration struct and outputs a DbConn. As well as creating the DbConn the function also executes sea-migrations. The goal of my unit test is to confirm that the database migrations were executed and hence that one of the tables that should exist does. I am not interested in checking every table and that the columns are correct as I can assume sea-migrations is functioning as expected. I want to achieve this check with as little code as possible so that the unit test is clear about what it is testing.

If anyone else is trying to do the same thing and comes across this I am temporarily using DELETE FROM '_sea_migrations'; and checking that only one row was affected. This is concise but relies on implementation details of sea-migrations (the name of its table and the fact that a row will always exist if the migrations are executed) which is not preferred in the long term for this application. Does/could a function exists in sea-query to check for the existence of a table because I don't think an SQL query exists that's database-agnostic to test this. I can open an issue in the repo if necessary.