DEV Community: VisuaLeaf

Best Database Design Tools for 2027: Windows, macOS & Linux

VisuaLeaf — Mon, 26 Oct 2026 13:15:24 +0000

Most database trouble starts with a schema nobody ever drew. A handful of tables turn into fifty, someone adds a foreign key late on a Friday, and a few months later nobody can explain how orders connect to customers without reading raw DDL.

A visual design tool fixes that. You see the tables, you see the relationships, and you can show them to a teammate who wasn’t in the room. The catch is that “database tool” covers a lot of ground. Some let you design a schema before the database exists. Others are SQL clients that draw a diagram once you connect to a live server.

This article compares eight popular tools, focusing on schema design and documentation. For each one, you get what it does well, where it falls short, how many databases it connects to, what it costs, and how painful the free trial is to start.

*Short on time?* Jump to the strengths and weaknesses overview at the end for a quick side-by-side of all eight tools.

What I looked at

Can you design a schema visually, or only view one that already exists?
Can it reverse engineer a live database into a diagram?
Can it generate SQL from the diagram (forward engineering)?
Does it help with schema comparison, documentation export, or team workflows?
How many databases can it connect to?
What is free, what is paid, and how easy is it to start a trial?

The eight tools side by side

Tool	Built around	Runs on	Databases	Price	Free trial
VisuaLeaf	One GUI for MongoDB and SQL	Windows, macOS, Linux	37+	Free Community Edition. Basic from $10/month or $79. Professional from $16/month or $149	14 days, email only; can be extended
DataGrip	SQL editing	Windows, macOS, Linux	50+	Free for non-commercial use. From $10.90/month or $109/year. Organizations from $259/user/year	30 days, starts straight away
DbSchema	An offline model file	Windows, macOS, Linux	100+	Free Community. Pro from $196 personal / $294 business. Architect from $313.60 personal / $470.40 business	15 days of Architect, one click; can be extended
DBeaver	Live database work	Windows, macOS, Linux	100+	Free Community. Lite $113/year, Enterprise $255/year, Ultimate $510/year	14 days; request and import a licence key
Navicat	Live database work	Windows, macOS, Linux	9	Free Premium Lite. Premium perpetual licence from 75$/month or $1,499 perpetual; subscriptions also offered	14 days, starts automatically
DbVisualizer	Live database work	Windows, macOS, Linux	40+ plus JDBC	Free edition. Pro $199 the first year, then $89/year	21 days, no account or card needed
TablePlus	A fast native client	macOS, Windows, Linux	22	Free edition with limits. Basic $99 one-time, Standard $129 one-time	Free forever, no signup needed
Beekeeper Studio	A clean SQL client	Windows, macOS, Linux	24	Free Community. Paid from $18/month or $168 billed yearly	14 days, no email or card

Prices are USD list prices before tax, checked in October 2026. Where a vendor splits business and personal plans, the business price is shown. Plans change often, so confirm on each vendor’s site before you buy.

How I Tested the 8 GUIs

All screenshots come from the same MySQL database, so the diagrams are comparable. For this comparison, I connected every tool to the same MySQL 8.4.11 database: super_bowl_ticketing, with 19 tables and 21 foreign-key relationships.

1. VisuaLeaf

*Platform:* Windows, macOS, Linux
*Databases:* MySQL, MongoDB, PostgreSQL, and 34 more
*Price:* free Community Edition. Basic from $10/month or $79 one-time; Professional $149 one-time (each includes 1 year of updates)
*Free trial:* 14 days of Pro features. Add your email in the app or on the dashboard and the trial starts, with the option to extend it

VisuaLeaf treats MongoDB and relational databases as equals, which is still rare. For schema work, you can create a diagram from scratch or reverse engineer an existing database into one, then stay in the same app for the rest of the job.

*Key features*

*Schema diagrams:* create one from scratch or reverse engineer an existing database. ER diagrams for SQL databases are in the free Community Edition
*SQL editor:* with inline editing and a visual query builder
*Index manager:* create, change, and drop indexes
*Query profiler:* find and diagnose slow queries (from Basic)
*Data compare and sync:* across MongoDB and SQL databases (Professional)
*Data masking:* hide sensitive fields in exports (from Basic)
*Task automation:* import, export, and migration tasks, including scheduled transfers
*Charts and dashboards:* built from your queries (Professional)
*AI Assistant* in the Basic plan or MCP in the Pro one.
*Database Documentation:* interactive HTML5, PDF, JSON, DDL (Pro)

VisuaLeaf visual schema designer showing relational MySQL tables alongside the expanded structure of a JSON column.

VisuaLeaf’s visual query builder and chart workspace: build a joined MySQL query on the left, or turn an aggregated SQL result into a payment chart on the right.

*What I like*

One app for MongoDB and 36 other databases, which helps if your stack mixes both
Diagrams you can create and reverse engineer, free for SQL databases
Compare, sync, and masking sit next to the diagram, so you don’t need a second tool
JSON fields can be expanded inside the diagram, which makes mixed relational and nested data easier to understand

*Disadvantages*

It’s a newer product, so tutorials and community answers are thinner than for DBeaver or DataGrip
The heavier features, such as data compare and sync or dashboards, need the Professional plan

2. DataGrip

*Platform:* Windows, macOS, Linux
*Databases:* MySQL, MongoDB, PostgreSQL, and dozens more (JetBrains doesn’t publish an exact number)
*Price:* from $10.90/month or $109/year for individuals, $259/user/year for organizations. Free for non-commercial use
*Free trial:* 30 days, starts directly in the app

DataGrip is JetBrains’ database IDE, and it’s the pick if you spend your day writing and refactoring SQL. The free license covers learning, hobby projects, open-source work, and content creation.

*Key features*

*ER diagrams:* generated automatically, showing tables, relationships, and foreign keys for a connected database
*Query console:* run queries in different modes, with local history so you don’t lose work
*Code completion:* aware of table structure, foreign keys, and the objects in the code you’re editing
*On-the-fly analysis:* flags probable bugs and suggests quick fixes
*Import and export:* CSV, JSON, HTML, Markdown, Excel, and more
*Version control integration* and query history
*AI assistance:* schema-aware help with queries, errors, and SQL dialect conversion
*Cloud discovery:* finds databases in your AWS, Google Cloud, or Azure account

DataGrip database diagram generated from the live super_bowl_ticketing MySQL schema.

DataGrip generates DDL for existing MySQL tables and compares schema changes before creating a migration script.

*What I like*

Smart completion, refactoring, and inspections for SQL
ER diagrams without leaving the IDE
The free non-commercial license is a real option for students and side projects

*Disadvantages*

Commercial work needs a paid subscription
It’s built for working with existing databases, not for designing new ones from a blank page

3. DbSchema

*Platform:* Windows, macOS, Linux
*Databases:* MySQL, MongoDB, PostgreSQL, and 70+ more
*Price:* free Community Edition. Pro $294 one-time ($196 for personal use), Architect $470.40 one-time. Both include the first year of updates, then $75/year (Pro) or $120/year (Architect) to keep getting new versions. Monthly plans are also available
*Free trial:* 15 days of the Architect edition, one click, no card.

DbSchema is a good design-first tool from the group. The diagram is the model, and you can save it to a file and work on it without a database connection. Community is free forever and covers connecting, reverse engineering, interactive diagrams, creating tables and columns, and a SQL editor. Pro and Architect add the design workflow on top.

*Key features*

*Interactive diagrams:* reverse engineer a live database, or create and edit tables and columns visually. Free in Community
*SQL editor:* with autocompletion, free in Community
*Offline model file:* save the design to a file and work without a connection (Pro). The file works well with Git
*Schema synchronization:* compare, diff, and generate migration scripts (Pro)
*Documentation:* interactive HTML5, PDF, and Markdown (Pro)
*Visual query builder and relational data browse* (Pro)
*Data generator and importer:* fill tables with test data, load CSV, XML and XLS files (Pro)
*Logical and conceptual design:* database-independent models that you can migrate to a physical schema later (Architect)
*AI assistant:* a separate $9/month add-on for any edition, or your own key in Architect

DbSchema keeps an offline visual model of the MySQL schema, including grouped tables, relationships, and expanded JSON fields.

DbSchema’s Relational Data Editor follows connected rows across linked panes, from a venue to its events, orders, and reservations.

*What I like*

You can model before the database exists, which most tools here can't do
Diagrams, reverse engineering, and the SQL editor are free for every supported database
Git-friendly model file and generated documentation make it good for teams

*Disavantages*

Saving the model, documentation, and schema sync need a paid edition. When the trial ends, Community can't open the project files you saved
Steeper learning curve: its model-first workflow and many features can feel less straightforward for beginners than a simpler live database client.

5. DBeaver

*Platform:* Windows, macOS, Linux
*Databases:* MySQL, MongoDB (paid editions), PostgreSQL, and 100+ more
*Price:* free Community Edition. Lite $113/year ($12/month), Enterprise $255/year ($26/month), Ultimate $510/year
*Free trial:* 15 days. Create an account, then import the license key into the app (the most involved sign-up of the eight)

DBeaver is the free all-rounder. The Community edition is open source and works with 100+ databases, and the paid editions add schema editing, comparison tools, and NoSQL support. It’s a general database tool first and a design tool second.

*Key features*

*ER diagrams:* for a whole schema or a single table, in every edition. Editing the schema from the diagram (ERD edit mode) starts at Enterprise
*SQL editor and data editor:* with query history and inline editing
*Visual query builder:* build queries by clicking tables and columns (paid editions)
*Import, export, and charts:* move data between databases and files, and draw analytical charts
*Schema comparison and table data comparison:* (Enterprise)
*Mock data generation:* fill tables with test data (Enterprise)
*Git sync and task scheduler:* (Enterprise)

DBeaver turns a live MySQL schema into a navigable ER map; it's useful when you need to understand an existing database rather than design one from scratch.

DBeaver goes beyond the diagram: compare a changed schema, then build a joined query visually and inspect the SQL it produces.

*What I like*

A genuinely useful free edition, ER diagrams included
Huge database coverage and a solid SQL editor
Easy to browse and edit real data

*Disadvantages*

Diagrams exist to explore a database, and editing the schema from them is an Enterprise feature
Starting the trial is the most involved of the eight: account, email, then a license key to import

5. Navicat

Navicat is the polished, long-established name in this group. It is a live-connection tool first, but the Enterprise edition adds a proper modelling workspace. Instead of only generating a diagram from an existing database, you can start with a conceptual, logical, physical, or hybrid design and take it forward to MySQL.

*Key features*

*Model-first design (Enterprise):* start with a conceptual, logical, physical, or hybrid model. For physical models, choose the target database and version before adding tables.
*Naming standards and documentation:* create naming rules, a shared glossary, and a data dictionary alongside the model.
*Forward and reverse engineering (Enterprise):* reverse-engineer a live database or SQL file into a model, then generate SQL or synchronize the finished model back to a database.
*Live ER diagram:* Standard and Enterprise can also generate a quick diagram from an existing schema. It supports MySQL, Oracle, PostgreSQL, SQLite, SQL Server, and MariaDB.
*Structure and data synchronization:* compare two databases and generate the scripts needed to bring them back in sync.
*Navicat Collaboration:* share connections, queries, snippets
*Import, export, backup, and SSH tunneling:* covers the everyday work around a live database connection.

Navicat generates a physical ER diagram from the MySQL ticketing schema, with fields, keys, and foreign-key links visible in one view.

Navicat Enterprise pairs a proper modelling workspace with BI dashboards, so you can plan a database before building it and present data through charts afterward.

*What I like*

A mature, polished interface with a long track record
Sync and collaboration tools are strong for teams working across several databases
The Premium Lite edition is free for commercial use, up to 5 users per organization

*Disadvantages*

It has the highest price on the list, and the free Premium Lite has no ER diagram view
The full modelling workspace may feel like overkill if you only need a quick, simple schema diagram.

6. DbVisualizer

*Platform:* Windows, macOS, Linux
*Databases:* MySQL, PostgreSQL, and 60+ others via JDBC
*Price:* Free edition. Pro is $199 for the first year, then $89/year. Premium Support is $229 for the first year, then $119/year. A paid term includes a perpetual license for versions released during that period.
*Free trial:* 21 days of Pro. Create an account, then start the trial from the app.

DbVisualizer is a mature JDBC database client built for databases that already exist. It is good for exploring a live schema, writing SQL, browsing data, and following relationships, but it is not really a design-from-scratch tool. The Free edition covers the essentials, while Pro adds data editing, visual querying, and analysis tools.

*Key features*

*References graph (Free):* automatically maps primary and foreign keys, offers several layouts, and exports to PNG, SVG, or PDF.
*SQL editor and query history (Free):* write queries, revisit previous work, and reuse it when needed.
*Connection management and SSH tunnelling (Free):* manage multiple databases and connect securely to remote servers.
*Table and data editor (Pro):* edit table structures and data directly in the app.
*Import and export tools (Pro):* move data between files and databases.
*Visual Query Builder (Pro):* build SELECT queries visually instead of writing every join and filter by hand.
*Explain plan and charts (Pro):* inspect query performance and turn result sets into charts.
*CLI and AI Assistant (Pro):* run DbVisualizer from the command line or use its built-in AI help.

DbVisualizer’s References graph automatically maps the foreign-key structure of the live MySQL ticketing database.

DbVisualizer Pro can load a multi-table SQL query into its visual builder, then turn an aggregated result into a chart directly in SQL Commander.

*What I like*

The references graph is included in the free edition
Works with any JDBC database, with extra support for the common ones
Pro is cheap to renew, and your license is perpetual for the versions released during your term

*Disavantages*

The graph is built from existing foreign keys, so it is a viewer, not a modeling tool. There is no offline model file or design-first workflow
Table management, the data editor, and the query builder are all Pro. The trial needs an account before you can start it

7. TablePlus

*Platform:* macOS, Windows, Linux (plus iOS)
*Databases:* MySQL, PostgreSQL, SQLite, SQL Server, MariaDB, Redis, MongoDB, and more (13 in total)
*Price:* free tier with limits. Basic $99 one-time (1 device), Standard $129 one-time (2 devices), Team $79 per seat from 3 seats. Each includes 1 year of updates, then an optional $59/device renewal
*Free trial:* 14 days, no sign-up needed

TablePlus is the fast, native client of the group. It is built for people who spend their day in the data grid and want a quick, clean interface with a one-time price. It focuses on everyday database work rather than design, so schema diagrams are an add-on, not a core feature.

*Key features*

*Native interface:* fast to open and responsive on large tables
*Data editor:* edit rows inline and review changes before you commit them
*Advanced filters and query editor:* with autocompletion
*Safe mode:* helps prevent accidental changes on production databases
*Built-in ER diagram:* generate a diagram directly from a live database or schema, then move tables around, zoom in, search for objects, and edit tables, columns, indexes, or foreign keys from the diagram.
*Plugin manager:* install extra tools such as a SQL formatter or table dump
*iOS app:* included for free with a valid license

TablePlus generates a clean ER diagram from the live MySQL ticketing database, with related tables and foreign-key links visible at a glance.

TablePlus combines a small SQL-powered Metrics Board with a fast spreadsheet-style grid for inspecting individual ticket orders.

*What I like*

Fast, native, and pleasant to use every day
A one-time price, and the app keeps working after the first year of updates
Opens the free tier without any sign-up

*Disavantages*

The free tier is limited to 2 tabs, 2 windows, and 2 advanced filters at a time
Supports fewer databases than other tools

8. Beekeeper Studio

*Platform:* Windows, macOS, Linux
*Databases:* MySQL, PostgreSQL, SQLite, SQL Server, and 22 more (26 in total). Some engines are paid-only: Oracle from Indie, MongoDB from Professional
*Price:* free Community Edition (open source). Indie $9/month, Professional $14/month, Business $18/month, all billed yearly. Monthly billing costs about double. Every paid plan includes a lifetime license for the versions released during your subscription
*Free trial:* 14 days of all paid features. Install the app, click "start free trial," and you're in, no email, account, or card needed.

Beekeeper Studio is a clean SQL client with an open-source Community edition. The ER diagram (called the Visual Schema Explorer) is built in, but it's a paid feature: it isn't included in the Community edition, and the trial unlocks it.

*Key features*

*ER diagrams (paid):* turn a live database schema into a visual map of its tables and foreign-key links. You can move tables around, use auto-layout when it gets messy, then export or copy the result.
*Table structure editor:* create or change tables, relations, triggers, and indexes without writing every statement by hand.
*SQL editor and data grid:* write SQL with autocomplete, then browse and edit rows in a clean spreadsheet-style view.
*Import, export, backup, and restore (paid):* useful for moving data around and keeping database backups close.
*JSON Sidebar (paid):* opens a selected row as formatted JSON, which is much easier to read than a long JSON value in one cell.
*AI Shell and Cloud Workspaces (Professional+):* use your own AI provider to explore the schema and help write SQL, or sync connections and saved queries across devices and with a team.

Beekeeper Studio’s trial maps the foreign-key relationships in the MySQL ticketing database, keeping the overview focused on how tables connect.

Beekeeper Studio makes everyday database work straightforward: filter live payment records in the grid, then save and run a multi-table query from the SQL editor.

*What I liked:* Beekeeper Studio is one of the easiest tools here to pick up. The interface is clean, browsing and editing data feels quick, and the SQL editor does not bury basic work behind lots of menus.

*Disadvantages:* The limitation comes after the trial: the free Community Edition does not include ER diagrams, import/export, or the JSON Sidebar. The ER diagram is also mainly built for following relationships; the full-database view keeps only primary and foreign keys visible, while a focused selection shows more detail.

So which one should you pick?

There’s no single winner, because these tools are built around different jobs. Start with whether you’re designing a schema from scratch or exploring one that already exists.

*You work with more than one kind of database:* VisuaLeaf. It has ER diagrams you can create and reverse engineer, free for SQL databases. The same app also works with MongoDB, DuckDB, ClickHouse, and more (37 in total). It’s the newest tool here, so expect fewer community answers.
*You want to design a schema before the database exists:* DbSchema. Diagrams are free, but saving the model file and generating docs need Pro.
*You want free ER diagrams:* DBeaver Community, or the DbVisualizer free edition. Editing the schema from the diagram is paid in DBeaver.
*You write SQL all day:* DataGrip, which is free for non-commercial use.
*You want a fast native app with a one-time price:* TablePlus, if you’re fine with a diagram plugin and the free tier limits.
*You want the cleanest interface and an open-source core:* Beekeeper Studio. Its ER diagram is in the paid editions, and the trial is the easiest to start.
*You manage several databases as a team and have the budget:* Navicat. It’s the most expensive option here, and the diagram view isn’t in the free Lite edition.

All eight have a free edition or a trial, so the quickest way to decide is to point two or three of them at the same database and see which diagram your team can read.

Strengths and weaknesses

Tool	Visual ER diagrams	Biggest strength	Biggest weakness
VisuaLeaf	Create from scratch or reverse-engineer an existing SQL database; free for SQL databases	MongoDB plus 36 other databases in one app, with compare, sync, and data masking	Newer product, with a smaller community than DBeaver or DataGrip
DataGrip	Generated from a connected database	The strongest SQL editor and refactoring help in this group	Paid for commercial use and aimed at databases that already exist
DbSchema	Design-first, offline model file	Model before a database exists, with Git support and interactive HTML documentation	Its model-first workflow can feel heavier than a simple live database client
DBeaver	Generated from a connection in every edition; diagram editing is Enterprise-only	A capable free Community edition with 100+ drivers	Visual schema editing is paid, and starting the trial is more involved than the others
Navicat	Live ER diagrams in Premium; full data modelling in Enterprise	Polished sync, collaboration, and modelling tools	The most expensive option here, while Premium Lite has no diagrams
DbVisualizer	References graph, including in the free edition	Export a clear relationship graph as PNG, SVG, or PDF	It reads existing foreign keys; it is not a design-from-scratch tool
TablePlus	Built-in ER diagram in paid editions, generated from a live database or schema	Fast native client with a one-time license option	The free tier limits workspaces and locks several advanced features
Beekeeper Studio	Auto-generated in paid editions and during the trial; the main view focuses on keys and relationships	One of the cleanest, easiest interfaces, with an open-source Community edition	Community does not include ER diagrams, import/export, or the JSON Sidebar

Frequently asked questions

Which database design tool is best?

It depends on the job: VisuaLeaf or DBeaver for broader everyday database work; DbSchema is strong for planning; and DataGrip for SQL.

Are there free ER diagram tools?

Yes. DBeaver Community, DbVisualizer Free, DbSchema Community, and VisuaLeaf for SQL databases all include diagram features.

Can I generate an ER diagram from an existing MySQL database?

Yes. Connect the database and reverse engineer its tables, primary keys, and foreign keys into a diagram.

Can I design a schema before creating the database?

Yes. VisuaLeaf and DbSchema let you plan the structure first, then generate the SQL later.

Final thoughts

Try two or three tools with the same database before choosing one. The best option is the one whose diagram, SQL editor, and everyday workflow make sense to you and your team.

If you want to try the workflow used in this article, download VisuaLeaf for free and connect it to your MySQL, PostgreSQL, MongoDB, or another supported database.

Best MySQL Database Design Tools in 2026

VisuaLeaf — Mon, 26 Oct 2026 12:57:02 +0000

Roxana-Maria Haidiner

Jun 12 '25

Best MySQL Database Design Tools in 2026

#mysql #database #designtools #databasemanagement

9 min read

Think of a clinic named HyperClinic. This clinic needs to store patients, doctors, specializations, and appointments.

A quick way to do this is to store everything in one table, called appointments:


appointment_id	patient_name	patient_phone	doctor_name	specialization	appointment_time
16	Marit Solheim	555-0147	Owen Wilson	Cardiology	2026-10-20 10:00

This approach works for one row, but it causes problems when you add another appointment for Marit, change Dr. Wilson's specialization, or have two patients with the same name.

The patient information is repeated. The doctor information is repeated. A typo can lead to inconsistency. And nothing prevents creating an appointment for a doctor who does not exist.

To avoid these problems, the team needs to plan the database before creating tables.

ER Diagram vs Database Schema

The key point of differentiation is not the shape of the boxes and arrows. It is the stage of development.


Stage	Key question	Objects defined	What the diagram shows
Conceptual ER diagram	What does the company need to store?	Entities and relationships	Boxes and lines, no fields
Logical data model	How should the data be structured?	Attributes, keys, normalized relationships	Fields with key icons and relationship lines, no data types
Physical database schema	How will PostgreSQL implement it?	Tables, columns, data types, constraints, indexes, and DDL statements	Fields with data types

A *schema diagram* is a graphic representation of the database schema. Such diagrams are also referred to as physical ER diagrams.

A *database schema* is the actual structure stored in PostgreSQL and comprises objects such as tables, columns, primary and foreign keys, constraints, and indexes.

Thus, this is not a binary choice. A database design is typically developed from an ER diagram through the logical data model to the database schema.

In this article, we will build HyperClinic in the same order: Step 1 is the conceptual ER diagram, Steps 2 to 4 build the logical model, and Step 5 creates the physical PostgreSQL schema.

Start with the Requirements

Nobody will start writing CREATE TABLE before the team figures out the task.

In our case, HyperClinic must be able to:

save patient information
save doctors and their specializations
create appointments
show which patient sees which doctor
ensure appointments refer to existing patients and doctors

The job names depend on the company. Requirements may be gathered by a business analyst and designed by a database architect, data modeler, backend developer, or DBA. In a small company, one person may do several of these jobs.

What is important is the workflow, not the name.

The team should ask itself such questions as:

Is it possible for one patient to have several appointments?
Is it possible for one doctor to have many patients?
Do all doctors have only one specialization?
Is it possible to create an appointment without a doctor?
Is it possible to create two appointments with the same doctor for the same time?

These are the business rules that PostgreSQL won't be able to guess.

Step 1: Create the Conceptual ER Diagram

Before deciding on columns, data types, or SQL, first identify what HyperClinic needs to store:

Patient
Doctor
Appointment
Specialization

An *entity* is simply something the system needs to store.

This is a *conceptual ER diagram*. It helps the team see the big picture without getting into implementation details. The boxes show the entities. The lines show their relationships. There are no columns, primary keys, foreign keys, or PostgreSQL data types yet.

The relationships are:

A patient has many appointments.
A doctor has many appointments.
An appointment is made by one patient and one doctor.
A specialization has many doctors.
Each doctor has one specialization.

Step 2: Add Attributes and Primary Keys

This is the point where the model becomes *logical*.

The conceptual diagram tells us that HyperClinic has patients, doctors, appointments, and specializations. The logical model answers the next question: *what information belongs to each one?*

You now add attributes, choose identifiers, and organize the data so the same fact is not stored in several places. You are still designing the structure. You are not choosing PostgreSQL types such as VARCHAR(100) or TIMESTAMP yet.

Think of it like planning a house:

the conceptual diagram says the house needs a kitchen, bedrooms, and a bathroom;
the logical model decides what belongs in each room;
the physical schema gives the builder the exact materials and measurements.

The logical model takes three steps: attributes and primary keys (this step), normalization (Step 3), and foreign keys (Step 4).

Start with the attributes. For each entity, write down what the system needs to know about it:


Entity	Attributes
Patient	first name, last name, date of birth, phone
Doctor	first name, last name, specialization
Specialization	name
Appointment	patient, doctor, date and time, reason, status

Then give every entity a *primary key*. A primary key is a column, or a set of columns, that uniquely identifies a row.

For instance, patient_id = 1 must identify only one patient. A name cannot be used as a primary key, as patients can have the same name and names change over time.

The same holds for doctors, specializations, and appointments: doctor_id, specialization_id, and appointment_id.

Step 3: Normalize the Logical Model

Normalization means organizing data so each fact is stored in the right place, instead of being repeated in several places.

You do not finish the ER diagram and normalize it later as a separate task. Usually, you draw the entities, test the structure, notice repeated data, and improve the model.

The examples below start from a flat table. The services are only for illustration: the final HyperClinic model does not include them.

First Normal Form: One Value per Field

First normal form, or *1NF*, means that one field should contain one value, not a list.

× Not in 1NF:


appointment_id	patient_name	doctor_name	services	appointment_at
16	Marit Solheim	Owen Wilson	Consultation, ECG	2026-10-20 10:00

The services field contains two values. This makes the data harder to filter, validate, or update.

✓ In 1NF:


appointment_id	patient_name	doctor_name	service	appointment_at
16	Marit Solheim	Owen Wilson	Consultation	2026-10-20 10:00
16	Marit Solheim	Owen Wilson	ECG	2026-10-20 10:00

Each row now contains one service. However, the appointment, patient, and doctor information is repeated. The next step fixes that.

Second Normal Form: Each Field Depends on the Whole Key

Second normal form, or 2NF, applies when a table uses a combined key.

Here, one appointment can have more than one service. The combination of appointment_id and service identifies one row.

× Not in 2NF:


appointment_id	service	patient_name	doctor_name	appointment_at
16	Consultation	Marit Solheim	Owen Wilson	2026-10-20 10:00
16	ECG	Marit Solheim	Owen Wilson	2026-10-20 10:00

The patient_name, doctor_name, and appointment_at values depend only on appointment_id. They do not depend on the full combination of appointment_id and service, so they are repeated.

✓ In 2NF:

appointments


appointment_id	patient_name	doctor_name	appointment_at
16	Marit Solheim	Owen Wilson	2026-10-20 10:00

appointment_services


appointment_id	service
16	Consultation
16	ECG

Now the appointment information is stored once. The appointment_services table only stores the services linked to that appointment. The patient and doctor names are still text here. Step 4 replaces them with IDs.

Third Normal Form: Do Not Repeat Details That Belong Elsewhere

Third normal form, or 3NF, removes indirect repetition.

× Not in 3NF:


doctor_id	first_name	last_name	specialization_id	specialization_name
1	Owen	Wilson	1	Cardiology
6	Odile	Marchetti	1	Cardiology

The specialization name depends on specialization_id, not on the doctor. Storing it in doctors means it will be repeated for every cardiologist.

✓ In 3NF:

doctors


doctor_id	first_name	last_name	specialization_id
1	Owen	Wilson	1
6	Odile	Marchetti	1

specializations


specialization_id	specialization_name
1	Cardiology

Now Cardiology is stored once. If the clinic changes its name, the team updates one row in specializations, not every doctor.

After normalization, each table holds only its own facts:


Table	Example Columns
`patients`	`patient_id`, `first_name`, `last_name`, `date_of_birth`, `phone`
`doctors`	`doctor_id`, `first_name`, `last_name`
`specializations`	`specialization_id`, `specialization_name`
`appointments`	`appointment_id`, `appointment_at`, `reason`, `status`

The lines show which tables are related, but the link columns, such as specialization_id in doctors, are not here yet. They come in the next step.

Step 4: Link Related Tables with Foreign Keys

To connect two tables, you add a column to one of them. That column is a foreign key: it stores the ID of a row in the other table. This is how a relationship is made.

An appointment should not store redundant information like a patient's name, phone, and date of birth.

Instead, it stores the ID of the linked patient:

appointment_id: 16
patient_id: 1
doctor_id: 1

The field appointments.patient_id is a foreign key that references patients.patient_id.

The field appointments.doctor_id links to doctors.doctor_id.

The field doctors.specialization_id links to specializations.specialization_id.

A foreign key performs two functions:

It describes the relationship between tables.
It prevents invalid references.

Without a foreign key, you can insert an appointment with patient_id = 9999, even if patient 9999 does not exist.

If there is a foreign key, PostgreSQL rejects this record.

A column named patient_id alone does not create a foreign key. The REFERENCES constraint creates it.

The diagram now shows the foreign key columns, marked with a link icon, and an arrow for each relationship. There are still no data types. Types depend on the database engine, so they come in the physical stage.

Step 5: Define the Physical PostgreSQL Model

The physical model describes the PostgreSQL tables, columns, data types, and rules that you want to create.

In this example, INT is used for IDs (the diagram shows it as INTEGER, which is the same type), VARCHAR for text, DATE for a date, and TIMESTAMP for a date and time.

Create the Database Schema

The physical model is ready. Now you need to create it on the PostgreSQL server.

You can do this in either of two ways:

Run the CREATE SCHEMA and CREATE TABLE statements in the SQL Editor.

The following statements are DDL, short for Data Definition Language. DDL is the SQL used to create the database structure.

CREATE SCHEMA clinic;

CREATE TABLE clinic.specializations (
  specialization_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  specialization_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE clinic.doctors (
  doctor_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  specialization_id INT NOT NULL
    REFERENCES clinic.specializations(specialization_id),
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL
);

CREATE TABLE clinic.patients (
  patient_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  date_of_birth DATE NOT NULL,
  phone VARCHAR(30)
);

CREATE TABLE clinic.appointments (
  appointment_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id INT NOT NULL
    REFERENCES clinic.patients(patient_id),
  doctor_id INT NOT NULL
    REFERENCES clinic.doctors(doctor_id),
  appointment_at TIMESTAMP NOT NULL,
  reason VARCHAR(200) NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'scheduled',
  CONSTRAINT appointments_status_check
    CHECK (status IN ('scheduled', 'completed', 'cancelled'))
);

This is how you can create it in VisuaLeaf's SQL Editor:

You can also use Materialize instead of running the SQL yourself. Starting with an empty database, VisuaLeaf creates the clinic schema and its tables directly from the physical model.

Materialize can also apply later changes. In this example, the four original clinic tables already exist, so they are marked as up to date while the new lab_results table is created.

Use one method to create the original schema. If you run the same CREATE TABLE statements after the tables already exist, PostgreSQL returns an “already exists” error.

Step 6: View the Created Database Schema

After either option succeeds, open Schema Manager and select the clinic schema.

You can now see the patients, appointments, doctors, and specializations tables on the PostgreSQL server. This is the implemented database schema: it is no longer only a model or a plan.

This schema contains several constraints:

NOT NULL requires a value.
PRIMARY KEY makes each ID unique.
REFERENCES creates foreign-key relationships.
UNIQUE prevents duplicate specialization names.
DEFAULT 'scheduled' gives new appointments an initial status.
CHECK prevents unsupported status values.

For example, PostgreSQL will reject this appointment if patient 9999 does not exist:

INSERT INTO clinic.appointments (
  patient_id,
  doctor_id,
  appointment_at,
  reason
)
VALUES (
  9999,
  3,
  '2026-11-20 10:00',
  'Annual check-up'
);

PostgreSQL returns an error like this:

ERROR:  insert or update on table "appointments" violates foreign key constraint "appointments_patient_id_fkey"
DETAIL:  Key (patient_id)=(9999) is not present in table "patients".

This is the difference between drawing a relationship in a diagram and enforcing it in the database.

ER Diagram vs Database Schema: The Difference

The conceptual, logical, and physical models are the steps between the first idea and the finished database.

	ER diagram	Database schema
Main job	Plans what data the system needs and how it connects	Stores the real database structure
Includes	Entities, attributes, and relationships	Tables, columns, data types, keys, constraints, and indexes
In this example	The HyperClinic models before the schema is created	The `clinic` schema after the SQL is run or the model is materialized
Where you see it	In the diagram designer	In PostgreSQL and Schema Manager

An ER diagram helps the team decide what to build. The database schema is the structure PostgreSQL creates from that plan.

The tool used for this demonstration is VisuaLeaf. You can download it for free and follow the same steps: design the model, create the schema, and view the tables and relationships in one place.

What changed in MongoDB 9.0, and how can it help in everyday database work? Practical examples in VisuaLeaf show the new behavior and compare results with MongoDB 8.2.5.

VisuaLeaf — Wed, 14 Oct 2026 17:43:06 +0000

Roxana-Maria Haidiner

Oct 14

What's New in MongoDB 9.0: Query Settings, Validation, and Resource Limits

#mongodb #nosql #database #softwaredevelopment

10 min read

How to Open a SQLite Database: Query and Explore Your Data

VisuaLeaf — Wed, 14 Oct 2026 17:36:15 +0000

You have a SQLite file and want to see the information inside it. To open a SQLite database, select the file in software that can read SQLite, such as VisuaLeaf. You can then browse the tables and use SQL to find the information you need.

SQL is the language you use to tell a database what you want. A query is one of those requests. It might ask for ten inspection records, the name of a technician, or the number of checks that failed.

This guide uses an example inspection database. You’ll open the file, browse its tables, join related records, read JSON values, export results to Excel, and create a chart. If you follow along with your own database, replace the example table and column names with yours.

First, understand what is inside the file

SQLite stores data in a database file. You don't need to set up a separate database server to open it.

Inside the file, the data is organised into *tables*. A table looks a little like a spreadsheet: it has rows and columns.

The inspections table’s columns on the left and five inspection records on the right.

The information is split across several tables:

sites holds details about the places being inspected,
technicians holds details about the people doing the inspections,
inspection_items holds the results of individual checks.

An inspection can therefore have one row describing the visit and several other rows describing its checklist items.

Open your SQLite database in VisuaLeaf

SQLite files often end in .db, .sqlite, or .sqlite3.

In VisuaLeaf:

Create a connection and choose SQLite.
Select your existing database file.
Connect, then expand main and *Tables* in the sidebar.
Open a table to see its records.

You select a file instead of entering a server address, port, or database username. If your database is already connected, go straight to its tables.

main is SQLite's name for the primary database opened by that connection. You don't need to create another database with that name.

Connecting to the local field_inspections.sqlite database in VisuaLeaf.

If you don’t have a SQLite database yet, click *New DB* in the connection form to create one. It starts empty, so you can add your own tables and data. The examples below use our existing inspection database.

Read a few inspections with SQL

Open inspections in the data view to browse its rows. You can also choose exactly which columns to display using the SQL Editor:

SELECT id, status, risk_score
FROM inspections
ORDER BY id
LIMIT 10;

Read this query one line at a time:

SELECT id, status, risk_score chooses the three columns to show.
FROM inspections tells SQLite which table to read.
ORDER BY id sorts the records by their identifier, from smallest to largest.
LIMIT 10 asks for at most ten rows.

This query reads data. It doesn't change the records.

Reading ten inspection records, ordered by ID, with their statuses and risk scores.

The id column is the inspection's *primary key*: the value that identifies that row. Keep it in your results so you can find the same inspection again.

Look at the values before writing a filter. A column called status doesn't tell you which words the application stores there. Check the records rather than guessing whether a status is called complete, completed, or something else.

What do the column types mean?

Open Table Management for sites. It shows how each column is defined.

The sites table in VisuaLeaf, showing each column’s data type, whether it allows missing values (NULL), and its default value.

The screenshot includes three common SQLite types:

Data type	What it stores	Example columns
`INTEGER`	Whole numbers, such as 1, 2, or 3	`id`
`TEXT`	Text, such as names and cities	`name`, `city`
`REAL`	Numbers with decimals, such as 44.43	`latitude`, `longitude`

The active column has a *default* of 1. That means SQLite supplies 1 when a new row leaves this column out of the insert.

A default alone also allows someone to enter another value. To restrict active to 0 and 1, the table would need a *constraint*, which is a rule that data must follow. For example, CHECK (active IN (0, 1)) checks that the value is one of those two numbers. A separate NOT NULL rule rejects a missing value.

Click *Show DDL* to see the SQL that defines the sites table. DDL means Data Definition Language. This definition shows the column types and rules, such as primary keys, foreign keys, NOT NULL, and default values.

You can also inspect the table's *indexes*. An index is a lookup structure, a little like the index at the back of a book. It can help SQLite find certain values without reading every row. Having an index on a column does not automatically connect it to another table.

SQLite is flexible with data types. In a regular table, a column marked INTEGER may still accept text. The declared type tells you what the column is intended to store, but it does not always enforce that type.

Understand foreign keys and join related tables

The inspections table stores site_id and technician_id to identify where each inspection took place and who performed it. The site and technician details live in their own tables.

A *primary key* identifies a specific row. For example, sites.id identifies each site.

A *foreign key* is a rule that connects a value in one table to a matching key in another. Here, inspections.site_id refers to sites.id.

If an inspection has site_id = 7, it belongs to the site whose id is 7. You can look up that site’s name and city in sites, without copying those details into every inspection row. Several inspections can have the same site_id because you can inspect the same site more than once.

The same idea applies to technician_id: it refers to the technician’s id in technicians.

The ER diagram shows these connections as lines between the tables.

Foreign keys connect each inspection to its site and technician. The diagram shows these relationships and the available rules for handling deletions.

A *join* uses those matching values to bring information from different tables into one result:

SELECT
    inspections.id,
    sites.name AS site,
    technicians.full_name AS technician,
    inspections.risk_score
FROM inspections
LEFT JOIN sites
    ON inspections.site_id = sites.id
LEFT JOIN technicians
    ON inspections.technician_id = technicians.id
ORDER BY inspections.id
LIMIT 10;

A name such as sites.name means “the name column in the sites table.” Writing the table name first makes it clear which table we mean.

The query starts with inspections, matches each one to its site, and then matches it to its technician. The result puts the inspection ID, both names, and the risk score together.

LEFT JOIN keeps the inspection in the result even if a matching site or technician is missing. SQLite shows NULL for the missing information. NULL means no value. A normal inner JOIN would leave out inspections without a match.

A null name alone doesn't prove a record is missing: the related row could exist but have no name. Check the matching IDs before deciding what is wrong.

Joining three SQLite tables to show each inspection’s site, technician, and risk score.

You can also create this query visually with VisuaLeaf’s Query Builder, without writing SQL. Select the tables, connect their matching columns, and choose which fields to include in the result.

Joining tables visually in VisuaLeaf’s Query Builder, filtering for risk scores above 2, and sorting inspections by ID.

Is SQLite checking the relationships?

SQLite can have foreign keys defined while their enforcement is turned off. To check the current connection’s setting, run:

PRAGMA foreign_keys;

A result of 1 means checking is on; 0 means it is off. To enable it, run this before making changes, outside an active transaction:

PRAGMA foreign_keys = ON;

This setting applies to the current connection. It helps prevent new invalid references, but it does not fix existing ones.

Query JSON data in SQLite

One database column can hold several related details as JSON. In our inspections table, device_info stores the device platform, app version, battery percentage, and whether the inspection was recorded offline.

Start by viewing the original values:

SELECT id, device_info
FROM inspections
LIMIT 3;

You can see the result on the left of the screenshot. Each device_info cell contains named values, such as "platform":"android". Here, platform is the name and android is its value.

To display the platform separately, use json_extract():

SELECT
    i.id AS inspection_id,
    s.name AS site,
    json_extract(i.device_info, '$.platform') AS platform,
    json_extract(i.device_info, '$.app_version') AS app_version,
    json_extract(i.device_info, '$.battery_percent') AS battery,
    i.risk_score
FROM inspections AS i
JOIN sites AS s ON s.id = i.site_id
WHERE json_extract(i.device_info, '$.offline') = 1
ORDER BY i.risk_score DESC
LIMIT 12;

Read that expression in three parts:

device_info is the column containing the JSON.
'$.platform' tells SQLite to find the value named platform inside it.
AS platform names the result column.

This creates a column in your query results. It does not add a new column to the database or change the stored JSON.

The longer query on the right extracts the platform, app version, and battery percentage. It also adds the site name using a join.

Its WHERE condition keeps inspections where offline is true. SQLite returns that JSON value as 1. Finally, ORDER BY ... DESC puts the highest risk scores first, and LIMIT 12 returns at most twelve rows.

Original device JSON on the left; extracted details for offline inspections on the right.

Export SQLite query results to Excel

Once your query returns the information you need, you can export it to an Excel workbook. This lets you share the results with someone who doesn’t use a database tool.

Open the export form for your query, then:

Set *Source Type* to *Query*.
Select your SQLite connection and the main database.
Check the SQL in the *Query* field.
Set *Target Type* to *Excel Workbook*.
Choose a file location and name, such as inspections.xlsx.
Click *Export Now*.

For our JSON query, the exported columns contain the inspection ID, site, platform, app version, battery percentage, and risk score.

The export follows your query’s filters and limits. If you keep LIMIT 12, it exports at most twelve rows. Remove that limit if you want all matching inspections.

The Excel file contains a copy of the query results. Editing it does not update your SQLite database.

Exporting SQLite query results to an Excel workbook.

Count and chart failed checks by category

Use GROUP BY to group failed checklist items by category, then COUNT(*) to count them:

SELECT
    issue_category,
    COUNT(*) AS failed_checks
FROM inspection_items
WHERE result = 'fail'
GROUP BY issue_category
ORDER BY failed_checks DESC
LIMIT 8;

The query returns up to eight categories with the most failed checks.

In VisuaLeaf’s Chart Builder, choose *Pie, set *Category** to issue_category, and *Value* to failed_checks. Each slice shows that category’s share of the failed checks returned by the query.

These totals count checklist items, not inspections. One inspection can contain several failed items. Remove LIMIT 8 if you want to include every category with failed checks.

Each slice shows a category’s share of the failed checks included in the chart.

Conclusion

Start by opening your SQLite file and checking its tables and columns. Then use a small SELECT query to read a few records before adding filters, joins, or calculations.

Once the results contain what you need, you can export them to Excel or turn them into a chart. Check your filters and row limits first—they determine which data appears in the final result.

[Boost]

VisuaLeaf — Fri, 18 Sep 2026 08:52:14 +0000

Roxana-Maria Haidiner

Sep 18

MongoDB Sharding: Manage a Sharded Cluster Visually

#mongodb #database #software #softwaredevelopment

5 min read

Learn how to use DuckDB for local analysis: open a database, query CSV and Parquet files, inspect relationships, edit data, and create charts.

VisuaLeaf — Thu, 03 Sep 2026 07:21:04 +0000

Roxana-Maria Haidiner

Sep 3

How to Use DuckDB for Local Data Analysis

#duckdb #sql #database #softwaredevelopment

8 min read

mongodump Tasks Automate and Schedule MongoDB Backups

VisuaLeaf — Mon, 31 Aug 2026 11:00:28 +0000

Running a MongoDB backup once is not difficult. With MongoDB Database Tools installed, the complete PowerShell command can be as short as this:

& "C:\Program Files\MongoDB\Tools\100\bin\mongodump.exe" `
  --uri="mongodb://127.0.0.1:27017" `
  --db="gui_test_db" `
  --gzip `
  --out="C:\MongoDBBackups\gui_test_db"

This example uses an unauthenticated local MongoDB deployment. Authenticated or remote deployments also require the appropriate credentials, authentication database, TLS settings, and connection options.

mongodump is MongoDB's command-line utility for creating a binary export of a database or collection. The problem is rarely the first run. It is repeating the same backup every day or week, keeping the settings consistent, using a fresh destination, and checking whether the operation actually completed.

That is where a saved task becomes useful. In VisuaLeaf, the same binary dump can be configured once, run manually when needed, scheduled, and checked later from its execution history.

Save the Same mongodump as a VisuaLeaf Task

Open *Task Manager*, create a new job, and choose the binary dump/restore operation. Select *Export to Dump* for the operation.

The full gui_test_db database saved as a reusable mongodump task in VisuaLeaf.

The task should have a name that still makes sense when you return to it later. I used:

Full Database mongodump – 4.6 GB (Gzip)

This is more useful than a generic name such as Backup or New Job. It tells me the scope, approximate source size, and whether compression is enabled.

For the source, I selected the complete gui_test_db database running locally on port 27017. Its uncompressed data size was approximately 4.6 GB, spread across four collections:

dataset_30000
large_collection_4GB
large_docs_10mb
medium_docs_1mb

The source panel displayed the database statistics before the job ran. This gave me a quick check that I had selected the full database before creating several gigabytes of output.

Back Up a Database or One Collection

The correct scope depends on why you need the backup.

Choose a *collection dump* when you only need one dataset, want to move a collection between environments, or need a smaller restore unit. Choose a *database dump* when the job should include all collections and their metadata.

Be deliberate here. A successful task using the wrong source is still the wrong backup.

Choose the Output Folder and Enable Gzip

I saved the VisuaLeaf output in its own folder and enabled *Compress with Gzip*. A separate folder for every run keeps completed backups distinct and prevents CLI files, task files, or partial attempts from being mixed.

The job summary also showed *auto threads* and *no timeout*. Auto threads leaves the worker configuration to VisuaLeaf. The gear icon opens the advanced settings if the task needs a specific thread limit or timeout. I left both settings at their defaults for this workflow.

A full gui_test_db backup configured as a binary dump task in VisuaLeaf.

After choosing the source and destination, save the job. The connection, database, folder, compression, and operation settings remain attached to the task, so the next backup does not require rebuilding the command.

Schedule and Monitor the MongoDB Backup

You can still start a saved job manually, but scheduling is the main reason to turn this workflow into a task. VisuaLeaf's Task Manager documentation lists one-time, hourly, daily, weekly, monthly, and custom cron schedules.

Choose the frequency based on how much recent data the application could afford to lose. A weekly backup is easy to manage, but it may be inadequate for a database that changes throughout the day.

Use a dated destination for recurring runs and decide how many older backups to keep. Otherwise, the task may work perfectly while the backup drive quietly runs out of space.

The full database dump is scheduled to run every day at 6:00 AM.

After a run, *Execution History* provides a detailed dump summary. In my test, it recorded:

Total duration, collections, documents, and database size.
The thread used for each collection.
Document count and size per collection.
Individual collection duration and processing speed.

The completed dump included four collections, 30,449 documents, and 4.72 GB of source data.

*Performance note:* On the same Windows machine, with Gzip enabled and the same 4.72 GB database, the VisuaLeaf task completed in 4 minutes 40 seconds. The equivalent direct mongodump CLI run took 6 minutes 12 seconds, so VisuaLeaf finished about 25% faster in this test. Results may vary depending on the hardware, storage, data, and task settings.

The equivalent direct mongodump CLI run completed in 6 minutes and 12 seconds and produced 3.1 GB of compressed output.

Before treating the exported files as a valid backup, check that the execution status is *Completed*. A cancelled or failed run may still leave partial files in the destination.

What Files Does the Backup Create?

A compressed directory dump creates one .bson.gz data file and one .metadata.json.gz file for each collection. The VisuaLeaf output for gui_test_db contained:

dataset_30000.bson.gz
dataset_30000.metadata.json.gz
large_collection_4GB.bson.gz
large_collection_4GB.metadata.json.gz
large_docs_10mb.bson.gz
large_docs_10mb.metadata.json.gz
medium_docs_1mb.bson.gz
medium_docs_1mb.metadata.json.gz

The BSON files contain the documents. The metadata files contain collection options and index definitions used during restoration. MongoDB describes this directory structure in its official [mongodump](https://www.mongodb.com/docs/database-tools/mongodump/) documentation.

The 4.6 GB test database produced approximately 3.18 GB of compressed output in VisuaLeaf. That result is specific to this data. A similarly sized database containing images, encrypted values, or already compressed content may shrink much less.

The compressed gui_test_db backup contains eight BSON and metadata files totaling 3.18 GB.

Can You Run mongodump in MongoDB Compass?

No. MongoDB Compass can export an individual collection as JSON or CSV, but MongoDB explicitly says Compass is not a backup tool. It does not create a full binary database dump containing compressed BSON files and collection metadata. For that, MongoDB directs users to its separate Database Tools. See the official Compass import and export documentation.

MongoDB Compass exports collection data as JSON or CSV; it does not create a binary database backup.

This is an important difference between exporting data and creating a backup. JSON can preserve MongoDB types when the correct Extended JSON format is used, while CSV can lose type information. Neither Compass export is the same workflow as a BSON database dump intended for mongorestore.

Restore the BSON Backup in VisuaLeaf

Creating compressed BSON files is only half of a backup test. You also need to confirm that MongoDB can restore them.

To avoid changing the original gui_test_db database, I created a separate VisuaLeaf restore job. Under *Source Configuration, I selected *Restore (BSON)** and chose the folder containing the .bson.gz and .metadata.json.gz files.

For the target, I selected the same MongoDB connection and entered a new database name: gui_test_db_restore. Because that database did not exist, VisuaLeaf indicated that it would create it when the restore job ran.

VisuaLeaf restore job configured to import a compressed BSON dump into a new MongoDB database.

This operation uses the binary dump-and-restore workflow. It reads the BSON data and collection metadata without converting the documents to JSON or CSV.

When restoring to a different deployment, check version compatibility first. MongoDB requires the source and destination to use the same major version or feature compatibility version. See the official mongodump behavior documentation.

After the job finishes, check the restored database rather than relying only on the Completed status. The restore took 7 minutes and 34 seconds and recreated all four collections with 30,449 documents:

Four collections.
30,449 documents in total.
30,000 documents in dataset_30000.
292 documents in large_collection_4GB.
57 documents in large_docs_10mb.
100 documents in medium_docs_1mb.
The expected collection indexes and options.

VisuaLeaf restored all four collections and 30,449 documents from the compressed BSON dump.

The document counts matched the source, confirming that the BSON files could be restored successfully. A production restore test should also verify indexes, collection options, and several real application queries.

Do not restore a test dump over the only copy of your original database. Use a new database name or a separate development environment.

A backup is not verified until the restore finishes successfully and the restored data matches the source.

If mongodump Does Not Run on Windows

Two small Windows problems appeared during this test.

If PowerShell says that mongodump is not recognized, MongoDB Database Tools may be installed without its bin directory being added to PATH. Using the complete path to mongodump.exe, as shown in the opening command, avoids that problem.

A connection error can also mean that the command is using the wrong host or port. One attempt used localhost:27019, while the current MongoDB instance was listening on 27017. Check the connection used by VisuaLeaf or MongoDB Compass before retrying.

Cancelling a dump may leave partial files in the destination. Delete those incomplete files or use a new output folder before starting another backup.

MongoDB Backup Limitations to Plan For

A mongodump backup still needs consistency planning, safe storage, and restore testing.

Limitation	Why it matters	What to do
Writes continue during the dump	A database-scoped dump may not represent one exact point in time.	`--oplog` cannot be combined with this `--db` command; it requires a full replica-set member dump. Use another backup method when exact point-in-time consistency is required.
Collection names differ only by capitalization	Directory dumps may conflict on case-insensitive Windows and macOS file systems.	Avoid ambiguous collection names or use archive output with the CLI.
The backup stays on the MongoDB machine	Disk failure, device loss, or ransomware could affect the database and backup together.	Keep another copy on separate or off-machine storage.
The backup is never restored	A Completed status and BSON files do not prove that the backup is usable.	Restore into a separate database and verify document counts, indexes, and collection options.
Old backups are never removed	Scheduled dumps can eventually fill the destination disk.	Define a retention policy and monitor available storage space.

MongoDB positions mongodump and mongorestore mainly for smaller deployments; larger or business-critical systems may require a different backup strategy.

Conclusion

A reliable MongoDB backup workflow does not end when files appear in a folder. It ends when those files can be restored, and the resulting database has been verified.

The command line remains useful for one-off dumps and scripts. In VisuaLeaf, you can save both export and restore jobs, schedule repeated backups, and review their status, duration, history, and logs. You still need off-machine copies, retention rules, consistency planning, and restore tests, but the recurring steps are easier to manage from one place.

PostgreSQL JSONB: Query, Update, and Index JSON Data

VisuaLeaf — Tue, 18 Aug 2026 11:20:25 +0000

JSONB allows you to store JSON data within a PostgreSQL table row. It is suitable when certain columns remain unchanged, while the remaining columns may vary across rows.

Let us take the support tickets table, where the status, priority, and the date a ticket was created cannot change. However, the client name, environment, tags, and even error details may be optional and have different formats. That is why the optional content may be stored in the JSONB field

This storage process is easy enough. However, you need answers to questions such as how to find a nested value, update a single field, or create an effective index for your database engine.

This article explores how to achieve that goal using a support_tickets table and the corresponding details column.

PostgreSQL JSONB operations used in this guide

We’ll use each of these against the same support_tickets.details column, so you can see how querying, updating, and indexing work together.

Create the support tickets table

CREATE TABLE support_tickets (
    ticket_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status     TEXT NOT NULL,
    priority   TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    details    JSONB NOT NULL
        CHECK (jsonb_typeof(details) = 'object')
);

I kept status, priority, and created_at outside JSONB because they have stable types and are useful for filtering and sorting. The less predictable ticket context goes into details.

Here is one record used in the test:

INSERT INTO support_tickets (status, priority, details)
VALUES (
    'open',
    'high',
    '{
      "customer": {
        "name": "Trevor Lisbon",
        "plan": "Professional"
      },
      "environment": {
        "browser": "Chrome",
        "os": "Windows 11",
        "appVersion": "4.8.2"
      },
      "tags": ["sync", "postgresql"],
      "error": {
        "code": "SYNC_TIMEOUT",
        "retryable": true
      }
    }'::jsonb
);

The CHECK constraint confirms that details contains a JSON object. It does not guarantee that customer.plan exists or that tags is always an array. JSONB validates the JSON format, not your complete application schema.

PostgreSQL table with regular columns and nested JSONB data displayed in VisuaLeaf.

Query nested JSONB values

PostgreSQL provides two operators that look similar but return different data types:

Use -> when you want an object or array to remain JSONB.

Use ->> when you need a scalar value for an ordinary SQL comparison.

This query returns the complete customer object and extracts the plan as text:

SELECT
    ticket_id,
    details -> 'customer' AS customer,
    details -> 'customer' ->> 'plan' AS plan
FROM support_tickets
WHERE ticket_id <= 4
ORDER BY ticket_id;

Expanding the customer JSONB object while plan is returned as text.

A common mistake is to use -> for the final value:

WHERE details -> 'customer' -> 'plan' = 'Professional'

The left side is JSONB, while Professional is being treated as a SQL string. PostgreSQL may return:

invalid input syntax for type json
Token "Professional" is invalid.

The clearer fix is:

WHERE details -> 'customer' ->> 'plan' = 'Professional'

To filter by a nested value, use ->> for the final part of the path:

SELECT
    ticket_id,
    status,
    details -> 'customer' ->> 'name' AS customer_name
FROM support_tickets
WHERE details -> 'error' ->> 'code' = 'SYNC_TIMEOUT';

Filtering tickets by a nested JSONB error code and returning the customer name as text.

If a row has no error object, PostgreSQL returns SQL NULL for that expression. It does not fail.

Search inside a JSONB array

You can search the tags array with the ? operator:

SELECT ticket_id, status, details -> 'tags' AS tags
FROM support_tickets
WHERE details -> 'tags' ? 'postgresql';

This assumes that tags contains an array of strings. If some rows store "tags": "postgresql" instead, the data is valid JSONB but has the wrong shape for this query.

Match part of a document with `@>`

The containment operator checks whether one JSONB value contains another:

SELECT ticket_id, status, details -> 'error' AS error
FROM support_tickets
WHERE details @> '{
  "error": {
    "code": "SYNC_TIMEOUT"
  }
}'::jsonb;

Using @> to find tickets containing the SYNC_TIMEOUT error object.

The structure must match the document. This does not work:

WHERE details @> '{"code": "SYNC_TIMEOUT"}'::jsonb

code is nested under error; it is not a top-level key. This matters when you add a GIN index, because containment queries can use that index directly.

Update a nested value with `jsonb_set`

Ticket 1 was reproduced in version 4.8.4. Instead of replacing the entire details document, I updated only environment.appVersion:

UPDATE support_tickets
SET details = jsonb_set(
    details,
    '{environment,appVersion}',
    to_jsonb('4.8.4'::text),
    false
)
WHERE ticket_id = 1
RETURNING
    ticket_id,
    details -> 'environment' AS environment;

The result shows appVersion as 4.8.4, while browser and os remain unchanged.

Updating environment.appVersion while preserving the other JSONB fields.

The final false means the key must already exist. The ::text cast is also important; without it, PostgreSQL may not know which type to convert:

could not determine polymorphic type because input has type unknown

When the parent object is missing

jsonb_set can create the final key, but not a missing parent object. Ticket 3 has no error object, so updating error.firstSeenAt would run without an error but change nothing.

This version creates the parent when needed:

UPDATE support_tickets
SET details = jsonb_set(
    details,
    '{error}',
    COALESCE(details -> 'error', '{}'::jsonb)
        || jsonb_build_object(
            'firstSeenAt',
            '2026-08-03T11:05:00Z'
        ),
    true
)
WHERE ticket_id = 3
RETURNING details -> 'error' AS error;

RETURNING lets you confirm that the field was actually added.

Creating the missing error object.

Add a GIN index for containment searches

For the indexing test, I added 40,000 generated tickets. The table contained 40,004 rows in total, while the original four remained available for the earlier examples.

Because the query uses @> against details, I created a containment-focused GIN index:

CREATE INDEX idx_support_tickets_details_gin
ON support_tickets
USING GIN (details jsonb_path_ops);

ANALYZE support_tickets;

jsonb_path_ops works well for containment searches, but it does not support every JSONB operator. For example, it cannot support the ? operator used in the tags query.

I then opened the same query in VisuaLeaf’s Explain view:

SELECT ticket_id
FROM support_tickets
WHERE details @> '{
  "error": {
    "code": "SYNC_TIMEOUT"
  }
}'::jsonb;

PostgreSQL found 402 matching tickets and used idx_support_tickets_details_gin through a Bitmap Index Scan.

The JSONB query uses the GIN index instead of scanning the full table.

The index worked for this query and dataset. PostgreSQL may still choose a sequential scan for a small table or when many rows match.

When to use JSONB

JSONB is useful for flexible data, but stable fields usually belong in regular columns.

In this example, environment and error fit inside JSONB. Fields such as status, priority, and created_at are better as regular columns.

I ran these examples in VisuaLeaf, using its SQL editor, nested JSONB table view, and visual query plan to inspect the results.

References

MongoDB Schema Design: Build or Reverse Engineer Visually

VisuaLeaf — Thu, 06 Aug 2026 11:45:16 +0000

MongoDB collections can become difficult to understand once they contain nested objects, arrays, references, and documents with slightly different structures.

Looking at one JSON document at a time shows you the data, but not the complete model. It is harder to see how collections connect, which fields use inconsistent BSON types, or whether a value is embedded or referenced elsewhere.

The opposite problem happens when the database does not exist yet. You may know which collections and fields you need, but reviewing the design across separate JSON files or application models is not easy.

VisuaLeaf’s Visual Schema supports both directions:

Generate a diagram from an existing MongoDB database.
Design a new schema from an empty diagram.
Keep the schema virtual or materialize it into real collections.

I tested both workflows using a travel-planning database containing collections such as travelers, places, flights, accommodations, restaurants, activities, carRentals, and vacationPlans.

VisuaLeaf generates a visual MongoDB schema showing collections, fields, BSON types, and relationships in the TravelPlannerDemo database.

Although this guide focuses on MongoDB, VisuaLeaf’s Visual Schema can also be used with SQL databases. If you’re deciding which database model fits your project or want to compare their structures, read MongoDB vs PostgreSQL: What’s the Difference?.

Reverse engineer an existing MongoDB database

If your database already contains data, you can generate a Visual Schema from its collections.

Connect to MongoDB, open *Visual Schema*, and select the database you want to analyze. VisuaLeaf reads the existing documents and creates a diagram containing:

collections and fields;
detected BSON types;
embedded objects;
arrays and their nested fields;
detected relationships between collections.

In my TravelPlannerDemo database, the generated diagram included the main travel collections and their nested structures.

For example, vacationPlans contains simple fields such as title, status, and createdAt, but it also includes more complex structures:

budget and dates as embedded objects;
itinerary, checklist, and documents as arrays;
destinationIds and travelerIds as arrays of references;
ownerTravelerId and selectedAccommodationId as individual references.

Instead of opening several documents to understand how these fields fit together, I could inspect the structure in one diagram.

This is particularly useful when you are working with a database you did not design, or when the structure has changed, but its documentation has not.

Review the detected structure

Here’s the trap most of us fall into: reverse-engineering a database shows what’s currently stored, not what was actually intended.

MongoDB allows documents in the same collection to have different fields and BSON types. For example, most documents might store a date correctly:

{
  createdAt: ISODate("2026-07-18T09:30:00Z")
}

while an older import stores it as a string:

{
  createdAt: "2026-07-18"
}

These values may look similar in a table, but one has the BSON type date and the other has the type string.

The same problem can occur with identifiers. A field such as ownerTravelerId may contain an ObjectId in most documents but a string in others. That difference matters when you try to connect it to travelers._id.

The generated schema represents the data VisuaLeaf analyzed. Rare fields may also be absent if they do not appear in the sampled documents. Review unusual records and any fields with multiple detected types before using the diagram as your final model.

Review and add relationships

MongoDB does not store relationships as enforced foreign keys. It stores the reference value, but the meaning of that value often needs to be confirmed.

In the travel database, relationships can connect fields such as:

vacationPlans.ownerTravelerId → travelers._id
vacationPlans.travelerIds[] → travelers._id
vacationPlans.destinationIds[] → places._id
vacationPlans.selectedAccommodationId → accommodations._id

Other collections use similar connections:

accommodations.placeId → places._id
restaurants.placeId → places._id
activities.placeId → places._id
flights.passengers.travelerId → travelers._id

Check both ends before accepting or adding a relationship. An ObjectId does not match a string containing the same characters.

For example, these two values use different BSON types:

ObjectId("66af9138ad73c91224345c10")

"66af9138ad73c91224345c10"

The diagram can document a relationship, but it does not make MongoDB enforce it. Your application can still insert an ownerTravelerId that does not exist in travelers.

The generated diagram may also need some visual cleanup. In a database with many related collections, lines can overlap and detected connections may not represent the relationship you intended. Reposition the collections and review each relationship rather than accepting every connection without checking.

A closer look at nested fields, BSON types, arrays, and relationships between `_vacationPlans___,__ _places___, and__ _accommodations`_ in VisuaLeaf.

Modify the generated schema

A reverse-engineered schema does not have to remain a read-only picture of the current database.

You can use it as a starting point and then:

add or remove fields;
correct BSON types;
define embedded objects and arrays;
add missing relationships;
remove incorrect relationships;
reorganize the diagram.

In this example, I added a new optional travelStyle field to vacationPlans. I also manually connected destinationIds to places._id to represent the reference between the two collections.

The same collection contains embedded data as well. The budget object belongs to one vacation plan, so storing it inside the document keeps the related values together. destinationIds, however, contains references to documents stored in the places collection.

vacationPlans
├── budget
│   ├── currency
│   ├── estimatedTotal
│   └── paidSoFar
├── dates
├── itinerary[]
├── ownerTravelerId
├── travelerIds[]
└── destinationIds[]

This shows both modeling approaches in the same collection: embedded data for information owned by the vacation plan and references for information stored elsewhere and reused.

Changing the virtual schema does not automatically migrate existing documents. If you rename a field, change its type, or move it into an embedded object, the records already stored in MongoDB remain unchanged.

The diagram describes the proposed model. Data migration remains a separate operation.

Design a new MongoDB schema from scratch

You can also use Visual Schema before the database exists.

Start with an empty diagram and add the collections you need. You can then define their fields, BSON types, embedded structures, arrays, and relationships.

For this example, I created a small tour-booking schema with two collections:

Collection	Example fields
`departures`	`_id`, `packageId`, `startDate`, `capacity`, `meetingPoint`, `status`
`reservations`	`_id`, `departureId`, `bookingReference`, `leadGuest`, `totalAmount`, `paymentStatus`, `createdAt`

The leadGuest field is an embedded object containing fields such as fullName and email. The relationship connects:

reservations.departureId → departures._id

This represents a one-to-many relationship: one departure can have multiple reservations.

Defining the schema visually makes several problems easier to notice:

references pointing to the wrong field;
incorrect BSON types;
fields that should be grouped inside an embedded object;
missing or unnecessary relationships;
inconsistent field names between collections.

After completing the design, save the schema and bind it to the MongoDB connection where you want to create the collections. The diagram remains a virtual model until you materialize it.

Keep the schema virtual or materialize it

The diagram can remain virtual if you only need it for documentation, planning, or discussing changes with your team.

Nothing is created in MongoDB until you choose to materialize it.

When the design is ready, save it and bind it to the MongoDB connection and target database. You can then click *Materialize* to review the collections and generated validators before applying the schema.

This allows both workflows:

Existing MongoDB database → Visual Schema

and:

Visual Schema → MongoDB database

You can therefore reverse engineer an existing database such as TravelPlannerDemo, modify its virtual schema, and materialize the result into a separate test database.

You can also begin with an empty diagram, as I did with TourBookingSchema, and create a new database from that design.

Choose whether to attach validators

Materializing a schema does not require you to enforce every field and BSON type.

The materialization screen lets you review the JSON Schema generated for each collection. You can then decide whether MongoDB should use it as a $jsonSchema validator.

If you want to create the collections without enforcing the schema, leave *Attach validator* disabled.

These are separate decisions:

The visual schema describes the intended structure.
Materialization creates the collections in MongoDB.
The generated JSON Schema describes the field and type rules.
The attached validator makes MongoDB enforce those rules during writes.

A field is mandatory only if it is included in the schema’s required array. Otherwise, its BSON type is checked only when the field is present.

This distinction matters when your application still writes inconsistent data.

Suppose the generated schema expects:

{
  createdAt: {
    bsonType: "date"
  }
}

If the application sends this instead:

{
  createdAt: "2026-08-03"
}

MongoDB will reject the write when the validator is attached. A document validation failure commonly returns MongoDB error code 121.

The solution is not necessarily to remove validation. First check what your application actually sends. You may need to fix the value before inserting it or adjust the schema if both types are intentionally supported.

Review the materialization plan

Before clicking *Apply to Database*, check:

the selected connection and database;
which collections will be created;
which collections already exist;
collection and field names;
BSON types;
embedded objects and arrays;
relationships in the visual diagram;
generated JSON Schemas;
which collections have the *Attach validator* enabled.

For the first test, use a new database instead of applying the model directly to an existing one.

For example:

TourBookingDemo

This makes the result easier to inspect and avoids mixing the materialized collections with the original TravelPlannerDemo data.

If you materialize changes against a database that already contains records, changing reservations.departureId from String to ObjectId in the diagram will not convert the stored values. Data migration remains a separate operation.

It also does not create enforced foreign keys. The relationship between reservations.departureId and departures._id remains part of the model, but your application must maintain valid references.

Verify the result

After applying the materialization plan, open the target database and confirm that the departures and reservations collections were created.

If you enabled *Attach validator*, inspect the validation settings and test them with:

one valid document that should be accepted;
one invalid document that should be rejected.

If you left the option disabled, the collections should still exist, but MongoDB will continue accepting documents with different structures.

Also remember that a validator does not replace indexes. Fields used for lookups and filtering may still need indexes, such as:

db.reservations.createIndex({
  departureId: 1,
  createdAt: 1
})

db.departures.createIndex({
  startDate: 1,
  status: 1
})

Validators control document structure. Indexes support query performance and can enforce uniqueness when created with the unique option.

Relationships explain how collections connect, but MongoDB does not enforce them as foreign keys.

One schema, two directions

Visual Schema is useful when you inherit an existing MongoDB database and need to understand its structure. It is also useful before you create the first collection.

In my travel-planning example, I could generate a diagram containing travelers, places, flights, accommodations, activities, vacationPlans, and the other related collections. I could inspect nested structures such as budget and itinerary, review the references, and correct the model where necessary.

The same workspace also supports the opposite workflow. You can start with an empty diagram, design the collections visually, and then decide whether to keep the model virtual or materialize it into MongoDB.

When you materialize it, attaching the generated JSON Schema validators remains optional.

*Want to turn your MongoDB database into a diagram—or your diagram into a database?*

Download VisuaLeaf and generate a schema from your existing MongoDB database, or design a new one from scratch.

Which MongoDB Client Handles Large Collections Best: VisuaLeaf vs Compass vs Studio 3T

VisuaLeaf — Tue, 04 Aug 2026 07:56:19 +0000

A MongoDB client can feel fast with a few hundred documents and become frustrating as soon as the result grows.

The database is not always the problem. MongoDB may finish the query while the desktop client is still fetching batches, converting BSON values, and building thousands of rows or JSON blocks.

I tested VisuaLeaf, MongoDB Compass, and Studio 3T with the same collection of 50,844 documents. Then I tested three related workflows: comparing collections, automating repeated work, and investigating an unindexed query.

Test setup

I ran all three applications on the same HP laptop with 16 GB of RAM and Windows 11 Pro. Each application was updated to the latest version available when I ran the test. All three connected to the same MongoDB database through the same internet connection and opened the same largeCollection. The collection contained nested objects and arrays, not only flat fields.

For the first test, I opened the collection without a filter, projection, or custom limit:

db.largeCollection.find({})

Here is what happened:

Test 1: What the large-result test actually shows

The three clients made different choices.

VisuaLeaf allowed one continuous result. That is useful when you need to scroll through a large set, select many documents, inspect distant rows, or export the current result without moving through pages.

VisuaLeaf loaded 50,000 documents in one result in 13.481 seconds.

Compass limited the amount of data visible at once. This protects the interface from an unexpectedly large query and works well when you only need to inspect a sample, edit one document, or confirm a filter. The tradeoff is that browsing the whole collection requires hundreds of pages.

MongoDB Compass displayed the collection in pages of up to 100 documents.

Studio 3T did not finish this particular operation before I stopped it. That does not prove that every version, display mode, or computer will behave the same way. It does mean that an unrestricted result was not practical in this setup.

Studio 3T became unresponsive while attempting to load 50,000 documents.

If a client struggles, reduce both the number of documents and the number of fields:

db.largeCollection.find(
  {},
  {
    _id: 1,
    company: 1,
    registered: 1
  }
).limit(1000)

This is usually the better daily workflow. A large result may be useful for inspection or export, but it still consumes database resources, network bandwidth, memory, and rendering time.

Test 2: Comparing two MongoDB collections

For this test, I used two controlled collections in the QA_Edit database: orders_compare_source and orders_compare_target.

Both collections started with 1,000 identical documents. I then modified 100 documents, removed 50, and added 50 in the target collection. I matched the documents by _id.

VisuaLeaf and Studio 3T both found 850 identical documents, 100 modified documents, 50 missing from the source, and 50 missing from the target.

A useful comparison must answer three questions:

Which documents exist on only one side?
Which fields changed inside matching documents?
What will be inserted, updated, or deleted during synchronization?

VisuaLeaf

VisuaLeaf has a dedicated Collection Compare workflow for schema and data differences. It keeps the comparison next to the document, query, and data-operation tools.

The important setup choice is the matching field. _id works only if both collections preserve the same values. If an import regenerated them, use a stable key such as customerId, orderId, or externalId.

VisuaLeaf found 850 identical, 100 modified, and 50 documents missing from each collection.

Studio 3T

Studio 3T provides the most detailed comparison controls of the three. Its Data Compare & Sync supports collections on different databases or servers, document- and field-level differences, result export, and synchronization in either direction.

Bi-directional synchronization is useful, but it increases the cost of a wrong choice. Decide which collection is the source of truth before applying changes.

Studio 3T returned the same differences using _id as the matching field.

MongoDB Compass

Compass does not provide the same dedicated collection-diff workflow. You can still build part of the comparison with an aggregation.

For example, this pipeline finds source documents without a matching _id in the target:

db.orders_compare_source.aggregate([
  {
    $lookup: {
      from: "orders_compare_target",
      localField: "_id",
      foreignField: "_id",
      as: "target"
    }
  },
  {
    $match: {
      target: {
        $eq: []
      }
    }
  },
  {
  $count: "missingInTarget"
  }
  }
])

This finds missing matches, not every field-level change. It also assumes both collections are in the same database. Comparing data on different servers requires a script, a temporary copy, or another tool.

Compass used an aggregation pipeline to find 50 source documents missing from the target.

Test 3: Automating Daily Orders

Large collections make small mistakes more expensive.

For this test, I used a daily orders JSON file.

In Studio 3T, I imported the file into a daily_orders collection. Studio 3T can also save and schedule tasks, so it works well when you want to repeat imports, exports, or other database jobs.

JSON import task: import a file into a collection

In VisuaLeaf, I used Task Manager to connect more than one step:

Import Daily Orders JSON → Add Orders to Main Collection → Export Sales Report

VisuaLeaf lets you connect tasks into a workflow, not just run one import.

The useful part is that tasks can have parent tasks. So the next task runs after the previous one is completed.

This is helpful when the work is not just “import this file.” In a real workflow, you may need to import data, map fields, move it into the main collection, mask sensitive data, and export a report after that.

For example, field mapping matters because orderedAt should be mapped to createdAt as a MongoDB Date, and values like email or phone numbers may need data masking before export.

So for me, Studio 3T is strong for saving and scheduling database tasks. VisuaLeaf is easier when I want to see a connected workflow in one place and control the order of the steps.

Compass is different. It is great for interactive work, but recurring workflows usually need something outside the desktop app, like a script, cron, Windows Task Scheduler, CI, or an Atlas service.

Test 4: Checking the Query Before Blaming the Client

The first test measured how each tool handled a large result on screen.

This test is different. Here, I wanted to check the work MongoDB was doing before the data even reached the client.

I started with a query that had no supporting index:

db.largeCollection.find({
  company: "ZENSOR"
})

The important part was the explain plan.

I checked:

scan stage in the winning plan: COLLSCAN or IXSCAN
totalDocsExamined
totalKeysExamined
nReturned
execution time

When the query uses COLLSCAN, MongoDB has to scan the collection to find matching documents. That means the slow part is not always the GUI. Sometimes the database is simply doing too much work.

A COLLSCAN is not automatically a problem. But in this case, scanning 50,844 documents to return only 51 showed that the query could benefit from an index.

VisuaLeaf detected a COLLSCAN, examining 50,844 documents to return 51.

Studio 3T reported a collection scan across 50,844 documents for 51 results.

Compass used a COLLSCAN, examining all 50,844 documents to find 51 matches.

After that, I created an index:

db.largeCollection.createIndex({
  company: 1
})

Then I ran the same explain operation again.

A lower execution time is useful, but the stronger evidence is that the winning plan now contains an IXSCAN. In this test, totalDocsExamined dropped from 50,844 to 51, matching the 51 returned documents.

VisuaLeaf’s Query Profiler is useful here because it brings slow queries, execution plans, index usage, scanned documents, returned documents, and optimization hints into one place.

After adding the company_1 index, VisuaLeaf examined 51 documents and 51 index keys.

Studio 3T has the deepest performance tools in this comparison, with Query Profiler, Visual Explain, and Index Manager.

With the company_1 index, Studio 3T used an index scan and examined 51 documents.

Compass also has Explain Plan, which works well when you already know which query you want to inspect.

After indexing company, Compass changed to IXSCAN and examined only 51 documents.

For this test, the main point was simple: before blaming the client, check if MongoDB is scanning too much data.

Which client fits which workflow?

If you mainly need to…	Best fit	Why
Open and work with a large continuous result	VisuaLeaf	It loaded 50,000 documents in 13.481 seconds and remained usable
Compare two collections visually	VisuaLeaf	It clearly separated identical, modified, and missing documents
Automate multi-step data workflows	VisuaLeaf	Import, copy, export, and scheduling can be managed in one workflow
Find inefficient queries in the same workspace	VisuaLeaf	Query Profiler combines plans, index usage, scanned documents, and optimization hints
Browse documents in small pages with a free official client	MongoDB Compass	Pagination keeps browsing controlled and predictable
Access advanced synchronization and administration controls	Studio 3T	It provides more granular diff, sync, and performance options

There is no single client for every MongoDB job. Compass is a good option for controlled, paginated browsing, while Studio 3T offers advanced administration and synchronization controls. But for everyday work with large results, collection comparisons, automated tasks, and query analysis, VisuaLeaf provides the most complete workflow in one place.

Choose the client around the operation that costs you time, not the longest feature list.

DEV Community: VisuaLeaf

Best Database Design Tools for 2027: Windows, macOS & Linux

What I looked at

The eight tools side by side

How I Tested the 8 GUIs

So which one should you pick?

Strengths and weaknesses

Frequently asked questions

Which database design tool is best?

Are there free ER diagram tools?

Can I generate an ER diagram from an existing MySQL database?

Can I design a schema before creating the database?

Final thoughts

Best MySQL Database Design Tools in 2026

Top Free ER Diagram Tools for PostgreSQL in 2026

ER Diagram vs Database Schema: What’s the Difference?

ER Diagram vs Database Schema

Start with the Requirements

Step 1: Create the Conceptual ER Diagram

Step 2: Add Attributes and Primary Keys

Step 3: Normalize the Logical Model

First Normal Form: One Value per Field

Second Normal Form: Each Field Depends on the Whole Key

Third Normal Form: Do Not Repeat Details That Belong Elsewhere

Step 4: Link Related Tables with Foreign Keys

Step 5: Define the Physical PostgreSQL Model

Create the Database Schema

Step 6: View the Created Database Schema

ER Diagram vs Database Schema: The Difference

What changed in MongoDB 9.0, and how can it help in everyday database work? Practical examples in VisuaLeaf show the new behavior and compare results with MongoDB 8.2.5.

How to Open a SQLite Database: Query and Explore Your Data

First, understand what is inside the file

Open your SQLite database in VisuaLeaf

Read a few inspections with SQL

What do the column types mean?

Understand foreign keys and join related tables

Is SQLite checking the relationships?

Query JSON data in SQLite

Export SQLite query results to Excel

Count and chart failed checks by category

Conclusion

[Boost]

Learn how to use DuckDB for local analysis: open a database, query CSV and Parquet files, inspect relationships, edit data, and create charts.

mongodump Tasks Automate and Schedule MongoDB Backups

Save the Same mongodump as a VisuaLeaf Task

Back Up a Database or One Collection

Choose the Output Folder and Enable Gzip

Schedule and Monitor the MongoDB Backup

What Files Does the Backup Create?

Can You Run mongodump in MongoDB Compass?

Restore the BSON Backup in VisuaLeaf

If mongodump Does Not Run on Windows

MongoDB Backup Limitations to Plan For

Conclusion

PostgreSQL JSONB: Query, Update, and Index JSON Data

PostgreSQL JSONB operations used in this guide

Create the support tickets table

Query nested JSONB values

Match part of a document with @>

Update a nested value with jsonb_set

When the parent object is missing

Add a GIN index for containment searches

When to use JSONB

References

MongoDB Schema Design: Build or Reverse Engineer Visually

Reverse engineer an existing MongoDB database

Review the detected structure

Review and add relationships

Modify the generated schema

Design a new MongoDB schema from scratch

Keep the schema virtual or materialize it

Choose whether to attach validators

Review the materialization plan

Verify the result

One schema, two directions

Which MongoDB Client Handles Large Collections Best: VisuaLeaf vs Compass vs Studio 3T

Test setup

Test 1: What the large-result test actually shows

Test 2: Comparing two MongoDB collections

VisuaLeaf

Match part of a document with `@>`

Update a nested value with `jsonb_set`