@jcook78, here's an almost fully automated approach.
Create a variable to hold the latest version latestAppVersion.
Add a version table app_versions:
Create a simple query to pull the latest version (or a list of them) getAppVersions:
select * from app_versions
order by updated_at desc
Note: order by updated_at desc is important in this case. You could also just pull the latest with LIMIT 1.
Create an upsert query upsertAppVersion:
INSERT INTO app_versions (app_version, app_id)
VALUES ({{ appVersion }}, {{ appId }})
ON CONFLICT (app_version, app_id) DO NOTHING;
Add a JS query checkAndSetAppVersion and set it to trigger on page load:
console.log("checkAndSetAppVersion started");
const appId = retoolContext.appUuid;
let appVersion = retoolContext.pageTag;
// Step 1: Trigger `getAppVersions` and capture latest version
const result = await getAppVersions.trigger();
console.log("Raw result from getAppVersions:", result);
// Step 2: Extract the latest version from returned data
const latestVersion = result?.app_version?.[0] || null;
console.log("Latest version from DB:", latestVersion);
// Step 3: Set `latestAppVersion`
latestAppVersion.setValue(latestVersion);
console.log("latestAppVersion set to:", latestVersion);
// Step 4: Determine which version to upsert
if (!appVersion || appVersion === "latest") {
console.log("Using latest version from DB as appVersion");
appVersion = latestVersion;
}
console.log("Final appVersion to upsert:", appVersion);
// Step 5: If appVersion is valid, trigger the upsert query
if (appVersion) {
console.log("Triggering upsertAppVersion...");
await upsertAppVersion.trigger({
additionalScope: { appVersion, appId },
onSuccess: async function() {
console.log("Upsert successful.");
// Step 6: Set `latestAppVersion` to the upserted value
latestAppVersion.setValue(appVersion);
console.log("latestAppVersion updated after upsert:", appVersion);
// Step 7: Refresh `getAppVersions`
console.log("Refreshing getAppVersions...");
await getAppVersions.trigger();
},
onFailure: function(error) {
console.error("Upsert failed:", error);
}
});
} else {
console.warn("No valid app version found. Skipping upsert.");
}
Once set up you just need to have open the user mode open while or after developing without ?_releaseVersion=latest in the URL.
That should be it. Now whenever you create a new release, go over to the user mode and hit refresh on the page. This will upsert the new version if needed. In public mode you will see the most recent version recorded in the table.
Hopefully this helps!
