Updating questions databases through the API

There is 3 databases connected to my Metabase instance. My team and I are planning to copy all the tables from these databases to a fourth one, which will replace these others. After this migration, we'll disconnect the 3 dbs to mantain just the new one.

We have almost 900 questions and all of it will need to be updated to the new db. I checked the API docs to find a solution that may allow us to bulk change it, but as far as I understood this type of update cannot be done in this way.

Is there a easy way to update all the questions at once?

I know we can just edit the current db connections and point them to the new database, but I really wanted to choose a elegant solution to this issue.

Hi @renankw
So Serialization could perhaps help, but would still be some work involved with that:

The upcoming version will allow you to do changes via the Audit Tools.

Otherwise I would probably do this:

  • Make a backup of the application database. Absolutely essential.
  • Decide on which database you want to keep, lets say it's ID 1 (from metabase_database.id)
  • Then go and find any references to the two other databases and change their IDs to 1. There are many references, like metabase_table.db_id, report_card.database_id etc, but also inside some JSON blobs like report_card.dataset_query (which can be updated via jsonb query commands or plain text replace)
  • Remove the two other database, which should be possible, since there should be no constraints, since you have changed all the references to the single database.

@flamber thanks for your response.

We don't use the enterprise version, so serialization is not a option for us right now.

I've tried some things on our development instance database and I'll leave the code here so it may help someone in the future.

First, I fetched the databases IDs:

SELECT id, name FROM metabase_database

id  name
11	new_db
12	old_db_1
13	old_db_2
14	old_db_3

After that, I performed the changes you've mentioned in report_card table:

UPDATE report_card
SET database_id = 11
WHERE database_id IN (12, 13, 14)

UPDATE report_card
SET dataset_query = jsonb_set(dataset_query::jsonb, '{database}', '11')
WHERE database_id IN (12, 13, 14)

These 2 updates worked fine to upgrade SQL questions, but it broke the native query ones because of the links to the tables on metabase_table.

So I tried to perform the following update to fix the native query questions:

UPDATE metabase_table
SET db_id = 11
WHERE db_id IN (12, 13, 14)

And it returned this constraint violation error. It was caused because the new database has the same tables as the old 3:

ERROR: duplicate key value violates unique constraint "idx_uniq_table_db_id_schema_name"
DETAIL: Key (db_id, schema, name)=(11, public, calendario) already exists.

As we were at a development instance, I tried to drop the constraint just to check if the update would fix the native query questions:

ALTER TABLE metabase_table DROP CONSTRAINT idx_uniq_table_db_id_schema_name

I performed the update again and now all the questions work fine.

For obvious reasons I won't change anything in production tables structure, so the secure way will be upgrading SQL questions directly in the database and native query questions manually.

1 Like

@renankw Awesome that you posted your brain-storm.

So all these databases contains the same tables and columns? If yes, then it's going to be quite a lot harder, since you'll need to decide which database you then want to keep, and then you need to update the table+field references everywhere too.

You would then have to do a "lookup" for the old tables+fields, so you'll get the "new" ID from the matching tables+fields belonging to the database you'll keep.

It is definitely not a simple project that you're doing.

@flamber yes, the new database contains the same structure and data as the existing ones.

We decided to take the ugly path I mentioned before: after the data migration was complete, we just edited the existing connections, directing all of them to the new host. As time passes we'll edit the questions if needed and direct it them the new database too.

It's not an elegant solution but it turned out to be the fastest and most secure one, since we didn't need to update the table and fields references.