Create migrations for Brazilian states and cities

cities.sql

CREATE TABLE IF NOT EXISTS cities (
	id INTEGER PRIMARY KEY,
	name VARCHAR,
	uf_id INTEGER REFERENCES ufs (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT cities_name_uf_id_key UNIQUE (name, uf_id)
);

---- create above / drop below ----

DROP TABLE IF EXISTS cities;

create_cities_insert.sh

#!/bin/bash
API="https://servicodados.ibge.gov.br/api/v1"

ufs=$(curl -sS "$API/localidades/estados?orderBy=nome" | jq -c '.[].id')

echo "INSERT INTO cities (id, name, uf_id) VALUES"

for uf in $ufs; do
	url="$API/localidades/estados/$uf/municipios?orderBy=nome"
	curl -sS "$url" | jq --raw-output ".[] | \"(\\(.id), '\\(.nome)', $uf)\"" | sed -e "s/'\(.*\)\('\)\(.*\)'/'\\1''\\3'/g"
done | paste -sd ',' - | sed -e "s/),(/),\n(/g"

printf ";\n\n---- create above / drop below ----\n\nTRUNCATE TABLE cities;\n"

create_ufs_inserts.sh

#!/bin/bash
API="https://servicodados.ibge.gov.br/api/v1"

echo "INSERT INTO ufs (id, name, sigla) VALUES"

curl -sS "$API/localidades/estados?orderBy=nome" | jq --raw-output ".[] | \"(\\(.id), '\\(.nome)', '\\(.sigla)')\"" | paste -sd ',' - | sed -e "s/),(/),\n(/g"

printf ";\n\n---- create above / drop below ----\n\nTRUNCATE TABLE ufs;\n"

ufsl.sql

CREATE TABLE IF NOT EXISTS ufs (
	id INTEGER PRIMARY KEY,
	name VARCHAR NOT NULL UNIQUE,
	sigla VARCHAR(2) NOT NULL UNIQUE
);

---- create above / drop below ----

DROP TABLE IF EXISTS ufs;