Overview

SchemaCrawler MCP Server is a small HTTP service that integrates the Model Context Protocol (MCP) with relational schema inspection. It helps developers and data engineers automatically produce syntactically correct SQL for a target database and probe column-name prefixes (or abbreviations) to infer meaning and context. The server is particularly useful when working with legacy schemas, data catalogs, or when automating query generation for web-scraping, analytics, or migration tasks.

By combining introspection of the live database schema with lightweight ML-driven contextual prompts, the MCP server reduces guesswork: it can generate queries that match the target SQL dialect and discover likely expansions for cryptic column prefixes (for example, turning cust_id into customer_id). This saves time during ETL development, reverse engineering, and when onboarding new engineers to unfamiliar databases.

Features

Automatic SQL generation tailored to the database dialect
Column prefix analysis to infer semantic expansions of abbreviations
Database schema-aware: inspects tables, columns, types, and constraints
HTTP API for easy integration with tools, scrapers, and automation scripts
Configurable connection settings and authentication
Lightweight deployment: run locally, in containers, or as part of CI

Installation / Configuration

Clone the repository and build, or use the provided Docker image. Replace environment values with your own connection credentials.

Clone and build (Maven or Gradle):

git clone https://github.com/schemacrawler/SchemaCrawler-MCP-Server-Usage.git
cd SchemaCrawler-MCP-Server-Usage

# Build with Maven
mvn -DskipTests package

# or with Gradle
./gradlew build

Run with Java (assuming target JAR produced):

java -jar target/schemacrawler-mcp-server.jar \
  --server.port=8080 \
  --db.url=jdbc:postgresql://db.example.com:5432/mydb \
  --db.user=dbuser \
  --db.password=secret

Docker example:

docker run -p 8080:8080 \
  -e DB_URL=jdbc:postgresql://db:5432/mydb \
  -e DB_USER=dbuser \
  -e DB_PASSWORD=secret \
  schemacrawler/mcp-server:latest

Common configuration variables:

DB_URL / db.url — JDBC connection string
DB_USER / db.user — database username
DB_PASSWORD / db.password — database password
SERVER_PORT / server.port — HTTP port
LOG_LEVEL / log.level — debug/info/warn

If you compile locally, the actual CLI flags may differ; check the repository README or bundled docs for exact options.

Available Tools / Resources

HTTP API (endpoints examples below)
Schema introspection via SchemaCrawler integration (table/column metadata)
Simple JSON request/response model for integration with scraping or orchestration processes
GitHub repository and issues tracker: https://github.com/schemacrawler/SchemaCrawler-MCP-Server-Usage

API Endpoint quick reference

Endpoint	Purpose
POST /sql	Generate valid SQL for the target DB given a prompt or context
POST /column-prefix	Probe and score possible expansions for a column prefix

Note: Exact endpoint paths may be confirmed in the deployed server’s OpenAPI spec or project README.

Example API Usage

Generate SQL for a table (curl):

curl -X POST http://localhost:8080/sql \
  -H "Content-Type: application/json" \
  -d '{
    "table": "orders",
    "goal": "retrieve latest 100 orders with customer name and total",
    "dialect": "postgresql"
  }'

Possible simplified response:

{
  "sql": "SELECT o.id, o.order_date, o.total, c.name FROM orders o JOIN customers c ON o.customer_id = c.id ORDER BY o.order_date DESC LIMIT 100;"
}

Probe column prefix analysis:

curl -X POST http://localhost:8080/column-prefix \
  -H "Content-Type: application/json" \
  -d '{
    "columnPrefix": "cust_id",
    "samples": ["cust_id", "cust_name", "cust_since"],
    "contextTables": ["customers", "orders"]
  }'

Possible simplified response:

{
  "prefix": "cust",
  "expansions": [
    {"suggestion":"customer","score":0.92},
    {"suggestion":"custodian","score":0.10}
  ],
  "notes":"Likely stands for 'customer' based on foreign key relationships and samples."
}

Use Cases

Reverse engineering: Quickly generate queries to sample data from unfamiliar schemas and establish column semantics before building ETL jobs.
Data catalog enrichment: Automatically suggest human-friendly expansions for short or encoded column names to improve documentation.
Web scraping + enrichment: Combine scraped data with schema-aware queries to validate or augment records using authoritative DB fields.
Migrations and refactoring: Generate migration queries in the correct dialect and identify columns whose prefixes should be expanded or standardized.
Onboarding: Provide new developers with runnable example queries and inferred column meanings to shorten ramp-up time.

Tips for Developers

Always run generation against a representative schema snapshot (dev or staging) rather than production when iterating.
Use the column-prefix endpoint to seed renaming or mapping tables before running wide-scale migrations.
Combine the API with your CI pipelines to enforce schema naming conventions and produce documentation artifacts automatically.

For exact API schemas, build flags, and advanced usage, consult the project repository on GitHub: https://github.com/schemacrawler/SchemaCrawler-MCP-Server-Usage

SchemaCrawler MCP Server: SQL, Column Prefix Analysis