Working with Serverless Open Source Databases in Eyevinn Open Source Cloud

Eyevinn Open Source Cloud reduces the barrier to open source while contributing to a sustainable model for open source by giving back a share of the revenue to the creator.

In this blog post we will walk through the different types of databases you have available at your fingertips without having to host the database servers yourself, and how easy it is to enable database support in your application.

In this guide

1. Get an API Access Token and setup project
2. Insert document in a NoSQL document database
3. Setup a messaging queue for your application
4. Setup a database for real-time data warehousing

Prerequisites

• An Eyevinn Open Source Cloud account. Sign up here for free.

Get an API Access Token and setup project

In the Eyevinn Open Source Cloud web console click on "Settings" in the navigation menu on the left. Then click on the tab "API" and copy the personal access token to the clipboard.

Store this token in your shell's environment in the environment variable OSC_ACCESS_TOKEN.

% export OSC_ACCESS_TOKEN=<access-token-copied-above>

Setup a NodeJS project.

% mkdir db
% cd db
% npm init

Install the Javascript client SDK.

% npm install --save @osaas/client-core @osaas/client-db

Insert a document in a NoSQL database

As a first example we will setup and connect to a NoSQL database, and then insert a document.

Create a file called nosql.js and then add the following code to setup a CouchDB instance in Eyevinn Open Source Cloud with the name "nosql" if it does not already exists.

const { setupDatabase } = require("@osaas/client-db");
const nano = require('nano');

async function main() {
  const dbUrl = await setupDatabase("couchdb", "nosql", { rootPassword: 'secret' });
  const client = nano(dbUrl.toString());
  const dbList = await client.db.list();
  if (!dbList.includes('mydb')) {
    await client.db.create('mydb');
  }

  const db = client.use('mydb');
  await db.insert({
    title: 'Working with databases in Eyevinn OSC is a blast!'
  });  
}

main();

Once the database instance is created it will use the CouchDB javascript library to connect to the database, check whether a database called mydb exists and create if it does not exist already.

Then it will select this database and insert a JSON document. Before we can run this script we need to install the CouchDB javascript library.

% npm install --save nano

Then we can run the script.

% node nosql.js

Once completed we can verify this by navigating to the CouchDB service in Eyevinn Open Source Cloud web console and clicking on the instance card named nosql.

This was all you had to do to get started to store documents in a NoSQL database in your application, and the database is already deployed and available for production use.

Setup a messaging queue for your application

In the second example we will use Valkey in Eyevinn Open Source Cloud to setup a publish / subscribe messaging queue. Let us start with the subscriber part.

Create a file called subscriber.js and then add the following code to setup a Valkey instance and a client waiting for messages on a queue called messages.

const { setupDatabase } = require("@osaas/client-db");
const Redis = require('ioredis');

async function main() {
  const redisUrl = await setupDatabase("valkey", "messagequeue", {});
  console.log(redisUrl.toString());

  const client = new Redis(redisUrl.toString());
  await client.subscribe('messages');
  console.log('Waiting for messages...');
  client.on('message', (channel, message) => {
    console.log(`Received message: ${message} from ${channel}`);
  });
}

main();

Before we can run this script we need to install a Redis client library.

% npm install --save ioredis

Then we can run the subscriber.

% node subscriber.js
redis://default@172.232.131.169:10518
Waiting for messages...

Now we can move on writing an application that publish a message on this queue. Create a file called publisher.js and insert this code.

const { setupDatabase } = require("@osaas/client-db");
const Redis = require('ioredis');

async function main() {
  const redisUrl = await setupDatabase("valkey", "messagequeue", {});
  console.log(redisUrl.toString());
  const client = new Redis(redisUrl.toString());
  try {
    const receivedCount = await client.publish('messages', 'Working with Eyevinn OSC is a blast!');
    console.log(`Sent message to ${receivedCount} subscribers`);
  } catch (err) {
    console.error(err);
  } finally {
    await client.quit();
  }
}

main();

Then when we run this script we get.

% node publisher.js
redis://default@172.232.131.169:10518
Sent message to 1 subscribers

And on the subscriber application side we will get.

Waiting for messages...
Received message: Working with Eyevinn OSC is a blast! from messages

Setup a database for real-time data warehousing

In the final example we will setup a database that is suitable for working with large datasets. For this we will use ClickHouse that is available as an open web service.

Create a file called warehouse.js and insert the following code.

const { setupDatabase } = require("@osaas/client-db");
const { createClient } = require('@clickhouse/client');

async function main() {
  const dbUrl = await setupDatabase('clickhouse', 'warehouse', { password: 'secret' });
  console.log(dbUrl.toString());
  const client = createClient({
    url: dbUrl.toString()
  });
  await client.command({
    query: `
  CREATE OR REPLACE TABLE trips
  (
    trip_id UInt32,
    vendor_id Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    pickup_date Date,
    pickup_datetime DateTime,
    dropoff_date Date,
    dropoff_datetime DateTime,
    store_and_fwd_flag UInt8,
    rate_code_id UInt8,
    pickup_longitude Float64,
    pickup_latitude Float64,
    dropoff_longitude Float64,
    dropoff_latitude Float64,
    passenger_count UInt8,
    trip_distance Float64,
    fare_amount Float32,
    extra Float32,
    mta_tax Float32,
    tip_amount Float32,
    tolls_amount Float32,
    ehail_fee Float32,
    improvement_surcharge Float32,
    total_amount Float32,
    payment_type Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    trip_type UInt8,
    pickup FixedString(25),
    dropoff FixedString(25),
    cab_type Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    pickup_nyct2010_gid Int8,
    pickup_ctlabel Float32,
    pickup_borocode Int8,
    pickup_ct2010 String,
    pickup_boroct2010 String,
    pickup_cdeligibil String,
    pickup_ntacode FixedString(4),
    pickup_ntaname String,
    pickup_puma UInt16,
    dropoff_nyct2010_gid UInt8,
    dropoff_ctlabel Float32,
    dropoff_borocode UInt8,
    dropoff_ct2010 String,
    dropoff_boroct2010 String,
    dropoff_cdeligibil String,
    dropoff_ntacode FixedString(4),
    dropoff_ntaname String,
    dropoff_puma UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime;
    `
  });
  await client.command({
    query: `
INSERT INTO trips
SELECT * FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/trips_{1..2}.gz',
    'TabSeparatedWithNames', "
    trip_id UInt32,
    vendor_id Enum8('1' = 1, '2' = 2, '3' = 3, '4' = 4, 'CMT' = 5, 'VTS' = 6, 'DDS' = 7, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14, '' = 15),
    pickup_date Date,
    pickup_datetime DateTime,
    dropoff_date Date,
    dropoff_datetime DateTime,
    store_and_fwd_flag UInt8,
    rate_code_id UInt8,
    pickup_longitude Float64,
    pickup_latitude Float64,
    dropoff_longitude Float64,
    dropoff_latitude Float64,
    passenger_count UInt8,
    trip_distance Float64,
    fare_amount Float32,
    extra Float32,
    mta_tax Float32,
    tip_amount Float32,
    tolls_amount Float32,
    ehail_fee Float32,
    improvement_surcharge Float32,
    total_amount Float32,
    payment_type Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4),
    trip_type UInt8,
    pickup FixedString(25),
    dropoff FixedString(25),
    cab_type Enum8('yellow' = 1, 'green' = 2, 'uber' = 3),
    pickup_nyct2010_gid Int8,
    pickup_ctlabel Float32,
    pickup_borocode Int8,
    pickup_ct2010 String,
    pickup_boroct2010 String,
    pickup_cdeligibil String,
    pickup_ntacode FixedString(4),
    pickup_ntaname String,
    pickup_puma UInt16,
    dropoff_nyct2010_gid UInt8,
    dropoff_ctlabel Float32,
    dropoff_borocode UInt8,
    dropoff_ct2010 String,
    dropoff_boroct2010 String,
    dropoff_cdeligibil String,
    dropoff_ntacode FixedString(4),
    dropoff_ntaname String,
    dropoff_puma UInt16
") SETTINGS input_format_try_infer_datetimes = 0    
`
  });
}

main();

When we run this script we will create a ClickHouse database, a table for Taxi trips and insert some sample data in that table.

Install the ClickHouse javascript client.

% npm install --save @clickhouse/client

Then run our script.

% node warehouse.js
https://default:[e-mail]/

We can now verify that we have inserted records in the table by going to the database web user interface available at https://eyevinnlab-warehouse.clickhouse-clickhouse.auto.prod.osaas.io/play or by clicking on the database instance card in the Eyevinn Open Source Cloud web console.

Let us count how many trips we have in the table.

We could then compute the average cost based on the number of passengers.

Conclusion

Now we have given you some examples of how quickly you can enabling database support in your application without having to worry about the deployment and infrastructure for it. Available as open web services in Eyevinn Open Source Cloud you are not locked in with a specific cloud vendor as it is based on open source.