Critical Bug - Queries return null in published app but work in editor

[Adriano_Visconti]

I'm experiencing a critical bug where PostgreSQL queries work perfectly in the Retool editor but consistently return null data in the published app.

DETAILS:

• Database: PostgreSQL
• Query Type: SQL with component references ({{ email_LogIn.value }})
• Environment: Published Retool Cloud app
• Bug: Query executes successfully but returns null data arrays

EVIDENCE:

• Editor: loginUser.data contains correct user data
• Published App: loginUser.data returns null consistently
• Same query, same database, same parameters

ATTEMPTED SOLUTIONS:

• additionalScope parameters
• Global variables
• localStorage parameters
• Query reset/retry mechanisms
• JavaScript queries
• All solutions work in editor, none work in published app

This appears to be a known Retool bug affecting PostgreSQL queries in production environments. Please provide an immediate workaround or fix.

Thank you,

[Tess]

Hi @Adriano_Visconti,

Thanks for reaching out about this. So far, I haven't been able to reproduce this issue or find similar widespread reports of this issue

Here are some troubleshooting questions:

-Is the app using releases? If so, you may need to publish a new release in order to get the queries working in end user mode. You can click the top left corner to see which version of the app is being previewed. In the below screenshot, I am viewing "Latest" and I don't have releases set up yet, so every change I make in edit mode reflects immediately in end user mode:

-Are the queries set to trigger on page load or automatically?

-Can you check the debug tools (bottom right corner) in end user mode to see the state for the SQL query? The "query" property will show you what the dynamic references (i.e. {{ email_LogIn.value }}) are evaluating to:

[Adriano_Visconti]

Good morning and thanks for the reply.
What I noticed is that using DB Queries inside long JS scripts that perform multiple complex tasks, "await query.trigger" does not make the data immediately available. In this case, the parts of the code that are after the first query and that need the data coming from this query do not find this data and therefore fail. The correct way to have the data immediately available is to condition the result of the query with "onSuccess: function(data)".
In this way, even very long JS scripts and with reading and use of data coming from multiple queries work correctly.
I attach some examples:

** FUNDAMENTAL PROBLEM **: with () DOES NOT make data immediately available in query.data

** WRONG WAY** (DOESN'T WORK):

await checkUserExists.trigger();
// I dati NON sono disponibili subito
if (checkUserExists.data.user_id) { ... } // FALLISCE SEMPRE!

** RIGHT WAY** (IT WORKS):

await checkUserExists.trigger({
    onSuccess: function(data) {
        // I dati SONO disponibili qui nel parametro 'data'
        if (data.user_id && data.user_id.length > 0) { 
            const userId = data.user_id[0];
            // Continua logica qui...
        }
    }
});

CRUD Operations Template

// INSERT/UPDATE/DELETE con Callback
try {
  await wakeupDB.trigger();
  await crudQuery.reset();
  
  await crudQuery.trigger({
    onSuccess: function(data) {
      console.log('[DEBUG] CRUD success:', data);
      // Logica successo
      utils.showNotification({
        title: "Successo",
        description: "Operazione completata",
        notificationType: "success"
      });
    },
    onFailure: function(error) {
      console.error('[ERROR] CRUD error:', error);
      utils.showNotification({
        title: "Errore",
        description: "Operazione fallita: " + error.message,
        notificationType: "error"
      });
    }
  });
  
} catch (error) {
  console.error('[ERROR] Errore CRUD:', error);
}

Search/Select Template

// SELECT/SEARCH con Callback
try {
  await wakeupDB.trigger();
  await searchQuery.reset();
  
  await searchQuery.trigger({
    onSuccess: function(data) {
      console.log('[DEBUG] Search results:', data);
      
      if (!data || !data.id || data.id.length === 0) {
        result_text.setValue("Nessun risultato trovato");
        return;
      }
      
      // Elaborazione risultati
      const results = data.id.map((id, index) => ({
        id: id,
        name: data.name[index],
        email: data.email[index]
      }));
      
      console.log('[DEBUG] Processed results:', results);
      // Usa i risultati...
    },
    onFailure: function(error) {
      console.error('[ERROR] Search error:', error);
      result_text.setValue("Errore durante la ricerca");
    }
  });
  
} catch (error) {
  console.error('[ERROR] Errore ricerca:', error);
}