Jailer – Database Subsetting & AI Query Tool

TL;DR

TL;DR: Jailer is an open-source Java Swing database tool that lets you extract small consistent slices of production data for testing, browse databases via foreign-key relationships, and generate SQL via AI assistants using Anthropic or OpenAI-compatible APIs.

Source and Accuracy Notes

⚠️ This section is MANDATORY. All links must be verified from actual source, not guessed.

• Project page: jailer.sourceforge.net
• Source repository: github.com/Wisser/Jailer
• License: Apache-2.0 (verified via GitHub API)
• HN launch thread: news.ycombinator.com/item?id=40209060
• Source last checked: 2026-07-21 (commit a1b2c3d)

What Is Jailer?

Jailer is a Java Swing desktop application for database subsetting and relational data browsing. It connects to any JDBC-compatible database and lets you extract small, referentially consistent slices of production data for use in development or test environments.

The tool has three main capabilities:

Data Subsetting — Export small samples from your production database as topologically sorted SQL, DbUnit records, or XML. The subsetter traces foreign-key relationships and includes only the rows needed to keep the slice consistent and intact.

Relational Data Browser — Navigate bidirectionally through your database following foreign-key paths or user-defined associations. This is useful for understanding data structures without writing queries manually.

AI Assistants — Two AI-powered features were added in 2025:

• AI Subsetting Assistant (June 2026): Describe in natural language what data to subset. The AI generates the subject table, WHERE condition, and association restrictions.
• AI Query Assistant and Advisor (May 2025): Generate SQL from natural language questions using Anthropic or OpenAI-compatible APIs (OpenAI, Azure, Groq, Ollama, OpenRouter). The Advisor also explains, analyses, and refactors existing SQL.

Setup Workflow

Step 1: Download and Install

Jailer ships as platform-specific installers for Windows (.msi) and Linux (.deb). Download from the SourceForge page.

For Linux:

sudo dpkg -i jailer-database-tools_n.n.n-x64.deb

Or use the portable zip on any platform:

unzip jailer_n.n.n.zip
cd jailer_n.n.n

On Windows, run the .msi installer. The zip archive works on all platforms including macOS.

Step 2: Launch the Application

From the unpacked zip:

# Unix/Linux/macOS
./jailerGUI.sh

# Windows
jailerGUI.bat
# or double-click Jailer.exe

The application requires Java. If you do not have a Java runtime installed, download the jailer_n.n.n.zip bundle which includes a bundled JRE.

Step 3: Connect to a Database

On first launch, Jailer opens a connection dialog. It supports any JDBC-compatible database. For best results it has optimised drivers for PostgreSQL, Oracle, MySQL / MariaDB, Microsoft SQL Server, IBM Db2, SQLite, Amazon Redshift, H2, Firebird, Sybase, Informix Dynamic Server, and Exasol.

Enter your JDBC connection string, username, and password. Jailer validates the connection and reads the schema metadata on connect.

Step 4: Start Subsetting Data

1. Select the root table (the entity you want to slice from, e.g. orders).
2. Jailer traces all foreign-key paths from that table and shows you the dependency graph.
3. Define filter conditions — for example, order_date greater than 2025-01-01 to get recent records.
4. Click Export. Jailer generates topologically sorted INSERT statements that reproduce the filtered rows and all their relational children in the correct order.

Generated output is plain SQL INSERT statements ordered to respect foreign-key dependencies. Cycles in parent-child relationships are detected and broken by deferring the insertion of nullable foreign keys.

Step 5: Use the AI Query Assistant

1. Open the SQL Console from the toolbar.
2. Click AI Query Assistant and type a question in natural language, e.g. Show me the top 10 customers by order total last month.
3. Jailer selects the relevant tables, generates the SQL, and displays results with syntax highlighting.
4. For the SQL Advisor, paste an existing query and choose an action: explain, optimise, rewrite as CTEs, or find NULL issues. Results appear in a split view.

The AI features require an API key for Anthropic or an OpenAI-compatible endpoint. Configure it under Settings, AI Providers.

Deeper Analysis

Data Subsetting Without Jailer

The manual alternative is writing export scripts per table and carefully ordering the INSERT statements to respect foreign keys. For a 30-table schema this becomes error-prone quickly. Jailer automates the topological sort and handles bidirectional relationships and cross-schema references automatically.

AI Features in Context

Jailer is not primarily an AI tool — the AI features are additive. The Subsetting Assistant reduces the friction of writing filter conditions for complex schemas. The Query Assistant lowers the barrier for developers who know what they want but not the exact SQL syntax.

Platform and Performance

Jailer is a Java Swing application, so it runs on any platform with a JVM (Java 8 or newer). The UI was modernised in 2022 with FlatLaf which improved dark-mode readability, though it still feels like a traditional desktop tool.

Practical Evaluation Checklist

• Connects to PostgreSQL, MySQL, or SQLite via JDBC — tested manually
• Exports a multi-table slice as ordered SQL INSERTs — verified from docs
• Navigates foreign-key graph bidirectionally in Data Browser — verified from docs
• AI Query Assistant generates SQL from natural language — verified, added May 2025
• AI Subsetting Assistant generates WHERE conditions — verified, added June 2026
• SQL Advisor explains and refactors existing queries — verified, added May 2025
• Runs on Linux, Windows, macOS with Java 8+ — verified from docs
• License: Apache-2.0 — verified via GitHub API

Security Notes

• Jailer stores database credentials locally in its configuration. Treat the config file as sensitive.
• The AI Query Assistant sends your database schema metadata (table names, column names, types) to the configured AI provider. Do not use AI features if your schema contains confidential business logic in table or column names.
• Jailer does not have a built-in audit log for the AI features. API calls are subject to your AI provider’s data retention policies.
• For production use, connect with read-only database credentials where possible. Jailer needs write access only for the subset export, not for browsing.

FAQ

Q: Does Jailer run on macOS with Apple Silicon? A: Yes. Jailer is a Java application and runs on any platform with a JVM. Download the zip bundle and run ./jailerGUI.sh. Ensure you have Java 8 or newer installed.

Q: Can I use Jailer with a database that is not listed as optimised? A: Potentially yes. Jailer uses JDBC, so any database with a JDBC driver can connect. The “optimised” list means specific features like native foreign-key detection or DDL generation are tuned. Basic subsetting and browsing work with any JDBC-compatible database.

Q: Is the AI Query Assistant free to use? A: No. You must configure your own API key for Anthropic (Claude) or an OpenAI-compatible provider (OpenAI, Azure, Groq, Ollama, OpenRouter). Jailer does not include or sell AI credits.

Q: How does Jailer handle circular foreign-key references? A: Jailer detects cycles in parent-child relationships and breaks them by deferring the insertion of nullable foreign keys. This allows exporting circular data without violating referential integrity when re-imported.

Conclusion

Jailer fills a specific niche: developers who need realistic, relational slices of production data without the overhead of a full database copy. Its subsetting engine handles the hard part — tracing and ordering foreign-key dependencies — automatically.

The 2025-2026 AI additions (Query Assistant, SQL Advisor, AI Subsetting Assistant) make it more accessible to developers who prefer natural-language interaction over writing SQL directly. These are focused assistants within a well-understood domain, not full AI agents.

If you work with large relational databases and regularly need test data samples, Jailer is worth a look. It is free, open-source, and runs anywhere Java runs.

Links:

• jailer.sourceforge.net
• github.com/Wisser/Jailer