API feature "user_field_names" in database?

Technical Help
1

Is the user_field_names parameter exposed via a view/function/procedure in the database? I want provide users the ability to configure their own charts and visualizations but the tables do not have user-friendly column names.

On a related note, does anyone have any experience with using Redash?

2

Hey @CharlesC ,

Yes if you’d like to see the field names of a given table you can call:

https://api.baserow.io/api/database/fields/table/<TABLE_ID/

It will give you a list of fields and their corresponding name attribute.

3

Hey @Alex,

Thanks for the response. I’m familiar with making API calls.

My goal is to give users the option of creating their own dashboards with business intelligence tools such as Tableau or PowerBI. Most of these software packages work better when connecting to a standard database object, e.g. table or view.

The native tables in the Postgres do not expose the user-defined column names. I’m looking for a database artifact that essentially does the internal translation similar to the API call you provided.

Charles

4

Hey @CharlesC,

There is the database_field table in Postgres that has a name column which is the name of the field.

Are you able to join tables together in your setup? Because in that case you can join your database_table_x table onto database_field using table_id and you should get the corresponding names of the fields.

Does that help?

5

Hi @Alex,

Yes, those joins can be made and the result would show the actual field names. However, what I’m trying to get to is an actual ready-to-go table/view that can be queried with meaningful names.

In the case of a table with two columns, first_name and last_name, I want to be able to do this:

select first_name, last_name from my_table

Currently, the implementation is this:

select field_###, field_### from database_table_###

Thanks.

Charles

6

I see, so you would something like a postgres view that applies the human readable column name as an alias to the field columns.

As far as I am aware we do not have such a view implemented in the core, but given that you are self hosting you might be able to construct one yourself?

@nigel any thoughts on this?

7

Yeh as @Alex says, you could make a plugin which automatically makes an maintains a view per user table with user field names as the column names would be you only option here other than manually making views yourself.

The reason we use field_XYZ for column names are:

  1. We don’t want to have to issue ALTER TABLE statements whenever a user renames a field
  2. We want users to use field names like id and order which would clash with internal columns we put on user tables
  3. There are limits on postgres column names which we don’t want for our users:

Column names in PostgreSQL: Must contain only letters (a-z, A-Z), numbers (0-9), or underscores ( _ ) Must begin with a letter or underscore. Must be less than the maximum length of 59 characters.

8

I understand and appreciate the rationale behind the column naming convention. It’s core to what makes the platform robust and flexible.

After dwelling on the issue, I ultimately created a procedure that dynamically creates a view with the column names as defined in the database_field table. I also do this for linked and single-select fields as the names are stored differently.

The next step is to drop and recreate the views whenever a view’s corresponding/counterpart table is modified, e.g. modifying, deleting, or adding columns.