Extracting user data tree from relational databases
Tags: raku, mysql, postgres
Published: 2022-09-28 23:18:40

## Problem

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.

## Solution

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](https://github.com/bbkr/UpRooted).

Goals were simple:
- **Fully automated.** I do not want to write a single `JOIN`.
- **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 `FOREIGN KEY` constraints.

## Example

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](https://github.com/bbkr/UpRooted) is to take Docker container.

Create working directory for scripts and output files, for example:

```bash
mkdir /Users/me/uprooted
```

Run Docker and mount this directory:

```bash
docker run --interactive --tty --volume /Users/me/uprooted:/home/uprooted bbkr2/uprooted:latest
```

[UpRooted](https://github.com/bbkr/UpRooted) is written in [Raku](https://www.raku.org/) so create `example.raku` file in this directory with following content (remember to adjust connection credentials):

```perl
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:

```bash
raku example.raku
```

Your user will be saved to `out.sql` file in `/Users/me/uprooted` directory.

## More info

I strongly recommend reading [UpRooted](https://github.com/bbkr/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 :)