DEV Community: Sualeh Fatehi

Reverse Engineer Any Database into dbdiagram.io, PlantUML, Mermaid, or QuickDBD - Then Keep Designing

Sualeh Fatehi — Sat, 30 May 2026 20:06:07 +0000

Most database diagram tools stop at documentation. They connect to your database, inspect the schema, and generate a report or a picture. That is useful, but it does not help if your next step is design work.

What if you want to start from an existing database, open the result in a design tool, add a few new tables, adjust relationships, and then turn that updated design back into SQL?

SchemaCrawler supports that workflow.

SchemaCrawler can connect to any database with a JDBC driver and generate editable output in four useful formats:

DBML for dbdiagram.io
PlantUML for PlantText, IntelliJ, and other PlantUML tools
Mermaid for GitHub, GitLab, Notion, and the Mermaid Live Editor
QuickDBD for QuickDatabaseDiagrams.com

That gives you a practical round-trip workflow:

Connect to an existing database
Export the schema into DBML, PlantUML, Mermaid or QuickDBD
Edit the design in the tool you already use
Generate DDL from the updated design when you need SQL again

SchemaSpy is strong when you want a browsable HTML report for stakeholders. But HTML is the end of the line. You can read it, click through it, and share it, but you cannot open it in a design tool and keep working. If you need reverse engineer -> edit design -> generate DDL, SchemaCrawler is the better fit.

In this article, I will use the Northwind sample SQLite database for all examples.

Step 1: Connect

Make sure you have Docker installed. Then download the Northwind sample SQLite database into your current directory.

All commands below mount your current directory into the SchemaCrawler container, so the generated files are written back to your machine.

If you are using PowerShell on Windows, replace the trailing backslash on each line with a back-tick "`".

Step 2: Export

Export to DBML for dbdiagram.io

DBML is the best choice if you want to keep designing and later generate SQL from the updated model.

sh docker run \ --mount type=bind,source="$(pwd)",target=/home/schcrwlr/share \ --rm -it \ schemacrawler/schemacrawler \ /opt/schemacrawler/bin/schemacrawler.sh \ --server=sqlite \ --database=share/northwind.db \ --info-level=standard \ --command=script \ --script-language=python \ --script=dbml.py \ --output-file=share/northwind.dbml

Open dbdiagram.io, paste in the contents of northwind.dbml, and you immediately have an editable diagram based on the live database.

Export to PlantUML

PlantUML is a good choice if your team keeps diagrams in source control or already uses PlantUML in docs and architecture notes.

Open northwind.puml in PlantText or your IDE and keep editing.

Export to Mermaid

Mermaid is the best choice if you want diagrams that render directly in Markdown-based tools such as GitHub, GitLab, and Notion.

Paste northwind.mmd into the Mermaid Live Editor or commit it straight into your documentation.

Export to QuickDBD

QuickDBD is a good choice when you want fast, text-first schema editing in a dedicated diagram editor.

Paste northwind.quickdbd into QuickDatabaseDiagrams.com to continue editing.

Step 3: Edit

This is the part most reverse-engineering tools do not support well.

Once you have exported the live schema into an editable design language, you are no longer stuck with a read-only report. You can continue designing.

For example, imagine that after reverse-engineering northwind you want to add a table for storing playlist tags.

In DBML, you could extend the exported design with something like:

`dbml
Table PlaylistTag {
PlaylistTagId integer [pk]
PlaylistId integer [not null]
TagName varchar [not null]
}

Ref: PlaylistTag.PlaylistId > Playlist.PlaylistId
`

That is the key distinction in this workflow:

you start from what is actually in production
you bring that schema into an editable format
you extend the design instead of redrawing it from scratch

The same idea works with PlantUML, Mermaid, and QuickDBD. Add entities, adjust relationships, rename columns, or reorganize sections of the model for clarity. SchemaCrawler gets you to a clean starting point from a live database instead of forcing you to recreate the schema by hand.

Step 4: Generate DDL

DBML is especially useful because it can be turned back into SQL.

Install the DBML CLI:

sh npm install -g @dbml/cli

Then generate SQL from your updated design:

sh dbml2sql northwind.dbml --postgres

Or for MySQL:

sh dbml2sql northwind.dbml --mysql

Now you have a full round-trip flow:

reverse engineer from a live database
edit the design in a modeling tool
generate SQL from the updated design

That is a much more useful workflow than producing a static HTML report and stopping there.

Worked Example: northwind from Live Database to Editable Design

Here is the full DBML flow in one sequence.

1. Export northwind to DBML

2. Open the result in dbdiagram.io

Paste the contents of northwind.dbml into dbdiagram.io.

3. Extend the model

Add new tables, fields, and relationships directly in DBML.

4. Generate SQL

sh dbml2sql northwind.dbml --postgres

At that point you have gone from a real database to an editable design and back into SQL without manually redrawing anything.

When to Choose Each Output Format

Choose DBML if you want the strongest design-tool workflow and the option to generate SQL later.
Choose PlantUML if your team prefers text-based diagrams in source control or already uses PlantUML in architecture docs.
Choose Mermaid if you want diagrams that live directly inside Markdown, GitHub, GitLab, or internal docs.
Choose QuickDBD if you want rapid text-based editing in the QuickDatabaseDiagrams editor and an easy way to iterate on schema shape.

You do not need to choose only one forever. The same database can be exported to all four, depending on what the next step in your workflow looks like.

Why This Matters

Reverse engineering is only half the job.

Developers often inherit an existing database and need to answer more than "what tables are there?" They need to ask:

what should we add next?
how do we model the next feature without breaking what exists?
how do we propose changes in a format that is easy to review?
how do we get from the current schema to future DDL with less manual work?

SchemaCrawler helps because it starts with the live database and produces output you can keep working with.

That is the real value of DBML, PlantUML, Mermaid, and QuickDBD export. Not just nicer diagrams, but a better workflow.

Try It Yourself

Start with the northwind sample database and generate one of the editable formats above. Once you have the workflow working locally, switch the connection to PostgreSQL, MySQL, SQL Server, Oracle, DB2, or any other JDBC database supported by SchemaCrawler.

If you want to customize the generated output, you can find and edit the built-in scripts:

SchemaSpy vs SchemaCrawler - Which Database Documentation Tool is Right for You?

Sualeh Fatehi — Tue, 26 May 2026 23:47:18 +0000

Both SchemaSpy and SchemaCrawler are free, open-source tools for documenting and analysing relational databases over JDBC. Both have been around for over 20 years. Both can generate entity-relationship diagrams. Yet the two tools are more different than they look.

Disclosure: I work on SchemaCrawler, so take this with appropriate scepticism. I have tried to represent SchemaSpy fairly.

What SchemaSpy Does Best

SchemaSpy's primary strength is its interactive HTML report. After a single run, you get a navigable website: clickable table pages, hyperlinked foreign keys, anomaly reports, and embedded ER diagrams for every table. It is exactly the kind of output you hand to a non-technical stakeholder, a consultant, or a new team member who needs to understand the data model quickly.

SchemaSpy also detects implied relationships - potential foreign keys that are not formally declared in the schema. It provides an orphan table page that surfaces tables with no relationships. These are genuinely useful for legacy databases.

If your goal is a shareable, browsable report that looks great in a browser, SchemaSpy delivers.

What SchemaCrawler Does Best

SchemaCrawler's strength is everything a developer needs before and after the report: searching, diffing, linting, scripting, and integration.

Diff-able text output

SchemaCrawler's "schema" command produces clean, structured text output - not HTML. Run it against production and staging, diff the outputs in git, and see exactly what changed. This is the foundation of schema change tracking in CI/CD.

Schema lint

The "lint" command catches design problems automatically: missing primary keys, nullable columns in unique constraints, redundant indices, tables with no relationships, and more. The lints can be extended to enforce your organization's rules, such as specific naming conventions.

Grep - regex search across the entire schema

--grep-tables and --grep-columns let you search all tables, columns, stored procedures, triggers, and foreign keys by regular expression. Find every column referencing a concept across a 500-table database in a single command. Combine it with --parents and --children to pull the related tables automatically.

Multiple output formats

Text, HTML, JSON, CSV, Markdown, and ER diagrams (via Graphviz). The Markdown output is useful for documentation-as-code; the JSON output is useful for tooling.

Schema extension with PlantUML and dbdiagram.io

SchemaCrawler can generate output in PlantUML and dbdiagram.io formats directly from your live database. This means you can start from what is actually in the database and then edit the diagram to model proposed additions or changes - something neither SchemaSpy nor most ERD tools support directly.

Scripting - Python, JavaScript

--command=script runs a script against live schema metadata. Generate custom reports, validate naming conventions, transform output - without writing a Java application.

Full Java API

SchemaCrawler is a JDBC metadata API. Embed it in a Java application and work with tables, columns, indexes, foreign keys, and routines as Java objects. SchemaSpy has no public API.

GitHub Actions integration

There is an official SchemaCrawler GitHub Action in the marketplace. Run lint, diff, and schema documentation generation as part of any CI/CD workflow. SchemaSpy has no equivalent.

Feature Comparison

Capability	SchemaCrawler	SchemaSpy
Interactive HTML report	✅	✅
Clickable navigation between tables	✅	✅
ER diagrams	✅	✅
Diff-able text output	✅	❌
Extensible schema lint / design checks	✅	❌
Grep / regex search across schema	✅	❌
Markdown, JSON, CSV output	✅	❌
PlantUML and dbdiagram.io output	✅	❌
Scripting (Python, JS, Groovy)	✅	❌
Java API	✅	❌
GitHub Actions integration	✅	❌
Implied relationship detection	✅	✅
Orphan table detection	✅	✅

Decision Guide

Choose SchemaSpy if…

Your primary output is a shareable, interactive HTML report for non-technical stakeholders
You want clickable navigation between related tables out of the box
You need implied/ virtual foreign key detection for a legacy schema with missing FK declarations

Choose SchemaCrawler if…

You need to track schema changes in version control - diff text output between environments
You want to catch design problems automatically - schema lint in CI
You need to search across a large schema - find all tables or columns matching a pattern
You are building schema checks into a CI/CD pipeline - GitHub Actions integration
You need output in Markdown, JSON, or CSV as well as HTML
You want to model future schema designs in PlantUML or dbdiagram.io, starting from your live database
You want to write scripts that process schema metadata programmatically
You are building a Java application that needs database metadata as objects

Can You Use Both?

Yes. They serve genuinely different workflows.

Use SchemaSpy to generate the stakeholder-facing HTML report. Use SchemaCrawler for diff, lint, and grep in your development and CI/CD workflow. The two tools are not competitors - they complement each other.

Try SchemaCrawler

The full documentation is at schemacrawler.com. The source is at github.com/schemacrawler/SchemaCrawler.

3-way Boolean Anti-pattern

Sualeh Fatehi — Sat, 14 Feb 2026 18:22:36 +0000

In Java, the "3-way Boolean" anti-pattern is what you get when you use the boxed type Boolean (instead of primitive boolean) and you implicitly allow it to represent three states:

true
false
null ← the third, often accidental, state

That "third state" becomes a trap because most code reads like it's dealing with a simple yes/ no flag, but at runtime it can behave differently (or crash) when the value is null.

Why it's an anti-pattern

It creates implicit tri-state logic without making it explicit
A variable named enabled, isReady, shouldRetry, etc. strongly implies binary logic, but Boolean quietly allows null, which means unknown, not set, not loaded, not applicable, or sometimes just "bug".
It can throw NullPointerException (NPE) during unboxing
Common expressions like these will NPE if the Boolean is null:

   Boolean flag = getFlag();     // could be null
   if (flag) {                   // auto-unboxing -> NPE if null
       // ...
   }

This exact failure mode is commonly seen during upgrades or refactors because the code compiles fine but fails at runtime.

It leads to confusing "null means false... except when it doesn't" logic People often "fix" the NullPointerException by doing inconsistent checks:

   if (flag != null && flag) ...
   // elsewhere
   if (Boolean.TRUE.equals(flag)) ...
   // elsewhere
   if (Boolean.FALSE.equals(flag)) ...

Now your codebase has multiple semantic interpretations of null.

Generate MCP Tool Schemas Directly From Java Code

Sualeh Fatehi — Thu, 20 Nov 2025 02:59:44 +0000

If you are building an MCP server, every tool you expose needs an inputSchema. MCP servers written with Spring AI support often start with a simple data class for tool inputs. Then come changes: a new field, a renamed property, or updated constraints. The JSON schema in the tool registration rarely keeps up - that means clients may send invalid payloads. By generating the schema from the source of truth — the Java type — you remove that drift.

Writing that JSON by hand is repetitive, easy to get wrong. MCP supports only a specific sub-type of the JSON Schema specification. The MCP JSON Schema library keeps the parameter schema and the code in lockstep by generating the MCP-compatible JSON Schema from a Jackson 3 annotated Java class or record.

What you get:

Use of Jackson 3 annotations for naming, required fields, and descriptions that carry over into the schema
Use of Jakarta Bean Validation to adds meaningful constraints to the schema (for example, @Max,@Min, @Positive, @PositiveOrZero, @Negative, @NegativeOrZero on numbers, or @Size, @NotBlank on strings)
Automatic handling of required fields, defaults, enums, and descriptions
Output that targets the MCP JSON Schema subset, not the entire JSON Schema specification

How it works

Add a dependency to us.fatehi:mcp-json-schema in Maven or Gradle.

<dependency>
    <groupId>us.fatehi</groupId>
    <artifactId>mcp-json-schema</artifactId>
    <version>1.0.1</version>
</dependency>

Define a parameters type as a Jackson‑annotated record or class and let the library produce the inputSchema JSON. Use annotations to describe intent, and let the library translate that into the MCP schema format.

For example:

import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.annotation.JsonPropertyDescription;
import tools.jackson.databind.PropertyNamingStrategies;
import tools.jackson.databind.annotation.JsonNaming;

@JsonNaming(PropertyNamingStrategies.KebabCaseStrategy.class)
public record SampleParameters(
    @JsonPropertyDescription("Type of database table dependant objects.")
    @JsonProperty(defaultValue = "NONE", required = true)
    DependantObjectType dependantObjectType,

    @JsonPropertyDescription("Table name.")
    String tableName) {

  public enum DependantObjectType 
    { NONE, COLUMNS, INDEXES, FOREIGN_KEYS, TRIGGERS }
}

Next, generate the MCP inputSchema:

import us.fatehi.mcp_json_schema.McpJsonSchemaUtility;

// Provide this value as the tool's input_schema 
// in your Spring AI MCP server implementation
String inputSchemaJson = 
  McpJsonSchemaUtility.inputSchema(SampleParameters.class);

Prefer a JsonNode for programmatic changes? Use:

var schemaNode = 
  McpJsonSchemaUtility.generateJsonSchema(SampleParameters.class);

The source code is available at sualeh/mcp-json-schema

Why Your Claude Skills Deserve Better: Escape the Sandbox with MCP Skill Hub

Sualeh Fatehi — Mon, 27 Oct 2025 12:21:07 +0000

If you've been working with Claude skills, you've probably felt the frustration of hitting sandbox limitations. Your Python code can't access files, make network requests, or interact with your local system. That's where the MCP Skill Hub comes in – it takes your existing Claude skills and unleashes their full potential locally.

The Claude Skills Sandbox Problem

Claude skills are great for quick demonstrations, but they're severely limited:

❌ No file-system access
❌ No network requests
❌ No system commands
❌ No persistent storage
❌ No real-world integrations

Your skills can show examples and explain concepts, but they can't actually do the work.

Skills That Actually Work

The MCP Skill Hub changes everything. It takes your existing Claude Skills (same YAML frontmatter format, same Markdown content) and runs them locally through the Model Context Protocol.

Check out the examples in srprasanna/mcp-skill-hub for working code.

What Changes When You Go Local

Before (Claude Sandbox):

"Here's how you would read an Excel file..."
"This code demonstrates the concept..."
"In a real environment, you could do this..."

After (MCP Local):

Your skill actually reads files from your computer
Real database connections, API calls, file operations
Integration with your actual development workflow

Beyond Claude: Any Agent, Any Model

Here's the kicker – you're not locked into Claude anymore. The MCP Skill Hub works with:

Claude Desktop (obvious choice)
Cline/Cursor (VS Code integration)
Open WebUI (local models)
Any MCP-compatible agent

Your skills become portable across the entire AI ecosystem.

Getting Started is Dead Simple

The server enforces a clean folder structure that makes sense:

~/my-skills/
├── excel-automation/
│   ├── SKILL.md          ← Your existing skill content
│   └── examples/         ← Working Python scripts
├── database-queries/
│   ├── SKILL.md
│   └── examples/
└── file-processing/
    ├── SKILL.md
    └── templates/

Run it with Docker:

docker run -i --rm \
  -v ~/my-skills:/skills:ro \
  srprasanna/mcp-skill-hub

Hot-reload is built-in – edit your skills and see changes instantly without restarting.

Production Ready Features

🔄 Hot-reload – edit skills without restarting
🐳 Docker support – run anywhere containers run
📊 Rich metadata – categories, tags, complexity levels
🔍 Search tools – find skills by query or category
📝 Full documentation – comprehensive guides and examples
✅ Type-safe – modern Python 3.13+ with full type hints

The Bottom Line

Your Claude skills are just the beginning. The MCP Skill Hub takes that same familiar format and removes all the limitations. Your skills can finally do real work – read files, make API calls, automate your actual workflow.

And since it's MCP-compliant, you can use those skills with any compatible AI agent, not just Claude.

Ready to escape the sandbox? Check out the MCP Skill Hub on GitHub or install directly from the MCP Registry.

Your skills deserve to run free. 🚀

The MCP Skill Hub is open source (MIT license) and available on Docker Hub. It's production-ready with comprehensive testing and documentation.

Revolutionize Your Database Development with SchemaCrawler MCP Server

Sualeh Fatehi — Sat, 24 May 2025 22:21:28 +0000

Imagine having an AI assistant that actually understands your database schema and helps you make sense of your tables and columns, helps you craft perfect SQL queries that actually work, and saves you from hours of documentation diving. The SchemaCrawler MCP Server is here, and it it free and open source.

Forget complex installations and configuration headaches. The SchemaCrawler MCP Server runs in a Docker container, meaning you can get up and running with just a few commands, using your favorite MCP Client in "Agent" mode.

What Can It Do For You?

🔍 Explore Your Database Structure

View all tables and views at a glance
Examine column details including data types and constraints
Understand relationships between tables with foreign key information

🛠️ Improve Your Database Design

Find design issues with the built-in schema linting
Discover missing indexes that could improve performance
Identify nullable columns in unique constraints

📝 Simplify SQL Development

Understand table schemas before writing queries
See sample data to better understand the information you're working with
Generate proper SQL based on your database structure

Getting Started in 4 Easy Steps

Clone https://github.com/schemacrawler/SchemaCrawler-MCP-Client-Usage
Start the Server

   docker-compose -f schemacrawler-mcpserver.yaml up -d

Verify It's Running
Check server health at http://localhost:8080/health
Connect in VS Code
The server is already configured in .vscode/mcp.json - just open VS Code and start asking questions!

Connect to Your Own Database

Want to use this with your own database? No problem!

Stop the current server

   docker-compose -f schemacrawler-mcpserver.yaml down -t0

Edit the connection details
Update schemacrawler-mcpserver.yaml with your database connection information
Restart the server

   docker-compose -f schemacrawler-mcpserver.yaml up -d

Start Exploring Today!

Simply ask questions about your database in VS Code's chat panel:

"What tables are available in my database?"
"Show me the columns in the Books table"
"What foreign keys reference the Authors table?"
"Are there any design issues with my database schema?"
"Write SQL to find books and their authors"

Calculate the DORA Lead Time Metric in Python

Sualeh Fatehi — Thu, 10 Apr 2025 23:03:47 +0000

DORA (DevOps Research and Assessment) metrics have become the gold standard for measuring software delivery performance. Among these metrics, Lead Time for Changes is a good indicator of your team's efficiency to deliver changes in production. Let us understand what this metric is, why it matters, and how you can calculate it using Jira and GitHub data with Python code.

What is the DORA Lead Time Metric?

Lead Time for Changes measures the duration from when code is first committed until it is successfully deployed to production. In simpler terms, it answers the question: "How long does it take for a code change to go from a developer's machine to serving users in production?"

According to DORA research, organizations typically fall into these performance categories:

Elite performers: Less than one hour
High performers: Between one day and one week
Medium performers: Between one week and one month
Low performers: Between one month and six months

A shorter lead time indicates:

Faster delivery of features to users
Quicker bug fixes and security patches
More agile response to changing requirements
Less work-in-progress building up
Reduced context switching for developers

So, lead time is a powerful indicator of your development process efficiency. Long lead times often signal bottlenecks in your development pipeline that need addressing.

Calculating Lead Time

If you use Jira and GitHub, you can calculate lead time by connecting data from both platforms. The calculation involves several steps:

Projects → Releases → Stories → Pull Requests → Commits

Projects: First, gather all software projects from Jira
Releases: For each project, collect released versions within a specified date range
Stories: Identify all Jira stories associated with each release
Pull Requests: For each story, find the linked GitHub pull requests
Commits: Within each pull request, analyze all commits

For each pull request, lead time is calculated as:

lead_time = release_date - earliest_commit_date + 1

The key here is using the earliest commit date rather than the pull request creation date. This captures the true beginning of work, even if the pull request was created later. The final DORA lead time metric is calculated by averaging all individual lead times over a specified time period, for a given set of projects.

Manage This in Jira and GitHub

To effectively use this approach, you need to understand how to manage the key components in Jira. Create Jira releases (or "versions") for each planned release, and set release dates when versions are published. Mark versions as "Released" once deployed. Assign the release to a project. In this context projects typically represent teams, products, or components. Stories are work items (features, bugs, etc.) included in releases. Use the Jira GitHub integration to connect your repositories. Reference Jira issues in pull request titles or descriptions** (e.g., "PROJ-123: Add new feature"). Use smart commits in your commit messages.

Using Python to Generate lead Time Reports

The dora-lead-time package provides a simple way to calculate and visualize lead time metrics. It connects to your Jira and GitHub data, calculates lead times, and generates reports. Here is how you might use the package to generate a monthly lead time report:

First set up your tokens to access Jira and GitHub. Set the following environmental variables.

Create ATLASSIAN_TOKEN containing the API token for Atlassian Jira access.
Create JIRA_INSTANCE for your Jira instance URL (e.g., company.atlassian.net)
Create EMAIL for your Atlassian account email address.
Create personal access tokens for each GitHub organization, for example, GITHUB_TOKEN_ORG1, GITHUB_TOKEN_ORG2, etc. to authenticate API requests to specific GitHub organizations. Each organization you need to access requires its own token.
Create an environmental variable GITHUB_ORG_TOKENS_MAP which is a JSON string mapping organization names to environment variable names. For example: GITHUB_ORG_TOKENS_MAP={"Org1": "GITHUB_TOKEN_ORG1", "Org2": "GITHUB_TOKEN_ORG2"}

You can optionally set

SQLITE_PATH which is the path where the SQLite database will be created.
START_DATE and END_DATE to define the date range for which to calculate lead time metrics, using ISO date strings (YYYY-MM-DD).

Here is a complete examples which you can put in a ".env" file:

GITHUB_TOKEN_ORG1=your_personal_access_token_for_org1
GITHUB_TOKEN_ORG2=your_personal_access_token_for_org2
GITHUB_ORG_TOKENS_MAP={"Org1": "GITHUB_TOKEN_ORG1", "Org2": "GITHUB_TOKEN_ORG2"}
ATLASSIAN_TOKEN=your_atlassian_api_token
JIRA_INSTANCE=your_company.atlassian.net
EMAIL=your_email@your_company.com
SQLITE_PATH=./releases.db
START_DATE=2023-01-01
END_DATE=2023-12-31

Then run code similar to the following to generate a lead time report:

from datetime import date
from dora_lead_time.lead_time_report import LeadTimeReport

# Initialize the report generator
report = LeadTimeReport("releases.db")

# Define the scope
project_keys = ["FRONTEND", "BACKEND", "MOBILE"]
start_date = date(2025, 1, 1)
end_date = date(2025, 12, 31)

# Generate a monthly report
monthly_data = report.monthly_lead_time_report(project_keys, start_date, end_date)

# Display and visualize the report
print(monthly_data)

# Create a visualization
plt = report.show_plot(monthly_data, title="2025 Monthly Lead Time", show_trend=True)
plt.savefig('lead_time_trend.png')

The full code is available on GitHub.

The report allows you to:

Track lead time trends over months
Compare performance across different projects
Identify when process changes impact lead time
Set targets based on DORA performance levels

Additionally, the project includes SQL-based outlier reports to identify issues like:

Projects without releases
Releases with open stories
Stories in multiple releases
Stories without pull requests

Conclusion

Calculating the DORA Lead Time metric provides valuable insights into your software delivery performance. By connecting data from Jira and GitHub, this approach gives you an accurate measurement that truly reflects your development process. The real power comes from using this data to identify bottlenecks and continuously improve your delivery pipeline. Whether you're aiming to move from "medium" to "high" performer status or already pursuing "elite" performance, measuring lead time is an essential step in the journey.

Use ChatGPT to Explore Your Database Schema

Sualeh Fatehi — Mon, 30 Dec 2024 20:27:31 +0000

SchemaCrawler is a relational database exploration tool. It obtains database schema metadata such as tables, stored procedures, foreign keys, triggers and so on, and makes them available for search. The traditional way to use SchemaCrawler has been the command-line or an interactive shell.

ChatGPT offers almost magical help with coding questions. You could use it to get help with SQL questions, but then you have to adapt that SQL to match your database tables and schemas.

What if ChatGPT could act as an expert on your database, and give you valid SQL that would work for you? Guess what - with a little help from SchemaCrawler, it can. You can use SchemaCrawler to "teach" ChatGPT what your database schema looks like.

SchemaCrawler is now integrated with ChatGPT models to provide an interactive way to interrogate your database schema metadata. When you start SchemaCrawler with the "aichat" command, you will have an interactive chat shell with ChatGPT, enhanced with information about your database metadata.

You can try prompts such as the following ones:

"List all tables"
"Describe the Track table"
"What are the indexes on the Track table?"
"What are the Track columns?"
"What is the Track primary key?"
"Show me the triggers on Track"
"Find the parents of Track"
"What are the dependents of Album?"
"What are the design problems with this database?"

To quit the console, you can type something like:

"I think I have everything I need" or simply, "done", "exit" or "quit".

To start using this integration, you will need to create your own OpenAI API key. Then download a SQLite database called "chinook-database-2.0.1.sqlite" into your current directory.

Run this command in a bash shell:

docker run \
  -v "${PWD}":/home/schcrwlr \
  --rm -it \
  -e OPENAI_API_KEY=<<your-openai-api-key>> \
schemacrawler/schemacrawler:extra-latest \
  /opt/schemacrawler/bin/schemacrawler.sh \
  --server=sqlite \
  --database=chinook-database-2.0.1.sqlite \
  --info-level=standard \
  --command=aichat

(If you are using PowerShell on Windows, replace the trailing backslash on each line with a back-tick, and map the current directory differently.)

Once the Docker container starts up, enter some of the prompts above.

If you are willing to share your database metadata with OpenAI, the creators of ChatGPT, you can provide an additional --use-metadata true on the commandline, and then you can get SQL statements customized to your database.

You can then try prompts such as the following ones:

"Get me the SQL statement to find all the tracks and their artists' names"
"Get me the SQL statement to find the number of tracks for each artist, for artists that have more than 25 tracks, sorted by those who have the most"
"What is the purpose or function of this database?"

After you have got this working, use the SchemaCrawler command-line to connect to your own database, and explore it using a natural language interface courtesy of ChatGPT.

Sufficient Software Tests Using Metrics

Sualeh Fatehi — Sun, 01 Dec 2024 18:47:15 +0000

The primary goal of software testing is to prevent bugs and defects from reaching end users. Effective testing ensures that the software is reliable, functional, and meets the specified requirements, enhancing user satisfaction. However, simply knowing how much of the code is executed (or covered) by tests is not enough. Testing metrics provide insight into how effective tests are, and whether the software system is tested sufficiently. The code coverage metric measures the percentage of code executed during testing but does not account for the quality of those tests. We need to have a combination of different metrics to evaluate various aspects of test sufficiency, such as defect detection, test case effectiveness, and automation coverage. By using a range of metrics, teams can gain a holistic view of their testing efforts, ensuring that the software is thoroughly tested and capable of handling real-world scenarios without failures.

Unit testing is a method where individual components of software are tested in isolation, forms the foundation of software quality assurance. Code must be instrumented to measure the extent of code execution during testing. This involves inserting additional code and using tools to collect execution data, helping developers detect untested areas, edge cases, and potential bugs. The code coverage metric for unit tests quantifies the proportion of tested source code. High code coverage suggests fewer untested paths and gives greater confidence in the software's reliability.

The testing pyramid is a conceptual framework that illustrates the different levels of testing in software development. At the base of the pyramid are unit tests, which are numerous and run frequently. As we move up the pyramid, the tests become broader and fewer in number. Integration tests focus on the interactions between different units or modules of the software, ensuring that combined parts function together as expected. At the system test level, the entire system is tested as a whole to verify that it meets the specified requirements. Acceptance tests, which are the highest level of tests, are often conducted by end-users or clients to validate the software against their expectations and business requirements. This hierarchical approach helps ensure that testing is comprehensive and efficient, covering both individual components and the integrated system.

Higher levels of testing, such as system and acceptance testing, are typically performed on built or containerized software. This means the entire application or significant parts of it are deployed in a test environment that closely mirrors the production environment. These tests validate the behavior of the application in real-world scenarios, ensuring that all components work together seamlessly. Unlike unit tests, achieving code coverage at the system or acceptance test levels is not feasible. This is because these tests operate on the application as a whole, rather than its individual components. They focus on the end-to-end functionality and user experience, rather than the internal workings of the code. It is not possible, nor is it a good idea, to instrument code in software built (or containerized) for production deployments. Thus, other metrics are needed to ensure comprehensive testing at these higher levels.

Here are some metrics to consider for gaining insight into test sufficiency:

Defect Density: This measures the number of defects found per unit of code or functionality. It helps identify areas that may need more thorough testing, guiding focus towards problematic areas.
Test Case Effectiveness: This evaluates how well test cases detect defects. It's measured by the number of defects found versus the number of test cases executed. High test case effectiveness indicates robust and thorough testing scenarios.
Test Automation Coverage: This metric assesses the percentage of test cases that are automated versus manual. Higher automation coverage leads to more efficient and consistent testing, reducing the risk of human error and increasing test repeatability.

By leveraging these metrics, teams can ensure that their testing efforts are comprehensive, efficient, and effective at all levels of the testing pyramid. Metrics not only help in assessing current testing effectiveness but aid in the iterative and continuous improvement of test coverage.

"Computer Use" for UAT

Sualeh Fatehi — Mon, 28 Oct 2024 01:14:53 +0000

Anthropic, a company working on advanced artificial intelligence (AI), has recently introduced a new feature for their AI model called Claude 3.5 Sonnet. This feature, called "computer use," allows the AI to interact with computer interfaces just like a human would. Using a programming interface (API), Claude can control your computer and

Move the cursor around the screen
Click buttons and icons
Type text using a virtual keyboard
Open and use different software programs

This is still an experimental feature and not perfect yet, but it's designed to help automate tasks that usually require human interaction, like filling out forms or navigating through software.

This opens an interesting possibility in user acceptance testing. User Acceptance Testing (UAT) is the final phase in the software development process to test the software in real-world scenarios to ensure it meets their needs and works as expected. User Acceptance Tests (UATs) are typically written collaboratively by Business Analysts, QA Engineers and business stakeholders. A standard human readable format for UAT popularized by Dan North, and in the Gherkin format, includes:

Scenario: Specific situation or use cases to be tested
Given: The initial context or state
When: The action or event
Then: The expected outcome or result

We might potentially see some tools that can take human readable test definitions written in Gherkin-like language and test them out on a user interface developed by a software team before software is released.

"Computer Use" to Speed Up UI Development

Sualeh Fatehi — Mon, 28 Oct 2024 01:06:13 +0000

Move the cursor around the screen
Click buttons and icons
Type text using a virtual keyboard
Open and use different software programs

This is still an experimental feature and not perfect yet, but it's designed to help automate tasks that usually require human interaction, like filling out forms or navigating through software.

This opens an interesting possibility in user interface (UI) design. UI design with focus groups involves gathering a small group of users (usually 5-10 people) to provide feedback on a product or design. Typically, a moderator prepares a discussion guide with questions and activities to guide the session. The session is recorded, and the feedback is analyzed to identify common themes and insights. Focus groups typically get hands-on access to the UI. This way, they can interact with the interface, explore its features, and provide direct feedback on their experience. Their reactions and suggestions help designers see what works, what doesn't, and what needs tweaking. It's all about making sure the final product is user-friendly and meets the needs of its audience.

Typically, companies run multiple sessions to ensure a diverse range of feedback. It's common to see anywhere from 3 to 10 focus groups, each with different participants, to gather comprehensive insights. The more varied the feedback, the better the final design can be tailored to meet user needs.

The recent developments with "computer use" have the potential to make UI development much faster, and reduce the cost by reducing number of focus groups that need to be brought in. UI designers can give AI simple instructions and find out quickly if the user interface is intuitive enough for the average user (represented by an AI system) to use. If it is not, they can improve the UI iteratively before bringing it to a human focus group.

Intercepted System.exit(...) on Java 21

Sualeh Fatehi — Sun, 25 Aug 2024 19:47:39 +0000

In Java 8, it is possible (easy enough) to substitute a custom security manager which can capture system exit calls and allow code to continue executing. Java 21 makes this more difficult. However, this can cause a problem for systems that are being upgraded from Java 8 to Java 21, since processes can sometimes fail without any exceptions or logs. This is usually because libraries that used to substitute a custom security manager no longer do that with Java 21. Tracing the source of the error can be difficult.

Consider this use case:

If "Library 1" starts supporting Java 21, and so does not substitute a custom security manager, it can cause the "Web Application" to terminate without warning.

For example, look at this note from Apache Ant which says a custom security manager will be substituted if running on versions of Java lower than Java 18, but not on Java 18 and above. Other libraries are taking similar approaches.

I have boiled this down to the essentials. Take a look at how this could happen: sualeh/system-exit-21.