How to Archive Old MySQL Rows to MongoDB Cold Storage

Build a scheduled Spojit workflow that selects aged rows from a MySQL table, copies them into a MongoDB archive collection, then deletes the originals so your operational table stays lean and fast.

What This Integration Does

Operational tables grow forever unless something prunes them. Old orders, completed jobs, expired sessions, and processed events keep accumulating long after anyone queries them day to day, which slows down indexes, backups, and reporting on your live MySQL database. This workflow moves rows older than a cutoff out of MySQL and into a MongoDB collection that you treat as cold storage: the data is still there if you ever need it, but it no longer weighs down the table your application hits constantly.

The run model is a Schedule trigger that fires on a cron expression (for example, nightly). Each run selects a bounded batch of rows past your age threshold from MySQL, normalizes them into JSON documents, inserts those documents into the MongoDB archive collection, then deletes exactly the rows it copied using their primary keys. Because the delete targets the same keys that were just archived, re-runs are safe: rows already moved are gone from MySQL, so the next run only sees rows that still qualify. If a run finds no qualifying rows, it copies and deletes nothing and ends cleanly.

Prerequisites

A MySQL connection in Spojit with read and delete permissions on the source table. See the MySQL connector article for setup.
A MongoDB connection in Spojit pointed at the database that will hold your archive. See the MongoDB connector article.
The source table name, its primary key column (for example, id), and the timestamp column you want to age on (for example, created_at).
A decision on your retention cutoff (for example, rows older than 90 days) and a sensible batch size so each run stays within your databases' load limits.

Step 1: Add a Schedule trigger

Start a new workflow and set the Trigger node type to Schedule. Enter a 5-field Unix cron expression and an IANA timezone. For a nightly run at 2am Sydney time, use 0 2 * * * with timezone Australia/Sydney. A single Schedule trigger can hold multiple schedules if you want to spread the work, but one nightly entry is enough to start. The trigger output is { scheduledAt }, which you can reference downstream as {{ scheduledAt }} if you want to log the run time. For more detail, see Setting Up a Schedule Trigger.

Step 2: Select aged rows from MySQL

Add a Connector node in Direct mode, choose the MySQL connector, and pick the execute-query tool. Write a bounded SELECT that returns only rows past your cutoff, ordered oldest-first, with a hard LIMIT so a single run can never grab the whole table. Use a ? placeholder for the cutoff and pass it through params:

query:
  SELECT *
  FROM orders
  WHERE created_at < ?
  ORDER BY created_at ASC
  LIMIT 1000

params:
  ["2025-03-22 00:00:00"]

You can compute the cutoff dynamically with a Connector node on the date connector (the subtract tool) before this step and feed it in as {{ cutoff.result }} instead of a hardcoded date. Name this node's output something like aged_rows; its rows will be available as {{ aged_rows.rows }} (the exact result field is shown in the node's output preview after a test run).

Step 3: Stop early when there is nothing to archive

Add a Condition node so the workflow does no write work on an empty batch. Check whether the selected set has any rows, for example testing that the count of {{ aged_rows.rows }} is greater than 0. Wire the false branch to nothing (the run simply ends), and continue the true branch into the next step. This keeps idle nightly runs cheap and avoids inserting or deleting on empty input. See Using Condition Nodes for branch setup.

Step 4: Shape the rows into archive documents

MySQL rows and MongoDB documents do not have to be identical. Add a Transform node (or a Connector node on the json connector) to add archive metadata such as the run time and source table so the cold copy is self-describing. A simple approach is the json connector's set tool to stamp a field, applied per row, or a structured Transform that maps each MySQL row to the document shape you want:

// each archive document
{
  ...row,
  _archivedAt: "{{ scheduledAt }}",
  _sourceTable: "orders"
}

Name the output archive_docs so the next step can reference {{ archive_docs.result }} as the array of documents to insert. Keeping the original primary key on each document lets you trace a document back to the row it came from.

Step 5: Insert the documents into MongoDB

Add a Connector node in Direct mode on the MongoDB connector and select the insert-documents tool. Set collection to your archive collection name (for example, orders_archive) and map documents to the array from the previous step, {{ archive_docs.result }}. The tool inserts one or more documents in a single call and returns { insertedCount }, which you should capture (for example as archive_result) so the next step can confirm the copy succeeded before anything is deleted.

collection: orders_archive
documents: {{ archive_docs.result }}

Step 6: Confirm the insert, then delete the originals

Add a Condition node that verifies the copy landed before you remove anything: check that {{ archive_result.insertedCount }} equals the number of rows you selected. Only on the true branch, add a Connector node in Direct mode on the MySQL connector with the delete-rows tool. Delete by primary key range or by the exact cutoff you selected with, so you never delete a row that was inserted after your SELECT ran. Set table to your source table, write a parameterized where clause, and pass values through params:

table: orders
where: created_at < ? AND id <= ?
params: ["2025-03-22 00:00:00", "{{ aged_rows.maxId }}"]

Bounding the delete to the maximum primary key you actually archived (capture it in Step 2, for example with the array or math connector) guarantees the workflow only removes rows it has already safely copied. If you prefer, delete by the same age predicate alone, but the key bound is the safer choice when the table is still receiving writes.

Tips

Keep the LIMIT small enough that a single run finishes comfortably, then let the nightly schedule chip away at the backlog over several runs rather than moving millions of rows at once.
Create an index on your age column (for example created_at) in MySQL so the SELECT in Step 2 stays fast even as the table grows.
Ask Miraxa, the intelligent layer across your automation, to scaffold this for you: "Build a scheduled workflow that selects rows older than 90 days from a MySQL table with execute-query, inserts them into a MongoDB collection with insert-documents, then deletes them with delete-rows." Then fine-tune each node in the properties panel.
Add a Send Email node at the end to report how many rows were archived using {{ archive_result.insertedCount }}, so you get a nightly confirmation without checking the run history.

Common Pitfalls

Deleting before confirming the insert. Always gate the delete-rows call behind a Condition that checks insertedCount against the selected count, or a failed insert could lose data.
Timezone drift in the cutoff. Your cron timezone and your MySQL timestamp timezone may differ; compute the cutoff explicitly and pass it as a parameter rather than relying on the database's notion of "now".
Unbounded deletes on a live table. Without a primary key bound, a WHERE that uses only the age predicate can remove rows that arrived after your SELECT. Bound the delete to the maximum key you archived.
Duplicate archives on overlapping runs. If a run is still finishing when the next one starts, both could pick up the same rows. Keep batches small so runs complete well within the schedule interval.

Testing

Validate on a tiny scope first. Point Step 2 at a cutoff that matches only a handful of rows (or add a tight extra predicate), and temporarily disable the Step 6 delete branch so you can confirm the documents land correctly in MongoDB. Run the workflow with the Run button instead of waiting for the schedule, then inspect the execution log to verify insertedCount matches your selected rows and that the documents look right in your archive collection. Once the copy is proven, re-enable the delete branch, run once more on a small batch, confirm both the MySQL rows are gone and the MongoDB documents remain, and only then leave the Schedule trigger enabled for nightly runs.