Convert ADOC to SQL

Why Convert ADOC to SQL?

Converting AsciiDoc documentation to SQL format is valuable when you have database schema documentation, data dictionaries, or table specifications written in AsciiDoc and need to generate executable SQL scripts. This conversion extracts structured data from your documentation and transforms it into SQL statements.

AsciiDoc is commonly used for documenting database schemas, including table structures, column definitions, data types, and relationships. When this documentation contains table specifications in AsciiDoc table format, the converter can extract this information and generate corresponding CREATE TABLE statements, INSERT statements for seed data, or other SQL operations.

This conversion is particularly useful in documentation-driven development workflows where the database schema is first documented in AsciiDoc as part of technical specifications, then converted to SQL for actual database implementation. It helps maintain consistency between documentation and implementation.

The converter extracts tabular data, code blocks containing SQL snippets, and structured content from AsciiDoc documents. AsciiDoc headings become SQL comments to preserve documentation context, while tables with column definitions can be transformed into CREATE TABLE statements or INSERT queries.

Key Benefits of Converting ADOC to SQL:

• Documentation to Code: Generate SQL from documented specifications
• Schema Extraction: Convert documented tables to CREATE statements
• Data Seeding: Transform tabular data to INSERT statements
• Consistency: Ensure docs and database match
• Automation: Integrate into CI/CD pipelines
• Version Control: Track both documentation and SQL changes
• Comment Preservation: Keep documentation context as SQL comments

Practical Examples

Example 1: Table Schema Documentation to SQL

Input ADOC file (schema.adoc):

= User Management Database Schema

== Users Table

Stores user account information.

|===
| Column | Type | Constraints | Description

| id | SERIAL | PRIMARY KEY | Auto-increment ID
| username | VARCHAR(50) | NOT NULL UNIQUE | Login name
| email | VARCHAR(100) | NOT NULL UNIQUE | Email address
| created_at | TIMESTAMP | DEFAULT NOW() | Registration date
|===

== Roles Table

Defines user permission roles.

|===
| Column | Type | Constraints

| id | SERIAL | PRIMARY KEY
| name | VARCHAR(30) | NOT NULL UNIQUE
| description | TEXT | -
|===

Output SQL file (schema.sql):

-- User Management Database Schema
-- Generated from AsciiDoc documentation

-- Users Table
-- Stores user account information.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Roles Table
-- Defines user permission roles.

CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(30) NOT NULL UNIQUE,
    description TEXT
);

Example 2: Seed Data Documentation to INSERT Statements

Input ADOC file (seed_data.adoc):

= Initial Data Setup

== Default Categories

|===
| id | name | description

| 1 | Electronics | Phones, computers, gadgets
| 2 | Clothing | Shirts, pants, accessories
| 3 | Books | Fiction, non-fiction, textbooks
|===

== Status Codes

|===
| code | label | active

| PENDING | Pending Review | true
| APPROVED | Approved | true
| REJECTED | Rejected | false
|===

Output SQL file (seed_data.sql):

-- Initial Data Setup
-- Generated from AsciiDoc documentation

-- Default Categories
INSERT INTO categories (id, name, description) VALUES
(1, 'Electronics', 'Phones, computers, gadgets'),
(2, 'Clothing', 'Shirts, pants, accessories'),
(3, 'Books', 'Fiction, non-fiction, textbooks');

-- Status Codes
INSERT INTO status_codes (code, label, active) VALUES
('PENDING', 'Pending Review', true),
('APPROVED', 'Approved', true),
('REJECTED', 'Rejected', false);

Example 3: SQL Code Blocks Extraction

Input ADOC file (queries.adoc):

= Common Database Queries

== Get Active Users

This query retrieves all active users:

[source,sql]
----
SELECT id, username, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC;
----

== User Statistics

Get user count by registration month:

[source,sql]
----
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS user_count
FROM users
GROUP BY month
ORDER BY month;
----

Output SQL file (queries.sql):

-- Common Database Queries
-- Generated from AsciiDoc documentation

-- Get Active Users
-- This query retrieves all active users:

SELECT id, username, email
FROM users
WHERE status = 'active'
ORDER BY created_at DESC;

-- User Statistics
-- Get user count by registration month:

SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS user_count
FROM users
GROUP BY month
ORDER BY month;

Frequently Asked Questions (FAQ)

Q: What kind of SQL is generated?

A: The converter generates standard SQL that is compatible with most relational database systems. The output uses common SQL syntax (CREATE TABLE, INSERT, SELECT) that works with MySQL, PostgreSQL, SQLite, and SQL Server. For database-specific features, you may need to adjust the generated SQL to match your target database dialect.

Q: How are AsciiDoc tables converted to SQL?

A: AsciiDoc tables are intelligently parsed based on their content. Tables with column definitions (containing type information like VARCHAR, INTEGER) are converted to CREATE TABLE statements. Tables containing data values are converted to INSERT statements. The first row is typically used as column headers or table structure definition.

Q: Are SQL code blocks preserved exactly?

A: Yes! SQL code blocks in AsciiDoc (marked with [source,sql]) are extracted and included in the output exactly as written. This is useful when your documentation already contains SQL snippets that you want to compile into a single executable script file.

Q: What happens to AsciiDoc headings and text?

A: AsciiDoc headings are converted to SQL comments (-- comment) to preserve documentation context in the generated script. Descriptive paragraphs are also converted to comments. This maintains the documentation structure within the SQL file for better readability and maintenance.

Q: Can I use the generated SQL directly in production?

A: The generated SQL should be reviewed and tested before production use. While the converter produces valid SQL syntax, you should verify that data types, constraints, and table relationships match your specific database requirements. Always test in a development environment first.

Q: Does it support database migrations?

A: The converter generates standalone SQL scripts rather than migration files for specific frameworks (like Rails migrations or Flyway). However, you can use the generated SQL as a starting point for creating migrations, or include it in your database seeding process.

Q: How are data types handled?

A: The converter recognizes common SQL data types mentioned in AsciiDoc tables (VARCHAR, INTEGER, TEXT, BOOLEAN, TIMESTAMP, etc.) and includes them in CREATE TABLE statements. If data types aren't specified, the converter may infer types from the data or use generic types that you can customize.

Q: What is the best way to document database schemas in AsciiDoc?

A: For optimal conversion, use AsciiDoc tables with clear column headers (Column, Type, Constraints, Description). Include headings for each table name, and use consistent naming conventions. Place actual SQL queries in [source,sql] code blocks. This structured approach ensures the best conversion results.