Populate Countries, States & Cities in SQL

If you like it please give it a STAR

Here's a detailed description for creating migrations and seeders for the countries, states, and cities tables, along with an example JSON file structure for the seed data.

Migrations Description

Countries Table Migration

The countries table stores information about different countries. Each country has a unique identifier, name, ISO codes, numeric code, phone code, capital, and currency.

Fields:

• id: Primary key, auto-incremented integer.
• name: Name of the country, string, maximum length of 80 characters, not nullable.
• iso3: ISO 3166-1 alpha-3 code, string of exactly 3 characters, not nullable, unique.
• iso2: ISO 3166-1 alpha-2 code, string of exactly 2 characters, not nullable, unique.
• numeric_code: Numeric code of the country, string of exactly 3 characters, not nullable, unique.
• phone_code: Country calling code, string, not nullable.
• capital: Capital city of the country, string, not nullable.
• currency: Currency of the country, string, not nullable.

States Table Migration

The states table stores information about the states or regions within countries. Each state is associated with a country.

Fields:

• id: Primary key, auto-incremented integer.
• name: Name of the state, string, maximum length of 80 characters, not nullable.
• country_id: Foreign key, references the id field in the countries table, not nullable.

Cities Table Migration

The cities table stores information about cities within states. Each city is associated with a state.

Fields:

• id: Primary key, auto-incremented integer.
• name: Name of the city, string, maximum length of 80 characters, not nullable.
• state_id: Foreign key, references the id field in the states table, not nullable.

Seeder Description

Location seeder will populate the countries, states, and cities tables with initial data from a JSON file.

Create Tables

Step 1: Create Countries, State, and Cities Table using migration or manually. Migrations and Tables for these schemas are in the Tables folder.

Step 2: Store locations.json file into the utility folder or anywhere in the project from where you want to access it.

Step 3: Create a seeder file with the name of locations like we have in the repository. Update the location of the file as required. And run the seed. It will automatically populate data within seconds to the database.