Design and Manage CrateDB Databases Visually with DbSchema

DbSchema lets you design, manage, and document CrateDB databases. Create ER diagrams, define tables and columns, and generate SQL scripts - with or without a live database connection.

Use Git to share the design, compare it with the CrateDB database, and deploy changes. DbSchema also includes a data editor, query builder, and HTML5 documentation - everything you need in one tool.

DbSchema Database Designer

Download DbSchema Download CrateDB JDBC Driver

Distributed Sharding Model and Schema Visualization

CrateDB is a distributed SQL database engineered for machine data and IoT workloads, offering PostgreSQL-compatible syntax alongside columnar storage and horizontal scaling through shards and replicas. Each table in CrateDB is physically split into a configurable number of shards distributed across cluster nodes, and each shard can be replicated for fault tolerance. DbSchema connects to CrateDB using its PostgreSQL-compatible wire protocol and the CrateDB JDBC driver, introspecting tables, columns, data types, and partition configurations. The resulting schema diagram captures the full table structure including ARRAY and OBJECT column types, which are particularly common in machine-data schemas where nested metadata accompanies sensor readings.

Writing SQL for IoT and Machine Data Analytics

CrateDB's SQL dialect includes full-text search predicates, array subscript expressions, and object-column path access that go beyond standard relational SQL. DbSchema's SQL editor understands the PostgreSQL-compatible connection and provides column auto-completion, syntax highlighting, and a results grid that handles CrateDB's nested OBJECT values by flattening them into readable columns. Queries involving time-bucket aggregations over large event tables benefit from CrateDB's columnar storage engine, and the SQL editor displays execution time so you can evaluate the impact of shard count and routing columns on query performance.

Writing CrateDB SQL for IoT analytics in the DbSchema SQL editor

Exploring Large-Scale Time-Series and Event Data

The DbSchema data explorer lets you navigate CrateDB tables row by row with filter and sort controls. For time-series event tables with hundreds of millions of rows, the explorer applies LIMIT and offset-based pagination automatically, ensuring that browsing remains responsive regardless of table size. You can filter by timestamp ranges or specific device identifiers, inspect ARRAY column contents inline, and copy rows to the clipboard for further analysis. The explorer also surfaces CrateDB's blob tables for object storage scenarios, letting you see blob metadata alongside relational data in the same session.

Exploring CrateDB time-series event data with DbSchema data explorer

Connection Setup and JDBC URL

To connect DbSchema to CrateDB, download the CrateDB JDBC driver JAR (io.crate.client.jdbc.CrateDriver) from the CrateDB releases page and register it in DbSchema's driver manager. The JDBC URL format is jdbc:crate://localhost:5432/, where port 5432 is CrateDB's default PostgreSQL-compatible port. Provide the CrateDB username and password in the connection dialog; newly installed clusters default to the crate superuser with no password. For production deployments, replace localhost with the hostname of a CrateDB node and enable TLS by appending SSL parameters to the JDBC URL. CrateDB clusters expose a single SQL endpoint regardless of the number of nodes, so no special load-balancer configuration is needed for DbSchema.

CrateDB schema documentation generated by DbSchema for a distributed IoT cluster

Why Teams Use DbSchema with CrateDB

  • Visualize CrateDB table schemas including complex ARRAY and OBJECT column types that are otherwise hard to document.
  • Write and test IoT analytics SQL with time-bucket aggregations and full-text predicates in DbSchema's SQL editor.
  • Browse large event tables row by row in the data explorer without writing paginated queries manually.
  • Generate schema documentation for CrateDB tables to share with data engineers and application developers onboarding to the platform.
  • Design new CrateDB table schemas with shard and partition annotations in DbSchema's offline model before deploying to the cluster.
  • Connect to CrateDB using the same familiar interface you use for PostgreSQL, taking advantage of protocol compatibility.