Tutorial - continued

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.

⇨ Go to the pgAdmin tab or window
⇨ Open the gallium server in the top left
⇨ Expand Databases
⇨ Select the postgres node

Click the Query Tool icon at the top:

⇨ Enter the query:

select * from gallium_demo.products

⇨ Hit F5 to run it

You should see a few rows appear at the bottom of the page. This data is coming from Postgres, through Gallium Data, but this query has not triggered any filter yet. So far so good.

Notice that some of the products have a status of "discontinued".

Let's assume that we don't want our database clients to see those products.

⇨ Go to the Gallium Data admin app at http://localhost:8089/web/index.html
⇨ Log in
⇨ Click the Simple Demo - PostgreSQL project
⇨ Click the request filter called Filter out discontinued products
⇨ Activate this filter by clicking the Active checkbox
⇨ Click the Publish button (top right)

⇨ Click on the Code tab at the top

Take a look at the JavaScript code: it does a simple substitution of any incoming SQL queries for the products table by changing:
gallium_demo.products
to:
(select * from gallium_demo.products
where status is null or
status <> 'discontinued' ) prods
This is not fool-proof, but it will work fine for our purpose.

⇨ Go back to pgAdmin
⇨ Re-run your query (F5)

This time you won't see the discontinued products.

You have just changed how the database behaves, without changing either the database server 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, a simple-minded way to add a limit to all queries might be:

var sql = context.packet.getQuery();
if ( !sql.includes("limit")) {
log.debug("Adding a limit to the query");
context.packet.setQuery(sql + " limit 10");
}

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";
}

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 Postgres server.

⇨ In pgAdmin, run the following query:

select * from gallium_demo.customers

You'll see a few customer rows.

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

⇨ Go back to the Gallium Data admin app
⇨ Click on the response filter called Modify customers
You'll see that it has some parameters:

These parameters specify that this filter only applies to rows that belong to the gallium_demo.customers table.

Now take a look at the JavaScript code for that filter:

log.info("Response filter running");
context.packet.first_name = context.packet.first_name.substring(0,1) + ".";

We just replace the value in the first_name column with its first letter, followed by a period.

⇨ Activate this filter by clicking the Active checkbox
⇨ Click the Publish button (top right)
⇨ Go back to pgAdmin
⇨ Re-run your query

This time, the result will be different : the first_name column will contain just the customer's initial instead of the full first name that is actually stored in the database. We have just changed how the database responds to a query. Note that nothing has changed in the database -- we are simply modifying the result set as it goes from the database 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 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 because she's in Denmark. This type of simple hiding can also be done without code, just with the configuration parameters of the filter.

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.

Continue this tutorial