Relational databases are great. Normalization of schema allows to store user's data in your application cheaper and faster than in document oriented NoSQL solutions. But complex, deep schemas can be real pain if you need to determine rows ownership and extract them.
There are few scenarios where you may want to extract some user and all records that belong to him (like addresses, orders, comments, etc) from database:
- For backup. To move inactive user data to cheap, long-term storage and free database space.
- For legal purposes. If you need to secure current state of user.
- For better load balancing. To migrate user between database shards.
- For sales. To prepare some role model demo user in your application, save his data and load it into demo instances of your application.
- For debugging. If your application is misbehaving and you want to analyze user data in isolated manner and clone it from production to local machine.
I work a lot with big schemas. Hundreds of databases, hundreds of tables in each database, deep relation chains, up to several schema changes a day. So I created a tool automate relational data extraction: UpRooted.
Goals were simple:
Fully automated. I do not want to write a single
- Optimal. Data in related tables should be reached in most efficient way from database standpoint.
Smart. Data should be processed in order that allows future loading into another database without breaking
Let's assume you have MySQL database and you need to extract user from
users table having
id = 1 with all his data from related tables.
The easiest way to start using UpRooted is to take Docker container.
Create working directory for scripts and output files, for example:
Run Docker and mount this directory:
docker run --interactive --tty --volume /Users/me/uprooted:/home/uprooted bbkr2/uprooted:latest
use DBIish; use UpRooted::Schema::MySQL; use UpRooted::Tree; use UpRooted::Reader::MySQL; use UpRooted::Writer::MySQLFile; my $connection = DBIish.connect( 'mysql', host => '***', port => 3306, user => '***', password => '***', database => '***' ); my $schema = UpRooted::Schema::MySQL.new( :$connection ); my $tree = UpRooted::Tree.new( root-table => $schema.table( 'users' ) ); my $reader = UpRooted::Reader::MySQL.new( :$connection, :$tree ); my $writer = UpRooted::Writer::MySQLFile.new; $writer.write( $reader, id => 1 );
Run your script from within container:
Your user will be saved to
out.sql file in
I strongly recommend reading UpRooted README for explanation of each module in
UpRooted:: namespace, more advanced use cases and potential schema design issues.
If you have found any bugs or have some suggestions feel free to create issue on GitHub.
Happy data extraction :)