Quran Database

A comprehensive Quran database for MySQL, PostgreSQL, and SQLite containing the complete text with multiple translations and editions.

Contents

File	Format	Size	Description
data/quran.sql.zip	MySQL dump	~187 MB uncompressed	Full database dump for MySQL
data/rukus.json	JSON	~92 KB	558 sourced Ruku boundaries (Quran Foundation convention)
quran.db.gz	SQLite database	~208 MB uncompressed	Full database for SQLite
convert_to_sqlite.py	Python script	—	Converts quran.sql to quran.db
convert_to_postgres.py	Python script	—	Imports quran.sql into PostgreSQL
manifest/quran-arabic.manifest.json	SHA-256 manifest	~490 KB	Verse-level checksums for the Arabic text
schema/<database>/schema.sql	SQL	—	Generated, readable schema reference per database
scripts/	Python	—	Text verification, schema export, and Ruku data export tools
tests/	Python	—	Unit tests for the converters and tooling
docs/	Markdown	—	architecture, provenance

Breaking change — August 2026. In the SQLite and PostgreSQL databases, ayahs.hizb_id is now ayahs.rub_id. It always held rubʿ al-hizb quarters (1–240), never hizbs (1–60), so the old name invited a join that silently returned wrong rows. Queries using hizb_id now fail loudly instead. Rename the column in your queries, or derive the real hizb with FLOOR((rub_id - 1) / 4) + 1. The MySQL dump is unchanged and still says hizb_id. See #18.

Provenance

The Arabic text is the Tanzil Project's Uthmani transcription, manually verified verse-by-verse against the KFGQPC Madinah Mushaf, distributed via alquran.cloud (edition quran-uthmani) and dumped here on 2018-06-07:

KFGQPC Madinah Mushaf → Tanzil → alquran.cloud → this repository

Re-checked against a fresh Tanzil download in August 2026: 5,927 of 6,236 verses (95.0%) are byte-identical, and the remaining 309 differ only in character carriers (ء vs ـٔ) and word spacing — no letter and no diacritic differs.

Verify your own copy at any time:

just verify

Full details, the exact Tanzil export options, and the limits of this claim are in docs/provenance.md.

Database Schema

Below is the enriched model the converters produce for SQLite and PostgreSQL. The MySQL dump is the source as supplied and differs in two column names, noted inline. Machine-readable references for all three live in schema/ and are regenerated by just schema.

surahs

Stores metadata for all 114 surahs.

Column	Description
id	Surah number
number	Surah number
name_ar	Surah name in Arabic (e.g. الفاتحة)
name_en	Surah name transliterated (e.g. Al-Fatiha)
name_en_translation	English meaning (e.g. The Opening)
type	Revelation type (Meccan / Medinan)

ayahs

Contains the original Arabic text of every ayah (6,236 verses).

Column	Description
id	Unique ayah ID (1–6236)
number	Global ayah number
text	Ayah text in Arabic
number_in_surah	Ayah number within its surah
page	Mushaf page number
surah_id	Reference to surah
rub_id	Rubʿ al-hizb — quarter-hizb segment (1–240); hizb_id in the MySQL dump
juz_id	Reference to juz (1–30)
sajda	Prostration marker (0/1) — 15 ayahs

Note on rub_id (1–240). The Quran has 30 juz, each split into 2 hizb (60 total), and each hizb into 4 rubʿ al-hizb (quarters) — 60 × 4 = 240. The column therefore does not join to the 60-row hizbs lookup table. To derive the hizb (1–60), use FLOOR((rub_id - 1) / 4) + 1. The MySQL dump calls this column hizb_id; the converters rename it, and enforce CHECK(rub_id BETWEEN 1 AND 240).

editions

Available translations and editions (134 entries).

Column	Description
id	Unique ID
identifier	Unique identifier (e.g. en.sahih)
language	Language code
name	Edition name in native language
english_name	Edition name in English
format	Format (text)
type	Type (translation, tafsir)

ayah_edition

Ayah-by-ayah translations (835,624 rows — 6,236 ayahs × 134 editions).

Column	Description
id	Unique ID
ayah_id	Reference to ayah
edition_id	Reference to edition
data	Translated/annotated text
is_audio	Audio flag

juzs (SQLite & PostgreSQL — added by the converters)

30 juz (parts) with ayah ranges.

Column	Description
id	Juz ID (1–30)
juz_number	Juz number
name_ar	Arabic name (e.g. الجزء الأول)
start_ayah_id	First ayah in this juz
end_ayah_id	Last ayah in this juz

hizbs (SQLite & PostgreSQL — added by the converters)

60 hizb (half-juz) with ayah ranges and juz references. Not referenced by ayahs.rub_id — see the note above.

Column	Description
id	Hizb ID (1–60)
hizb_number	Hizb number
juz_id	Parent juz
name_ar	Arabic name
start_ayah_id	First ayah in this hizb
end_ayah_id	Last ayah in this hizb

pages (SQLite & PostgreSQL — added by the converters)

The 604-page Madinah Mushaf navigation map. It is derived at import time from the ayahs.page metadata supplied in the versioned source dump, so its ayah boundaries stay aligned with the imported text.

Column	Description
id	Page ID (1–604)
page_number	Mushaf page number (1–604)
start_ayah_id	First ayah assigned to the page
end_ayah_id	Last ayah assigned to the page

Setup

Project commands

This repository uses just to provide a small set of consistent project commands. After installing just, run:

just

The main commands are:

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

Text encoding

All text is UTF-8. The dump declares SET NAMES utf8mb4 and every text column is utf8mb4_unicode_ci, so Arabic is stored with full tashkeel and no escaping. Import with a utf8mb4 connection (mysql --default-character-set=utf8mb4) — using utf8/latin1 will mangle the text. SQLite and PostgreSQL copies are UTF-8 as well.

MySQL

1. Extract the SQL file:

   just extract
   

2. Import into MySQL:

   mysql --default-character-set=utf8mb4 -u <username> -p <database_name> < quran.sql
   

3. Optionally drop the Laravel leftovers:

   The dump came from a Laravel application, so it also creates users, password_resets, and migrations. They are unused here — users and password_resets are empty definitions, and migrations holds only the eight 2018 migration filenames. No account or credential is in this repository. The SQLite and PostgreSQL converters skip all three; for a direct MySQL import:

   DROP TABLE IF EXISTS users, password_resets, migrations;
   

SQLite

1. Extract the database (--keep, or you delete the archive):

   gunzip --keep quran.db.gz
   

2. Open with any SQLite client:

   sqlite3 quran.db
   

   Or use in Python:

   import sqlite3
   db = sqlite3.connect("quran.db")
   # Get Surah Al-Fatiha
   rows = db.execute("SELECT * FROM ayahs WHERE surah_id = 1").fetchall()
   # Get ayah with translation
   rows = db.execute("SELECT arabic, translation FROM ayah_with_translation WHERE surah_id = 1 AND language = 'en'").fetchall()
   

3. Check the copy you just extracted:

   just verify
   # OK — 6236 verses match tanzil-uthmani, root 5b58aa48fb07265a...
   

4. To regenerate from the MySQL dump:

   just sqlite
   

Both the SQLite and PostgreSQL versions add:

• Proper foreign key constraints and CHECK constraints, including CHECK(rub_id BETWEEN 1 AND 240)
• Indexes on commonly queried columns (surah_id, juz_id, rub_id, page, number_in_surah, sajda)
• Pre-populated juzs and hizbs lookup tables with ayah ranges
• Views: surah_stats (ayat counts per surah), ayah_with_translation (joined ayah + translation)
• english_name instead of the dump's englishName, and rub_id instead of its misnamed hizb_id
• ANALYZE statistics for the query planner — plus a VACUUMed file on SQLite, which PostgreSQL cannot do inside the import transaction
• None of the Laravel leftover tables (users, password_resets, migrations)

PostgreSQL

1. Install the PostgreSQL driver and extract the source dump:

   python3 -m pip install psycopg2-binary
   just extract
   

2. Create the target database:

   createdb quran
   

3. Run the converter:

   PGDATABASE=quran just postgres
   

Connection settings can be supplied through PGHOST, PGPORT, PGUSER, PGPASSWORD, and PGDATABASE. The converter validates the input before connecting, loads tables in foreign-key dependency order, and recreates the schema in one transaction so a failed import rolls back cleanly.

Docker

Build and initialize all three database targets from the tracked MySQL dump:

just docker-up

This starts MySQL 8.4 and PostgreSQL 16 with persistent named volumes and writes the enriched SQLite database to output/quran.db. Set SQLITE_OUTPUT_DIR to write it elsewhere. The just recipes pass your local UID/GID to the SQLite exporter so its generated file remains writable on the host. MySQL retains the supplied source schema; SQLite and PostgreSQL use the enriched rub_id model. The MySQL and PostgreSQL health checks wait for all 835,624 ayah_edition rows, so a listening server is not reported ready before its import has finished.

The development defaults are MYSQL_ROOT_PASSWORD=quran, POSTGRES_USER=postgres, and POSTGRES_PASSWORD=quran; override them (and the host ports) with environment variables before exposing either service:

MYSQL_ROOT_PASSWORD=change-me POSTGRES_PASSWORD=change-me just docker-up

Useful commands:

just docker-check   # validate Compose and initialization scripts
just docker-logs    # follow imports and service logs
just docker-down    # stop services, retaining their volumes

Development

just check    # compile both converters and run the test suite
just verify   # check the Arabic text against the SHA-256 manifest
just schema   # regenerate schema/<database>/schema.sql

Or without just:

PYTHONDONTWRITEBYTECODE=1 python3 -m unittest discover -s tests -v

CI runs the suite on Python 3.10 and 3.14, lints the Markdown, tests both archives, verifies the Arabic text against the manifest, and fails if the generated schema references are out of date.

Converter notes worth knowing before changing one:

• Both load from an allow-list (TABLE_ORDER) of four tables, so an unrecognised table in a future dump is skipped rather than imported blindly.
• INSERTs name their columns, and COLUMN_RENAMES maps the two the enriched model changes (hizb_id → rub_id, englishName → english_name). A dump that reorders or adds a column now fails loudly instead of loading values into the wrong fields.
• The SQLite converter runs ANALYZE and VACUUM before finishing, so a regenerated quran.db carries query planner statistics like the shipped one.

See CONTRIBUTING.md for the full workflow and docs/architecture.md for the data flow.

Roadmap

We welcome contributions! Here's the planned roadmap for this project. Pick any item and submit a PR.

Database Improvements

• Add proper indexes for faster queries
• Add juz (parts) table with ayah ranges
• Add hizb and rub (quarter) divisions
• Add pages table (Mushaf page mapping)
• Add word-by-word breakdown table (Arabic root, morphology)
• Add sajdah (prostration) markers
• Support PostgreSQL and SQLite exports
• Add foreign key constraints and proper normalization
• Publish a verse-level SHA-256 manifest and verify it in CI
• Document text provenance back to Tanzil and the Madinah Mushaf
• Generate readable schema references per database

Data Expansion

• Add more translations (Urdu, French, Turkish, Indonesian, etc.)
• Add Tafsir (exegesis) data — Ibn Kathir, Al-Tabari, Al-Sa'di, etc.
• Add audio recitation references (Mishary, Al-Husary, Abdul Basit, etc.)
• Add transliteration for each ayah
• Add asbab al-nuzul (reasons of revelation)
• Add hadith references related to each ayah
• Add dua (supplication) extractions from the Quran

API / Backend

• Build a RESTful API (Node.js or Python)
• GraphQL endpoint for flexible queries
• Search endpoint with full-text Arabic search
• Pagination and filtering support
• API rate limiting and authentication
• Docker setup for easy deployment
• API documentation (Swagger / OpenAPI)

Frontend / App

• Web app for browsing surahs and ayahs
• Ayah-by-ayah reader with translation toggle
• Audio player integration with reciter selection
• Search functionality (by surah, ayah, keyword)
• Bookmarking and progress tracking
• Dark mode and responsive design
• Mobile-friendly PWA support

Contributing

Contributions are welcome. CONTRIBUTING.md has the full workflow — branching, the verification commands to run, and the rules that apply specifically to Quranic text and metadata.

The short version:

• Never hand-edit Quranic text, translations, or generated artifacts. Change the source or the generator, regenerate, and say in the PR how you verified it.
• Cite a source, version, and licence for any data change.
• Run just check before opening a PR.

Unsure where to start? Pick an unchecked roadmap item above, or open an issue.

Screenshots

These show the MySQL source database, so they use its column names — hizb_id rather than rub_id, englishName rather than english_name.

Related Projects

• Quran Lumen API — Laravel Lumen API
• Quran Vue

Community ports

Built from this database by the community:

• quran-database-malay — Bahasa Melayu edition, cross-checked against the official Malaysian translation, with a SQLite + WASM search page.
• quranchecksum — verse-level SHA-256 manifest for verifying an imported copy of the Quran text.

Ported or built something? Open an issue and we'll list it.

Sponsor

Become a Sponsor

License

The code and packaging in this repository are MIT licensed. The Quran text is in the public domain; individual translations remain under the terms set by their translators and publishers — see docs/provenance.md. data/rukus.json is derived from Quran Foundation API metadata and is subject to their developer terms, not the MIT license — see data/README.md.