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

There are several versions of this tutorial: 

• this one, which uses Microsoft SQL Server as a database,

• an equivalent tutorial that uses MySQL as a database,

• one for PostgreSQL,

• one for IBM DB2,

• one for MongoDB,

• one for Cassandra,

• one for Redis,

• and one for Vertica.

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

This tutorial will take about 10 minutes, depending on your download speed. It can be run on any Intel/AMD machine that runs Docker.

You can also watch this tutorial as a (slightly outdated) video (below, 5 minutes 23 seconds).

You should see some output similar to this:

Client:
  Version:           27.4.0
  API version:       1.47
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 three 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 all containers in their own Docker network, so let’s create that network. 

⇨ Run the following command:

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 the error and carry on.

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

Step 2: Start the three containers

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

1 - Start the SQL Server database 

This may take a minute as Docker downloads the image and starts it up. 

This image is simply the standard SQL Server image from Microsoft, preloaded with a small sample database. This happens to be the Linux version of SQL Server 2022, but Gallium Data works equally well with the Windows versions of SQL Server.

There is nothing special about this image:  Gallium Data can run with any SQL Server database (2012 and above), including in the cloud (AWS, Azure, etc...)

2 - Start Gallium Data

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.

3 - Start the database client

Finally we’ll also need a database client. Here we’ll be using DBGate, but any other SQL Server client would work equally well (like Microsoft's SQL tools, or SQL Server Management Studio if you're on Windows). If you want to use one of these other tools, though, you will have to expose port 1433 in the Gallium Data container.

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 - SQL Server

⇨ Expand the Request filters area

⇨ Open the filter named Filter out discontinued products

Note that the parameters are set so that it executes whenever a simple query is run agains the products table. As it turns out, DBGate executes SQL statements via a stored procedure (sp_executesql), which is not unusual with SQL Server. If it was just using a straight SQL call, we could filter that too (there is a filter called Change SQL to hide some products already defined but disabled -- take a look if you're interested).

⇨ Select the Code tab

The code simply rewrites the query to exclude discontinued products.

⇨ Select the Active checkbox

⇨ Click Publish (top)

⇨ Go back to DBGate and refresh the list of products (select another table, then products)

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. Any time you make a change, you'll need to click Publish (top right) to deploy your changes.  For instance, you can hide the value of the status column with:

let param = context.packet.parameters[0];
let regexp = /\[basetbl\].\[status\] AS \[status\]/g;
if (param.value.match(regexp)) {
    param.value = param.value.replace(regexp, "'??' as status");
    log.debug("Edited SQL to: " + param.value);
}

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");
}
// Caution: JavaScript may return a UTC date

Any changes in Gallium Data 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 gallium-data

You can also use the debugger to stop execution and step through your code.

Nothing has changed in the database -- we're only transforming the data as it goes from SQL Server to the database client.

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 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 in the filter 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 by setting 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 = 'Synthetic';
  context.packets.addPacket(newRow);
}

If you deploy this code and re-run your query, you will see a new row appear after 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:

• change the behavior of existing applications and databases without changing either the application or the database

• catch unwanted or inefficient requests and change or reject them

• tailor database responses to your exact needs, with a level of precision that would be almost impossible otherwise

• monitor all traffic to and from the databases and react to whatever events are relevant to you

• have a control point in front of your databases, which can run customized logic for your specific needs

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:

• Time of day connection filter

• Various logging filters showing requests and/or responses -- see the output in the Logs page of Gallium Data, or with:

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.