Convert TOML to SQL

Why Convert TOML to SQL?

Converting TOML configuration files to SQL is essential when you need to import structured configuration data into a relational database. Many applications store their settings in TOML files during development but require database-backed configuration in production for dynamic updates, auditing, and multi-instance consistency. Generating SQL from TOML automates the transition from file-based to database-based configuration management.

TOML's typed values map naturally to SQL column types. TOML strings become VARCHAR or TEXT columns, integers become INTEGER, floats become FLOAT or DECIMAL, booleans become BOOLEAN, and dates become TIMESTAMP or DATE. TOML tables translate to SQL table definitions, and arrays of tables produce INSERT statements with multiple rows. This type-preserving conversion ensures that your database schema accurately reflects the original configuration structure.

The conversion is particularly valuable for seed data and database fixtures. Configuration data defined in TOML -- such as user roles, feature flags, default settings, or application parameters -- can be converted into SQL INSERT statements for populating development and staging databases. This approach is cleaner than maintaining separate SQL seed files because the TOML source serves as both human-readable documentation and the authoritative data source.

For DevOps and infrastructure teams, TOML-to-SQL conversion enables automated configuration deployment. Server inventories, environment variables, deployment parameters, and service configurations stored in TOML can be converted to SQL for loading into a centralized configuration database. This supports configuration management at scale, where hundreds of services need consistent, queryable settings that can be audited and rolled back.

Key Benefits of Converting TOML to SQL:

• Database Import: Load configuration data directly into any SQL database
• Type Mapping: TOML types convert accurately to SQL column types
• Seed Data: Generate INSERT statements for development databases
• Schema Generation: Auto-create CREATE TABLE from TOML structure
• Migration Scripts: Produce database migration files from config
• Audit Trail: Database storage enables change tracking
• Scalable Config: Query and manage configuration across services

Practical Examples

Example 1: User Seed Data Generation

Input TOML file (users.toml):

[[users]]
username = "admin"
email = "[email protected]"
role = "administrator"
active = true
created_at = 2024-01-15T09:00:00Z

[[users]]
username = "jdoe"
email = "[email protected]"
role = "editor"
active = true
created_at = 2024-02-20T14:30:00Z

[[users]]
username = "bsmith"
email = "[email protected]"
role = "viewer"
active = false
created_at = 2024-03-10T11:15:00Z

Output SQL file (users.sql):

-- Generated from users.toml
CREATE TABLE IF NOT EXISTS users (
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP
);

INSERT INTO users (username, email, role, active, created_at) VALUES
    ('admin', '[email protected]', 'administrator', TRUE, '2024-01-15 09:00:00'),
    ('jdoe', '[email protected]', 'editor', TRUE, '2024-02-20 14:30:00'),
    ('bsmith', '[email protected]', 'viewer', FALSE, '2024-03-10 11:15:00');

Example 2: Application Configuration Table

Input TOML file (app-config.toml):

[features]
dark_mode = true
beta_features = false
max_upload_mb = 50
session_timeout = 3600
default_language = "en"

[email]
smtp_host = "smtp.company.com"
smtp_port = 587
use_tls = true
from_address = "[email protected]"

Output SQL file (app-config.sql):

-- Application configuration settings
CREATE TABLE IF NOT EXISTS app_config (
    section VARCHAR(100) NOT NULL,
    key VARCHAR(255) NOT NULL,
    value TEXT,
    PRIMARY KEY (section, key)
);

INSERT INTO app_config (section, key, value) VALUES
    ('features', 'dark_mode', 'true'),
    ('features', 'beta_features', 'false'),
    ('features', 'max_upload_mb', '50'),
    ('features', 'session_timeout', '3600'),
    ('features', 'default_language', 'en'),
    ('email', 'smtp_host', 'smtp.company.com'),
    ('email', 'smtp_port', '587'),
    ('email', 'use_tls', 'true'),
    ('email', 'from_address', '[email protected]');

Example 3: Server Inventory Database

Input TOML file (inventory.toml):

[[servers]]
hostname = "web-01"
ip = "10.0.1.10"
cpu_cores = 4
memory_gb = 16
environment = "production"

[[servers]]
hostname = "web-02"
ip = "10.0.1.11"
cpu_cores = 4
memory_gb = 16
environment = "production"

[[servers]]
hostname = "staging-01"
ip = "10.0.2.10"
cpu_cores = 2
memory_gb = 8
environment = "staging"

Output SQL file (inventory.sql):

-- Server inventory
CREATE TABLE IF NOT EXISTS servers (
    hostname VARCHAR(255) PRIMARY KEY,
    ip VARCHAR(45) NOT NULL,
    cpu_cores INTEGER,
    memory_gb INTEGER,
    environment VARCHAR(50)
);

INSERT INTO servers (hostname, ip, cpu_cores, memory_gb, environment) VALUES
    ('web-01', '10.0.1.10', 4, 16, 'production'),
    ('web-02', '10.0.1.11', 4, 16, 'production'),
    ('staging-01', '10.0.2.10', 2, 8, 'staging');

Frequently Asked Questions (FAQ)

Q: How are TOML types mapped to SQL column types?

A: TOML strings become VARCHAR or TEXT, integers become INTEGER or BIGINT, floats become FLOAT or DECIMAL, booleans become BOOLEAN, and TOML datetime values become TIMESTAMP. The converter selects appropriate SQL types based on the values encountered in your TOML data.

Q: Which SQL dialect does the converter output?

A: The converter generates standard ANSI SQL that is compatible with PostgreSQL, MySQL, SQLite, and most other relational databases. Vendor-specific syntax variations are minimal for basic CREATE TABLE and INSERT statements, so the output works across database systems with little or no modification.

Q: How are TOML tables converted to SQL structure?

A: Each TOML table can become a SQL table. Arrays of tables ([[table]]) generate INSERT statements with multiple rows. Simple tables with key-value pairs can be stored as a configuration key-value table with section, key, and value columns.

Q: Does the converter handle nested TOML tables?

A: Yes. Nested tables can be flattened into dot-notation keys within a key-value configuration table, or they can be split into separate related SQL tables with foreign key relationships, depending on the depth and structure of the nesting.

Q: Can I use the SQL output for database migrations?

A: Yes. The generated SQL includes CREATE TABLE IF NOT EXISTS statements that are safe to run as migration scripts. You can integrate the output into migration frameworks like Flyway, Liquibase, Alembic, or Django migrations with minor adjustments.

Q: How are TOML arrays handled in SQL?

A: Simple arrays can be stored as comma-separated values in a TEXT column, as JSON arrays (where supported), or normalized into a separate junction table with one row per array element. Arrays of tables naturally become multiple INSERT rows in the corresponding SQL table.

Q: Is the generated SQL safe from injection vulnerabilities?

A: The converter properly escapes string values using SQL single-quote escaping (doubling single quotes within strings). However, for production use, it is always recommended to use parameterized queries rather than executing raw SQL strings directly.

Q: Can I convert Cargo.toml dependency lists to SQL?

A: Yes. Dependency sections from Cargo.toml or pyproject.toml can be converted to SQL tables with package name, version constraint, and feature columns. This is useful for building dependency tracking databases or software inventory systems.