Using SQL to check data connectivity in Basedash

The other day we had a customer request support for a view that used to work fine, but now was not loading. It turned out to be a connectivity issue.

Context: tables “found” and “not found”

Basedash keeps track of the schemas and tables within a database that you connect. The current implementation of this functionality is basic but viable for the majority of situations. We hope to make things more sophisticated down the road, but here’s how it currently works:

1. Basedash queries your database and when it finds schemas and tables that it does not know about, it adds them and marks them as “found”.
2. If it finds a schema or table previously known to Basedash it marks them as “found”.
3. If a schema or table previously known to Basedash is no longer found in your database for some reason, Basedash marks them as “not found”.

The Basedash user interface only presents schemas and tables which are “found”. So, if you delete or rename a schema or table in your database, it will no longer be present in the Basedash user interface, say by browsing in the sidebar of the “Data” tab. Likewise, a table which is “not found” won’t be an option to use as the basis of a view.

This implementation has been working adequately. If you delete or rename a schema or table in your database itself, it remains known to Basedash but is simply marked as “not found” under the hood.

There are cases which are not handled so gracefully at the moment, such as, for an existing view, if the table that it is based on is renamed or deleted in your database itself, the view will be unable to load. (Under the hood, the view points to a table known to Basedash but marked as “not found”). We have plans to improve error messages when a view fails to load, and this is one case where an improved error message would go a long way, something along the lines of “This view is based on a table which is not found in the database X”.

Using Basedash to check on this user table

This customer turned out to have a view where the base table had been renamed in their database. The view failed to load.

As we often do in customer support cases, we use Basedash ourselves to help get to the bottom of things. With a bit of browsing, we can pull up the faulty view in question, find the user database, schema, and table it is based on, and take a look. Sure enough, that particular table was marked “not found”. We helped this customer to create the view anew with the desired table (the one with the new name) and the case was resolved.

Down the road we plan to more gracefully handle this edge case of table renaming, but being a startup and having to make hard choices about what to prioritize, at the moment we have this gap in functionality.

A handy tool made with Basedash

This support case underscored the importance of being able to quickly pull up user schemas and tables and see at a glance their “found” or “not found” status. So, as we often do, we created a view in Basedash—in this case an SQL view, which takes a user database ID, and reports on the schemas and tables within and their connection status.

Note the “id = 3” line. To use this SQL view, we replace that with the ID of the user database in question. Down the road we intend to support dynamic parameters to SQL views, so for example, a non-admin could make use of this view by setting an input in a field of the UI and seeing the results accordingly.

SELECT 
  *, 
  "Connected tables" = "Total tables" as "All good?" 
FROM 
  (
    SELECT 
      "Schema".name AS "Schema name", 
      "Schema".id AS "Schema id", 
      "Schema"."foundInDb" AS "Schema connected?", 
      COUNT(
        CASE WHEN "Table"."foundInDb" IS TRUE THEN TRUE ELSE NULL END
      ) AS "Connected tables", 
      COUNT("Table"."tableName") AS "Total tables" 
    FROM 
      "Table" 
      INNER JOIN "Schema" ON "Table"."schemaId" = "Schema".id 
      INNER JOIN "SqlDatabase" ON "Schema"."sqlDatabaseId" = "SqlDatabase".id 
    WHERE 
      "SqlDatabase".id = 3 -- the ID of the database in question
    GROUP BY 
      "Schema".name, 
      "Schema".id, 
      "Schema"."foundInDb"
  ) as subquery;

And here’s a related SQL view, for viewing the tables within a given schema and seeing their “found” status. Likewise note the line where we specify the ID of the schema in question.

SELECT 
  "Table"."tableName" as "Table name", 
  "Table"."foundInDb" as "Table found in DB" 
FROM 
  "Table" 
  INNER JOIN "Schema" ON "Table"."schemaId" = "Schema".id 
WHERE 
  "Schema".id = 17 -- the ID of the schema in question
ORDER BY 
  "Table name";

Conclusion

It can be extremely liberating to be able to take the power and expressiveness of custom SQL and create an internal tool to help you and your team get a picture of whats going on with your application, with your customers, and with issues that come up. We believe that the best products are ones that you make because you yourself find them useful. This is a nice case in point, a situation known as “dogfooding”, which we’ve written about elsewhere in our blog.