Use the Database Connections API in PyQGIS

Older PyQGIS scripts talk to databases in a patchwork of ways: psycopg2 for PostgreSQL with credentials typed into the script, sqlite3 for GeoPackage, provider-specific URI strings assembled by hand. Since the 3.10 series QGIS has offered one abstraction instead — the provider connections API — which gives every database provider the same interface: list schemas and tables, run SQL, create and drop tables, and build layer URIs, all using the connections saved in QGIS's Browser with their stored authentication.

This recipe belongs to PostGIS & Database Workflows. It finds stored connections, explores schemas and tables, runs SQL and reads results, manages tables, loads layers from table metadata, and writes helpers that work the same on PostGIS, GeoPackage and SpatiaLite.

One interface, many databasesThe provider registry returns metadata for each database provider: postgres, ogr for GeoPackage, spatialite, mssql, oracle and hana. Each provider's metadata lists stored connections by name. A connection object offers the same methods everywhere: schemas, tables with geometry column details, executeSql, createVectorTable, dropVectorTable and tableUri for building layers.Same methods for every providerproviderspostgresogr (GeoPackage)spatialitemssql · oraclefindConnectionby stored nameauth includedconnectionschemas() · tables()executeSql()createVectorTable()tableUri()

Prerequisites

  • QGIS 3.34 LTR or newer, or the QGIS 4 series.
  • At least one database connection saved in QGIS's Browser — PostGIS, GeoPackage or SpatiaLite. Saving it with an authentication configuration keeps passwords out of scripts, as in storing credentials with QgsAuthManager.

Find stored connections

Each database provider's metadata knows its saved connections. Listing them is the first step of any script that should not hard-code connection details.

from qgis.core import QgsProviderRegistry

registry = QgsProviderRegistry.instance()
for key in ("postgres", "ogr", "spatialite", "mssql"):
    md = registry.providerMetadata(key)
    if md is None:
        continue
    try:
        conns = md.connections()
    except Exception:
        continue
    for name, conn in conns.items():
        print(f"{key:<11} {name:<24} {type(conn).__name__}")

pg = registry.providerMetadata("postgres").findConnection("assets_db")
print(pg.uri())

Breakdown: connections() returns a dictionary of name to connection object for providers that support the API. findConnection(name) fetches one by the name shown in the Browser. The connection carries its URI, including the authentication configuration id rather than a password, so scripts never handle credentials. For GeoPackage, connections saved in the Browser appear under the ogr provider; a GeoPackage that is not saved can be wrapped directly with md.createConnection(path, {}).

Explore schemas and tables

The connection lists schemas and tables with metadata about each: whether it is a view, which geometry columns it has, their type and CRS, and the primary key.

from qgis.core import QgsAbstractDatabaseProviderConnection as Conn

for schema in pg.schemas():
    if schema.startswith("pg_") or schema == "information_schema":
        continue
    for t in pg.tables(schema):
        geom = t.geometryColumnTypes()
        kind = "view" if t.flags() & Conn.View else "table"
        g = ", ".join(f"{c.wkbType.name if hasattr(c.wkbType, 'name') else c.wkbType} {c.crs.authid()}"
                      for c in geom) or "no geometry"
        print(f"{schema}.{t.tableName():<28} {kind:<5} pk={t.primaryKeyColumns()} {g}")

Breakdown: tables(schema) returns table property objects; geometryColumnTypes() lists each geometry column's type and CRS, and primaryKeyColumns() the key. Flags distinguish views and materialised views from tables, which matters because views often lack a usable key. This inventory is the basis for documentation, for checking that every table has a spatial index and key, and for loading layers by metadata rather than by hand-built strings.

A database inventoryThe inventory loop produces one row per table with schema, name, kind, primary key, geometry type and CRS. Problems stand out: a view without a primary key cannot be edited and loads slowly, a table with geometry but a different CRS from the rest breaks spatial joins, and a table with no geometry is plain data. The same loop can check for missing spatial indexes.What is in the database, and what is oddwater.hydrants table pk=hydrant_pk Point 25832water.mains table pk=main_pk MultiLineString 25832water.v_old_valves view pk=[] Point 31467 ← no key, other CRSwater.meter_readings table pk=reading_id no geometry

Run SQL and read results

executeSql runs any statement and returns rows as lists. Queries return data; DDL and DML return an empty list.

rows = pg.executeSql("""
    SELECT district, count(*) AS n, round(sum(ST_Length(geom))::numeric / 1000, 1) AS km
    FROM water.mains
    WHERE material = 'CI'
    GROUP BY district ORDER BY km DESC LIMIT 5
""")
for district, n, km in rows:
    print(f"{district:<16} {n:>5} mains  {km:>8} km")

pg.executeSql("UPDATE water.hydrants SET checked = false WHERE last_check < now() - interval '1 year'")

Breakdown: Results come back as Python values, ready for printing, pandas or writing to layers. Aggregating in the database means only the summary crosses the network — far faster than loading the mains into QGIS and summing there. Statements run in their own transaction; for several statements that must succeed together, put them in one call separated by semicolons, or use BEGIN … COMMIT explicitly. Build SQL from user input with care: the API does not parameterise queries, so quote identifiers and escape literals yourself or restrict inputs to known values. More SQL patterns are in executing SQL on PostGIS.

Create and drop tables

The connection can create an empty spatial table with the right columns, geometry type and CRS — in any supported database — without writing provider-specific DDL.

from qgis.core import QgsFields, QgsField, QgsWkbTypes, QgsCoordinateReferenceSystem
from qgis.PyQt.QtCore import QVariant

fields = QgsFields()
fields.append(QgsField("valve_no", QVariant.String, len=20))
fields.append(QgsField("diameter_mm", QVariant.Int))
fields.append(QgsField("installed", QVariant.Date))

pg.createVectorTable("water", "valves_new", fields, QgsWkbTypes.Point,
                     QgsCoordinateReferenceSystem("EPSG:25832"), True,
                     {"geometryColumn": "geom", "primaryKey": "valve_pk"})
print([t.tableName() for t in pg.tables("water")][-3:])
# pg.dropVectorTable("water", "valves_new")

Breakdown: createVectorTable takes schema, table name, fields, geometry type, CRS, an overwrite flag and options such as the geometry column and primary key names. The same call creates a GeoPackage table when pg is an ogr connection, which is what makes helpers portable across databases. dropVectorTable removes a table with its geometry registration; it is commented out here because there is no undo. Renaming and schema management (createSchema, renameVectorTable) follow the same pattern.

Audit spatial indexes

A spatial table without a spatial index works but is slow for every map draw and spatial query. Combining the table inventory with a catalogue query finds the tables that lack one.

indexed = {(r[0], r[1]) for r in pg.executeSql("""
    SELECT n.nspname, c.relname
    FROM pg_index i
    JOIN pg_class ci ON ci.oid = i.indexrelid
    JOIN pg_am am ON am.oid = ci.relam AND am.amname = 'gist'
    JOIN pg_class c ON c.oid = i.indrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
""")}
for t in pg.tables("water"):
    if t.geometryColumnTypes() and ("water", t.tableName()) not in indexed:
        print("no spatial index:", t.tableName())

Breakdown: The catalogue query lists every table with at least one GiST index, the index type PostGIS uses for geometry. Any table in the inventory with geometry but absent from that set has no spatial index — a one-line CREATE INDEX … USING gist (geom) fixes it. Views never have indexes of their own; their speed depends on the underlying tables. Running an audit like this after imports is cheap and catches the most common cause of a "slow PostGIS layer".

Load layers from table metadata

Instead of assembling provider URIs by hand, ask the connection for the URI of a table and create the layer from it.

from qgis.core import QgsVectorLayer, QgsProject

for t in pg.tables("water"):
    if not t.geometryColumnTypes():
        continue
    uri = pg.tableUri("water", t.tableName())
    lyr = QgsVectorLayer(uri, t.tableName(), "postgres")
    if lyr.isValid():
        QgsProject.instance().addMapLayer(lyr)
    else:
        print("could not load", t.tableName())

Breakdown: tableUri produces a URI with the connection's authentication, schema, table, geometry column and key, exactly as the Browser would when dragging the table onto the map. For tables with several geometry columns, set the column explicitly with QgsDataSourceUri after decoding. Loading the whole schema this way is a quick way to open a database's contents in a fresh project, and it works unchanged for GeoPackage connections with the ogr provider key.

Write helpers that work across databases

Because every connection shares the interface, a helper written once works for PostGIS during production and for a GeoPackage during testing.

Same helper, different backendsA helper function receives a connection object and calls only API methods: tables, executeSql with standard SQL, tableUri. Passed a PostGIS connection it works against the server; passed a GeoPackage connection created from a local file it works offline in tests. Only SQL dialect differences need care.Test on a file, run on the serverhelper(conn)API calls onlyPostGISproductionGeoPackagetests, offlinewatchSQL dialectfunction names

def count_features(conn, schema, table):
    q = f'SELECT count(*) FROM "{schema}"."{table}"' if schema else f'SELECT count(*) FROM "{table}"'
    return conn.executeSql(q)[0][0]

gpkg = QgsProviderRegistry.instance().providerMetadata("ogr").createConnection(
    "/data/tests/sample.gpkg", {})
print("server:", count_features(pg, "water", "hydrants"))
print("local: ", count_features(gpkg, "", "hydrants"))

Breakdown: GeoPackage has no schemas, so the helper omits the schema when it is empty — the one structural difference to handle. SQL dialects differ beyond the basics: PostGIS has ST_Length with geography casts, GeoPackage via SpatiaLite functions has ST_Length too but different extras. Keep portable helpers to standard SQL and API methods, and isolate dialect-specific queries in clearly named functions. Testing helpers against a small GeoPackage in CI, as in unit testing a QGIS plugin, needs no database server.

QGIS version compatibility

The provider connections API is available on QGIS 3.34 LTR, 3.40 LTR and QGIS 4, with executeSql, createVectorTable, tables, tableUri and findConnection stable across them. Recent releases add execSql returning a result iterator with column names, useful for large results. On QGIS 4, table flags and geometry types use scoped enums.

Troubleshooting

  • findConnection returns None or raises. The name differs from the Browser's, or the connection was saved under another provider.
  • SQL errors mention quoting. Mixed-case identifiers need double quotes; lower-case names avoid the issue.
  • Permission denied on insert. The role lacks rights on the table's sequence as well as the table.
  • Large results are slow. Aggregate in SQL, or use execSql and iterate rather than fetching everything.

Conclusion

Use stored connections through findConnection instead of credentials in scripts, inventory schemas and tables with their geometry and key metadata, run SQL with executeSql and keep work in the database, create and drop tables through the API, load layers from tableUri, and write helpers against the common interface so they run on PostGIS and GeoPackage alike.

Frequently Asked Questions

Should I still use psycopg2? For complex transactional applications, it is fine. For scripts and plugins that work alongside QGIS, the connections API avoids separate credentials and dependencies.

Can I list connections in a plugin dialog? Yes — QgsProviderConnectionComboBox lists them and returns the selected connection name.

Does the API support raster tables? PostGIS raster and GeoPackage tiles appear in tables() with raster flags; create layers from tableUri with the matching provider.

How do I get column names from a query? Use execSql on recent releases, which returns a result object with columns().