Skip to content
Phone Guides

Asterisk databases, ODBC and realtime configuration

A database connection, a dialplan query and realtime configuration are separate arrangements. Decide which data belongs in each path before replacing text files or adding a synchronous query to a caller's route.

Source-reviewed, 20 October 2026. Prepared from the cited documentation. Database integrations have not been independently tested by Phone Guides. Scope: Asterisk 22 design, with Sorcery mappings and PJSIP schema details checked against the 22.11.0 source tag; exact database engine, driver and deployed schema still need their own versioned checks.

Name the data path

Path Purpose Failure to plan for
ODBC connection resource Supplies a configured connection through the chosen driver Driver/DSN/credential/network failure
Dialplan database query Obtains or updates data used during a call Slow query, invalid input or unavailable backend
Realtime configuration Supplies supported Asterisk objects/settings from a backend Missing object, incompatible schema or stale/cached state
CDR/CEL backend Stores reporting information Failed writes and incomplete reports

The official ODBC documentation is a starting point. Choose the exact module and backend reference for the job. Do not treat a successful database login as evidence that the required Asterisk objects or reports are correct.

Map selected PJSIP objects to realtime

For the Asterisk 22 PJSIP realtime example below, the supported path is the realtime wizard backed by the configured database connector. The PJSIP realtime guide names endpoint, auth, AOR, domain alias, contact and IP-identify tables. The tagged Asterisk 22.11.0 Sorcery sample explicitly lists endpoint, auth, AOR and domain-alias mappings under res_pjsip, and IP identify under the separate res_pjsip_endpoint_identifier_ip module. Its sample does not include contact; the upstream realtime guide calls contact optional for its example. Add that mapping only when the deployed design stores contacts through realtime and its schema matches the selected release. Do not infer that every Sorcery object or every table in extconfig.conf should be mapped.

Use an explicit mapping list and keep transports in pjsip.conf:


[res_pjsip]
endpoint=realtime,ps_endpoints
auth=realtime,ps_auths
aor=realtime,ps_aors
domain_alias=realtime,ps_domain_aliases
contact=realtime,ps_contacts

[res_pjsip_endpoint_identifier_ip]
identify=realtime,ps_endpoint_id_ips

The realtime wizard resolves each named family through [settings] in extconfig.conf. The tagged Asterisk 22.11.0 extconfig sample shows the backend mapping format. For an ODBC connection named pg_reference, entries for the selected mappings can be:


[settings]
ps_endpoints => odbc,pg_reference
ps_auths => odbc,pg_reference
ps_aors => odbc,pg_reference
ps_domain_aliases => odbc,pg_reference
ps_contacts => odbc,pg_reference
ps_endpoint_id_ips => odbc,pg_reference

The names after realtime, are the mapped families, and the odbc entries select the driver and connection resource. Table names and available fields must match the selected Asterisk branch's migration scripts and the exact loaded modules. The official guide says the IP-identify object comes from an optional module and warns that some object types, including transports, can behave incorrectly when stored in realtime. Keep transports statically configured unless version-specific upstream guidance establishes otherwise.

If a deployment intentionally combines file and realtime sources, Sorcery checks mappings in order. For example, endpoint=realtime,ps_endpoints followed by endpoint=config,pjsip.conf,criteria=type=endpoint makes the database the first lookup source and the file the later source. Verify duplicate names and precedence before relying on fallback. A second source does not turn a partial or stale database result into an atomic failover design.

Check schema and object identity separately

The upstream Alembic guide explains that Asterisk's migration scripts track a schema revision and that scripts can differ across release branches. The 22.11.0 PJSIP migration chain creates the PJSIP tables. Later revisions in that same tagged chain add contact-routing columns and [add endpoint while changing expiration_time to BIGINT](https://github.com/asterisk/asterisk/blob/22.11.0/contrib/ast-db-manage/config/versions/ef7efc2d3964_ps_contacts_add_endpoint_and_modify_.py). A table made from an old tutorial or hand-written from an early schema can therefore be missing fields or have obsolete types.

Use the migration directory shipped with the deployed Asterisk source, record its revision and database engine/driver versions, and compare the current database revision before changing it. upgrade head means the head of the migration files selected for that run; it is not a portable target across releases. Do not copy an older article's package-install steps or treat migration downgrade as a safe rollback plan. Back up the database and rehearse the branch change against a restorable copy; recovery may require restoring data, not only switching the Asterisk binary.

For a reader-side check, inspect one known object end to end: confirm its table/columns against that branch's migration output, read the row by its exact configured ID, then ask Asterisk for the corresponding PJSIP object using the version's documented CLI. Confirm the endpoint's referenced auth and AOR IDs exist and that an identify row is visible through the IP identifier module when that path is used. A schema revision match proves migration state only. It does not prove table mapping, row contents, module loading, object lookup or a phone registration.

Decide how cached objects become visible

Sorcery can add memory_cache before the realtime mapping for an object type. The order matters: Asterisk checks the cache first, then the backend. The official Sorcery caching guide states that a cache with no expiry or staleness settings can retain old values indefinitely. Define a deliberate update path before enabling it.

For example, endpoint caching with expiry on a res_pjsip reload can be configured as:


[res_pjsip]
endpoint/cache=memory_cache,expire_on_reload=yes
endpoint=realtime,ps_endpoints

This sample sets no time-based expiry. A database change may therefore remain hidden until the cache is cleared by the configured module reload or a documented cache command. If using stale refresh or maximum lifetimes, choose values from the deployment's change and outage needs, then document the expected stale-read interval. Cache invalidation is scoped by object type and module; do not assume that reloading an unrelated module refreshes every PJSIP object.

Before enabling a cache, record the current object output. Change one non-critical test object, inspect the object again without cache actions, then use the exact cache invalidation or module reload path documented for the deployed release and read it back. This is a reader procedure, not a Phone Guides test result. The cache can change which backend reads occur and when new data takes effect; it does not make the database highly available or establish safe failure behaviour.

Start with a fixed read-only probe

Before allowing a caller-dependent query, establish a connection and a predictable result without subscriber input. The following original example assumes an existing system DSN called pg-guide-dsn, a driver/database combination supporting SELECT 1, and a suitably restricted database identity. It does not install a driver, create a database or provision authentication.

An illustrative connection resource in the configuration source owning res_odbc.conf is:


[pg_reference]
enabled => yes
dsn => pg-guide-dsn
pre-connect => no

Here pg_reference is the Asterisk resource name; pg-guide-dsn is the separately configured driver-manager DSN. They need not have the same name. Follow the exact driver/database documentation for authentication and protected credential storage. Keep real credentials out of examples, tickets and diagnostic output.

The tagged Asterisk 22.11.0 ODBC sample defines connection options. pre-connect => no avoids treating startup-time connection as the demonstration's success signal; the query still needs a working connection when invoked. Set connection and query limits according to the supported driver/module path before allowing a call-time dependency.

In the configuration owning func_odbc.conf, define only a fixed read:


[PG_PROBE]
readhandle=pg_reference
readsql=SELECT 1

The tagged function sample describes handles, SQL statements and function naming. This section uses the default ODBC function prefix and provides no write statement or SQL argument. Do not copy a generic function that accepts its entire query from caller-controlled dialplan input.

A small isolated route can check the returned marker:


[pg-odbc-demo]
exten => 6095,1,Set(PG_PROBE=${ODBC_PG_PROBE()})
 same => n,GotoIf($["${PG_PROBE}" = "1"]?ready:unavailable)
 same => n(ready),NoOp(Database probe returned expected marker)
 same => n,Hangup()
 same => n(unavailable),NoOp(Database probe did not establish readiness)
 same => n,Hangup()

Inspect for context/number collisions and grant only the intended internal access. A failed or missing expected marker takes the unavailable path; the example neither authorises a caller nor routes to an external number. It does not answer or play a prompt. A working probe establishes only this fixed query, not realtime object mappings, database writes or report completeness.

If your engine needs different probe SQL, use its documented harmless equivalent. Keep a driver failure, an authentication failure and an unexpected result distinct in private diagnostic evidence. Do not log a full connection string to explain them.

Version production schemas with the application

Use the selected release's migration material and check the database currently in use before changing tables. The upstream Alembic/realtime guide describes separate schema paths and warns about cross-series changes and data loss. Its historical dependency-install examples are not a current OS installation prescription.

Record migration revision, engine/driver versions, applicable Asterisk branch and rollback limitations. Back up the database consistently before a migration. Restoring only an older Asterisk binary is insufficient if the schema or data also changed.

Keep database authority narrow

Use a dedicated runtime account with only the operations its path needs. Keep schema-migration authority separate where practical. Protect connection credentials and do not print connection strings containing secrets in error messages or example output.

Validate caller and external input before using it in a query. Follow the selected database/interface's documented parameterisation or escaping rules; do not interpolate arbitrary caller text into SQL because the query lives inside a dialplan function.

Define outage behaviour

For every call-time query, state a connection/read time budget and the fallback. A timeout must not accidentally authorise a caller or create an empty destination that matches a broader route. Use a clear distinction between no matching record and failure to obtain a result. For realtime configuration, separately define what the system should do if a lookup or update reaches an unavailable backend; the source material here does not establish a universal Asterisk 22 fallback across object types and modules.

For realtime configuration, record the supported object types, mappings and caching behaviour for the actual module. Confirm which changes become visible through which reload or refresh action. Do not delete a text configuration until the new ownership and recovery path are established.

Reader checks

On your deployment, inspect the connection, query the intended record and read back the resulting Asterisk object or report. Include a missing record, wrong credentials, unavailable database and a slow response. Verify the caller's bounded fallback and absence of unintended writes.

Keep schema/data recovery with backup planning and application changes with upgrade planning. The official database/realtime hub links detailed backends; its unfinished overview is not evidence that every module supports every database.

Return to Asterisk documentation.