benmorel/smartdump

Dumps selected MySQL tables together with their relationships

0.2.0 2020-11-23 13:02 UTC

Requires

• php: ^7.4 || ^8.0
• ext-pdo: *
• symfony/console: ^5.1

Requires (Dev)

• vimeo/psalm: ^4.1

MIT

README

Exports a referentially intact subset of a MySQL database.

Note: although this tool targets MySQL only for now, it is designed to be able to support other RDBMS in the future.

Introduction

Did you ever need to export just a couple tables from your MySQL database, but end up with broken foreign key constraints? What if you could import every single foreign row your data depends on as well, and nothing more? This tool does just that.

Let's say you want to dump the order_items table below:

If you use mysqldump, you'll get the whole order_items table, with broken foreign key constraints to orders and products.

If you use smartdump instead, you'll get the rows you want from order_items (or the whole table, as you wish), plus the rows from orders and products required to satisfy their constraints, plus, in turn, the rows from users and countries required to satisfy the remaining constraints! 💪

The key takeaway here is that smartdump will only import the rows required to satisfy the constraints of the requested tables/rows.

Installation

The only currently supported installation method is through Composer:

composer require benmorel/smartdump

Usage

To dump some tables, just run:

vendor/bin/smartdump db.table1 db.table2

or, if all your tables are in the same database:

vendor/bin/smartdump --database db table1 table2

To dump only a subset of a table, add extra conditions after the table name:

vendor/bin/smartdump "db.table:LIMIT 100"

Or even:

vendor/bin/smartdump "db.table:WHERE user_id=123 ORDER BY id DESC LIMIT 10"

Options

Options that take a value:

Options that don't take a value:

Future scope (todo, ideas)

• standalone PHAR version
• support for other RDBMS
• support for loading incoming relationships to the tables (?)
  Right now, only the outgoing relationships are followed, it could be interesting to follow incoming relationships to each row we're exporting as well; at least as an option?
• a mode that does not dump, but scans the whole database for broken foreign key constraints

Database diagram courtesy dbdiagram.io.

Logo by Pixel perfect.