Dct Js2sql

DCT JS2SQL - JSON Schema to SQL

Convert JSON Schema files to DuckDB CREATE TABLE statements.

When to Use

Use this skill when you need to:

• Convert API specifications to database schemas
• Generate SQL from JSON Schema definitions
• Create tables from OpenAPI schemas
• Transform schema files to DDL
• Migrate from document to relational models

Installation

which dct || go build -o dct && chmod +x ./dct

Usage

dct js2sql <schema_file> [flags]

Arguments

• schema_file: Path to JSON Schema file

Flags

• -t, --table <name>: Table name (default: "test")
• -o, --output <file>: Output to file instead of stdout

Examples

Basic conversion:

dct js2sql schema.json

With custom table name:

dct js2sql api-schema.json -t api_events

Save to file:

dct js2sql user-schema.json -t users -o create_users.sql

Supported JSON Schema Features

Primitive Types

Maps JSON Schema types to DuckDB types:

Arrays

Arrays with item types:

{
  "type": "array",
  "items": {
    "type": "string"
  }
}

Output: array(varchar)

Objects/Structs

Nested objects become row types:

{
  "type": "object",
  "properties": {
    "name": {"type": "string"},
    "age": {"type": "integer"}
  }
}

Output: row(name varchar, age integer)

References ($ref)

Supports local $ref references:

{
  "$ref": "#/definitions/User"
}

Example Schema Conversions

Simple Schema

Input:

{
  "type": "object",
  "properties": {
    "id": {"type": "integer"},
    "name": {"type": "string"},
    "email": {"type": "string"},
    "active": {"type": "boolean"}
  }
}

Output:

create table test (
    id integer,
    name varchar,
    email varchar,
    active boolean
);

Complex Schema with Arrays

Input:

{
  "type": "object",
  "properties": {
    "order_id": {"type": "integer"},
    "items": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "product_id": {"type": "integer"},
          "name": {"type": "string"},
          "price": {"type": "number"},
          "quantity": {"type": "integer"}
        }
      }
    }
  }
}

Output:

create table test (
    order_id integer,
    items array(row(product_id integer, name varchar, price double, quantity integer))
);

Schema with References

Input:

{
  "definitions": {
    "Address": {
      "type": "object",
      "properties": {
        "street": {"type": "string"},
        "city": {"type": "string"}
      }
    }
  },
  "type": "object",
  "properties": {
    "name": {"type": "string"},
    "address": {"$ref": "#/definitions/Address"}
  }
}

Output:

create table test (
    name varchar,
    address row(street varchar, city varchar)
);

Best Practices

• Review generated SQL for type accuracy
• Add constraints (PRIMARY KEY, NOT NULL) manually after generation
• Consider normalizing nested arrays to separate tables
• Test the SQL in your target database
• Use meaningful table names with -t flag

Integration Examples

With DuckDB

# Convert and create table
dct js2sql schema.json -t events | duckdb mydb.duckdb

# Save and review first
dct js2sql schema.json -o schema.sql
# Review schema.sql
duckdb mydb.duckdb < schema.sql

In Data Pipeline

#!/bin/bash
# Convert all JSON schemas in a directory
for schema in schemas/*.json; do
    table=$(basename "$schema" .json)
    dct js2sql "$schema" -t "$table" > "sql/${table}.sql"
done

Limitations

• Supports JSON Schema Draft 7 features
• Complex validations (min/max, patterns) are not converted to constraints
• External $ref references (URLs) are not resolved
• Database-specific features (indexes, triggers) must be added manually

Related Skills

• dct-flattify: Convert actual JSON data (not schemas) to SQL
• dct-infer: Generate SQL from data files rather than schemas
• dct-peek: Preview JSON Schema files

Schema Sources

Works with schemas from:

• OpenAPI specifications
• JSON Schema Store
• API documentation
• Data validation frameworks
• TypeScript-to-JSON-Schema converters

Converted and distributed by TomeVault — claim your Tome and manage your conversions.