Aiven

In This Example

Create and connect to an enterprise grade SQL Database hosted by Aiven.

Do you want direct access to a database that is managed but gives you full control? If yes, this is for you!

Prerequisites

You will need:

• An Aiven account

Aiven Database Setup

tip

This example uses a mySQL database. It will work equally well with PostgreSQL, just tweak the SQL accordingly.

Log into Aiven and create a project and database of your choice. Aiven has a generous free tier for their SQL databases and upgrade to paid databases with high availability is seamless.

Get Your Connection Details

Find your connection details in Aiven:

Then set your Database connection info in the Comnoco Workspace Configuration panel. Remember items in this screen allow you to set different values per environment eg. development and production.

To do this:

1. First set your Database Password as a Secret
2. Use the secret in a new connection which you need to populate with your details from Aiven. See here for connection settings.

tip

To manage your database tables and records outside of Comnoco, use an app like Beekeeper Studio which has an open source version available via GitHub.

Use Your Connection

To use your connection, you'll need to set up a Comnoco Function to talk to your database. We're going to create two functions so that you can use the first one, once only, to set up some tables and data.

You can follow the instructions below and build as you go, or you can download the 📦 example and import it into your Comnoco Workspace (if you do this, make sure you name your connection in the above steps Aiven-mysql so that it matches what is in the example).

Use Comnoco to Set Up a Database

Let's look at how to create a function that you would run only once to create some sample tables and data to play around with.

1. Create a Comnoco Function Collection file
2. Add a Component Function to that file
3. In the logic section of your tree add a SQL Database Connect and Validate block, and in it's properties select the database connection you just set up. This block opens a connection - if the connection fails, it will return an error (that you can choose to catch to control what you do if the database is unavailable).
4. Next, we'll setup a variable Data structure in Internal Variables to receive a response from the database and use a Set Data Structure to fill it with a SQL Database Execute (which we'll setup next).
5. And now we're ready to play with SQL. Let's set up our database, on the SQL Database Execute block, chose your connection name and then copy the SQL below and paste it into a Text block that you place on the Query slot. You can now go ahead and run your function:

Sample SQL Schema (for mySQL)

Let's create a simple database schema themed around Formula 1 drivers and their teams. The database will consist of three tables:

1. drivers - This table will store information about F1 drivers.
2. teams - This table will store information about F1 teams.
3. driver_standings - This table will record the standings of drivers for different seasons.

Here is a basic schema for each of the tables followed by sample data:

info

You will need to add &multiStatements=true to your database connection Additional Arguments in order to allow Comnoco to run multiple statements in one go.

-- Creating the 'drivers' table
CREATE TABLE drivers (
    driver_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    nationality VARCHAR(50),
    dob DATE
);

-- Creating the teams table
CREATE TABLE teams (
    team_id INT AUTO_INCREMENT PRIMARY KEY,
    team_name VARCHAR(100),
    base VARCHAR(100),
    team_chief VARCHAR(100),
    technical_chief VARCHAR(100),
    engine_supplier VARCHAR(100)
);

-- Creating the driver_standings table
CREATE TABLE driver_standings (
    standing_id INT AUTO_INCREMENT PRIMARY KEY,
    driver_id INT,
    team_id INT,
    season YEAR,
    points INT,
    wins INT,
    FOREIGN KEY (driver_id) REFERENCES drivers (driver_id),
    FOREIGN KEY (team_id) REFERENCES teams (team_id)
);

-- Inserting data into 'drivers'
INSERT INTO drivers (first_name, last_name, nationality, dob) VALUES
('Michael', 'Schumacher', 'German', '1969-01-03'),
('Ayrton', 'Senna', 'Brazilian', '1960-03-21'),
('Fernando', 'Alonso', 'Spanish', '1981-07-29'),
('Lewis', 'Hamilton', 'British', '1985-01-07'),
('Sebastian', 'Vettel', 'German', '1987-07-03');

-- Inserting data into 'teams'
INSERT INTO teams (team_name, base, team_chief, technical_chief, engine_supplier) VALUES
('Ferrari', 'Maranello, Italy', 'Jean Todt', 'Enrico Cardile', 'Ferrari'),
('McLaren', 'Woking, UK', 'Ron Dennis', 'James Key', 'Mercedes'),
('Mercedes', 'Brackley, UK', 'Toto Wolff', 'Mike Elliott', 'Mercedes'),
('Renault', 'Enstone, UK', 'Flavio Briatore', 'Pat Fry', 'Renault'),
('Red Bull Racing', 'Milton Keynes, UK', 'Christian Horner', 'Adrian Newey', 'Renault');

-- Inserting data into 'driver_standings'
INSERT INTO driver_standings (driver_id, team_id, season, points, wins) VALUES
(1, 1, 2004, 148, 13),
(2, 2, 1991, 96, 7),
(3, 3, 2020, 347, 11),
(4, 4, 2005, 133, 7),
(4, 4, 2006, 134, 7),
(5, 5, 2010, 256, 5), 
(5, 5, 2013, 397, 13);

6. You should see a green tick next to the function that you ran which indicates it worked. You'll now have some tables and data in your new database that you can play with.

tip

Though some database operations don't return records, it is best practice to put the SQL Database Execute block inside a Set Data Structure incase they do.

Database Queries (CRUD examples)

Now, let's create an example function that performs some queries on our database:

info

This section covers the basics of database operations, you will need to use some Comnoco magic 🪄 (loops, references, dynamic input data etc.) to program your process how you want.

1. Right click on the the first function we built and select Duplicate.
2. Rename the new function to Example Queries
3. Duplicate the Internal Variable three times and name each of them the following in turn: Data Structure - Create, Data Structure - Read, Data Structure - Update, Data Structure - Delete. Your function will now look like this:

Now, for each of these, let's set up the Comnoco blocks:

Create (Insert)

Add a new a person to our drivers table.

To do this, we'll change the SQL Database Execute block that we duplicated. Expand your tree to find it under the Set Data Structure (Which you should rename to Create) then:

1. Delete the Text block that contained our SQL. In it's place put a SQL Database Safe Operation block.
2. In the Query Template slot, add a Text block and fill it with the below SQL:

INSERT INTO drivers (first_name, last_name, nationality, dob) VALUES (?, ?, ?, ?);

3. Now in the Arguments list slot, add the input parameters as Text with values you'd like to add, like this:
4. You can now run your Example Queries function and it will add this record to your drivers table. It will give you a green tick to show it has worked.

Read (Query)

Select all information about a driver and their total career points by passing in a drivers Last Name.

Let's move onto querying (reading) from our database:

1. Duplicate your Create Set Data Structure block and rename it to Read.
2. In the Query Template slot, change the content of the Text block to the below SQL:

SELECT d.first_name, d.last_name, SUM(ds.points) AS total_points
FROM drivers d
JOIN driver_standings ds ON d.driver_id = ds.driver_id
WHERE d.last_name = ?
GROUP BY d.driver_id;

3. Delete your unneeded Arguments leaving just last_name and change it's value to Alonso (that will return a good result).
4. Change the data structure that it sets the result to from the Create to Read one (you can right click and select replace).
5. Now you can run your Example Queries function and it will run both the Create and Read actions. We just set up the Read so to see the result click on your Data Structure - Read internal variable. It should look like this:

Other Examples

Select all teams and the number of wins they've achieved.

SELECT t.team_name, SUM(ds.wins) AS total_wins
FROM teams t
JOIN driver_standings ds ON t.team_id = ds.team_id
WHERE t.team_name = ?
GROUP BY t.team_id;

Select all drivers with their respective teams for a particular season (e.g., 2004).

SELECT d.first_name, d.last_name, t.team_name, ds.season, ds.points, ds.wins
FROM drivers d
JOIN driver_standings ds ON d.driver_id = ds.driver_id
JOIN teams t ON ds.team_id = t.team_id
WHERE ds.season = ?;

Update

To see how to update a record:
Follow the same steps we took for Read but with the below sql and two parameters of last_name driver_id (eg. setting it to 2 will allow you to over write Senna with a last_name you choose).

UPDATE drivers SET last_name = ? WHERE driver_id = ?;

Delete

To see how to delete a record:
Follow the same steps we took for Read but with the below sql and one parameter of driver_id (eg. setting it to 1 will allow you to delete Schumacher).

DELETE FROM drivers WHERE driver_id = ?;

---

Your final Function Collection should look like this: