Can't use custom SQL segments with multiple databases

Help me!
1

Expected behavior

I am using multiple database within a single Forest installation following the guide here https://docs.forestadmin.com/documentation/how-tos/databases/plug-multiple-schemas.

This is working as expected as I can browse table data from both databases in the “all” view and within a custom segment using the Simple query. However, if I use a custom SQL query (one that worked prior to adding the second database), I receive an error.

My expectation is that the segment with custom SQL would continue to work after adding a second database.

Actual behavior

Receive error in Forest Admin UI and on server output.

Screen Shot 2020-07-13 at 1.28.06 PM
Screen Shot 2020-07-13 at 1.28.06 PM1552×888 39.4 KB

Failure Logs

[forest] 🌳🌳🌳  Invalid SQL query for this Live Query segment:
relation "exchanges" does not exist
Executing (default): select * from exchanges where description is null

Context

Please provide any relevant information about your setup.

"chalk": "~1.1.3",
"cookie-parser": "1.4.4",
"debug": "~4.0.1",
"dotenv": "~6.1.0",
"express": "~4.16.3",
"forest-express-sequelize": "^6.3.2",
"mime": "^2.4.4",
"morgan": "1.9.1",
"node-fetch": "^2.6.0",
"pg": "^8.3.0",
"require-all": "^3.0.0",
"sequelize": "^5.22.0
1 Like
2

Can you please have a try specifying the schema name in your segment request?

select * from schema_name.exchange where description is null

Take care the schema name and the table name (certainly without s)

It’s allways good to specify the schema name: https://www.red-gate.com/simple-talk/blogs/why-you-should-always-use-schema-name-in-queries/

3

Thanks for the reply.

The table name is exchanges so it needs the s and I’ve tried with and without the schema. This exact same query worked prior to migrating to a multi-database setup. I feel like the raw SQL is getting confused as to what database to use but that’s just a hunch.

4

Hi @trestrantham,

Thank you for reporting this issue.

I’ve been able to reproduce and it seems, indeed, that ForestAdmin is unable to correctly target a single table using raw SQL.

I just made a ticket out of it so we can work on a fix.

Best regards

5

Hi @trestrantham,

How many databases did you plug in your new configuration?
Looking at this code, it seems to take the first database connection declared by default.

If that’s not the case, can you set your the database of the exchanges table as the first connection in your configuration and give it another try?

Thanks.

6

I confirmed it is trying to use only the first database connection and after flipping my two databases, I am able to query the exchanges table without error. However, I always get No Exchanges match the segment when running the query even the table has data matching the query conditions. Pasting the exact same query from the server log into my SQL editor returns the correct data as expected.

7

thanks for your feedback @trestrantham.

I guess your new issue would deserve a new post if it persists.
Are you sure you don’t have additional filtering with your live query segment (filters, scopes,…) that could explain that you have no results displayed in your admin panel?

8

I am sure about no additional filters. However, this still feels like a bug? I flipped my connections to get custom SQL segments working for this database but I need to do the same for my other database and am unable to do so.

9

Is there any additional information available here? I am still running into the original issue of not being able to use multiple databases in SQL query segments at the same time.

1 Like
10

Hi @trestrantham,

Unfortunately, as you experienced, SQL queries are not well supported on multiple database projects.
Only the first connection tables can be queried with the current implementation.

This support cannot be done with a simple patch, it needs some kind of product design and additional features to deliver the best experience possible.
This is a topic will should start to tackle in September.

I hope it helps.

11

Thanks for the update. I’ll watch for any further updates in the coming months. Cheers!