Connection options for SQL resource

[Roland_Alden]

I use an Azure SQL database with Retool. I recently modified its configuration to be “serverless” meaning when there are no queries to process it will “auto pause” and the cost will get close to zero (charges are for storage only, not compute). However, I was seeing no cost savings. In fact, costs went up because the cost of compute was higher. So, I investigated, and my working theory is that having an active connection with no query traffic crossing is sufficient to keep the database “up” and high charges apply.

ChatGPT sez: “Azure SQL Serverless requires the database to detect complete inactivity, which includes no active connections. Persistent connections, even if idle, are interpreted as activity, preventing the auto-pause mechanism from triggering.”

I also talk to this database through Google Cloud Functions and there I am using the Node driver provided by Microsoft. E.g.: const sql = require('mssql');

and then:
foo = new sql.ConnectionPool(sql_config);

Where sql_config looks like:

const sql_config = {
  user: process.env.AZURE_SQL_DATABASE_USERNAME,
  password: process.env.AZURE_SQL_DATABASE_USERPWD,
  server: process.env.AZURE_SQL_HOST,
  database: process.env.AZURE_SQL_DATABASE_NAME,
  options: {
    encrypt: true
  },
  pool: {
    max: 5, // Maximum number of connections in the pool
    min: 0, // Allow the pool to reduce connections to zero when idle
    idleTimeoutMillis: 30000 // Close idle connections after 30 seconds
  }
};

In this code I have explicit control over the creation of a connection pool via max, min, etc.

A Retool SQL connection resource has a section called "Connection options" which takes a set of key/value strings. However I don't know how these values might flow into the connection to the SQL server. If I were to define some "key/value" pairs in the Retool resource how would I express these key values? Would the "key" be, for example:

pool.max
pool.min

etc.?

or perhaps this?

key = pool 
value = { max: 5, min: 0, idleTimeoutMillis: 30000 }

??

[Tess]

Hi @Roland_Alden,

Thanks for joining office hours & posting here

The connection key value pairs aren't Retool specific features. Your resource should have connection options listed in their documentation (i.e. different options you can include in your connection string).

As far as connection pooling in Retool, I will refer you to this community post that recommends putting a connection pooler in front of your database so that you can manage and limit the connections. Connections are generally freed shortly after the query completes, but if you have a lot of users or queries being run simultaneously, you may run into a situation where a large amount of connections are opened (a max of 5 would likely be too low).

[Roland_Alden]

So the examples in my post are just from the documentation:

mssql

The problem here is that with a Retool resource I am not working directly with the mssql driver; I am working with a layer that Retool provides.

Assuming this is a very thin layer then yes, it may be possible for me to pass in values that end up populating:

pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
}

But how? A Retool resource is not a Node or a Microsoft thing, it is a Retool thing. I need Retool to explain how it works.

I realize that the correct values for max and min and idle timeout may be tricky to work out. But unless I can actually set them that does not matter.

I read the post you referenced. It sounds like other people are having trouble because a Retool "resource" is a black box. But I didn't find anything that might help me.

A simple question. Are how these key/value pairs map onto the mssql driver documented anywhere?

[Tess]

Hi Roland,

The optional parameters you define there will be appended to the connection string for your resource

[Roland_Alden]

So. What is meant by "connection string"?

The MSFT documentation for their Node driver shows an Object being the main parameter:

const config = {
    user: 'username', // better stored in an app setting such as process.env.DB_USER
    password: 'password', // better stored in an app setting such as process.env.DB_PASSWORD
    server: 'your_server.database.windows.net', // better stored in an app setting such as process.env.DB_SERVER
    port: 1433, // optional, defaults to 1433, better stored in an app setting such as process.env.DB_PORT
    database: 'AdventureWorksLT', // better stored in an app setting such as process.env.DB_NAME
    authentication: {
        type: 'default'
    },
    options: {
        encrypt: true
    }
}

Of course I don't know if you are actually using this API in the engine room because Retool is collecting information (such as user/password/server) in a form associated with the "resource".

Since this is a Javascript object, not a "string", it is not clear exactly what "appended" might mean or how a key/value pair would be mapped into the object.

To be clear

1. The Microsoft documentation for their Node driver is clear about how to go about specifying the details for a connection pool.
2. It is not clear how to do in Retool.

[Tess]

Hi @Roland_Alden,

We already set pool to a max of 10 and min of 0. We don't expose a setting in the resource for configuring this on Cloud. The only customization we expose is the connection string options (i.e. this section options: {encrypt: true}, in your example). If you had other options listed there besides encrypt, you could add them to Retool in the Connection options UI.

Do you have access to any Azure SQL logs that show Retool is maintaining idle connections? It might also be worth double checking workflow run history & app audit logs around the time you'd expect Retool to not be connected to your resource. Again, we should be freeing connections shortly after queries complete, but I can review any logs you have with my team internally.

[Roland_Alden]

As a matter of fact I have been studying Azure logs and I see that if I close all my Retool app browser windows indeed database sessions drops to zero.

Long ago I wrote some code which polls the database in various places in order to refresh the screen as there are various other processes (including some that are outside of Retool) that can add data. Restructuring this code to be more reactive/event driven will be a bunch of work.

A quick hack would be to have some code that detects when a browser has actually been "touched" by the user and allows this polling/update code to run only if the user has touched the browser in the last n minutes.

Do you know of any sort of "user-browser activity" API or method that would enable me to do this?

[Tess]

Hey @Roland_Alden,

I'm not aware of an api/method that would be helpful here I checked in with a handful of folks internally as well. Definitely let me know if you find something!

Not quite the same as browser activity, but happy to review query logs during a given timeframe if you'd like me to

[Roland_Alden]

I found this trick and it seems to work:

foo.setValue(document.visibilityState === "visible");

In my code where I am hitting an expensive resource I guard it with if (foo.value){}.

My friendly ChatGPT sez:

document.visibilityState is a property of the Page Visibility API that tells whether a web page is currently visible to the user or hidden (e.g., in a background tab).