Convert Properties to SQL

Why Convert Properties to SQL?

Converting Java Properties files to SQL enables organizations to move from file-based configuration to database-driven configuration management. While .properties files work well for single-application settings, modern microservice architectures often need centralized configuration that multiple services can query. SQL databases provide the infrastructure for shared, queryable, and version-tracked configuration storage.

Database-stored configuration offers significant advantages over flat files for production environments. SQL tables support access control through database permissions, audit trails through triggers or change-tracking columns, and atomic updates through transactions. When a configuration change needs to be rolled back, a database makes this trivial compared to restoring a previous version of a .properties file across multiple servers.

The conversion generates properly structured SQL with CREATE TABLE definitions that include appropriate column types, primary keys on property names, and category columns derived from the dotted namespace prefixes. Properties like spring.datasource.url and spring.datasource.username are automatically categorized under "datasource," making it easy to query all database-related settings with a simple WHERE clause.

For teams using database migration tools like Flyway or Liquibase, converted SQL files integrate directly into the migration pipeline. Configuration changes can be tracked as versioned migration scripts alongside schema changes, ensuring that the correct configuration accompanies each database version. This is especially valuable in environments where configuration and schema must evolve together.

Key Benefits of Converting Properties to SQL:

• Centralized Configuration: Store settings in a database accessible by all services
• Queryable Settings: Use SELECT statements to search and filter configuration
• Audit Trails: Track who changed which property and when with database triggers
• Access Control: Database permissions restrict who can read or modify settings
• Transaction Safety: Atomic updates prevent partial configuration changes
• Migration Integration: SQL files work with Flyway, Liquibase, and other tools
• Namespace Categorization: Dotted prefixes become queryable category columns

Practical Examples

Example 1: Application Configuration Table

Input Properties file (application.properties):

# Application metadata
app.name=order-service
app.version=2.3.1
app.description=Order processing microservice

# Server settings
server.port=8080
server.context-path=/api/orders
server.max-threads=200

Output SQL file (application_config.sql):

-- Application Configuration
-- Generated from: application.properties

CREATE TABLE IF NOT EXISTS app_config (
    property_key   VARCHAR(255) NOT NULL PRIMARY KEY,
    property_value TEXT,
    category       VARCHAR(100),
    description    VARCHAR(500),
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Application metadata
INSERT INTO app_config (property_key, property_value, category) VALUES
    ('app.name', 'order-service', 'app'),
    ('app.version', '2.3.1', 'app'),
    ('app.description', 'Order processing microservice', 'app');

-- Server settings
INSERT INTO app_config (property_key, property_value, category) VALUES
    ('server.port', '8080', 'server'),
    ('server.context-path', '/api/orders', 'server'),
    ('server.max-threads', '200', 'server');

Example 2: Multi-Environment Seed Script

Input Properties file (application-prod.properties):

# Production database
spring.datasource.url=jdbc:postgresql://db-primary:5432/proddb
spring.datasource.username=prod_user
spring.datasource.pool-size=50

# Production cache
spring.cache.type=redis
spring.redis.cluster.nodes=redis-1:6379,redis-2:6379,redis-3:6379

Output SQL file (prod_config.sql):

-- Production Environment Configuration
-- Source: application-prod.properties

BEGIN TRANSACTION;

INSERT INTO app_config (property_key, property_value, category, environment)
VALUES
    ('spring.datasource.url', 'jdbc:postgresql://db-primary:5432/proddb', 'datasource', 'prod'),
    ('spring.datasource.username', 'prod_user', 'datasource', 'prod'),
    ('spring.datasource.pool-size', '50', 'datasource', 'prod'),
    ('spring.cache.type', 'redis', 'cache', 'prod'),
    ('spring.redis.cluster.nodes', 'redis-1:6379,redis-2:6379,redis-3:6379', 'redis', 'prod')
ON CONFLICT (property_key, environment) DO UPDATE
    SET property_value = EXCLUDED.property_value,
        updated_at = CURRENT_TIMESTAMP;

COMMIT;

Example 3: Feature Flags Configuration

Input Properties file (features.properties):

# Feature flags
feature.new-checkout=true
feature.dark-mode=false
feature.beta-api=true
feature.ab-test.variant=B
feature.rate-limit.enabled=true
feature.rate-limit.max-requests=1000

Output SQL file (feature_flags.sql):

-- Feature Flags Configuration
-- Source: features.properties

CREATE TABLE IF NOT EXISTS feature_flags (
    flag_key    VARCHAR(255) NOT NULL PRIMARY KEY,
    flag_value  VARCHAR(500) NOT NULL,
    is_enabled  BOOLEAN GENERATED ALWAYS AS (flag_value = 'true') STORED,
    category    VARCHAR(100),
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO feature_flags (flag_key, flag_value, category) VALUES
    ('feature.new-checkout', 'true', 'feature'),
    ('feature.dark-mode', 'false', 'feature'),
    ('feature.beta-api', 'true', 'feature'),
    ('feature.ab-test.variant', 'B', 'feature.ab-test'),
    ('feature.rate-limit.enabled', 'true', 'feature.rate-limit'),
    ('feature.rate-limit.max-requests', '1000', 'feature.rate-limit');

-- Query active features:
-- SELECT flag_key FROM feature_flags WHERE is_enabled = TRUE;

Frequently Asked Questions (FAQ)

Q: What SQL dialect does the converter produce?

A: The converter generates standard ANSI SQL that is compatible with all major databases including MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. The output uses common data types (VARCHAR, TEXT, TIMESTAMP) and standard syntax that works across platforms without modification.

Q: How are property namespaces mapped to SQL columns?

A: Dotted property key prefixes are extracted into a "category" column. For example, spring.datasource.url gets category "datasource" and spring.redis.host gets category "redis." This enables efficient querying with WHERE category = 'datasource' to retrieve all database-related settings.

Q: Does the SQL output include CREATE TABLE statements?

A: Yes, the converter generates a complete SQL script including CREATE TABLE IF NOT EXISTS with appropriate columns (property_key, property_value, category, timestamps), followed by INSERT statements for each property. The table definition uses the property key as primary key to prevent duplicate entries.

Q: How are property values with special characters handled?

A: Property values containing single quotes, backslashes, or other SQL-sensitive characters are properly escaped in the generated INSERT statements. URLs, file paths, and connection strings are safely quoted to prevent SQL syntax errors when the script is executed.

Q: Can I use the SQL output with Flyway or Liquibase?

A: Yes, the generated SQL file can be directly used as a Flyway migration script (name it V1__insert_config.sql) or referenced in a Liquibase changelog. This integrates configuration management into your database migration pipeline, ensuring settings are versioned alongside schema changes.

Q: Does the conversion support multiple environments?

A: Yes, the converter can add an environment column based on the properties file name (e.g., application-prod.properties creates entries with environment='prod'). This allows storing configurations for dev, staging, and production in the same table with environment-based filtering.

Q: Are comments from the properties file preserved in SQL?

A: Yes, comments from the .properties file (lines starting with # or !) are converted to SQL comments (-- prefix) placed above the corresponding INSERT statements. This preserves the documentation context from the original configuration file.

Q: How do I update existing configuration in the database?

A: The converter can generate UPSERT statements (INSERT ... ON CONFLICT DO UPDATE for PostgreSQL, or INSERT ... ON DUPLICATE KEY UPDATE for MySQL) that safely update existing values while inserting new ones. This makes re-running the script safe and idempotent.