The easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:
TypeScript analyticsbibusiness-intelligencebusinessintelligenceclojuredashboarddatadata-analysisdata-visualizationdatabasemetabasemysqlpostgrespostgresqlreportingslacksql-editorvisualization
import {
QA_DB_CONFIG,
QA_DB_CREDENTIALS,
QA_MONGO_PORT,
QA_MYSQL_PORT,
QA_POSTGRES_PORT,
WRITABLE_DB_CONFIG,
WRITABLE_DB_ID,
} from "e2e/support/cypress_data";
import { createQuestion } from "./api";
/*****************************************
** QA DATABASES **
******************************************/
const SYNC_RETRY_DELAY_MS = 500;
export function addMongoDatabase(displayName = "QA Mongo") {
const { host, user, password, database: dbName } = QA_DB_CREDENTIALS;
const port = QA_MONGO_PORT;
// https://hub.docker.com/layers/metabase/qa-databases/mongo-sample-4.4/images/sha256-8cdeaacf28c6f0a6f9fde42ce004fcc90200d706ac6afa996bdd40db78ec0305
return addQADatabase({
engine: "mongo",
displayName,
details: {
"advanced-options": false,
"use-conn-uri": true,
"conn-uri": `mongodb://${user}:${password}@${host}:${port}/${dbName}?authSource=admin`,
"tunnel-enabled": false,
},
});
}
export function addPostgresDatabase(
displayName = "QA Postgres12",
writable = false,
dbName,
idAlias,
) {
// https://hub.docker.com/layers/metabase/qa-databases/postgres-sample-12/images/sha256-80bbef27dc52552d6dc64b52796ba356d7541e7bba172740336d7b8a64859cf8
return addQADatabase({
engine: "postgres",
displayName,
dbName,
port: QA_POSTGRES_PORT,
enable_actions: writable,
idAlias,
});
}
export function addMySQLDatabase({
displayName = "QA MySQL8",
writable = false,
}) {
// https://hub.docker.com/layers/metabase/qa-databases/mysql-sample-8/images/sha256-df67db50379ec59ac3a437b5205871f85ab519ce8d2cdc526e9313354d00f9d4
return addQADatabase({
engine: "mysql",
displayName: displayName,
port: QA_MYSQL_PORT,
enable_actions: writable,
});
}
function addQADatabase({
engine,
displayName,
dbName,
port,
enable_actions = false,
idAlias,
details,
}) {
const OPTIONS = engine === "mysql" ? "allowPublicKeyRetrieval=true" : null;
const db_name =
dbName ??
(enable_actions
? WRITABLE_DB_CONFIG[engine].connection.database
: QA_DB_CREDENTIALS.database);
const credentials = enable_actions
? WRITABLE_DB_CONFIG[engine].connection
: QA_DB_CREDENTIALS;
cy.log(`**-- Adding ${engine.toUpperCase()} DB --**`);
return cy
.request("POST", "/api/database", {
engine: engine,
name: displayName,
details: details ?? {
dbname: db_name,
host: credentials.host,
port: port,
user: credentials.user,
password: QA_DB_CREDENTIALS.password,
"additional-options": OPTIONS,
"tunnel-enabled": false,
},
auto_run_queries: true,
is_full_sync: true,
schedules: {
cache_field_values: {
schedule_day: null,
schedule_frame: null,
schedule_hour: 0,
schedule_type: "daily",
},
metadata_sync: {
schedule_day: null,
schedule_frame: null,
schedule_hour: null,
schedule_type: "hourly",
},
},
})
.then(({ status, body }) => {
expect(status).to.equal(200);
cy.wrap(body.id).as(idAlias ?? `${engine}ID`);
})
.then((dbId) => {
// Make sure we have all the metadata because we'll need to use it in tests
assertOnDatabaseMetadata(engine);
// it's important that we don't enable actions until sync is complete
if (dbId && enable_actions) {
cy.log("**-- Enabling actions --**");
cy.request("PUT", `/api/database/${dbId}`, {
settings: { "database-enable-actions": true },
}).then(({ status }) => {
expect(status).to.equal(200);
});
}
});
}
function assertOnDatabaseMetadata(engine) {
cy.request("GET", "/api/database").then(({ body }) => {
const { id } = body.data.find((db) => {
return db.engine === engine;
});
recursiveCheck(id);
});
}
function recursiveCheck(id, i = 0) {
// Let's not wait more than 20s for the sync to finish
if (i === 40) {
cy.task(
"log",
"The DB sync isn't complete yet, but let's be optimistic about it",
);
return;
}
cy.wait(SYNC_RETRY_DELAY_MS);
cy.request("GET", `/api/database/${id}`).then(({ body: database }) => {
cy.task("log", {
dbId: database.id,
syncStatus: database.initial_sync_status,
});
if (database.initial_sync_status !== "complete") {
recursiveCheck(id, ++i);
} else {
recursiveCheckFields(id);
}
});
}
function recursiveCheckFields(id, i = 0) {
// Let's not wait more than 10s for the sync to finish
if (i === 20) {
cy.task("log", "The field sync isn't complete");
return;
}
cy.wait(SYNC_RETRY_DELAY_MS);
cy.request("GET", `/api/database/${id}/schemas`).then(({ body: schemas }) => {
const [schema] = schemas;
if (schema) {
cy.request(
"GET",
`/api/database/${id}/schema/${encodeURIComponent(schema)}`,
)
.then(({ body: schema }) => {
return schema[0].id;
})
.then((tableId) => {
cy.request("GET", `/api/table/${tableId}/query_metadata`).then(
({ body: table }) => {
const field = table.fields.find(
(field) => field.semantic_type !== "type/PK",
);
if (!field.last_analyzed) {
recursiveCheckFields(id, ++i);
}
},
);
});
}
});
}
export const setupWritableDB = (type = "postgres") => {
cy.task("createWritableDb", { type });
};
export function queryQADB(query, type = "postgres") {
return cy.task("connectAndQueryDB", {
connectionConfig: QA_DB_CONFIG[type],
query,
});
}
/**
* Executes a query against a writable database
* @param {string} query - The SQL query to execute
* @param {("postgres"|"mysql")} [type="postgres"] - Database type to connect to
* @returns {Cypress.Chainable<{rows: any[]}>} - Cypress chainable that resolves to query results
*/
export function queryWritableDB(query, type = "postgres") {
return cy.task("connectAndQueryDB", {
connectionConfig: WRITABLE_DB_CONFIG[type],
query,
});
}
export function resetWritableDb({ type = "postgres" }) {
cy.log(`Resetting ${type} writable DB`);
cy.task("resetWritableDb", { type });
}
/**
* Note: this function MUST come after the restore() function in the file, or it will get wiped out
*/
export function resetTestTable({ type, table }) {
cy.task("resetTable", { type, table });
}
export function createTestRoles({ type, isWritable }) {
cy.task("createTestRoles", { type, isWritable });
}
// will this work for multiple schemas?
/**
* @param {Object} obj
* @param {number} [obj.databaseId] - Defaults to WRITABLE_DB_ID
* @param {string} obj.name - The table's real name, not its display name
* @param {string} [obj.schema] - The table's schema name
*/
export function getTableId({ databaseId = WRITABLE_DB_ID, name, schema }) {
return cy.request("GET", "/api/table").then(({ body: tables }) => {
const table = tables.find(
(table) =>
table.db_id === databaseId &&
table.name === name &&
(schema ? table.schema === schema : true),
);
if (!table) {
throw new TypeError(`Table with name ${name} cannot be found`);
}
return table.id;
});
}
export function getFieldId({ tableId, name }) {
return cy
.request("GET", `/api/table/${tableId}/query_metadata`)
.then(({ body: table }) => {
const fields = table.fields ?? [];
const field = fields.find((field) => field.name === name);
if (!field) {
throw new TypeError(`Field with name ${name} cannot be found`);
}
if (typeof field.id !== "number") {
throw new TypeError("Unexpected non-integer field id.");
}
return field.id;
});
}
export function getTable({ databaseId = WRITABLE_DB_ID, name }) {
return cy
.request("GET", `/api/database/${databaseId}/metadata`)
.then(({ body }) => {
const table = body?.tables?.find((table) => table.name === name);
return table || null;
});
}
export const createModelFromTableName = ({
tableName,
modelName = "Test Action Model",
idAlias = "modelId",
}) => {
getTableId({ name: tableName }).then((tableId) => {
createQuestion(
{
database: WRITABLE_DB_ID,
name: modelName,
query: {
"source-table": tableId,
},
type: "model",
},
{
wrapId: true,
idAlias,
},
);
});
};
const RESYNC_TRIGGER_INDEX = 10;
const MAX_RESYNC_ITERATIONS = 40;
export function waitForSyncToFinish({
iteration = 0,
dbId = 2,
tableName = "",
tableAlias,
tables = [],
retrigger = false,
}) {
// 40 x 500ms (20s) should be plenty of time for the sync to finish.
if (iteration === MAX_RESYNC_ITERATIONS) {
throw new Error("The sync is taking too long. Something is wrong.");
}
// `sync_schema` submits to a single-threaded task pool and can be queued
// behind a slow task or bump into an already-running sync, so in some scenarios one-shot
// schema sync may be silently dropped. If we assume such scenario, we force
// schema sync to be retriggered occasionally.
// (https://linear.app/metabase/issue/QUE2-663/calls-to-sync-schema-are-occasionally-silently-dropped)
if (retrigger && iteration > 0 && iteration % RESYNC_TRIGGER_INDEX === 0) {
cy.request("POST", `/api/database/${dbId}/sync_schema`);
}
cy.wait(SYNC_RETRY_DELAY_MS);
const rerunSync = () =>
waitForSyncToFinish({
iteration: iteration + 1,
dbId,
tableName,
tables,
tableAlias,
retrigger,
});
return cy
.request("GET", `/api/database/${dbId}/metadata`)
.then(({ body }) => {
if (!body.tables.length) {
return rerunSync();
}
if (tables.length) {
const completed = new Set(
body.tables
.filter((table) => table.initial_sync_status === "complete")
.map((table) => table.name),
);
return tables.every((name) => completed.has(name)) ? null : rerunSync();
}
if (tableName) {
const table = body.tables.find(
(table) =>
table.name === tableName &&
table.initial_sync_status === "complete",
);
if (!table) {
return rerunSync();
}
if (tableAlias) {
cy.wrap(table).as(tableAlias);
}
return null;
}
});
}
/**
* @param {object} options
* @param {number} [options.dbId]
* @param {string} [options.tableName] - wait until this table is synced
* @param {string} [options.tableAlias]
* @param {string[]} [options.tables] - wait until all of these tables are synced
* @param {boolean} [options.retrigger] - occasionally re-trigger the schema sync while waiting
*/
export function resyncDatabase({
dbId = 2,
tableName = "",
tableAlias = undefined,
tables = [],
retrigger = false,
}) {
// must be signed in as admin to sync
cy.request("POST", `/api/database/${dbId}/sync_schema`);
cy.request("POST", `/api/database/${dbId}/rescan_values`);
return waitForSyncToFinish({
iteration: 0,
dbId,
tableName,
tables,
tableAlias,
retrigger,
});
}
export function addSqliteDatabase(displayName = "sqlite") {
return addQADatabase({
engine: "sqlite",
displayName,
details: { db: "./resources/sqlite-fixture.db" },
});
}