World Country, State and City Database in SQL and JSON Format (Step-by-Step Guide)

If you want to add all countries, states/provinces, and cities of the world into your database, the most efficient and reliable approach is to use a pre-built global dataset instead of manually collecting everything.

Many developers try using free APIs or random CSV files from GitHub. However, they quickly face common issues like:

Incomplete country lists
Missing states or provinces
Duplicate city records
Encoding problems
Outdated or inconsistent data

Cleaning and fixing all of this can take days or even weeks.

The smarter solution is to use a structured world database that is already properly organized in SQL, JSON, and CSV formats.

🌍 Recommended World Database Dataset

You can use this complete dataset:

👉 Full Continent, Country, State, City & Currency Dataset

It is designed for developers who want ready-to-use global location data without any manual effort.

📦 What’s Included in the Dataset

This dataset includes complete worldwide location information such as:

Countries
States / Provinces
Cities
ZIP / Postal Codes
Currency information
ISO country codes
Phone dialing codes
Latitude & Longitude
Timezones

The data is available in multiple formats:

SQL (for databases)
JSON (for APIs and apps)
CSV (for spreadsheets and analysis)

This makes it compatible with almost any technology stack.

⚙️ Step-by-Step Guide to Import Data into Your Database

Step 1: Download and Extract Files

After downloading the dataset, extract it on your system. You will see files like:

countries.sql
states.sql
cities.sql
countries.json
cities.csv

Step 2: Create a Database

If you are using MySQL, create a database like this:

CREATE DATABASE world_data;

Then select it:

USE world_data;

Step 3: Import SQL Files

You can import SQL files in two ways:

Method 1: phpMyAdmin

Open phpMyAdmin
Select your database
Click Import
Upload SQL files one by one

Method 2: Command Line

mysql -u root -p world_data < countries.sql
mysql -u root -p world_data < states.sql
mysql -u root -p world_data < cities.sql

Step 4: Verify Data

Run simple queries to check data:

SELECT * FROM countries LIMIT 10;
SELECT * FROM states LIMIT 10;
SELECT * FROM cities LIMIT 10;

🧱 Recommended Database Structure

A clean relational structure should look like this:

Countries Table

id
name
iso2
iso3
phone_code
currency
timezone

States Table

id
country_id
name
state_code

Cities Table

id
country_id
state_id
name
latitude
longitude

🔗 How This Helps Your Application

With this structure, you can easily build:

Country → State → City dropdowns
Location-based filters
Shipping systems
CRM segmentation
Geo-targeted applications

For example:
When a user selects “United States,” you can dynamically load its states, and then cities inside each state.

⚡ JSON Format Usage (For Modern Apps)

If you are building APIs or modern applications, JSON format is extremely useful.

You can directly use it in:

Node.js
Laravel
Python
React
MongoDB

🚀 Performance Tip

For large city datasets, always add indexes:

country_id
state_id
city name

This improves search speed and overall performance significantly.

🌐 Where This Dataset Is Useful

This global database is extremely useful for:

Ecommerce platforms
SaaS applications
CRM systems
Shipping systems
AI applications
Analytics dashboards
Lead generation tools
Geo-targeting systems
Mobile applications

💡 Final Thoughts

Instead of building everything from scratch, using a ready-made global database is the fastest and most reliable approach. It saves development time, avoids data inconsistencies, and gives you production-ready data instantly.

📥 Get the Complete Dataset