# A renamed column and a checksum manifest make this Quran database different from the usual dump

> gaitco/quran-database ships the full Quran text with 134 editions as MySQL, SQLite or PostgreSQL artefacts, plus converters, a SHA-256 manifest of the Arabic and a Compose file whose healthchecks wait for 835,624 rows. The interesting decision is that it renamed a column to stop a silent wrong answer.

**gaitco/quran-database** — A complete, structured Quran database — verses, translations and metadata, free for any project.

- Repository: https://github.com/gaitco/quran-database
- Website: https://gaitco.com/open-source/quran-database
- Stars: 1,946 · Forks: 335
- Language: Python
- License: MIT
- Published: 2026-10-16 · Updated: 2026-10-16 · Language: en
- Canonical page: https://hysenlabs.com/projects/gaitco-quran-database

## One source dump, three databases, and the row count that gates the load

The project describes itself as a comprehensive Quran database for MySQL, PostgreSQL and SQLite, containing the complete text with multiple translations and editions. What is actually shipped is a MySQL dump plus two converters.

The contents table is specific about sizes. `data/quran.sql.zip` is a MySQL dump, roughly 187 MB uncompressed. `quran.db.gz` is a SQLite database, roughly 208 MB uncompressed. `convert_to_sqlite.py` turns `quran.sql` into `quran.db`, and `convert_to_postgres.py` imports the same dump into PostgreSQL. Alongside them sit `data/rukus.json` with 558 sourced Ruku boundaries following the Quran Foundation convention, a SHA-256 manifest at `manifest/quran-arabic.manifest.json` for verse-level checksums of the Arabic, generated schema references under `schema/<database>/schema.sql`, and `scripts/`, `tests/` and `docs/`.

The row count 835,624 appears in two places and both matter. `ayah_edition` is documented as ayah-by-ayah translations, 6,236 ayahs multiplied by 134 editions. And in the Compose file, both the MySQL and PostgreSQL services carry a healthcheck that runs a `SELECT COUNT(*) FROM ayah_edition` and compares the result against the literal 835624, with a five-minute start period and 30 retries. The container will not report healthy until the whole translation table has loaded.

That is a better import signal than a port being open, and it is also a decision baked into the design: correctness is checked by row count before anything else is trusted.

## The renamed column is the project's best idea

The breaking change notice is the first thing after the contents table and it explains a decision that is easy to miss and expensive to inherit.

In the SQLite and PostgreSQL databases, `ayahs.hizb_id` is now `ayahs.rub_id`. The old name was wrong. The column always held rubʿ al-hizb quarters, values 1 to 240, never hizbs, which run 1 to 60. The old name invited a join against the 60-row `hizbs` lookup table that would return wrong rows silently.

The fix is deliberately loud. Queries using `hizb_id` now fail rather than return bad data, and the notice tells you to rename the column in your queries or derive the real hizb with `FLOOR((rub_id - 1) / 4) + 1`. The converters go further and enforce `CHECK(rub_id BETWEEN 1 AND 240)`, so the range is a constraint in the schema rather than a comment.

The arithmetic is stated in the README: 30 juz, each split into 2 hizb for 60 total, each hizb split into 4 quarters, so 60 times 4 equals 240. That is why `rub_id` does not join to `hizbs`, and the `hizbs` table entry says so explicitly.

One inconsistency remains, and it is a real trap. The MySQL dump is unchanged and still calls the column `hizb_id`. So the same logical field has a different name depending on which artefact you loaded. Anyone migrating between them needs to know that.

## Provenance you can check yourself, and the limits of the claim

The README documents the chain of custody for the Arabic text in unusual detail. The text is the Tanzil Project's Uthmani transcription, manually verified verse by verse against the KFGQPC Madinah Mushaf, distributed via alquran.cloud under the edition name `quran-uthmani`, and dumped into this repository on 2018-06-07.

The chain is written out as KFGQPC Madinah Mushaf, then Tanzil, then alquran.cloud, then this repository.

What makes this more than a claim is that the project checked itself again. Re-checked against a fresh Tanzil download in August 2026, 5,927 of 6,236 verses, stated as 95.0 percent, are byte-identical. The remaining 309 differ only in character carriers, giving `ء` against `ـٔ`, and in word spacing. No letter and no diacritic differs.

And you can run that check on your own copy. The command is short:

```bash
just verify
```

Behind it is `python3 scripts/checksum_text.py`, which reads the shipped manifest and compares it against the Arabic in `quran.db`. A sibling command, `just manifest`, runs the same script with `--generate` to rebuild the checksums after an intentional, sourced text change.

The README also says where the claim stops, pointing at `docs/provenance.md` for the exact Tanzil export options and the limits. A text database that ships its verification method and its caveats is doing something most data dumps do not.

## Just commands cover extraction, conversion, verification and tests

The tooling is driven by `just`, and the justfile is short enough to read as documentation. The default target lists the available commands, and a `doctor` target checks that `python3` and `unzip` are present before anything else runs.

The main commands the README lists are:

```bash
just doctor   # Check required local tools
just extract  # Extract quran.sql when it is not already present
just sqlite   # Generate quran.db from the MySQL dump
just verify   # Check the Arabic text against the SHA-256 manifest
just schema   # Regenerate the readable schema references
just check    # Validate the converters and run the test suite
```

The dependency chain in the justfile is worth noting. `extract` depends on `doctor` and unzips `data/quran.sql.zip` to `quran.sql` only if it is not already there, so it will not clobber a file you have edited. `sqlite` depends on `extract` and then runs `python3 convert_to_sqlite.py`. `postgres` is not in the README list but exists in the justfile, also depending on `extract`, running `convert_to_postgres.py` and honouring `PGHOST`, `PGUSER` and `PGDATABASE`.

`check` is the gate. It tests the archive with `unzip -tqq`, byte-compiles `convert_to_sqlite.py`, `convert_to_postgres.py`, `scripts/checksum_text.py`, `scripts/export_schema.py` and `scripts/export_rukus.py`, then runs `python3 -m unittest discover -s tests`. That list of five files is also a decent map of what the project considers its own surface.

There are Docker targets too, `docker-doctor` and `docker-check`, which verify that Docker and Compose are available and that the Compose model and initialisation scripts are valid.

## Seven tables, and three of them exist only after conversion

The schema documentation distinguishes what you get from the dump and what the converters add, which is a distinction many database projects skip.

The base model is `surahs` with 114 rows and columns for the number, the Arabic name, the transliteration, the English meaning and the revelation type as Meccan or Medinan. Then `ayahs` with the Arabic text and, per row, the ayah id from 1 to 6236, the number within its surah, the Mushaf page, the surah reference, `rub_id`, the juz reference and a `sajda` prostration marker that is set on 15 ayahs.

Then `editions`, 134 entries, each with an identifier such as `en.sahih`, a language code, native and English names, a format of `text`, and a type of `translation` or `tafsir`. And `ayah_edition`, the join that carries the actual translated text in a `data` column with an `is_audio` flag.

Three tables are marked as added by the converters and therefore exist only in SQLite and PostgreSQL. `juzs` gives the 30 parts with Arabic names and start and end ayah ids. `hizbs` gives the 60 half-juz with a parent juz reference and ayah ranges. `pages` gives the 604-page Madinah Mushaf navigation map, derived at import time from the `ayahs.page` metadata in the source dump so that page boundaries stay aligned with the imported text rather than with some external table.

So the same logical content is three schemas, and the enriched one is the one you want.

## Encoding is the quiet way to corrupt this data

Everything is UTF-8, and the README is emphatic about it. The dump declares `SET NAMES utf8mb4` and every text column is `utf8mb4_unicode_ci`, so Arabic is stored with full tashkeel and no escaping.

The instruction that matters is the connection setting. Import with a `utf8mb4` connection, given as `mysql --default-character-set=utf8mb4`. Using `utf8` or `latin1` will mangle the text.

This is the kind of failure that does not announce itself. A mis-encoded import produces a database with the right row counts, so the Compose healthcheck on 835,624 rows passes, and the Arabic is quietly wrong. Nothing in the schema catches it.

That is precisely why the manifest exists. If the Arabic in your `quran.db` matches the SHA-256 checksums, the encoding is right, whatever else happened along the way. Run `just verify` after the import rather than assuming, especially on a path that skipped the Docker route.

One more operational note. The Compose file defines a named project, `quran-database`, and both the MySQL and PostgreSQL services default their passwords to the literal `quran` when the environment variable is not set, with `MYSQL_ROOT_PASSWORD` and `POSTGRES_PASSWORD` as the overrides. Ports default to 3306 and 5432. Both services carry `restart: "no"`, which is correct for a one-shot load and means a container that exits after loading will not come back on its own.

## Conclusion

This database suits an application that needs the Arabic text with translations in a real relational store, because 134 editions, 835,624 translation rows and per-verse checksums are already assembled and the SQLite and PostgreSQL builds add juz, hizb and page tables that a raw dump will not have. It is the wrong source if you need a text whose provenance you cannot verify yourself, since the shipped text comes from Tanzil via alquran.cloud and only 95.0 percent of verses matched byte-for-byte on the August 2026 re-check. Before importing, run `just verify` on your own copy of quran.db, decide whether you are loading the MySQL dump, which still uses `hizb_id`, or a converted SQLite or PostgreSQL build, which renamed it to `rub_id` and will fail loudly on the old name, and set `MYSQL_ROOT_PASSWORD` rather than accepting the compose default of `quran` on any host reachable by others.

## FAQ

### What is the difference between hizb_id and rub_id in this Quran database?

In the SQLite and PostgreSQL builds the column was renamed to rub_id because it always held rub al-hizb quarters from 1 to 240, not hizbs from 1 to 60. The MySQL dump still uses the old name hizb_id, and the converters enforce a check that rub_id is between 1 and 240.

### How do I verify the Arabic text in quran.db?

Run `just verify`, which executes `python3 scripts/checksum_text.py` to compare the text against the shipped SHA-256 manifest at manifest/quran-arabic.manifest.json.

### Which database engines does the quran-database support?

MySQL, PostgreSQL and SQLite. The MySQL dump is quran.sql, with convert_to_sqlite.py and convert_to_postgres.py converting or importing it for the other two.

### How many translations and editions does the database contain?

There are 134 entries in the editions table, and the ayah_edition table holds 835,624 rows, being 6,236 ayahs multiplied by 134 editions.

### What is the source of the Arabic text?

It is the Tanzil Project's Uthmani transcription, manually verified verse by verse against the KFGQPC Madinah Mushaf, distributed via alquran.cloud under the edition quran-uthmani and dumped on 2018-06-07. An August 2026 re-check found 5,927 of 6,236 verses byte-identical to a fresh Tanzil download.

## Sources

- [gaitco/quran-database on GitHub](https://github.com/gaitco/quran-database)
- [Issues](https://github.com/gaitco/quran-database/issues)
- [License: MIT](https://github.com/gaitco/quran-database/blob/main/LICENSE)
- [Project website](https://gaitco.com/open-source/quran-database)
- [README](https://github.com/gaitco/quran-database/blob/main/README.md)

---

Hysen Labs editorial analysis, written from the project's own repository and release notes. Cite the canonical page: https://hysenlabs.com/projects/gaitco-quran-database