This tutorial will show you how to run Gallium Data with MySQL on your machine. Nothing will be installed permanently, it’s all done with Docker containers, so you can throw everything away when you’re done.

There are several versions of this tutorial: 

The concepts are the same, pick the one you're most familiar with.

This tutorial will take about 10 minutes, depending on your download speed.

Step 1: Docker is required

For this tutorial, we will require Docker. To verify that Docker is indeed available:

run the following command from a command line:

docker version

You should see some output similar to this:

Client: Docker Engine - Community
Cloud integration  1.0.33
Version:           24.0.2
API version:       1.43
etc...

The exact version numbers are not important -- the important thing is that Docker needs to be running. 

If you get an error message, you'll need to get Docker up and running before you can continue with this tutorial. Fortunately, there are lots of resources that can help you with that.

We’ll be starting two Docker containers — there are easier ways of doing this using e.g. Docker Compose or Kubernetes, but for this tutorial we want to make sure every component is visible and clearly understood.

We’ll be running both containers in their own Docker network, so let’s create that network

Run this from a command line:

docker network create gallium-net

The response should be a long string of letters and numbers, which you can safely ignore, something like:

9ef282f6d3cce819a etc...

If you get an error, it may be because you've run another Gallium Data tutorial before, in which case you can ignore it.

Docker is now ready, let’s move on to the next step.

Step 2: Start MySQL and Gallium Data

If this is your first time running this tutorial, note that it will download about 800MB of container images, which can take a while on slower connections.

1 - Start the MySQL database server

Run this from a command line:

docker run -d --rm --name gallium-mysql --network gallium-net galliumdata/mysql-demo:22

This may take a minute as Docker downloads the image and starts it up. This image is simply the standard MySQL image, preloaded with a small sample database

There is nothing special about this image:  Gallium Data can run with any MySQL database.


2 - Start Gallium Data

Run this from a command line:

docker run -d --rm --name gallium-data --network gallium-net -p 8089:8080 -e repository_location=/galliumdata/repo_mysql galliumdata/gallium-data-engine:1.8.8-1999

Again, this may take a minute. This is the standard Gallium Data image, with a demo repository, which is set up for this tutorial. In the real world, you will typically use additional options to create your own repository.

Step 3: Take a look at the database

Run this from a command line:

docker exec -it gallium-mysql mysql -h gallium-data -D gallium_demo -u root

Note: if this does not work right away, give it a minute, sometimes MySQL can take a little while to start.

You are now connected to the MySQL database through Gallium Data, which is going to allow us to do some interesting things.

Run this from  the MySQL command line:

select * from products;

You will see a list of products:

mysql> select * from products;

+----+-------------------------+------------+-----------+--------------+

| id | name                    | list_price | category  | status       |

+----+-------------------------+------------+-----------+--------------+

|  1 | Art Deco lamp           |      99.99 | Furniture | NULL         |

|  2 | Ballroom gown           |     549.99 | Clothing  | discontinued |

|  3 | Callipers               |       4.50 | Tools     | NULL         |

|  4 | Drapes                  |      46.15 | Furniture | back-ordered |

|  5 | Elixir of youth         |    2499.99 | Pharmacy  | NULL         |

      [...21 more rows here...]

+----+-------------------------+------------+-----------+--------------+

26 rows in set (0.00 sec)

Notice that some products have a status of discontinued -- this is about to become relevant.

Everything is now in place, let's see Gallium Data in action.

Step 4: Let's change how the database works

A simple demonstration of the power of Gallium Data can be seen with just a basic filter that will modify certain types of requests. 

⇨ Connect to Gallium Data at: http://127.0.0.1:8089

⇨ Log in

⇨ Open the project named Simple Demo - MySQL

⇨ Expand the Request filters area

⇨ Open the filter named Filter out discontinued products

Note that its parameters are set so that it executes whenever a query is run agains the products table.

⇨ Select the Code tab

Take a quick look at the code: it simply rewrites the query to exclude discontinued products.

⇨ Select the Active checkbox

⇨ Click Publish (top)

⇨ Go back to the MySQL command line and re-run the query (use up-arrow)

Note that all discontinued products have now disappeared. You have changed the behavior of the database without changing either the database or the database client.

Feel free to experiment with the code in the Gallium Data filter. Any time you make a change, you'll need to click Publish (top right) to deploy your changes.  For instance, a simple-minded way to change the limit to all queries might be:

let sql = context.packet.query;
let re = /\blimit\s+\d+,\s*\d+/gim;
let match = re.exec(sql);
if (match) {
    context.packet.query = sql.substring(0, match.index) + "limit 0,10" +
            sql.substring(match.index + match[0].length);
    log.debug("Changed limit in SQL: " + context.packet.query);
}

We can also reject queries based on any desired condition, for instance:

if (new Date().getDay() === 0) {  // 0 == Sunday, 1 == Monday, etc...
    context.result.success = false;
    context.result.errorMessage = "You can't query products on Sunday";

    context.result.errorCode = 12345;

    log.debug("Rejecting query because it's Sunday");
}

Any changes take effect as soon as you hit the Publish button. You can use the log object to print out debugging messages, which you can see in the Logs page of Gallium Data, or by running the following in a command line:

docker logs -f gallium-data

Hit ctrl-c to regain control of the command line when you're done.

Step 5: Changing data

Now let’s make Gallium Data modify data that's coming back from the MySQL server.

Run this from  the MySQL command line:

select * from customers;

You will see a number of customers:

mysql> select * from customers;

+----+-----------------------+------------------------------+---------+

| id | first_name            | last_name                    | country |

+----+-----------------------+------------------------------+---------+

|  1 | Andrea                | Albinioni                    | AR      |

|  2 | Bruno                 | Botticelli                   | BE      |

|  3 | Charles               | Corleone                     | CA      |

|  4 | Daniella              | Durango                      | DK      |

|  5 | Eric                  | Edmunds                      | ES      |

 [ ... 28 more rows here ... ]

+----+-----------------------+------------------------------+---------+

33 rows in set (0.00 sec)


Hint: if you see some weird characters in the last rows, try set character set 'utf8';

Let's say we don't want our database users to see the full first name of the customers : we can easily make that happen with a response filter.

⇨ Go back to the Gallium Data admin app
⇨  Expand the Response filters area
⇨ Click on the response filter called Show customers initials 

You'll see that it has some parameters that activate this filter for result sets that include data from the gallium_demo.customers table.

⇨ Click on the Code tab

The code is a single line of code, which replaces the value for the first_name column with its initial.

⇨ Select the Active checkbox

⇨ Click Publish (top)

⇨ Go back to the MySQL command line and re-run the query (use up-arrow)

Note that you now only get the initials for all customers.

This is obviously a simple example -- we could get very fancy here. We could change data depending on any number of factors, we could generate random data, we could hide certain rows and/or columns, we could even insert rows into the result set. We have complete control, and we can make the database behave in ways that would normally be almost impossible, all without any changes to the database server or the database client.

A few things to try at this point, if you feel like it:

Hide a row using the remove() method:

if (context.packet.country === 'DK') {
  context.packet.remove();
}

If you deploy this code and re-run your query, you will see that customer 4 (Daniella Durango) no longer appears in the result set. This type of simple hiding can also be done without code, just with the configuration parameters of the filter, but you get more flexibility with code.


Insert a synthetic row:

if (context.packet.id === 2) {

    let newRow = context.packet.clone();

    newRow.id = -2;

    newRow.first_name = 'Gallium';

    newRow.last_name = "Data is fun!";

    // Insert before the original row

    newRow.sequenceId--;

    context.packets.addPacket(0, newRow);

}

If you deploy this code and re-run your query, you will see a new row appear before customer 2. Again, no changes are made to the database, only to the result set received by the database client.

What have we seen?

In this tutorial, you got a glimpse of how Gallium Data can intercept the traffic between database client and database server, and modify this traffic. This enables you to:

Gallium Data has a number of pre-defined filters, but it also makes it easy to create your own filters and be as sophisticated as you want.

Now, the question is: how will you use it?

What to do next

We encourage you to take Gallium Data for a spin with your own database(s). It's always more interesting to work with your own data than with demo data.

The tutorial project contains several other filters, but they are not active. You can take a look at them and try to activate them:

docker logs -f gallium-data

Gallium Data is free for end users, so you can use it as much as you want, on your machines, servers, in the cloud, wherever, as long as you do not redistribute it (you'll need a license for that).

Consult the documentation for all the gritty details, such as how to use the debugger, or the API for various types of database packets.

Cleanup

Once you're done with this tutorial, and you want to remove everything that was installed,

⇨ Exit from the MySQL command line with ctrl-D or quit

⇨ Execute the following commands from a command line:

docker stop gallium-mysql
docker stop gallium-data
docker network rm gallium-net

This will stop all the Docker containers started during this tutorial.

If you also want to remove the Docker images:

docker rmi galliumdata/gallium-data-engine:1.8.8-1999
docker rmi galliumdata/mysql-demo:22

This will remove everything installed by this tutorial.

We'd love to hear from you -- good or bad! Please drop us an email and let us know if you have any questions or comments.


feedback at gallium data dot com