Skip to main content

Database architecture

This reference describes the SQLite export in v5.2.0-export.1, published 3 November 2026. Types, indexes, constraints and file layout can differ between export formats and releases. Inspect the source file you install. The underlying dataset links regions, subregions, countries, states, counties, city records and postcodes. A city can have a null state link; city names are not unique identifiers. Coordinates describe points, not boundary polygons or validated delivery addresses.
Export column names and SDK model attributes can differ. For this snapshot, country records use phonecode, states use iso2, and cities use state_code. The Python package models expose phone_code and state_code for some of those fields. Do not infer SQL column names from an API response or package model.

Core tables

regions

The pinned SQLite snapshot contains 6 rows.

subregions

The pinned SQLite snapshot contains 22 rows.

countries

The pinned SQLite snapshot contains 250 rows.

states

The pinned SQLite snapshot contains 5,327 rows.

counties

The pinned SQLite snapshot contains 14,058 rows.

county_states

The pinned SQLite snapshot contains 16 rows.

cities

The pinned SQLite snapshot contains 156,169 rows.

postcodes

The pinned SQLite snapshot contains 728,556 rows.

Relationships and constraints

  • subregions.region_id links to regions.id.
  • Countries can link to a region and subregion through region_id and subregion_id.
  • states.country_id links to countries.id. Subdivision classifications and hierarchy levels vary by country.
  • cities.country_id links to countries.id; cities.state_id and cities.county_id can be null.
  • A county can use counties.state_id and/or additional rows in county_states. Preserve both when looking up county-to-state relationships.
  • Postcodes carry a required country link and optional state/city links. Read the postcode reference for coverage and validation limits.

Foreign key constraints

Inspect and verify constraints in the export for your engine. In SQLite:
Enabling foreign keys is a connection setting. foreign_key_check returns violations; no rows means no violations were found. The checked snapshot returns no rows.

Common queries

Country and state names are joined from their tables rather than read from nonexistent cities.country_name or cities.state_name columns:
Get county-to-state links without discarding counties that use the scalar state column:
Use a left join for optional parents. Keep IDs and their country relationships when storing selected places; matching names alone can combine unrelated places.

Data types by platform

SQLite stores JSON-bearing fields such as translations and timezones as text in this snapshot. PostgreSQL, MySQL, MongoDB and other exports can represent these values differently. Check nulls and parse JSON only when the value is present and valid. SQL Server uses tables in the world schema inside the selected database, such as world.cities. PostgreSQL uses public.cities. Use the appropriate engine syntax; SQL dialects and import formats are not interchangeable.

Snapshot updates

Keep a source release and its notices with your local copy. Review release migrations when IDs are merged or removed; a change of version can affect joins and stored selections. API responses, npm/PyPI packages and downloaded files can describe different versions.

Next steps

Choose an installation recipe, inspect the release snapshot, or use the hosted API for managed access. For API field availability, compare the current plans.