SQL and MongoDB

Lily converts in both directions. Your AI tool can refine the model, and Lily checks what it suggests.

SQL to MongoDB

In Migrate a database, choose a MongoDB connection as the target, then the Target database (a new name is fine; MongoDB creates it on the first write).

Lily builds a document model from the foreign keys:

• Tables nothing points to are embedded in the document of the table they belong to (order items inside orders), one level deep.
• Other tables become collections, and their foreign keys to other collections become references.

The sheet previews this model. With AI set up, Lily sends the table names, keys and foreign keys (no rows) and lets the AI decide embed or reference; if no AI answers, or its answer does not fit, the preview's model is used and Lily says so.

In the documents:

• a single-column primary key becomes _id;
• embedded children drop the column that linked them to the parent;
• NULL values are left out.

Rows are read in batches and written one document at a time. If something fails, the collections created by this run are dropped; documents written into collections that already existed stay, and Lily tells you which.

MongoDB to SQL

In a MongoDB workspace, open the Database menu and choose Convert to SQL…, then pick a SQL target.

Lily reads up to the first 1,000 documents of each collection and infers tables from them:

• _id becomes the primary key;
• plain fields become columns;
• a field that is always an object is flattened into parent_child columns, one level deep; deeper levels are stored as JSON;
• a field that is always an array becomes a child table <collection>_<field>, with a foreign key back to the parent, an index and the element's position;
• fields with mixed shapes become JSON columns.

With AI set up, Lily asks for better names and narrower types (text to integer, decimal, boolean, date or timestamp). Each suggestion is checked against the data and dropped if any value would not fit. This step sends up to three sample values per column to your AI tool.

The tables and rows are then written like a migration, with the same rollback.

Large collections

Only the first 1,000 documents of each collection are converted. For bigger collections, use the result as the schema and move the rest of the data another way.