SQL Server

Lime CRM uses Microsoft SQL Server as its database.

Configuration

Each Lime Application on a Lime Server can have different connection details.

They can be configured as follows:

database:
    load_databases_from_registry: true
    cache_databases_in_registry: true
    engine:
        pool_size: 20
        max_overflow: 5
        pool_pre_ping: true
        isolation_level: READ UNCOMMITTED
        query_cache_size: 500

load_databases_from_registry

Controls where database connection information is loaded from. When enabled, it is loaded from the Windows registry. When disabled, it is loaded from application.yaml instead.

The default value is true.

This setting only works on Windows. On Unix/Linux, database information is always loaded from application.yaml.

cache_databases_in_registry

Controls whether database metadata from the Windows registry is cached to avoid repeated lookups. The cache is automatically invalidated when the registry changes.

Only effective when load_databases_from_registry is enabled.

The default value is true.

This setting only works on Windows.

pool_size

Sets the maximum number of connections kept persistently in the connection pool.

It is not possible to disable the pool by setting the pool_size to 0.

The default value is 20.

max_overflow

Defines how many additional connections, on top of pool_size, can be active. This means that the maximum number of active connections per process is pool_size + max_overflow.

The default value is 5.

Note that services using multiprocessing, for example when uWSGI is the webserver, mean the maximum number of connections has to be multiplied by the process count. Each worker process forked from the uWSGI main process will have its own connection pool. Lime CRM normally uses 2 worker processes when running uWSGI, so a pool size of 10 will show up to 20 persistent connections on the database server.

pool_pre_ping

Performs a simple SELECT 1 when a connection is checked out from the pool to ensure it is still alive. If it fails the connection is quietly re-established.

When disabled, checking out a dead connection will instead give an error the first time it is used.

The default value is true.

isolation_level

Defines how, and if, users can access the same data at the same time.

The default value is READ UNCOMMITTED. This means statements can read rows that have been modified by other transactions but not yet committed.

More information about isolation levels can be found on Microsoft Learn.

query_cache_size

Sets the size of the query cache, which stores compiled SQL statements.

The default value is 500.

The compiled SQL string and metadata for each unique statement are cached so that subsequent executions of the same statement skip the compilation step. When the cache reaches its limit, the least recently used entries are pruned.

More information about the query cache can be found in the SQLAlchemy documentation.