### Install Dependencies with npm

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/prisma/README.md

Run this command to install project dependencies.

```bash
npm install
```

--------------------------------

### DuckDB CLI Query Examples

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/duckdb/README.md

Execute SQL queries directly from the command line using the DuckDB CLI. Install the CLI first if you haven't already.

```bash
duckdb duckdb/world.db -c "SELECT COUNT(*) as total_cities FROM cities"
```

```bash
duckdb duckdb/world.db -c "SELECT region, COUNT(*) as country_count FROM countries GROUP BY region"
```

--------------------------------

### Install Python Dependencies

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Installs the necessary Python libraries for the project, including mysql-connector-python, timezonefinder, and duckdb.

```bash
pip install mysql-connector-python timezonefinder duckdb
```

--------------------------------

### Install Browser Package

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

Install the @countrystatecity/countries-browser package using npm. This package is optimized for browsers and supports lazy loading via CDN.

```bash
npm install @countrystatecity/countries-browser
```

--------------------------------

### Get Cities of a Country - JavaScript

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Fetches all cities for a given country using its ISO2 code. The NPM package must be installed.

```javascript
import { City } from '@countrystatecity/countries';

// Get cities of a country
const usCities = City.getCitiesOfCountry('US');
console.log(`US has ${usCities.cities.length} cities`);
```

--------------------------------

### Get All Countries - JavaScript

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Retrieves all available country data using the NPM package. Ensure the package is installed.

```javascript
import { Country } from '@countrystatecity/countries';

// Get all countries
const allCountries = Country.getAllCountries();
console.log(`Total countries: ${allCountries.length}`);
```

--------------------------------

### Get States of a Country - JavaScript

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Retrieves all states for a given country using its ISO2 code. The NPM package must be installed.

```javascript
import { State } from '@countrystatecity/countries';

// Get all states of a country
const usStates = State.getStatesOfCountry('US');
console.log(`US has ${usStates.length} states`);
```

--------------------------------

### Download and Import SQL Dumps

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Download SQL dumps for MySQL and PostgreSQL and import them into your database. Ensure you have the respective database client installed and configured.

```bash
# Download MySQL dump
curl -LO https://github.com/dr5hn/countries-states-cities-database/releases/latest/download/sql-world.sql.gz
gunzip sql-world.sql.gz

# Import to MySQL
mysql -u root -p world < sql-world.sql

# Download PostgreSQL dump
curl -LO https://github.com/dr5hn/countries-states-cities-database/releases/latest/download/psql-world.sql.gz
gunzip psql-world.sql.gz

# Import to PostgreSQL
psql -U postgres -d world -f psql-world.sql
```

--------------------------------

### Install JavaScript/TypeScript NPM Package

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

Install the @countrystatecity/countries package using npm. This package is zero-dependency, TypeScript-supported, offline-first, and tree-shakeable.

```bash
npm install @countrystatecity/countries
```

--------------------------------

### Get Cities of a Country - Python

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Fetches all cities for a given country using its ISO2 code. The Python package must be installed.

```python
from countrystatecity_countries import City

# Get all cities of a country
india_cities = City.get_cities_of_country('IN')
print(f"India has {len(india_cities)} cities")
```

--------------------------------

### Get All Countries - Python

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Retrieves all available country data using the Python package. Ensure the package is installed.

```python
from countrystatecity_countries import Country

# Get all countries
all_countries = Country.get_all_countries()
print(f"Total countries: {len(all_countries)}")
```

--------------------------------

### Install Python PyPI Package

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

Install the countrystatecity-countries package using pip. This package provides an easy-to-use Python interface and works with Django/Flask.

```bash
pip install countrystatecity-countries
```

--------------------------------

### Python DuckDB Query Example

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/duckdb/README.md

Connect to the DuckDB database and execute SQL queries to retrieve data. Ensure the database file exists at the specified path.

```python
import duckdb

# Connect to the database
conn = duckdb.connect('duckdb/world.db')

# Query examples
regions = conn.execute("SELECT * FROM regions").fetchall()
countries_in_asia = conn.execute("SELECT name FROM countries WHERE region = 'Asia'").fetchall()
cities_in_us = conn.execute("SELECT name FROM cities WHERE country_code = 'US' LIMIT 10").fetchall()

conn.close()
```

--------------------------------

### Get States of a Country - Python

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Retrieves all states for a given country using its ISO2 code. The Python package must be installed.

```python
from countrystatecity_countries import State

# Get states of a country
indian_states = State.get_states_of_country('IN')
print(f"India has {len(indian_states)} states/territories")
```

--------------------------------

### Create New Symfony Console Command

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

Example of a new command class extending Symfony's Command. Implement `configure` for metadata and `execute` for logic. Ensure proper namespace and return type for `execute`.

```php
<?php
namespace bin\Commands;

use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class NewExportCommand extends Command
{
    protected static $defaultName = 'export:new-format';
    protected static $defaultDescription = 'Export data to new format';

    protected function configure(): void
    {
        $this->setHelp('This command exports the database to a new format');
    }

    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        // Implementation
        return Command::SUCCESS;
    }
}
```

--------------------------------

### Get Country by Code - JavaScript

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Fetches a specific country's details using its ISO2 code. Requires the NPM package to be installed.

```javascript
import { Country } from '@countrystatecity/countries';

// Get a specific country by ISO2 code
const usa = Country.getCountryByCode('US');
console.log(`Country: ${usa.name}, Capital: ${usa.capital}, Currency: ${usa.currency}`);
```

--------------------------------

### Get Country by Code - Python

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Fetches a specific country's details using its ISO2 code. Requires the Python package to be installed.

```python
from countrystatecity_countries import Country

# Get country by ISO2 code
india = Country.get_country_by_code('IN')
print(f"Country: {india['name']}, Capital: {india['capital']}, Currency: {india['currency']}")
```

--------------------------------

### Get Cities of a State - JavaScript

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Retrieves all cities within a specific state, identified by country and state ISO codes. Ensure the NPM package is installed.

```javascript
import { City } from '@countrystatecity/countries';

// Get all cities in a state
const californiaCities = City.getCitiesOfState('US', 'CA');
console.log(`California has ${californiaCities.length} cities`);
```

--------------------------------

### Install DuckDB Python Package

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

Install the DuckDB Python package using pip. This is a prerequisite for converting SQLite files to DuckDB format.

```bash
pip install duckdb
```

--------------------------------

### State/Province Data Structure Example

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Example of a state or province record, linking to its parent country and including ISO codes and native names. This structure is used for administrative divisions within countries.

```json
{
    "id": 1416,
    "name": "California",
    "country_id": 233,
    "country_code": "US",
    "country_name": "United States",
    "iso2": "CA",
    "iso3166_2": "US-CA",
    "fips_code": "06",
    "type": "state",
    "level": null,
    "parent_id": null,
    "native": "California",
    "latitude": "36.77826100",
    "longitude": "-119.41793240",
    "timezone": "America/Los_Angeles",
    "translations": {
        "ko": "캘리포니아주",
        "de": "Kalifornien",
        "es": "California",
        "fr": "Californie",
        "ja": "カリフォルニア州",
        "zh-CN": "加利福尼亚州"
    },
    "wikiDataId": "Q99",
    "population": 39538223
}
```

--------------------------------

### REST API - Get States of a Country

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Fetches all states for a given country using its ISO2 code via the REST API. Requires an API key.

```bash
curl -X GET "https://api.countrystatecity.in/v1/countries/US/states" \
  -H "X-CSCAPI-KEY: YOUR_API_KEY"
```

--------------------------------

### SQL-First Contribution Workflow

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

This workflow is for maintainers who prefer to edit the MySQL database directly. It involves starting MySQL, making changes, syncing MySQL back to JSON, and then committing the updated JSON files. GitHub Actions handles the subsequent exports.

```bash
# 1. Start MySQL and edit database directly
sudo systemctl start mysql.service
mysql -uroot -proot world
# ... make your changes ...

# 2. Sync MySQL back to JSON
python3 bin/scripts/sync/sync_mysql_to_json.py

# 3. Review and commit
git diff
git add contributions/
git commit -m "feat: add new cities to database"

# 4. Push - GitHub Actions will handle exports
git push
```

--------------------------------

### DuckDB Analytical Queries

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Python code demonstrating how to connect to a DuckDB database and execute analytical SQL queries. Includes examples for city population statistics per country and timezone analysis.

```python
# DuckDB analytical queries
import duckdb

conn = duckdb.connect('duckdb/world.db')

# Fast aggregation - cities per country with population stats
result = conn.execute('''
    SELECT
        c.name as country,
        COUNT(ci.id) as city_count,
        SUM(ci.population) as total_city_population,
        AVG(ci.population) as avg_city_population
    FROM countries c
    JOIN cities ci ON ci.country_id = c.id
    WHERE ci.population IS NOT NULL
    GROUP BY c.name
    ORDER BY city_count DESC
    LIMIT 10
''').fetchdf()

print(result)

# Timezone analysis
timezone_stats = conn.execute('''
    SELECT
        timezone,
        COUNT(*) as city_count,
        COUNT(DISTINCT country_code) as country_count
    FROM cities
    WHERE timezone IS NOT NULL
    GROUP BY timezone
    ORDER BY city_count DESC
    LIMIT 10
''').fetchdf()

print("\nTop timezones by city count:")
print(timezone_stats)

conn.close()
```

--------------------------------

### City Data Structure Example

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Example of a city record, including geographic coordinates, timezone, population, and links to its parent state and country. This structure is used for city-level data.

```json
{
    "id": 110965,
    "name": "San Francisco",
    "state_id": 1416,
    "state_code": "CA",
    "country_id": 233,
    "country_code": "US",
    "type": "adm2",
    "level": null,
    "parent_id": null,
    "latitude": "37.77493000",
    "longitude": "-122.41942000",
    "native": "San Francisco",
    "population": 873965,
    "timezone": "America/Los_Angeles",
    "translations": {
        "ko": "샌프란시스코",
        "de": "San Francisco",
        "es": "San Francisco",
        "fr": "San Francisco",
        "ja": "サンフランシスコ",
        "zh-CN": "旧金山"
    },
    "created_at": "2019-10-06T10:31:43",
    "updated_at": "2025-12-02T14:38:56",
    "flag": 1,
    "wikiDataId": "Q62"
}
```

--------------------------------

### Country Data Structure Example

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Example of a country record, including ISO codes, currency, timezones, and translations. This structure is used as the single source of truth for country data.

```json
{
    "id": 233,
    "name": "United States",
    "iso3": "USA",
    "iso2": "US",
    "numeric_code": "840",
    "phonecode": "1",
    "capital": "Washington",
    "currency": "USD",
    "currency_name": "United States dollar",
    "currency_symbol": "$",
    "tld": ".us",
    "native": "United States",
    "population": 331002651,
    "gdp": 25462700,
    "region": "Americas",
    "region_id": 2,
    "subregion": "Northern America",
    "subregion_id": 6,
    "nationality": "American",
    "area_sq_km": 9372610,
    "postal_code_format": "#####-####",
    "postal_code_regex": "^\\d{5}(-\\d{4})?$",
    "timezones": [
        {
            "zoneName": "America/New_York",
            "gmtOffset": -18000,
            "gmtOffsetName": "UTC-05:00",
            "abbreviation": "EST",
            "tzName": "Eastern Standard Time"
        }
    ],
    "translations": {
        "ko": "미국",
        "de": "Vereinigte Staaten",
        "es": "Estados Unidos",
        "fr": "États-Unis",
        "ja": "アメリカ合衆国",
        "zh-CN": "美国",
        "ru": "Соединённые Штаты"
    },
    "latitude": "38.00000000",
    "longitude": "-97.00000000",
    "emoji": "🇺🇸",
    "emojiU": "U+1F1FA U+1F1F8",
    "wikiDataId": "Q30"
}
```

--------------------------------

### REST API - Get All Countries

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Fetches a list of all countries via the REST API. Requires an API key and should be used with `curl`.

```bash
# Get API key from https://countrystatecity.in/

# Get all countries
curl -X GET "https://api.countrystatecity.in/v1/countries" \
  -H "X-CSCAPI-KEY: YOUR_API_KEY" \
  -H "Content-Type: application/json"
```

--------------------------------

### Convert SQLite to DuckDB

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Command-line instruction to install the DuckDB Python package and then use a provided script to convert a SQLite database file to the DuckDB format. This is useful for enabling high-performance analytical queries.

```bash
# Install DuckDB
pip install duckdb

# Convert SQLite to DuckDB using provided script
python3 bin/scripts/export/import_duckdb.py \
  --input sqlite/world.sqlite3 \
  --output duckdb/world.db
```

--------------------------------

### Get Cities of a State - Python

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Retrieves all cities within a specific state, identified by country and state codes. Ensure the Python package is installed.

```python
from countrystatecity_countries import City

# Get cities of a state
mumbai_cities = City.get_cities_of_state('IN', 'MH')
print(f"Maharashtra has {len(mumbai_cities)} cities")
```

--------------------------------

### Install Timezones Package

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

Install the @countrystatecity/timezones package using npm. This package provides dedicated timezone data for JavaScript/TypeScript applications.

```bash
npm install @countrystatecity/timezones
```

--------------------------------

### Get Specific State - JavaScript

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Fetches a specific state's details by its code and the country's ISO2 code. Requires the NPM package.

```javascript
import { State } from '@countrystatecity/countries';

// Get a specific state
const california = State.getStateByCodeAndCountry('CA', 'US');
console.log(`State: ${california.name}, ISO: ${california.iso3166_2}`);
```

--------------------------------

### Query SQLite Database in Python

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Connect to the SQLite database using Python's `sqlite3` module and execute queries. This example demonstrates fetching countries in Asia and city coordinates.

```python
# Python SQLite usage
import sqlite3

conn = sqlite3.connect('sqlite-world.sqlite3')
cursor = conn.cursor()

# Get all countries in Asia
cursor.execute('''
    SELECT name, capital, currency, population
    FROM countries
    WHERE region = 'Asia'
    ORDER BY population DESC
    LIMIT 10
''')
for row in cursor.fetchall():
    print(f"{row[0]}: {row[1]}, {row[2]}, Pop: {row[3]:,}")

# Output:
# China: Beijing, CNY, Pop: 1,402,112,000
# India: New Delhi, INR, Pop: 1,380,004,385
# Indonesia: Jakarta, IDR, Pop: 273,523,615
# ...

# Get city coordinates for mapping
cursor.execute('''
    SELECT ci.name, ci.latitude, ci.longitude, ci.population
    FROM cities ci
    JOIN countries c ON ci.country_id = c.id
    WHERE c.iso2 = 'JP' AND ci.population > 1000000
    ORDER BY ci.population DESC
''')
japanese_cities = cursor.fetchall()
print(f"Japanese cities with 1M+ population: {len(japanese_cities)}")

conn.close()
```

--------------------------------

### Get States of a Country in Python

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

Import Country, State, and City from the countrystatecity_countries package and retrieve all states for a given country code.

```python
from countrystatecity_countries import Country, State, City
us_states = State.get_states_of_country('US')
```

--------------------------------

### Add New City to Contributions

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Example JSON structure for adding a new city to the database via the contributions directory. Note that the 'id' field should not be included as it is auto-assigned.

```json
// contributions/cities/US.json - Add a new city
{
    "name": "New City Name",
    "state_id": 1416,
    "state_code": "CA",
    "country_id": 233,
    "country_code": "US",
    "latitude": "37.77490000",
    "longitude": "-122.41940000",
    "timezone": "America/Los_Angeles"
}
// Note: Do NOT include "id" field for new entries - auto-assigned by system
```

--------------------------------

### Add New Country to Contributions

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Example JSON structure for adding a new country to the database. This includes essential details such as name, ISO codes, phone code, capital, currency, region, and geographical coordinates.

```json
// contributions/countries/countries.json - Add a new country
{
    "name": "New Country",
    "iso2": "NC",
    "iso3": "NCO",
    "numeric_code": "999",
    "phonecode": "999",
    "capital": "Capital City",
    "currency": "NCC",
    "currency_name": "New Country Currency",
    "region": "Asia",
    "region_id": 3,
    "latitude": "0.00000000",
    "longitude": "0.00000000"
}
```

--------------------------------

### Download and Unzip JSON Data

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Download the combined JSON data and unzip it for use in Node.js applications. Ensure you have `curl` and `gunzip` installed.

```bash
curl -LO https://github.com/dr5hn/countries-states-cities-database/releases/latest/download/json-countries%2Bstates%2Bcities.json.gz
gunzip json-countries+states+cities.json.gz
```

--------------------------------

### Composer PHP Requirements

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

Defines the PHP version and necessary Symfony components for the project. Ensure these dependencies are met before installation.

```json
{
    "require": {
        "php": ">=8.1",
        "symfony/console": "^7.0",
        "symfony/filesystem": "^7.0",
        "symfony/process": "^7.0",
        "symfony/yaml": "^7.0",
        "spatie/array-to-xml": "^3.3.0",
        "monolog/monolog": "^3.7",
        "vlucas/phpdotenv": "^5.6",
        "hassankhan/config": "^3.1",
        "robmorgan/phinx": "^0.16.1"
    }
}
```

--------------------------------

### Get Specific State - Python

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Fetches a specific state's details by its code and the country's ISO2 code. Requires the Python package.

```python
from countrystatecity_countries import State

# Get specific state by code
maharashtra = State.get_state_by_code_and_country('MH', 'IN')
print(f"State: {maharashtra['name']}, Type: {maharashtra['type']}")
```

--------------------------------

### Get States of a Country in JavaScript/TypeScript

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

Import Country, State, and City from the @countrystatecity/countries package and retrieve all states for a given country code.

```javascript
import { Country, State, City } from '@countrystatecity/countries';
const usStates = State.getStatesOfCountry('US');
```

--------------------------------

### Add New State to Contributions

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Example JSON structure for adding a new state or province to the database. This includes fields for name, country association, ISO codes, type, geographical coordinates, and timezone.

```json
// contributions/states/states.json - Add a new state
{
    "name": "New Province",
    "country_id": 1,
    "country_code": "AF",
    "iso2": "NP",
    "type": "province",
    "latitude": "34.00000000",
    "longitude": "66.00000000",
    "timezone": "Asia/Kabul"
}
```

--------------------------------

### Example City JSON Structure

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

When manually editing city data, ensure all required fields are included and auto-managed fields are omitted. This structure serves as a template for new city entries.

```json
{
  "name": "San Francisco",
  "state_id": 1416,
  "state_code": "CA",
  "country_id": 233,
  "country_code": "US",
  "latitude": "37.77493",
  "longitude": "-122.41942",
  "timezone": "America/Los_Angeles"
}
```

--------------------------------

### REST API - Get Specific Country Details

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Retrieves detailed information for a specific country using its ISO2 code via the REST API. An API key is required.

```bash
curl -X GET "https://api.countrystatecity.in/v1/countries/US" \
  -H "X-CSCAPI-KEY: YOUR_API_KEY"
```

--------------------------------

### REST API - Get Cities of a State

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Retrieves all cities within a specific state, identified by country and state ISO codes, via the REST API. An API key is required.

```bash
curl -X GET "https://api.countrystatecity.in/v1/countries/US/states/CA/cities" \
  -H "X-CSCAPI-KEY: YOUR_API_KEY"
```

--------------------------------

### Analyze Country Data with Pandas

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Loads country data from a CSV file into a pandas DataFrame for analysis. Demonstrates filtering by region, sorting by population, and merging with state data. Requires pandas library installed.

```python
# Python pandas usage
import pandas as pd

# Load countries data
countries = pd.read_csv('countries.csv')

# Basic statistics
print(f"Total countries: {len(countries)}")
print(f"Regions: {countries['region'].unique()}")

# Filter by region and sort by population
asia = countries[countries['region'] == 'Asia'].sort_values('population', ascending=False)
print("\nTop 5 Asian countries by population:")
print(asia[['name', 'capital', 'population', 'currency']].head())

# Output:
#             name    capital   population currency
# 42         China    Beijing   1402112000      CNY
# 105        India  New Delhi   1380004385      INR
# 104    Indonesia    Jakarta    273523615      IDR
# 173     Pakistan  Islamabad    220892340      PKR
# 19    Bangladesh      Dhaka    169828911      BDT

# Load and merge states with countries
states = pd.read_csv('states.csv')
merged = states.merge(countries[['id', 'name', 'region']],
                       left_on='country_id',
                       right_on='id',
                       suffixes=('_state', '_country'))

# Count states per region
states_per_region = merged.groupby('region').size().sort_values(ascending=False)
print("\nStates/provinces per region:")
print(states_per_region)
```

--------------------------------

### Generate Prisma Client

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/prisma/README.md

Execute this command to generate the Prisma client after setting up the database URL.

```bash
npm run generate
```

--------------------------------

### Database Migration Commands

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

These commands manage database migrations and seeding. Execute them from the 'bin' directory.

```bash
php console manage:create     # Create a new migration
```

```bash
php console manage:migrate    # Run migrations
```

```bash
php console manage:rollback   # Rollback migrations
```

```bash
php console manage:status     # Show migration status
```

```bash
php console manage:breakpoint # Set/unset breakpoint
```

```bash
php console seed:create      # Create a new seeder
```

```bash
php console seed:run         # Run database seeds
```

--------------------------------

### Import MongoDB Dump

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

Download and import the MongoDB dump from GitHub Releases. Ensure mongorestore is available in your PATH.

```bash
# Download from latest release
curl -LO https://github.com/dr5hn/countries-states-cities-database/releases/latest/download/mongodb-world-mongodb-dump.tar.gz

# Extract and restore
tar -xzvf mongodb-world-mongodb-dump.tar.gz
mongorestore --host localhost:27017 --db world mongodb-dump/world
```

--------------------------------

### Download SQLite Database

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Download the pre-built SQLite database file and unzip it. This database is suitable for embedded applications and serverless environments.

```bash
# Download SQLite database
curl -LO https://github.com/dr5hn/countries-states-cities-database/releases/latest/download/sqlite-world.sqlite3.gz
gunzip sqlite-world.sqlite3.gz
```

--------------------------------

### Seed the Database

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/prisma/README.md

Run this command to populate the database with geographical data. Ensure a stable internet connection as it fetches data from GitHub.

```bash
npm run seed
```

--------------------------------

### Execute Sync Scripts from Project Root

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Runs bidirectional database synchronization scripts from the project's root directory.

```bash
python3 bin/scripts/sync/sync_mysql_to_json.py         # MySQL → contributions/
python3 bin/scripts/sync/import_json_to_mysql.py       # contributions/ → MySQL
```

--------------------------------

### Import Database into MongoDB

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Steps to download and restore the MongoDB dump. Ensure MongoDB is running on localhost:27017 before executing the mongorestore command.

```bash
# Download MongoDB dump from GitHub Releases
curl -LO https://github.com/dr5hn/countries-states-cities-database/releases/latest/download/mongodb-world-mongodb-dump.tar.gz

# Extract and restore
tar -xzvf mongodb-world-mongodb-dump.tar.gz
 mongorestore --host localhost:27017 --db world mongodb-dump/world
```

--------------------------------

### Apply Database Schema with Prisma

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/prisma/README.md

Use this command to apply the Prisma schema to your database.

```bash
npm run db:push
```

--------------------------------

### Export Data to XML

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

Use this command to export the World Database into multiple XML files. Ensure you are in the 'bin' directory.

```bash
php console export:xml
```

--------------------------------

### Export Data to SQL Server Format

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

Use this command to export the World Database in SQL Server format, including schema and data. Ensure you are in the 'bin' directory.

```bash
php console export:sql-server
```

--------------------------------

### Export Data to YAML

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

Use this command to export the World Database into multiple YAML files. Ensure you are in the 'bin' directory.

```bash
php console export:yaml
```

--------------------------------

### Download Cities JSON (Gzipped)

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Downloads the large cities data file as a gzipped JSON from GitHub Releases and then unzips it using `curl` and `gunzip`.

```bash
# Download cities JSON (large file, from GitHub Releases)
curl -LO https://github.com/dr5hn/countries-states-cities-database/releases/latest/download/json-cities.json.gz
gunzip json-cities.json.gz
```

--------------------------------

### GeoJSON FeatureCollection Structure

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

An example of the GeoJSON structure for a FeatureCollection containing Point geometries. This format is standard for geographic data interchange.

```json
{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "geometry": {
        "type": "Point",
        "coordinates": [-122.41942, 37.77493]
      },
      "properties": {
        "id": 111001,
        "name": "San Francisco",
        "state_code": "CA",
        "country_code": "US",
        "population": 873965,
        "timezone": "America/Los_Angeles"
      }
    }
  ]
}
```

--------------------------------

### Workflow 1: JSON-First Contribution

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Illustrates a workflow where contributors edit JSON files, commit changes, and trigger GitHub Actions for automatic import into MySQL and export to JSON format.

```bash
# 1. Edit JSON
vim contributions/cities/US.json

# 2. Commit & Push
git add contributions/
git commit -m "feat: add new cities"
git push

# 3. GitHub Actions automatically:
#    - Runs import_json_to_mysql.py (contributions → MySQL, IDs auto-assigned)
#    - Runs php console export:json (MySQL → json/ directory)
#    - Creates PR with all exports
```

--------------------------------

### MySQL/PostgreSQL Queries

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Execute SQL queries against the imported database to retrieve information about countries, states, and cities. These queries are compatible with both MySQL and PostgreSQL.

```sql
-- MySQL/PostgreSQL queries

-- Get all countries with their region
SELECT c.name, c.iso2, c.capital, c.currency, r.name as region
FROM countries c
JOIN regions r ON c.region_id = r.id
ORDER BY c.name;

-- Get states with city counts
SELECT s.name, s.iso2, c.name as country, COUNT(ci.id) as city_count
FROM states s
JOIN countries c ON s.country_id = c.id
LEFT JOIN cities ci ON ci.state_id = s.id
WHERE c.iso2 = 'US'
GROUP BY s.id
ORDER BY city_count DESC
LIMIT 10;

-- Find cities in a timezone
SELECT ci.name, ci.latitude, ci.longitude, s.name as state, c.name as country
FROM cities ci
JOIN states s ON ci.state_id = s.id
JOIN countries c ON ci.country_id = c.id
WHERE ci.timezone = 'America/New_York'
LIMIT 20;

-- Search cities by name pattern
SELECT ci.name, s.name as state, c.name as country, ci.population
FROM cities ci
JOIN states s ON ci.state_id = s.id
JOIN countries c ON ci.country_id = c.id
WHERE ci.name LIKE 'San%'
ORDER BY ci.population DESC
LIMIT 10;
```

--------------------------------

### Export Data to CSV

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

Use this command to export the World Database into multiple CSV files. Ensure you are in the 'bin' directory.

```bash
php console export:csv
```

--------------------------------

### Export Data to NPM Package Format

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

Use this command to export data into JSON files suitable for an NPM package. Ensure you are in the 'bin' directory.

```bash
php console export:csc-npm
```

--------------------------------

### Export Data to JSON

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

Use this command to export the World Database into multiple JSON files. Ensure you are in the 'bin' directory.

```bash
php console export:json
```

--------------------------------

### Execute Export Scripts from Project Root

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Runs scripts to export data, such as importing into DuckDB.

```bash
python3 bin/scripts/export/import_duckdb.py --input sqlite/world.sqlite3 --output duckdb/world.db
```

--------------------------------

### Project Attribution

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

Provide attribution when using the data. This includes the data source name and a link to the GitHub repository, along with the license.

```text
Data by Countries States Cities Database
https://github.com/dr5hn/countries-states-cities-database | ODbL v1.0
```

--------------------------------

### Export Data to Property List (.plist)

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Exports data from the database into Apple Property List (.plist) format. The output is organized into .plist files within the 'plist/' directory.

```bash
python3 scripts/export/export_plist.py
```

--------------------------------

### Query Geographic Data from MongoDB

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Demonstrates how to perform queries on a MongoDB database using mongosh or the Node.js driver. Includes finding countries by region and aggregating cities per country. Requires a running MongoDB instance with the 'world' database.

```javascript
// MongoDB queries using mongosh or Node.js driver
const { MongoClient } = require('mongodb');

async function queryGeographicData() {
  const client = new MongoClient('mongodb://localhost:27017');
  await client.connect();

  const db = client.db('world');

  // Find countries by region
  const europeanCountries = await db.collection('countries')
    .find({ region: 'Europe' })
    .sort({ population: -1 })
    .limit(10)
    .toArray();

  console.log('Top 10 European countries by population:');
  europeanCountries.forEach(c =>
    console.log(`${c.name}: ${c.population?.toLocaleString()}`)
  );

  // Aggregate cities by country
  const citiesPerCountry = await db.collection('cities').aggregate([
    { $group: { _id: '$country_code', count: { $sum: 1 } } },
    { $sort: { count: -1 } },
    { $limit: 5 }
  ]).toArray();

  console.log('\nCountries with most cities:');
  citiesPerCountry.forEach(c => console.log(`${c._id}: ${c.count} cities`));

  await client.close();
}

queryGeographicData();
```

--------------------------------

### Direct Download - JSON Files

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Allows direct download of JSON files containing geographical data for use in any programming language.

```APIDOC
## Direct Download - JSON Files

### Description
Allows direct download of JSON files containing geographical data for use in any programming language.

### Download Links

#### Countries JSON
*   **Source:** Raw GitHubusercontent
*   **URL:** `https://raw.githubusercontent.com/dr5hn/countries-states-cities-database/master/json/countries.json`
*   **Example Download:**
    ```bash
    curl -O https://raw.githubusercontent.com/dr5hn/countries-states-cities-database/master/json/countries.json
    ```

#### States JSON
*   **Source:** Raw GitHubusercontent
*   **URL:** `https://raw.githubusercontent.com/dr5hn/countries-states-cities-database/master/json/states.json`
*   **Example Download:**
    ```bash
    curl -O https://raw.githubusercontent.com/dr5hn/countries-states-cities-database/master/json/states.json
    ```

#### Cities JSON (Large File)
*   **Source:** GitHub Releases (compressed)
*   **URL:** `https://github.com/dr5hn/countries-states-cities-database/releases/latest/download/json-cities.json.gz`
*   **Example Download and Decompression:**
    ```bash
    curl -LO https://github.com/dr5hn/countries-states-cities-database/releases/latest/download/json-cities.json.gz
    gunzip json-cities.json.gz
    ```
```

--------------------------------

### Download and Unzip Cities JSON Export

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

Download the latest cities JSON export file from GitHub Releases and unzip it. Large export files are distributed via GitHub Releases.

```bash
# Example: download cities JSON
curl -LO https://github.com/dr5hn/countries-states-cities-database/releases/latest/download/json-cities.json.gz
gunzip json-cities.json.gz
```

--------------------------------

### Convert SQLite to DuckDB

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/README.md

Use the provided Python script to convert SQLite database files to DuckDB format. Specify input SQLite file and output DuckDB file paths.

```bash
# Convert the complete world database
python3 bin/scripts/export/import_duckdb.py --input sqlite/world.sqlite3 --output duckdb/world.db

# Convert individual table databases
python3 bin/scripts/export/import_duckdb.py --input sqlite/regions.sqlite3 --output duckdb/regions.db
python3 bin/scripts/export/import_duckdb.py --input sqlite/subregions.sqlite3 --output duckdb/subregions.db
python3 bin/scripts/export/import_duckdb.py --input sqlite/countries.sqlite3 --output duckdb/countries.db
python3 bin/scripts/export/import_duckdb.py --input sqlite/states.sqlite3 --output duckdb/states.db
python3 bin/scripts/export/import_duckdb.py --input sqlite/cities.sqlite3 --output duckdb/cities.db
```

--------------------------------

### Import JSON to MySQL

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Automatically adds new columns from JSON to MySQL.

```bash
python3 scripts/sync/import_json_to_mysql.py
```

--------------------------------

### Convert Database to DuckDB Format

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Converts data from MySQL or SQLite databases into the DuckDB format. Specify input database file and desired output DuckDB file path.

```bash
python3 scripts/export/import_duckdb.py --input sqlite/world.sqlite3 --output duckdb/world.db
```

--------------------------------

### Export Data to PLIST Format

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

Use this command to export data in Apple Property List format using a Python script. Ensure you are in the 'bin' directory.

```bash
php console export:plist
```

--------------------------------

### Schema Evolution Handling (JSON to MySQL)

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

When new columns are added to JSON files, this script automatically detects and adds them to the MySQL database with appropriate data types. No manual schema updates are needed.

```python
python3 bin/scripts/sync/import_json_to_mysql.py
```

--------------------------------

### Download Countries JSON

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Downloads the countries data as a JSON file directly from the GitHub repository using `curl`.

```bash
# Download countries JSON (small file, directly from repo)
curl -O https://raw.githubusercontent.com/dr5hn/countries-states-cities-database/master/json/countries.json
```

--------------------------------

### Generate DuckDB Database

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/duckdb/README.md

Use this script to generate the DuckDB database. Options allow for global ID support, enhanced conversion with pandas, and custom output paths.

```bash
python3 bin/import_duckdb.py
```

```bash
python3 bin/import_duckdb.py --global-ids --output duckdb/world_global_ids.db
```

```bash
python3 bin/import_duckdb.py --enhanced --output duckdb/world_enhanced.db
```

```bash
python3 bin/import_duckdb.py --enhanced --global-ids --output duckdb/world_full.db
```

```bash
python3 bin/import_duckdb.py --input custom.sqlite3 --output custom.db
```

--------------------------------

### Python Package Integration

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Offers easy integration with Django, Flask, and other Python frameworks with offline data access.

```APIDOC
## Python Package Integration

### Description
Offers easy integration with Django, Flask, and other Python frameworks with offline data access.

### Installation
```bash
pip install countrystatecity-countries
```

### Usage
```python
from countrystatecity_countries import Country, State, City

# Get all countries
all_countries = Country.get_all_countries()
print(f"Total countries: {len(all_countries)}")

# Get country by ISO2 code
india = Country.get_country_by_code('IN')
print(f"Country: {india['name']}, Capital: {india['capital']}, Currency: {india['currency']}")

# Get states of a country
indian_states = State.get_states_of_country('IN')
print(f"India has {len(indian_states)} states/territories")

# Get specific state by code
maharashtra = State.get_state_by_code_and_country('MH', 'IN')
print(f"State: {maharashtra['name']}, Type: {maharashtra['type']}")

# Get cities of a state
mumbai_cities = City.get_cities_of_state('IN', 'MH')
print(f"Maharashtra has {len(mumbai_cities)} cities")

# Get all cities of a country
india_cities = City.get_cities_of_country('IN')
print(f"India has {len(india_cities)} cities")
```
```

--------------------------------

### Synchronize MySQL to JSON

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Automatically detects schema from MySQL and synchronizes data to JSON files. Preserves IDs, handles NULL values, and adapts to schema changes. Ideal for local development workflows.

```bash
python3 scripts/sync/sync_mysql_to_json.py
```

```bash
python3 scripts/sync/sync_mysql_to_json.py --host localhost --user root --password root
```

--------------------------------

### Download States JSON

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Downloads the states data as a JSON file directly from the GitHub repository using `curl`.

```bash
# Download states JSON (small file)
curl -O https://raw.githubusercontent.com/dr5hn/countries-states-cities-database/master/json/states.json
```

--------------------------------

### Workflow 2: SQL-First Maintenance

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Describes a workflow for maintainers involving direct MySQL edits, followed by synchronization of changes to JSON format using a dedicated script.

```bash
# 1. Edit MySQL
mysql -uroot -proot world
# ... make changes ...

# 2. Sync MySQL → JSON
python3 scripts/sync/sync_mysql_to_json.py
```

--------------------------------

### Import JSON to MySQL

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

This Python script imports data from JSON files into a MySQL database, automatically updating the schema. Optional host, user, and password parameters can be provided. Run from the 'scripts/sync/' directory.

```python
python3 scripts/sync/import_json_to_mysql.py [--host HOST --user USER --password PASS]
```

--------------------------------

### Schema Evolution Handling (MySQL to JSON)

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/README.md

When new columns are added to MySQL, this script automatically detects and includes them when synchronizing data to JSON files. Commit the updated JSON files after running the script.

```python
python3 bin/scripts/sync/sync_mysql_to_json.py
```

--------------------------------

### Load and Query JSON Data in Node.js

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Load country and state data from JSON files and perform queries using Node.js. Requires the `fs` module for file system operations.

```javascript
// Node.js - Load and query JSON data
const fs = require('fs');

const countries = JSON.parse(fs.readFileSync('countries.json', 'utf8'));
const states = JSON.parse(fs.readFileSync('states.json', 'utf8'));

// Find all European countries
const europeanCountries = countries.filter(c => c.region === 'Europe');
console.log(`European countries: ${europeanCountries.length}`);
// Output: European countries: 52

// Find states with specific timezone
const pacificStates = states.filter(s =>
    s.timezone && s.timezone.includes('America/Los_Angeles')
);
console.log(`Pacific timezone states: ${pacificStates.length}`);
```

--------------------------------

### Sync MySQL to JSON

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Automatically detects and adds new columns from MySQL to JSON.

```bash
python3 scripts/sync/sync_mysql_to_json.py
```

--------------------------------

### Find Country ID and Code

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/contributions/README.md

Use this JSON structure to find the `id` and `iso2` values for a specific country by searching for its name in `contributions/countries/countries.json`.

```json
{
  "id": 233,
  "name": "United States",
  "iso2": "US",
  "iso3": "USA"
}
```

--------------------------------

### Add New City JSON Object

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/contributions/README.md

Use this JSON structure when adding a new city to a country's file. Do not include an 'id' field as it will be auto-assigned.

```json
{
    "name": "New City Name",
    "state_id": 1234,
    "state_code": "CA",
    "country_id": 1,
    "country_code": "US",
    "latitude": "37.77490000",
    "longitude": "-122.41940000",
    "timezone": "America/Los_Angeles"
}
```

--------------------------------

### JavaScript/TypeScript Integration (NPM Package)

Source: https://context7.com/dr5hn/countries-states-cities-database/llms.txt

Provides a zero-dependency, TypeScript-supported, offline-first library for accessing geographical data in Node.js and browser environments.

```APIDOC
## JavaScript/TypeScript Integration (NPM Package)

### Description
Provides a zero-dependency, TypeScript-supported, offline-first library for accessing geographical data in Node.js and browser environments.

### Installation
```bash
npm install @countrystatecity/countries
```

### Usage
```javascript
import { Country, State, City } from '@countrystatecity/countries';

// Get all countries
const allCountries = Country.getAllCountries();
console.log(`Total countries: ${allCountries.length}`);

// Get a specific country by ISO2 code
const usa = Country.getCountryByCode('US');
console.log(`Country: ${usa.name}, Capital: ${usa.capital}, Currency: ${usa.currency}`);

// Get all states of a country
const usStates = State.getStatesOfCountry('US');
console.log(`US has ${usStates.length} states`);

// Get a specific state
const california = State.getStateByCodeAndCountry('CA', 'US');
console.log(`State: ${california.name}, ISO: ${california.iso3166_2}`);

// Get all cities in a state
const californiaCities = City.getCitiesOfState('US', 'CA');
console.log(`California has ${californiaCities.length} cities`);

// Get cities of a country
const usCities = City.getCitiesOfCountry('US');
console.log(`US has ${usCities.length} cities`);
```
```

--------------------------------

### Find State ID and Code

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/contributions/README.md

Use this JSON structure to find the `id` and `iso2` values for a specific state by searching for its name in `contributions/states/states.json`.

```json
{
  "id": 1416,
  "name": "California",
  "country_id": 233,
  "country_code": "US",
  "state_code": "CA",
  "iso2": "US-CA"
}
```

--------------------------------

### Add New Country JSON Object

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/contributions/README.md

Add new countries to the 'countries.json' file using this structure. Omit the 'id' field, as it will be auto-assigned.

```json
{
    "name": "New Country",
    "iso2": "NC",
    "iso3": "NCO",
    "capital": "Capital City",
    ...
}
```

--------------------------------

### Import JSON to MySQL

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Imports data from JSON files into MySQL, automatically updating the schema to include new columns. Features intelligent type inference and batch inserts for performance. Suitable for CI/CD pipelines.

```bash
python3 scripts/sync/import_json_to_mysql.py
```

```bash
python3 scripts/sync/import_json_to_mysql.py --host $DB_HOST --user $DB_USER --password $DB_PASSWORD
```

--------------------------------

### Commit Changes to Contributions

Source: https://github.com/dr5hn/countries-states-cities-database/blob/master/bin/scripts/README.md

Stages and commits changes in the contributions directory.

```bash
git diff ../../contributions/
git add ../../contributions/
git commit -m "feat: update database"
```