DEV Community: ScyllaDB

Database 101: SSL/TLS for beginners

Daniel Reis — Fri, 04 Oct 2024 15:33:01 +0000

Once again, I'm bringing some knowledge from my day-to-day work as a Developer Advocate at ScyllaDB, and this time I'm going to teach you about secure and encrypted connections ! It's a topic that can cause a lot of anxiety for newbies, but I'm going to make it easier for you.

If you're just getting started with databases in general or databases in particular, you might want to start by reading my first article, Database 101: Data Consistency for Beginners.

This article captures my own exploration of how many database paradigms exist as I look far beyond my previous experience with just SQL and MySQL. I'm keeping track of my studies in this Database 101 series.

1. Prologue
2. What is SSL and TLS?
3. OpenSSL for Noobs
4. Database Authentication
- 4.1 No Authentication
- 4.2 Username/Password Authentication
- 4.3 Creating Users and Roles
- 4.4 Certificated / Role Certificated Authentication
  - 4.4.1 Change the Authentication Type
  - 4.4.2 Enable the Encryption Port
  - 4.4.3 Enable the Client Encryption Port
5. Testing Encrypted Connections
6. Conclusion

1. Prologue

Working on a NoSQL database is challenging in the sense that I have to learn stuff that I'd NEVER touch or study as a regular web developer. I mean, mostly you build a CRUD application, try your best not to screw up the database indexes (right guys ??????) and paint a few buttons.

But if you've been following my Database 101 journey, you probably know that I came to Scylla knowing NOTHING about databases except what MySQL was and how to build things with Laravel, and now I'm into it:

Building Highly Scalable Applications with ScyllaDB
Still learning Rust - 1 year and 6 months (and I feel like I know shit about it).
Started learning ShellScript, Go, Python, JavaScript, and anything that has a ScyllaDB driver
Learned the basics of observability using Grafana/Prometheus
and my new friend Transport Layer Security (a.k.a. TLS)

If you're setting up your own servers, you've probably already used certbot to install certificates to run HTTPS smoothly, and that's what I've been doing for the last 6~7 years. To be really honest, knowing how to use a tool is what we developers do.

It even seems like an omen, but I'm working on a browser extension whose backend is written in Rust, and I didn't manage to use certbot there, so I had to create my certificate using weird commands and add a weird openssl crate to my project and trust that it would work:

let mut certs_file = BufReader::new(File::open(cert_path).unwrap());
let mut key_file = BufReader::new(File::open(key_path).unwrap());

// load TLS certs and key to create a self-signed temporary cert for testing:
// `openssl req -x509 -newkey rsa:4096 -nodes -keyout key.pem -out cert.pem -days 365 -subj '/CN=localhost'`
let tls_certs = rustls_pemfile::certs(&mut certs_file)
    .collect::<Result<Vec<_>, _>>()
    .unwrap();
let tls_key = rustls_pemfile::pkcs8_private_keys(&mut key_file)
    .next()
    .unwrap()
    .unwrap();

After a few hours of trying/catching, I finally managed to deploy the API with the certificate, but I didn't learn anything about it. THEN, a few days later, my boss asked me to extend the ScyllaDB Security Pages and learn how Certificated Based Connection works, since I'd never used it.

I spent 10 hours on it and managed to get it to work, but mostly it wasn't about the documentation, it was about my lack of knowledge in this area, which is no longer a problem and I will teach you everything I've learned so far!

2. What is SSL and TLS?

TLDR: this is that boring section which you can skip if you want.

SSL (Secure Sockets Layer) was introduced in 1995 by Netscape to provide privacy, authentication, and data integrity for internet communications, primarily in browsers. TLS (Transport Layer Security), which succeeded SSL, is now the standard security protocol used across the internet for applications like email, messaging, and VoIP.

Without SSL/TLS, data transmitted between a client and a server is unencrypted and vulnerable to interception and misuse, making it an easy target for phishing and other attacks. SSL/TLS ensures that a secure "handshake" occurs before data transmission, meaning that all data is encrypted for that specific session.

However, even though SSL/TLS provides encryption, it doesn’t guarantee that the server itself is fully trustworthy or secure. SSL, being deprecated since 1996 due to its vulnerabilities, is still often referenced alongside TLS by developers as "SSL/TLS" because of its historical significance.

With this understanding of SSL/TLS, let’s now dive into the tools that help manage secure connections.

3. OpenSSL for Noobs

There are many data encryption toolkits available today, but we're going to talk about a very specific one: the openssl-library.org.

This library, which had its first release in 1998, is attached to every single tutorial on data encryption, and is used by many other tools around the open/closed source environment - such as Let's Encrypt and Certbot - and the one we're going to use here.

The purpose of this tool is to create keychains and credentials to authorize someone to do whatever you want.

Imagine you work in a commercial building where you have to show your badge to security to get in:

Your company creates a "master key" and generates a certificate for it.
You also generate a key and certificate for the security guard and give them to him along with a list of all the employee's certificates.
The guard has a truststore keychain with all sorts of tags on it.
When you arrive at the entrance, he will check that your tag has the same root certificate as his to decide whether you can pass or not.

In matter of commands, we did something like this:

# Creating the root/master key and certificate
# -----
openssl genpkey -algorithm RSA -out root_key.pem -pkeyopt rsa_keygen_bits:2048 # Generate the root key
openssl req -x509 -new -key root_key.pem -days 3650 -out root_cert.pem -subj "/CN=administrator" # Generate the root certificate
# -----

# Creating the Employee Key and Certificate
# -----
openssl genpkey -algorithm RSA -out employee_key.pem -pkeyopt rsa_keygen_bits:2048 # Generate the employee base key
openssl req -new -key employee_key.pem -out employee.csr -subj "/CN=employee" # Generate the employee CSR
openssl x509 -req -in employee.csr -CA root_cert.pem -CAkey root_key.pem -CAcreateserial -out employee_cert.pem -days 365 # Sign and generate the employee certificate with the root/master key and certificate
cat root_cert.pem employee_cert.pem > employee_truststore.pem # Create a truststore (tag) to be used together with the key.
# -----

# Creating the Security (server) Key and Certificate
# -----
openssl genpkey -algorithm RSA -out security_key.pem -pkeyopt rsa_keygen_bits:2048 # Generate the security key
openssl req -new -key security_key.pem -out security.csr -subj "/CN=server" # Generate the security CSR
openssl x509 -req -in security.csr -CA root_cert.pem -CAkey root_key.pem -CAcreateserial -out security_cert.pem -days 365 # Sign and generate the security certificate with the root/master key and certificate
cat root_cert.pem security_cert.pem > security_truststore.pem # Create a truststore (tag) to be used together with the key.
# -----


# Creating the Malicious Root and Client Key and Certificate
# -----
openssl genpkey -algorithm RSA -out malicious_key.pem -pkeyopt rsa_keygen_bits:2048 # Generate the malicious root key
openssl req -x509 -new -key malicious_key.pem -days 3650 -out malicious_cert.pem -subj "/CN=fake-admin" # Generate the malicious root certificate
cat root_cert.pem malicious_cert.pem > malicious_truststore.pem # Combine root and malicious root certificates into a truststore
# -----

There are too many commands, but basically this is what happened:

A root_key.pem and root_cert.pem were generated.
These keys and certificates were used to sign other keys and certificates for employees:
- security_key.pem was signed by root_key.pem.
- employee_key.pem was signed by root_key.pem.
- server_key.pem has been signed by root_key.pem.
All these keys are in a file called security_truststore.pem.
Someone tried to create a fake key called malicious_key.pem using the root_cert.pem.

Now let's do some validation. Theoretically, only the keys in security_truststore.pem should be allowed to join:

# Validating the certificates with the truststores
# ----- 
openssl verify -CAfile security_truststore.pem employee_cert.pem # Check if the certificate is valid
# employee_cert.pem: OK

openssl verify -CAfile security_truststore.pem malicious_cert.pem # Check if the certificate is valid
# CN = fake-admin
#  error 18 at 0 depth lookup: self-signed certificate
#  error malicious_cert.pem: verification failed
# -----

And as expected, since the malicious key is not signed by the root user, authentication fails and that person is kicked out.

So now we know the basics of encryption. But where do you use it in the real world? Let me tell you more about my current task and then you will understand.

4. Database Authentication

Say you're running any database (e.g. MySQL, Postgres, ScyllaDB, Redis, etc.) in Docker. When you spin up these instances in a containerized environment, you usually don't need a password unless you set one. In any case, we have a number of options when we talk about authentication in any part of the modern web, and the same is true when we're talking about databases.

Were going to use ScyllaDB in this example however this flow is pretty common in many databases.

At this step, we're going to learn about:

No Authentication: Literally no credentials required, just spin your instance and be happy;
Username/Password Authentication: Some basic credentials are fine to keep people away from your data;
Certified / Role Certified Authentication: This is where the big companies like to play and you should learn that.

After this brief introduction of encryption 101, we can get started with the content itself.

4.1 No Authentication

In Scylla, if you're running a node or cluster in the development environment (locally or using Docker), there is no password by default. Also, ScyllaDB uses the CQL protocol on port 9042 by default, which can be easily changed in the config files.

Why am I telling you this port information? Because this information will be important later, trust me.

You can spin up a Docker instance by running it:

# Running a ScyllaDB Node with Docker
docker  run --name some_scylla -p 9042:9042 -p 9142:9142 -d scylladb/scylla:6.1.2 \
  --overprovisioned 1 --smp 1 

# Checking the node status -> Expect for UN (Up And Running)Cassandra
docker exec -it some_scylla nodetool status
# Datacenter: datacenter1
# =======================
# Status=Up/Down
# |/ State=Normal/Leaving/Joining/Moving
# -- Address   Load      Tokens Owns Host ID                              Rack 
# UN 10.10.5.2 509.46 KB 256    ?    9f597eb5-a77f-493f-9835-85dd1e571fcc rack1
# --

After that, you can login at cqlsh (CQL Shell) by running:

docker exec -it some_scylla cqlsh
# Connected to  at 10.10.5.5:9042
# [cqlsh 6.0.18 | Scylla 6.0.1-0.20240612.bc89aac9d017 | CQL spec 3.3.1 | Native protocol v4]
# Use HELP for help.
# cqlsh> select address, port, client_type, username from system.clients;
#
#  address   | port  | client_type | username
# -----------+-------+-------------+-----------
#  10.10.5.5 | 50854 |         cql | anonymous
#  10.10.5.5 | 50868 |         cql | anonymous
# -----------+-------+-------------+-----------

Yeah, we're connected as anonymous users! Perfect for developing new stuff and testing locally. But then you decide to deploy the same configuration into production...

And OF COURSE that, in less than an hour you'll be owned by some random database crawler LOL.

And you can read more about this ransomware if you want. Anyway, let's add some base credentials and configure it properly in the next step.

Seriously, don't open your database to the Internet with the default configurations or without binding a unique IP address to access it.

4.2 Username/Password Authentication

While running any of these other databases, you have the ability to switch authentication types by changing a bunch of configuration files. At ScyllaDB, we're really proud of how modular and easy the configuration is.

If you look at /etc/scylla/scylla.yaml in your docker (you can get a better look at the repository by clicking here (file: scylla.yaml#247), you will find these configuration flags:

# file: /etc/scylla/scylla.yaml
# ...
# ...
# Authentication backend, identifying users
# Out of the box, Scylla provides org.apache.cassandra.auth.{AllowAllAuthenticator,
# PasswordAuthenticator}.
#
# - AllowAllAuthenticator performs no checks - set it to disable authentication.
# - PasswordAuthenticator relies on username/password pairs to authenticate
#   users. It keeps usernames and hashed passwords in system_auth.credentials table.
#   Please increase system_auth keyspace replication factor if you use this authenticator.
# - com.scylladb.auth.TransitionalAuthenticator requires username/password pair
#   to authenticate in the same manner as PasswordAuthenticator, but improper credentials
#   result in being logged in as an anonymous user. Use for upgrading clusters' auth.
# authenticator: AllowAllAuthenticator
# ...

The default is the AllowAllAuthenticator which allows anonymous connection with superpowers. Now, we'll uncomment the # authenticator: line and replace with PasswordAuthenticator:

# file: /etc/scylla/scylla.yaml
# ...
-#authenticator: AllowAllAuthenticator  # line ~247
+authenticator: PasswordAuthenticator

Okay! Now we just need to tell our Scylla node that we have made this change. Since this is a "critical" change to our system environment, we'll need to drain and then restart the node. Here's a step-by-step guide:

# Before Editing
docker exec -it some_scylla nodetool drain # Stop gossiping and preparing to shut down
docker exec -it some_scylla supervisorctl stop scylla # Stop ScyllaDB
docker exec -it some_scylla cat /etc/scylla/scylla.yaml | grep authenticator: # Check the current flag

# Edit /etc/scylla/scylla.yaml ...
docker exec -it some_scylla sed -i 's/# authenticator:.*/authenticator: PasswordAuthenticator/' /etc/scylla/scylla.yaml

# After Edit
docker exec -it some_scylla cat /etc/scylla/scylla.yaml | grep authenticator: # Check if the update is there
docker exec -it some_scylla supervisorctl start scylla # Run ScyllaDB

With that we're finally able to run cqlsh and receives a huge "YOU NEED CREDENTIALS, BITCH":

docker exec -it some_scylla cqlsh
Connection error: ('Unable to connect to any servers', {'10.10.5.5:9042': AuthenticationFailed('Remote end requires authentication')})

and by default, at ScyllaDB, we can login using the username/password "cassandra" (superuser):

docker exec -it some_scylla cqlsh -u cassandra -p cassandra
# Connected to  at 10.10.5.5:9042
# [cqlsh 6.0.18 | Scylla 6.0.1-0.20240612.bc89aac9d017 | CQL spec 3.3.1 | Native protocol v4]
# Use HELP for help.
# cassandra@cqlsh> select address, port, client_type, username from system.clients; 

#  address   | port  | client_type | username
# -----------+-------+-------------+-----------
#  10.10.5.5 | 56998 |         cql | cassandra
#  10.10.5.5 | 57014 |         cql | cassandra
# -----------+-------+-------------+-----------

... with that, we know the basics of how to configure authentication in ScyllaDB, and if you want to know why the user/password is "cassandra", you can read my first article from this series. Also, be sure to change your default credentials before deploying it.

The CQL protocol runs on port 9042 by default and is well known. In the same way that you can get owned by just letting your database run without authentication, you can get owned by letting the default credentials there.

At the moment, all connections still unencrypted but at least we have a minimum security, but don't worry! We're about fix the encryption soon! Before that, let's create some users/roles inside our database.

4.3 Creating Users and Roles

Before creating new users for this database, we need to understand how it works. At Scylla we use Role Based Authentication every day. So every user is placed in system.roles as a role.

This is not a problem because you can set a PASSWORD to a role. It looks weird, but I'll show you how it works. Check it out:

scylladb@cqlsh> desc system.roles;

CREATE TABLE system.roles (
    role text,
    can_login boolean,
    is_superuser boolean,
    member_of set<text>,
    salted_hash text,
    PRIMARY KEY (role)
);

scylladb@cqlsh> select * from system.roles;

 role        | can_login | is_superuser |     member_of | salted_hash
-------------+-----------+--------------+---------------+--------------------
 scylladb    |      True |         True |          null | $6$rAJ6FflUo8Chf...
 employee    |     False |        False |          null | null
 danielhe4rt |      True |        False |  {'employee'} | $6$AF4F6CflAA8Cw...
-------------+-----------+--------------+---------------+--------------------

This output gives us some information about the authentication itself, such as

A role becomes an authenticatable when the can_login flag is set to true;
A role must/can have a password after it becomes an authenticatable;
A role can belong to another role, which will inherit all privileges from it.

This is just an environment created to give you an example of how the modeling and features work. Be sure to read the documentation.

In a matter of DCL (Data Control Language) and DML (Data Manipulation Language), we'll do basic commands like:

-- Create our user/roles
CREATE ROLE developer;
CREATE ROLE danielhe4rt WITH PASSWORD = 'some_cool_password' AND LOGIN = true;

-- Grant roles to our users
GRANT developer TO danielhe4rt;

... which we will be running at our ScyllaDB Docker Instance following the keys created in the beginning of the article:

docker exec -it some-scylla cqlsh -u cassandra -p cassandra -e "CREATE ROLE IF NOT EXISTS 'employee' WITH LOGIN = true;";
docker exec -it some-scylla cqlsh -u cassandra -p cassandra -e "CREATE ROLE IF NOT EXISTS 'server' WITH LOGIN = true;";

Note: You can only create new roles/users using an authenticated account

4.4 Certificated / Role Certificated Authentication

At the beginning of this tutorial we introduced the topic by talking about a story of someone with valid keys to enter a building and now the goal is to understand what is different from a regular authentication. As I said, without TLS/SSL, your data is in transit without any encryption, and that's BAD depending on the content you're sending/receiving.

Using a certificate means that before you send your data out, we have guarantees that if someone intercepts the data, it won't be a problem if they don't have the key to decrypt it.

By default, ScyllaDB listens to the CQL protocol on port 9042, which can be configured using the native_transport_port configuration option.
Scylla also supports the CQL protocol via TLS/SSL encryption, which is disabled by default and can be enabled using the native_transport_port_ssl configuration option.

The traditional choice of port for secure connections is 9142, but if client_encryption_options is specified and native_transport_port_ssl is not, then native_transport_port will only handle encrypted connections. The same thing happens if native_transport_port and native_transport_port_ssl are set to the same value.

I know, it seems a bit crazy with all these flags and options, but I'll try to make it simpler. Check out the rules that govern port assignment/encryption, which are summarized in the table below:

np  := native_transport_port is set
nps := native_transport_port_ssl is set
ceo := client_encryption_options are enabled
eq  := native_transport_port_ssl == native_transport_port

+-----+-----+-----+-----+
|  np | nps | ceo |  eq |
+-----+-----+-----+-----+
|  0  |  0  |  0  |  *  |   =>   listen on native_transport_port, unencrypted
|  0  |  0  |  1  |  *  |   =>   listen on native_transport_port, encrypted
|  0  |  1  |  0  |  *  |   =>   don't listen
|  0  |  1  |  1  |  *  |   =>   listen on native_transport_port_ssl, encrypted
|  1  |  0  |  0  |  *  |   =>   listen on native_transport_port, unencrypted
|  1  |  0  |  1  |  *  |   =>   listen on native_transport_port, encrypted
|  1  |  1  |  0  |  *  |   =>   listen on native_transport_port, unencrypted
|  1  |  1  |  1  |  0  |   =>   listen on native_transport_port, unencrypted + native_transport_port_ssl, encrypted
|  1  |  1  |  1  |  1  |   =>   listen on native_transport_port(_ssl - same thing), encrypted
+-----+-----+-----+-----+

// More at: https://github.com/scylladb/scylladb/blob/master/docs/dev/protocols.md#cql-client-protocol

This tells us that if we want to enable encryption, we need to update a few more things in scylla.yaml. Instead of using the PasswordAuthenticator, we'll switch to the com.scylladb.auth.CertificateAuthenticator feature.

Before we change anything, let's drain and stop our cluster:

docker exec -it some_scylla nodetool drain # Stop gossiping and preparing to shut down
docker exec -it some_scylla supervisorctl stop scylla # Stop ScyllaDB

In the previous step we created some roles with LOGIN enabled. And we added these "roles" to our OpenSSL commands at the top with a CN=server/blabla, right? Now it's time to make it useful and not use credentials anymore.

Private Keys + Certs can hold more information, like raw strings that can be used after the handshake is done. In this case, we have stored the user/role that will be used to log in inside our keys. With this, we can do a pattern match with the certificate content and check if there's any CN=something that matches our select * from system.roles where role = 'something'.

Let's start the modifications by entering our ScyllaDB instance:

# Enter the ScyllaDB Instance
docker exec -it some_scylla shell

# root@c531i213hu:/#
# Install any editor of your choice
apt install nano

# Enter the ScyllaDB Config File
nano -l /etc/scylla/scylla.yaml

With your config file opened, let's work on the modifications needed.

4.4.1 Change the Authentication Type

This is the last time we'll open this file, I promise! The idea here is to change the authentication type from PasswordAuthenticator to com.scylladb.auth.CertificateAuthenticator. We're also going to set a rule that will extract the CN= flag from each certificate and use the content for authentication purposes:

# file: /etc/scylla/scylla.yaml

-authenticator: PasswordAuthenticator # line ~247
+authenticator: com.scylladb.auth.CertificateAuthenticator
+auth_certificate_role_queries:
+  - source: SUBJECT
+    query: CN=([^,\s]+)

4.4.2 Enable the Encryption Port

We still haven't enabled TLS/SSL even after setting the authenticator. We still need to uncomment the native_transport_port_ssl to get port 9142 (which is used to transport our encrypted data) into the game. So, back to the scylla.yaml, lets change it:

# file: /etc/scylla/scylla.yaml
# ...
-# native_transport_port_ssl: 9142 # <- line ~131
+native_transport_port_ssl: 9142

4.4.3 Enable the Client Encryption Port

Step by step, we'll make it! Our final change is to the client_encryption_options as defined in the table above. We need to uncomment everything and make sure everything matches in these configurations. Here's a brief explanation of each configuration:

enabled: enables TLS/SSL encryption -> change to true (default: false)
certificate: absolute path to your server certificate (security_cert.pem / server_cert.pem)
keyfile: absolute path your server key signed by the root_key (security_key.pem / server_key.pem)
truststore: absolute path your server truststore containing server + root certificates (security_truststore.pem / server_truststore.pem)
require_client_auth: your server must receive a certificate to authenticate -> change to true

So, let's work on these changes:

# file: /etc/scylla/scylla.yaml
# ...
# enable or disable client/server encryption.
-# client_encryption_options:
-#   enabled: true
-#   certificate: /etc/scylla/certs/cert.pem
-#   keyfile: /etc/scylla/certs/key.pem
-#   truststore: /etc/scylla/certs/truststore.pem
-#   require_client_auth: true
+client_encryption_options:
+  enabled: true
+  certificate: /etc/scylla/certs/server_cert.pem
+  keyfile: /etc/scylla/certs/server_key.pem
+  truststore: /etc/scylla/certs/server_truststore.pem
+  require_client_auth: true

Ok! Now we're good to go. Let's turn on our ScyllaDB cluster by running:

docker exec -it some_scylla supervisorctl start scylla # Start ScyllaDB

Let's see if our port 9142 is running and listening for TLS/SSL connections:

openssl s_client -connect localhost:9042 
# CONNECTED(00000003)
# 40873B60D2750000:error:0A00010B:SSL routines:ssl3_get_record:wrong version number:../ssl/record/ssl3_record.c:354:
# no peer certificate available
# No client certificate CA names sent

openssl s_client -connect localhost:9142
# CONNECTED(00000003)
# Can't use SSL_get_servername
# depth=0 CN = server
# verify error:num=20:unable to get local issuer certificate
# verify return:1
# depth=0 CN = server
# verify error:num=21:unable to verify the first certificate
# verify return:1
# depth=0 CN = server
# verify return:1
# ---
# Certificate chain
# 0 s:CN = server
#   i:CN = administrator
#   a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256
#   v:NotBefore: Aug  1 20:09:38 2024 GMT; NotAfter: Aug  1 20:09:38 2025 GMT
# ---
# YAY IT'S ASKING FOR CERTIFICATES!!!

At this point, we are 100% sure that TLS is enabled and requesting the certificates that match our previously generated keys.

5. Testing Encrypted Connections

Too much stuff to configure, but how do we connect to it? Instead of using CQLSH (because it's a pain in the ass to set it up and I LITERALLY PREFERED TO MAKE IT WORK on Node than explain how to use it), we're going to use NodeJS for the sake of simplicity.

First, let's quick setup our driver by running:

npm install @lambda-group/scylladb

After that, you can already create a demo script pointing your nodes and don't forget to switch the ports at the connection string:

import { Cluster } from "@lambda-group/scylladb";

const cluster = new Cluster({  
    nodes: ["127.0.0.1:9142"],
    ssl: {  
        enabled: true,  
        truststoreFilepath: "/your/path/to/certificates/developer_cert.pem",  
        privateKeyFilepath: "/your/path/to/certificates/developer_key.pem",  
        caFilepath: "/your/path/to/certificates/developer_truststore.pem",  
        verifyMode: VerifyMode.Peer,  
    }});
let result = await session.execute(
    "SELECT address, port, username, driver_name, driver_version FROM system.clients"
    );

console.log(result)
// [
//  {
//     address: '127.0.0.1',
//     driver_name: 'scylla-js-driver',
//     driver_version: '0.0.1',
//     port: 58846,
//     username: 'developer' // We're logged in as the role 'developer'
//  }
// ]

As the output tells us, we managed to connect by only sending our key and certificate to the server!

6. Conclusion

This "article/tutorial" took me a long time to write for several reasons, but one of them was to make sure of the content by going through it a few times.

I also made a demo (gh: danielhe4rt/scylladb-role-tls-auth) where you can run a makefile command and set up all these steps. So if you got here somehow, please don't forget to drop a star :D

Security is one of the topics that people are starting to pay more attention to nowadays and learning more about it was very exciting. Anyway, let me know what topics you would like to see in this series!

Stay safe and don't forget to drink water!

How to Build a Low-Latency Video Streaming App with ScyllaDB & NextJS

Attila Toth — Tue, 16 Jan 2024 13:47:14 +0000

We created a new video streaming app to help you learn about ScyllaDB. This blog covers its features, tech stack, and data modeling.

We just published a new ScyllaDB sample application, a video streaming app. The project is available on GitHub. This blog covers the video streaming application’s features, tech stack, and breaks down the data modeling process.

Video streaming app features

The app has a minimal design with the most essential video streaming application features:

List all videos, sorted by creation date (home page)
List videos that you started watching
Watch video
Continue watching a video where you left off
Display a progress bar under each video thumbnail

Technology stack

Programming language: TypeScript
Database: ScyllaDB
Framework: NextJS (pages router)
Component library: Material_UI

Using ScyllaDB for low-latency video streaming applications

ScyllaDB is a low-latency and high-performance NoSQL database compatible with Apache Cassandra and DynamoDB. It is well-suited to handle the large-scale data storage and retrieval requirements of video streaming applications. ScyllaDB has drivers in all the popular programming languages and, as this sample application demonstrates, it integrates well with modern web development frameworks like NextJS.

Low latency in the context of video streaming services is crucial for delivering a seamless user experience. To lay the groundwork for high performance, you need to design a data model that fits your needs. Let’s continue with an example data modeling process to see what that looks like.
Video streaming app data modeling

In the ScyllaDB University Data Modeling course, we teach that NoSQL data modeling should always start with your application and queries first. Then you work backwards and create the schema based on the queries you want to run in your app. This process ensures that you create a data model that fits your queries and meets your requirements.

With that in mind, let’s go over the queries that our video streaming app needs to run on each page load!

Page: Continue Watching

On this page, you can list all the videos that they’ve started to watch. This view includes the video thumbnails and the progress bar under the thumbnail.

Query – get watch progress:

SELECT video_id, progress FROM watch_history WHERE user_id = ? LIMIT 9;

Schema – watch history table:

CREATE TABLE watch_history (
   user_id text,
   video_id text,
   progress int,
   watched_at timestamp,
   PRIMARY KEY (user_id)
);

For this query, it makes sense to define user_id as the partition key because that is the filter we use to query the watch history table. Keep in mind that this schema might need to be updated later if there is a query that requires filtering on other columns beyond the user_id. For now, though, this schema is correct for the defined query.

Besides the progress value, the app also needs to fetch the actual metadata of each video (for example, the title and the thumbnail image). For this, the video table has to be queried.

Query – get video metadata:

SELECT * FROM video WHERE id IN ?;

Notice how we use the “IN” operator and not “=” because we need to fetch a list of videos not just a single video.

Schema – video table:

CREATE TABLE video (
   id text,
   content_type text,
   title text,
   url text,
   thumbnail text,
   created_at timestamp,
   duration int,
   PRIMARY KEY (id)
);

For the video table, let’s define the id as the partition key because that’s the only filter we use in the query.

Page: Watch Video

If you click on any of the “Watch” buttons, they will be redirected to a page with a video player where they can start and pause the video.

Query – get video content:

SELECT * FROM video WHERE id = ?;

This is a very similar query to the one that runs on the Continue Watching page. Thus, the same schema will work just fine for this query as well.

Schema – video table:

CREATE TABLE video (
   id text,
   content_type text,
   title text,
   url text,
   thumbnail text,
   created_at timestamp,
   duration int,
   PRIMARY KEY (id)
);

Page: Most Recent Videos

Finally, let’s break down the Most Recent Videos page, which is the home page of the application. We analyze this page last because it is the most complex one from a data modeling perspective. This page lists ten of the most recently uploaded videos that are available in the database ordered by the video creation date.

We will have to fetch these videos in two steps: first get the timestamps, then get the actual video content.

Query – get the most recent ten videos’ timestamp:

SELECT id, top10(created_at) AS date FROM recent_videos;

You might notice that we use a custom function called top10(). This is not a standard function in ScyllaDB. It’s a UDF (user-defined function) that we created to solve this data modeling problem. This function returns an array of the most recent created_at timestamps in the table. Creating a new UDF in ScyllaDB can be a great way to solve your unique data modeling challenges.

These timestamp values can then be used to query the actual video content that we want to show on the page.

Query – get metadata for those videos:

SELECT * FROM recent_videos WHERE created_at IN ? LIMIT 10;

Schema – recent videos:

CREATE MATERIALIZED VIEW recent_videos_view AS
   SELECT * FROM streaming.video
   WHERE created_at IS NOT NULL
   PRIMARY KEY (created_at, id);

In the recent videos materialized view, the created_at column is the primary key because we filter by that column in our first query to get the most recent timestamp values. Be aware that in some cases, this can cause a hot partition.

Furthermore, the UI also shows a small progress bar under each video’s thumbnail which indicates the progress you made watching that video. To fetch this value for each video, the app has to query the watch history table.

Query – get watch progress for each video:

SELECT progress FROM watch_history WHERE user_id = ? AND video_id = ?;

Schema – watch history:

CREATE TABLE watch_history (
   user_id text,
   video_id text,
   progress int,
   watched_at timestamp,
   PRIMARY KEY (user_id, video_id)
);

You might have noticed that the watch history table was already used in a previous query to fetch data. Now this time, the schema has to be modified slightly to fit this query. Let’s add video_id as a clustering key. This way, the query to fetch watch progress will work correctly.

That’s it. Now let’s see the final database schema!
Final database schema

CREATE KEYSPACE IF NOT EXISTS streaming WITH replication = { 'class': 'NetworkTopologyStrategy', 'replication_factor': '3' };


CREATE TABLE streaming.video (
   id text,
   content_type text,
   title text,
   url text,
   thumbnail text,
   created_at timestamp,
   duration int,
   PRIMARY KEY (id)
);


CREATE TABLE streaming.watch_history (
   user_id text,
   video_id text,
   progress int,
   watched_at timestamp,
   PRIMARY KEY (user_id, video_id)
);


CREATE TABLE streaming.recent_videos (
   id text,
   content_type text,
   title text,
   url text,
   thumbnail text,
   created_at timestamp,
   duration int,
   PRIMARY KEY (created_at)
);
view raw
q.cql hosted with ❤ by GitHub

User-defined function for the Most Recent Videos page

-- Create a UDF for recent videos
CREATE OR REPLACE FUNCTION state_f(acc list<timestamp>, val timestamp)
CALLED ON NULL INPUT
RETURNS list<timestamp>
LANGUAGE lua
AS $$
   if val == nil then
       return acc
   end
   if acc == nil then
       acc = {}
   end


   table.insert(acc, val)
   table.sort(acc, function(a, b) return a > b end)
   if #acc > 10 then
       table.remove(acc, 11)
   end
   return acc
$$;




CREATE OR REPLACE FUNCTION reduce_f(acc1 list<timestamp>, acc2 list<timestamp>)
CALLED ON NULL INPUT
RETURNS list<timestamp>
LANGUAGE lua
AS $$
   result = {}
   i = 1
   j = 1

   while #result < 10 do
       if acc1[i] > acc2[j] then
           table.insert(result, acc1[i])
           i = i + 1
       else
           table.insert(result, acc2[j])
           j = j + 1
       end
   end
   return result
$$;



CREATE OR REPLACE AGGREGATE top10(timestamp)
SFUNC state_f
STYPE list<timestamp>
REDUCEFUNC reduce_f;

This UDF uses Lua, but you could also use Wasm to create UDFs in ScyllaDB. Creating the function make sure to enable UDFs in the scylla.yaml configuration file (location: /etc/scylla/scylla.yaml).

Clone the repo and get started!

git clone https://github.com/scylladb/video-streaming

Install the dependencies:

npm install

Modify the configuration file:

APP_BASE_URL="http://localhost:8000"
SCYLLA_HOSTS="172.17.0.2"
SCYLLA_USER="scylla"
SCYLLA_PASSWD="xxxxx"
SCYLLA_KEYSPACE="streaming"
SCYLLA_DATACENTER="datacenter1"

Migrate the database and insert sample data:

npm run migrate

Run the server:

npm run dev

Wrapping up

We hope you enjoy our video streaming app and it helps you build low-latency and high-performance applications with ScyllaDB. If you want to keep on learning, check out ScyllaDB University where we have free courses on data modeling, ScyllaDB drivers, and much more! If you have questions about the video streaming sample app or ScyllaDB, go to our forum and let’s discuss!

More ScyllaDB sample applications:

Performance e elegância! Escrevendo uma CLI CRUD utilizando ScyllaDB e Ruby

Cherry Ramatis — Mon, 28 Aug 2023 11:24:53 +0000

Boas pessoas desenvolvedoras precisam saber fazer CRUD não é mesmo? Então já pensou em ser capaz de produzir um CRUD com um banco de dados NoSQL montado para alta escalabilidade e ainda mais utilizando uma linguagem elegante e simples? Não? Pois muito que bem, nesse artigo você vai aprender como construir uma CLI utilizando a gem dry-cli consumindo o banco de dados ScyllaDB com a gem cassandra-driver

Para saber um pouco mais sobre o que é ScyllaDB e em quais contextos essa ferramenta é útil, recomendo ler a documentação oficial

Disclaimer: Esse artigo assume conhecimento básico com bancos de dados visto que o objetivo vai ser focar na utilização em conjunto com ruby, para mais informações especificas sobre Scylla DB recomendo:

Os artigos criados pelo Developer Advocate da ScyllaDB DanielHe4rt no Dev.To

Os cursos gratuitos promovidos pela própria ScyllaDB no ScyllaDB University

Iniciando o projeto
Definindo a camada de injeção de dependências
Definindo o boilerplate para nossa CLI
Implementando nossos comandos
Conclusão

1. Iniciando o projeto

1.1 Resolvendo bibliotecas de sistema para instalar o driver

A gem que vamos utilizar para comunicar com o ScyllaDB se chama cassandra-driver, infelizmente ela exige bibliotecas de sistema relacionadas ao banco de dados cassandra para serem instaladas, a forma mais simples de ter essas bibliotecas instaladas é instalar o próprio cassandra na maquina:

Disclaimer: Não vamos utilizar o banco cassandra para nada na prática desse artigo, apenas precisamos do pacote instalado para que a gem consiga buscar as bibliotecas necessárias ao seu funcionamento. Visto que o ScyllaDB é baseado originalmente no Cassandra podemos utilizar tranquilamente.

Para instalar o cassandra no MacOS:

brew install cassandra

Para instalar o cassandra no Linux(Debian):

sudo apt-get install -y cassandra

Caso sua Distro/Sistema operacional não tenha sido listada, recomendo sempre recorrer a Documentação Oficial

1.2 Iniciando o projeto

Com as bibliotecas de sistema corretamente instaladas, podemos iniciar nosso projeto utilizando bundler:

mkdir project_scylla && cd project_scylla && bundle init

1.3 Instalando dependências de projeto

Agora podemos finalmente adicionar as gems necessárias para o projeto:

bundle add cassandra-driver
bundle add dry-auto_inject
bundle add dry-system
bundle add zeitwerk
bundle add dry-cli
bundle add dotenv

1.4 Definindo um REPL

Como somos bons rubistas devemos fazer o setup do nosso IRB como a primeira ação no projeto correto?

Para isso crie um script executável em bin/console com:

touch bin/console && chmod +x bin/console

Nesse arquivo vamos incluir um setup básico com IRB + Dotenv:

#!/usr/bin/env ruby

require 'dotenv/load'

require 'irb'
IRB.start

Excelente! Agora temos o básico do nosso setup concluído! Vamos prosseguir definindo a camada de injeção de dependência e um provedor para incorporar a funcionalidade do ScyllaDB.

2. Definindo a camada de injeção de dependências

Nesse artigo vamos seguir um modelo igual mostrado no meu artigo sobre injeção de dependências com sinatra, para um detalhamento maior de como esse setup e feito e o porquê é feito, por favor referencie a esse link.

2.1 Criando o container principal

Como descrito no artigo referenciada acima, para nossa camada de injeção de dependências funcionar precisamos definir um container principal que vai servir de referencia para buscar dependências e registrar novos providers.

Agora vamos criar um arquivo em config/application.rb com o seguinte conteúdo:

# frozen_string_literal: true

require 'dry/system'

class Application < Dry::System::Container
  configure do |config|
    config.root = Pathname('.')

    config.component_dirs.loader = Dry::System::Loader::Autoloading

    config.component_dirs.add 'lib'
    config.component_dirs.add 'config'
  end
end

loader = Zeitwerk::Loader.new
loader.push_dir(Application.config.root.join('lib').realpath)
loader.push_dir(Application.config.root.join('config').realpath)
loader.setup

Neste arquivo recém criado estamos definindo a localização dos componentes ao longo da nossa aplicação, a principio isso vai definir auto loading desses arquivos para que não precisemos usar require sempre que usamos alguma classe de algum arquivo.

Com o container definido já podemos modificar nosso script de REPL para que contenha o método finalize! da nossa aplicação recém definida.

#!/usr/bin/env ruby

require 'dotenv/load'
require_relative '../config/application'

Application.finalize!

require 'irb'
IRB.start

Também vamos criar um arquivo main.rb na raiz do nosso projeto apenas com o finalize! para servir como um ponto de entrada na nossa CLI.

require 'dotenv/load'
require_relative '../config/application'

Application.finalize!

2.2 Criando o provider de banco de dados

Agora que temos um container principal para nossa aplicação, podemos definir o único provider desse projeto que vai ser a conexão com o ScyllaDB.

Para isso crie um arquivo em config/provider/database.rb com o seguinte conteúdo:

# frozen_string_literal: true

Application.register_provider(:database) do
  prepare do
    require 'cassandra'
    require_relative '../../lib/migration_utils'
    require_relative '../constants'
  end

  start do
    cluster = Cassandra.cluster(
      username: ENV.fetch('DB_USER', nil),
      password: ENV.fetch('DB_PASSWORD', nil),
      hosts: ENV.fetch('DB_HOSTS', nil).split(',')
    )

    connection = cluster.connect

    MigrationUtils.create_keyspace(session: connection) if MigrationUtils.keyspace_exist?(session: connection)
    MigrationUtils.create_table(session: connection) if MigrationUtils.table_exist?(session: connection)

    connection = cluster.connect(KEYSPACE_NAME)

    register('database.connection', connection)
  end
end

Para as credenciais recomendo utilizar o serviço cloud ScyllaDB, nele você consegue criar um cluster super rápido e ganhar acesso a todas as credenciais de maneira super simples.

Um exemplo .env pode ser visto abaixo:

DB_USER=scylla
DB_PASSWORD=password
DB_HOSTS=node-0.amazonaws,node-1.amazonaws,node-2.amazonaws

Caso você tenha lido o artigo sobre injeção de dependências mencionado acima esse provider parece bem simples, mas também temos o uso de algumas classes novas que ainda não criamos e portanto vamos verificá-las passo a passo:

Definindo as constantes para nosso projeto

Nessa aplicação vamos manter nomes de keyspace e tabela definidos em constantes, você pode alterar para receber por parâmetro ou variável de ambiente, mas para simplicidade vamos deixar apenas com uma constante mesmo.

Crie um arquivo em config/constants.rb com o seguinte conteúdo:

# frozen_string_literal: true

KEYSPACE_NAME = 'media_player'
TABLE_NAME = 'playlist'

Criando uma classe utilitária para criar nosso banco

Como podemos ver no exemplo do provider anterior, estamos usando a classe MigrationUtils para produzir atividades comuns para a inicialização do nosso keyspace e da nossa tabela.

Agora vamos seguir passo a passo pelos métodos necessários para a criação do nosso keyspace e tabelas.

Checando se um keyspace ou tabela existe

Antes de prosseguirmos com a criação dos nossos keyspaces e tabelas, é crucial verificar se eles já existem a fim de evitar a execução desnecessária da função. Para isso, vamos implementar métodos booleanos da seguinte maneira:

Primeiramente, criaremos um arquivo chamado migration_utils.rb, localizado em
lib/migration_utils.rb, e o preencheremos com o código descrito abaixo:

class MigrationUtils
  # @param session [Cassandra#Cluster]
  # @return [Boolean]
  def self.keyspace_exist?(session:)
    query = <<~SQL
      select keyspace_name from system_schema.keyspaces WHERE keyspace_name=?
    SQL

    session.execute_async(query, arguments: [KEYSPACE_NAME]).join.rows.size.zero?
  end

  # @param session [Cassandra#Cluster]
  # @return [Boolean]
  def self.table_exist?(session:)
    query = <<~SQL
      select keyspace_name, table_name from system_schema.tables where keyspace_name = ? AND table_name = ?
    SQL

    session.execute_async(query, arguments: [KEYSPACE_NAME, TABLE_NAME]).join.rows.size.zero?
  end
end

Aqui, estamos implementando métodos estáticos que serão executados antes de configurarmos a camada de injeção de dependência. Por essa razão, aceitamos a sessão do banco de dados como parâmetro para essas funções. Com essa sessão, podemos usar o método execute_async para enviar uma consulta CQL. Esse método também nos permite utilizar placeholders ? para os parâmetros e especificar os valores em um objeto arguments: [].

Como esse método funciona de forma assíncrona, precisamos usar o método join para esperar essa Future finalizar e nos retornar um objeto. Com o objeto em mãos, podemos acessar a propriedade rows sendo esta uma lista contendo todas as linhas referentes a query mostrada acima.

Para finalizar a implementação do método retornando um booleano, vamos checar se a lista esta vazia ou não checando se o tamanho da lista é zero com .size.zero?

Disclaimer: Precisamos usar o .size.zero? pois o retorno é um Enumerator, que não possui o método .empty?

Criando os keyspaces e tabelas

Agora que criamos métodos responsáveis por checar a existência de um keyspace e uma tabela, precisamos criar os métodos que vão criar eles caso já não existam correto?

Para isso, vamos continuar trabalhando na classe localizada em lib/migration_utils.rb com o seguinte conteúdo:

class MigrationUtils
  # @param session [Cassandra#Cluster]
  # @return [void]
  def self.create_keyspace(session:)
    query = <<~SQL
      CREATE KEYSPACE #{KEYSPACE_NAME}
      WITH replication = {
        'class': 'NetworkTopologyStrategy',
        'replication_factor': '3'
      }
      AND durable_writes = true
    SQL

    session.execute_async(query).join
  end

  # @param session [Cassandra#Cluster]
  # @return [void]
  def self.create_table(session:)
    query = <<~SQL
      CREATE TABLE #{KEYSPACE_NAME}.#{TABLE_NAME} (
        id uuid,
        title text,
        album text,
        artist text,
        created_at timestamp,
        PRIMARY KEY (id, created_at)
      );
    SQL

    session.execute_async(query).join
  end
end

Aqui vamos seguir o mesmo padrão onde recebemos o session como parâmetro e o usamos para executar uma query assíncrona com execute_async, como não precisamos lidar com o retorno dessas queries podemos apenas usar o join para esperar ela finalizar.

Note: Para qualquer duvida referente as queries em si recomendo fortemente os links mencionados da ScyllaDB University.

2.3 Carregando o novo provider na nossa aplicação

Agora que definimos e entendemos o nosso provider de banco de dados, precisamos carregá-lo para que seja injetado nos nossos dois pontos principais da aplicação:

No main.rb vamos adicionar o require:

require_relative 'config/provider/database'

E no bin/console a mesma coisa:

require_relative '../config/provider/database'

3. Definindo o boilerplate para nossa CLI

Agora que temos uma camada de banco de dados e auto requiring pronta pra ser usada, vamos utilizar a outra grande gem desse projeto para definir os comandos da nossa CLI. Vem ai dry-cli senhoras e senhores!

Nesse primeiro momento vamos nos preocupar em apenas definir o boilerplate para nossa CLI, sem nos preocupar com a implementação real certo?

Para isso, vamos definir o modulo que vai registrar todos os comandos, localizado em lib/cli.rb com o seguinte conteúdo:

# frozen_string_literal: true

require 'dry/cli'

module Cli
  extend Dry::CLI::Registry

  register 'add', Commands::Add
end

Nesse modulo inicial podemos usar uma DSL para registrar novos comandos com o register, essa DSL é fornecida ao extender o modulo Dry::CLI::Registry.

Agora que registramos um comando Add, vamos criar a classe referente a ele localizada em lib/cli/commands/add.rb com o seguinte conteúdo:

# frozen_string_literal: true

require 'dry/cli'

module Cli
  module Commands
    class Add < Dry::CLI::Command
      desc 'This command add a new song to the playlist'

      argument :title, type: :string, required: true, desc: 'The title of the song'
      argument :album, type: :string, required: true, desc: 'The name of the album of that song'
      argument :artist, type: :string, required: true, desc: 'The name of the artist of band'

      def call(title:, album:, artist:)
        puts "Add command -> Title: #{title}, Album: #{album}, Artist: #{artist}"
      end
    end
  end
end

Nessa classe podemos ver outra DSL fornecida por herdar a classe Dry::CLI::Command, com ela podemos prover uma descrição para o comando usando desc, declarar quais argumentos vamos receber para esse comando junto com seu tipo, validação e descrição com argument e muito mais!

Logo após definir os metadados do nosso comando, definimos um método call que vai receber os argumentos definidos como parâmetros nomeados.

Em nosso arquivo main.rb podemos inicializar a nossa CLI com:

require 'dry/cli'

Dry::CLI.new(Cli).call

Finalmente, executando nossa aplicação com ruby main.rb devemos ter o seguinte output:

$ ruby main.rb
Commands:
  main.rb add TITLE ALBUM ARTIST                 # This command add a new song to the playlist

4. Implementando nossos comandos

Agora que temos um boilerplate e um entendimento básico quanto ao funcionamento da gem dry-cli podemos nos preocupar em implementar alguns comandos simples para o nosso CRUD.

4.1 Implementando o primeiro comando Add

Como já temos a classe para esse comando criada, vamos apenas começar a trabalhar na implementação do método call da seguinte forma:

# frozen_string_literal: true

require 'dry/cli'

module Cli
  module Commands
    class Add < Dry::CLI::Command
      desc 'This command add a new song to the playlist'

      argument :title, type: :string, required: true, desc: 'The title of the song'
      argument :album, type: :string, required: true, desc: 'The name of the album of that song'
      argument :artist, type: :string, required: true, desc: 'The name of the artist of band'

      def initialize
        super
        @repo = Application['database.connection']
      end

      def call(title:, album:, artist:)
        query = <<~SQL
          INSERT INTO #{KEYSPACE_NAME}.#{TABLE_NAME} (id,title,artist,album,created_at) VALUES (now(),?,?,?,?);
        SQL

        @repo.execute_async(query, arguments: [title, artist, album, Time.now]).join

        puts "Song '#{title}' from artist '#{artist}' Added!"
      end
    end
  end
end

Nesta implementação, primeiro vamos injetar nossa conexão com o banco de dados por meio da camada de injeção de dependência no construtor da classe e então iremos utilizar o método já conhecido execute_async para inserir um novo dado na tabela correta.

Um ponto importante a ressaltar é o uso da função now() na query, essa função é nativa do banco de dados e serve para inserir um novo UUID no padrão esperado pelo ScyllaDB, dessa forma não precisamos lidar com geração de UUID pelo lado da linguagem.

Bem simples certo? vamos continuar com os próximos comandos do nosso CRUD seguindo a mesma arquitetura já proposta.

4.2 Implementando o comando List

Após ter elaborado um comando para adicionar novas músicas, avançaremos para a criação de um comando destinado a listar todas as músicas já criadas. Para isso vamos criar um arquivo em lib/cli/commands/list.rb com o seguinte conteúdo:

# frozen_string_literal: true

require 'dry/cli'

module Cli
  module Commands
    class List < Dry::CLI::Command
      desc 'This command shows all the created songs'

      def initialize
        super
        @repo = Application['database.connection']
      end

      def call
        query = <<~SQL
          SELECT * FROM #{KEYSPACE_NAME}.#{TABLE_NAME};
        SQL

        @repo.execute_async(query).join.rows.each do |song|
          puts <<~MSG
            ID: #{song['id']} | Song Name: #{song['title']} | Album: #{song['album']} | Created At: #{song['created_at']}
          MSG
        end
      end
    end
  end
end

Novamente, estamos aqui fazendo uma query SELECT simples e percorrendo pelos resultados no array rows para mostrar ao usuário final, é importante ressaltar que os fields acessados com row['title'] correspondem aos fields que criamos quando fizemos o CREATE TABLE no provider.

Não podemos esquecer de registrar esse comando então vamos modificar o arquivo lib/cli.rb:

# frozen_string_literal: true

require 'dry/cli'

module Cli
  extend Dry::CLI::Registry

  register 'add', Commands::Add
  register 'list', Commands::List # <= Novo comando
end

Perfeito! Agora podemos tanto adicionar quanto listar musicas :D

Uma simples demo do que temos agora tanto com add quanto list:

4.3 Implementando o comando Delete

Vamos explorar agora um comando bastante interessante. Neste caso, iremos exibir uma lista de músicas acompanhadas por seus indices, permitindo ao usuário selecionar uma música específica através da posição correspondente.

Para realizar isso, vamos desenvolver o comando e implementar um método que seja responsável por listar as músicas com suas respectivas posições numéricas. Além disso, iremos também aguardar o input do usuário.

A seguir, vamos criar um novo arquivo localizado em lib/cli/commands/delete.rb com o seguinte conteúdo:

# frozen_string_literal: true

require 'dry/cli'

module Cli
  module Commands
    class Delete < Dry::CLI::Command
      desc 'This command will prompt for a song to be deleted and then delete it'

      def initialize
        super
        @repo = Application['database.connection']
      end

      def call
        songs = @repo.execute_async("SELECT * FROM #{KEYSPACE_NAME}.#{TABLE_NAME}").join.rows

        song_to_delete_index = select_song_to_delete(songs:)

        query = <<~SQL
          DELETE FROM #{KEYSPACE_NAME}.#{TABLE_NAME} WHERE id = ?
        SQL

        song_to_delete = songs.to_a[song_to_delete_index]

        @repo.execute_async(query, arguments: [song_to_delete['id']]).join
      end

      private

      # @param songs [Array<Hash>]
      def select_song_to_delete(songs:)
        songs.each_with_index do |song, index|
          puts <<~DESC
            #{index + 1})  Song: #{song['title']} | Album: #{song['album']} | Artist: #{song['artist']} | Created At: #{song['created_at']}
          DESC
        end

        print 'Select a song to be deleted: '
        $stdin.gets.chomp.to_i - 1
      end
    end
  end
end

No método select_song_to_delete nós recebemos uma lista de musicas e percorremos por ela com um índice usando o método each_with_index, dessa forma conseguimos mostrar uma mensagem no modelo 1) Song: | Album: | Artist: | Created At:. Ainda nesse método esperamos o input do usuário com o $stdin.gets.chomp e repassamos no retorno como um integer convertendo com .to_i.

Já no método call, começamos fazendo uma consulta para obter todas as músicas registradas na tabela. Em seguida, passamos esse conjunto de dados para o método que permite ao usuário selecionar uma em especifico e retornar o índice da mesma. Usamos esse índice para escolher uma música específica no array e, em seguida, executamos uma consulta DELETE para removê-la.

Disclaimer: Antes de selecionar um item especifico, precisamos transformar em um array com .to_a visto que essas linhas são um Enumerator.

Uma demo mostrando o uso prático do comando:

Não podemos esquecer de registrar esse comando então vamos modificar o arquivo lib/cli.rb:

# frozen_string_literal: true

require 'dry/cli'

module Cli
  extend Dry::CLI::Registry

  register 'add', Commands::Add
  register 'list', Commands::List
  register 'delete', Commands::Delete # <= Novo comando
end

4.4 Implementando o comando Update

Agora vamos criar um comando que vai juntar todos os conceitos mostrados
anteriormente, este será um update e vai performar da seguinte forma:

Vamos aceitar parâmetros como title, album, artist para usar como parte do update (semelhante ao que fizemos no comando add)
Vamos mostrar para o usuário uma lista das musicas cadastradas e esperar o input do usuário com um índice (semelhante ao que fizemos no comando delete)

Perfeito! Vamos criar um comando localizado em lib/cli/commands/update.rb com o seguinte conteúdo:

# frozen_string_literal: true

require 'dry/cli'

module Cli
  module Commands
    class Update < Dry::CLI::Command
      desc 'This command will prompt for a song to be updated and use the argument information to updated it'

      argument :title, type: :string, required: true, desc: 'The title of the song'
      argument :album, type: :string, required: true, desc: 'The name of the album of that song'
      argument :artist, type: :string, required: true, desc: 'The name of the artist of band'

      def initialize
        super
        @repo = Application['database.connection']
      end

      def call(title:, album:, artist:)
        songs = @repo.execute_async("SELECT * FROM #{KEYSPACE_NAME}.#{TABLE_NAME}").join.rows

        song_to_update_index = select_song_to_update(songs:)

        query = <<~SQL
          UPDATE #{KEYSPACE_NAME}.#{TABLE_NAME} SET title = ?, artist = ?, album = ? WHERE id = ? AND created_at = ?
        SQL

        song_to_update = songs.to_a[song_to_update_index]

        @repo.execute_async(query, arguments: [title, artist, album, song_to_update['id'] song_to_update['created_at']]).join
      end

      private

      # @param songs [Array<Hash>]
      def select_song_to_update(songs:)
        songs.each_with_index do |song, index|
          puts <<~DESC
            #{index + 1})  Song: #{song['title']} | Album: #{song['album']} | Artist: #{song['artist']} | Created At: #{song['created_at']}
          DESC
        end

        print 'Select a song to be updated: '
        $stdin.gets.chomp.to_i - 1
      end
    end
  end
end

Como pode ver, esse comando é realmente uma junção entre os conceitos do comando add (arguments) e conceitos do comando delete (método select pelo índice).

Sobre a query update é importante ressaltar que no ScyllaDB temos duas primary keys(nesse caso id e created_at), portanto precisamos utilizar ambas as informações para que o banco de dados ache corretamente nossa linha.

Uma demo mostrando o funcionamento do comando:

Conclusão

Espero que esse artigo tenha sido útil! Tentei ao máximo focar na integração entre Ruby e ScyllaDB pois não encontrei nada com uma linguagem simples para iniciantes. Para duvidas direcionadas especificamente ao ScyllaDB recomendo fortemente os artigos do DanielHe4rt e o ScyllaDB University.

E não se esqueça de dar continuidade a este projeto que você acaba de criar! Deixo como um desafio a busca por aprimoramentos na arquitetura que construímos. Abaixo, elenco alguns pontos de melhoria evidentes, porém, encorajo você a identificar outros que possam ter passado despercebidos por mim. A prática é o segredo para se tornar uma pessoa desenvolvedora habilidosa!
😄

As funções select_song_to_delete e select_song_to_update são iguais, talvez mover ela para algum local comum?
No comando add nós printamos uma mensagem de sucesso para o usuário, mas não seguimos isso nos outros comandos, podemos melhorar essa experiencia de usuário?

May the force be with you! 🍒

Database 101: How does migration between databases work in the real world?

Daniel Reis — Mon, 21 Aug 2023 13:05:09 +0000

Recently my new task at ScyllaDB is to study how to Migrate data between Databases and as I promised before, I'll keep you folks about my latest studies.

If you’re just getting started with databases in general or databases, you might want to start off by reading my initial post, Database 101: Data Consistency for Beginners for Beginners. That article captures my own exploration of how many database paradigms exist as I look far beyond my previous experience with just SQL and MySQL. I’m keeping track of my studies in this Database 101 series.

1. Prologue
2. Database Migrations: How to Start
3. Why are we doing this migration?
4. Proof of Concept: where to start?
- 4.1 PoC: Same Paradigm
- 4.2 PoC: Different Paradigm
5. Testing it before it's too late! (seriously)
6. Migration Strategies
- 6.1 Strategy: Cold Migration
- 6.2 Strategy: Hot Migration
7. Final Considerations

1. Prologue

Since I started my journey in this "database environment", I have asked myself so many questions that probably will take a few years to be answered. But one of those questions is how to properly perform a database migration, and I still don't have the answer.

Actually, when I migrated the He4rt Developers (community I lead) bot, I did my best to make everything on the backend and the database model as perfect as possible. But when I had to migrate everything, it was a real mess. I decided to use the "Big Bang" (pull request with 532 files changed) migration approach, and it was one of my bad decisions as an open source maintainer.

At that time, I didn't have any knowledge about this topic (database migrations) at all. But I learned a lesson that I'm going to teach you in this article.

2. Database Migrations: How to Start

If you ever considered receive such a task, of investigate and research how properly migrate data from a database to another, you should know a few things before do anything related to code. What do I mean by that? Just throw queries between databases is not where you're going to start the investigation.

Let's just ask ourselves a few questions before everything:

What is the motivation for database migration?
What migration approach should we use on the project?
Are we going to jump from one paradigm to another?
Does the new database have all the support we need for the things we're currently using?

With these four questions in mind, we can start researching until answer all of that and then put hands on queries/code.

3. Why are we doing this migration?

When we talk about replacing any part of the stack of a running project, the reason needs to be clear to the entire team that is planning this huge movement inside the product.

So what are you looking for in the replacement? A few possibilities:

Lower latency for I/O operations;
Cheaper database to maintain;
Scalable database to keep up with the product.

Just a reminder that these are just a few items on the giant list of motivations for why to replace a database, ok? Ask your team/leaders all the details you need to understand the motivation and make sure this is what you're looking for!

4. Proof of Concept: where to start?

Good! We have a clear idea of why we are doing this. Now let's figure out the things that will be important to make this work. Do you know the difference between each database paradigm? Well, if you're migrating data between two different databases, it should be a constant concern. Let's understand which type of PoC you will be running.

4.1 PoC: Same Paradigm

Imagine a few scenarios where you want to migrate from:

CassandraDB to ScyllaDB (Wide Column);
MySQL to PostgreSQL (Relational);
Memcache to Redis (Key-value);
MongoDB to DynamoDB or Firebase (document).

All of these scenarios are kinda easier to migrate, because they share the same Database Paradigm, which means that they follows the same architecture.

So, they have mostly the same features, indexing and data types. Maybe will have slight differences but are things that you can handle by doing tests and migrating from a feature/data type to another.

Even when Discord was migrating from CassandraDB to ScyllaDB had a few things to fix, but at the end of the day it's simpler than between different paradigms. To be super honest, if you have a problem during the PoC, remember that it's part of the job. ¯\(ツ)/¯

4.2 PoC: Different Paradigm

Ok, and if our problem is to go from some document based database to a wide column? Like going from a MongoDB to a ScyllaDB?

Sounds like a tough task, and probably will be, and this part especially needs to be carefully designed.

When Discord started migrating from Document Based Database to the Wide Column Database in 2017, they had the same issue.

How do you split an infinite JSON object? Can you imagine? Check the JSON example below:

{
  "servers": [
    {
      "218378123781": {
        "id": "218378123781",
        "server_name": "He4rt Developers",
        "channels": [
          {
            "78931278921723": {
              "name": "Test Channel 1",
              "messages": [
                {
                  "312783712867": {
                    "message_id": "312783712867",
                    "chatter_id": "danielhe4rt",
                    "content": "oh hi lol",
                    "created_at": "1691972222",
                    "updated_at": "1691972222"
                  }
                },
                {
                  "312783712867": {
                    "message_id": "312783712867",
                    "chatter_id": "danielhe4rt",
                    "content": "oh hi lol",
                    "created_at": "1691972222",
                    "updated_at": "1691972222"
                  }
                }
              ]
            }
          }
        ]
      }
    }
  ],
  "users": [
    {
      "danielhe4rt": {
        "chatter_id": "danielhe4rt",
        "joined_at": "1691972222"
      }
    },
    {
      "dont_forget_to_follow_me_on_socials": {
        "chatter_id": "dont_forget_to_follow_me_on_socials",
        "joined_at": "1691972222"
      }
    }
  ]
}

This is how a briefly imagined a social modeling like Discord. Yeah, I know. That's far from the reality but let's stick into the problem here, ok? And in fact, if you have a good DBA and architects on your side, it will not be a problem.

First, you need the whole team to know more about the paradigm and how to use it. With document-oriented, you can push whatever you want because there's no strong data consistency at all. But if you move to ScyllaDB, you'll need to model tables, focus on what query you want to run, and also understand how things like "consistency level" and "replication factor" work.

At the end we should have something like:

CREATE TABLE servers (
    server_id bigint
    owner_id text
    server_name text,
    created_at timestamp,
    PRIMARY KEY (server_id, created_at)
);

CREATE TABLE channels (
    server_id bigint
    channel_id bigint
    channel_name text,
    created_at timestamp
    PRIMARY KEY ((server_id), created_at)
) WITH CLUSTERING ORDER BY (created_at DESC);

CREATE TABLE channel_messages (
    message_id bigint
    channel_id bigint,
    chatter_id text,
    content text,
    created_at timestamp,
    PRIMARY KEY ((message_id, channel_id), created_at)
) WITH CLUSTERING ORDER BY (created_at DESC);

CREATE TABLE users (
    user_id text,
    user_name text,
    created_at timestamp,
    PRIMARY KEY (user_id, created_at)
);

Yeah, CQL (Cassandra Query Language) is very similar to SQL (Structured Query Language). I invite you to have a try doing this ScyllaDB: NoSQL 101 Essentials course. You will learn a bunch of cool things about the Wide Column paradigm :D

5. Testing it before it's too late! (seriously)

Ok, we ran our PoC and we know which database and paradigm will be the best for our new environment. Now your team is focused to finish the data modeling ASAP, right? Once that is done, the focus will be on getting all the scripts ready to make this a successful migration.

The best thing that you can do is to split your tests with 1% and 5% of your database in a staging environment. Why? If your "new system" or "new version" of the system crashes with 1% of the database, it will reflect that there's something missing in the data modeling or even at the backend. So, stop the migration scripts and write some tests for your application.

Now that you're done with the 1% migration bugs, let's do the same process with 5% of your database. In this step you should test everything carefully, because it will be the last testing step you will do before choosing the migration strategy and executing it.

6. Migration Strategies

Of course, migrating between databases isn't a "new" thing, so there are a few things planned and ready to make your life easier. Also, a few rules that together will make your database migration look like it was done by a pro.

In this regard, there are two very popular strategies called "Hot Migration" and "Cold Migration". The names of the strategies themselves say something that we've probably been asking ourselves since the beginning of this research: Should I shut everything down or migrate while the old database is still running? Well, that depends on your needs. The process itself is called ETL (Extract, Transform and Load) in any case btw.

6.1 Strategy: Cold Migration

Let's start with the "easiest" and safer migration strategy: Cold Migration. The reason to use this type of migration is understand if you can stop your system for the time needed without any problem. Basically, know if there's a maintenance period and let users know that. It also may known as "Big Bang Migration", since it will be migrated entirely to a new environment that had nothing before.

This approach is used on small/medium systems and is usually done at dawn, since it's probably the time of day when fewer users are requesting the system.

There's a checklist of things you need to be aware of when doing a cold migration. Let's check it out:

1. Planning: what time of day and who will be responsible for this task?
2. Extraction: how are we going to dump all this data? Maybe there's specific tooling already done on the target database to be used.
3. Transformation: is the data in the right form for the new database? Do both databases have the same structure? Is there any tooling to transform this data for us?
4. Load: how do we make sure that our new database gets all of our data? Any broken query? All good?
5. Validation: ok, we loaded everything. Now we need to validate if the data matches with the legacy system. Good Luck!
6. Testing: let's connect it in the migrated database into the system and run all the test suites. Almost there!!!
7. Activation: all good. Now it's time to shut down the legacy database. Congratz, buddy!

Basically, this is the migration flow when you have the possibility to shut everything down and now you know how to properly do it.

6.2 Strategy: Hot Migration

Ok... If you want to migrate things while your system is still running... Now you're in trouble! Wait, I'm just kidding :p

This is where things get a bit complicated because you have to write to two databases at the same time. So you can imagine that instead of having 1 instance of the database, you will need two, and depending on that

We also have a checklist to help us with this. So let's check the flow:

1. Planning: time of the day is not the main issue here, but a proper infrastructure to support the transition between both databases;
2. Replication: both systems need to be feed at the same time while the migration is happening
3. Tests and Validation: during the replication, you should validate all the data that you're inserting along with the migrated one.
4. Minimal Interruption: the system should be online all the time, and if any problem occurs, it should not be perceptible by the user.
5. Switch: When all the important data is finally migrated, is time to point the app to the new database;
6. Activation: all good. Now it's time to shut down the legacy database. Congratz, buddy!

This migration strategy is indicated for companies that require high availability and have critical services that cannot be stopped.

7. Final Considerations

Well, my task was to understand more about DynamoDB and create some content about the ScyllaDB Alternator, a migration tool that converts any DynamoDB I/O into a ScyllaDB. I'm still working in a PoC to understand more about it, but I certainly learned some really cool things along the way to know more tips, tricks, and last names of things related to migrations.

Also there will be an "NoSQL Database Migration Masterclass" for free event tomorrow (22/08)! I'll be there on the chat learning more about the topic and invite you to be there with me!

I hope that this tutorial could help you to understand briefly this topic! And please, let me know in the comments which things that I should learn about.

Don't forget to like and share this article with your friends and go fill your water bottle! Let's keep in touch:

Twitter DanielHe4rt PT-BR
Twitter DanielHe4rt EN
Twitch Channel

Database 101: Twitch Bot in a higher level for beginners.

Daniel Reis — Tue, 18 Jul 2023 16:44:21 +0000

I kinda like the idea of writing about social media + databases and it seems the readers here are too. So, let’s explore it in a new direction: looking at Twitch.tv or any type of platform with instant messaging.

If you’re just getting started with databases in general or databases + social media in particular, you might want to start off by reading my initial post, Database 101: Data Consistency for Beginners for Beginners. That article captures my own exploration of how many database paradigms exist as I look far beyond my previous experience with just SQL and MySQL. I’m keeping track of my studies in this Database 101 series.

1. Prologue
2. The Project: Twitch Sentinel
3. Making Good Decisions
- 3.1 ACID Acronym
- 3.2 BASE Acronym
- 3.3 Concluding a Good Decision
4. Modeling our Ideas into Queries
- 4.1 Number of messages per user (top 5)
- 4.2 Number of unique users per stream(er)
- 4.3 Earliest and latest message of a given user
5. Twitch as our payload!
6. Make it BURN!
7. Final Considerations

1. Prologue

One of my tasks as a Developer Advocate is to inspire developers to create and try new things. I also need to challenge myself to create interesting things that will engage people.

In the past few weeks, I was working on a talk called “ScyllaDB from the Developer Perspective: Building Applications.” My goal was to build a simple application with a database that would consume 1k/3k operations. In this article, I’ll share what I learned. By the end of this article, a Beginner Developer should know how to build an application with a cool database to consume tons of IOPS (or maybe not – , let me know in the comments).

And at the end of your reading, you, a Beginner Developer (or maybe not, let me know on the comments) can build an application with a cool database to consume tons of IO/s.

2. The Project: Twitch Sentinel

Some things that I really like to do in my free time are:

Create useless PoCs (Proof of Concepts) that help me understand more about concepts in computer science.
Do daily live coding at Twitch.tv (here is my channel).
Write blog posts about my learnings and experiences.

Reflecting on that, I decided to start a Twitch project that involves high data ingestion; let’s call it Twitch Sentinel.

The idea is simple: collect all the messages from as many channels as possible on Twitch.tv, store the data, and retrieve some metrics from it.

Screenshot of my Twitch chat saying "hi" to you. lol

Can you imagine how to store more than 1k messages per second? It sounds like a cool challenge. Here are a few metrics that you can generate for your study:

How many messages a specific streamer receives on their chat daily.
How many messages a user sends per day.
The most probable hour that a streamer or user is online, based on their messages.
Peak of messages by hour, and so on.

But you might be asking, "Why should I create that if Twitch gives me the Analytics Panel?" The answer is: USELESS PoCs that will teach you something new!

Have you ever created an analytics dashboard before? With a really fast database? Thinking of new ways to model it? If the answer is YES, drop a like on this post! And if not, let me teach you what I've learned about this subject!

3. Making Good Decisions

When you start a new project, you should know that every part of the code and the tools that you will use to deliver it will directly impact the future of your project.

Also, the database that you choose for it directly impacts = the performance of the entire application.

If you read the first post of my series Database 101, you probably remember discussions about the CAP Theorem and Database Paradigms. Now we're about to check some properties related to how fast or consistent a database can be.

3.1 ACID Acronym

If you're interested in Data Modeling, you’ve probably already heard about ACID. If not, ACID is an acronym for Atomicity, Consistency, Isolation and Durability. Each item of this acronym makes up something called a Transaction in Relational Databases.

A Transaction is a single operation that tells your Database that if any of these concepts of ACID fails, your query will not succeed. Let's explore this more:

Atomicity: Each piece of your transaction is unique and if any of these pieces fails, it will fail and rollback to the original state of that piece of data.
Consistency: Guarantee that your data will only be accepted if it's in the right shape of that model, based on: table modeling, triggers, constraints etc.
Isolation: All transactions are independent and will not interfere with a running transaction.
Durability: If the transaction is finished, the data will be persisted even if the system fails after that.

As you can see, ACID gives you an interesting safety guard for all your data, but this requires some tradeoffs:

It's EXPENSIVE to maintain, since for each transaction you will LOCK (Isolation) your data. It will need many nodes
If your goal is to speed things up with higher throughput, ACID will keep you from that since all these operations need to be succeed.
You can't even think in Flexible Schemas, a.k.a "Documents" since it broke the Consistency property.

So what? We just start to write JSON or TXT as our database? Well, it's not a bad thing but let's do it with Non Relational Databases (a.k.a NoSQL.).

3.2 BASE Acronym

When we jump into NoSQL databases, probably the most important thing to know is: there are paradigms and each paradigm is strong for something specific.

But something that is common between most NoSQL Database Paradigms is the BASE properties. This acronym stands for: Basically Available, Soft State and Eventually Consistency.

Now things get interesting because those properties allow you to do "less validation" on your data because you guarantee the integrity from the developer side. But before talking about that, let's understand why and all the meanings:

Basically Available: You can Read/Write data from your database anytime you want, but it doesn't mean that this data is updated.
Soft State: You can shape your schema on the fly mostly and it turns into a Developer task, not a DB Administrator one.
Eventually Consistency: All the data will be synced between many datacenters until it gets the Consistency Level needed.

It seems, BASE properties give us a database that can receive any type of data with respect to data modeling. Also it's always available to query it and it's a good option for high data ingestions. But there are also tradeoffs:

If you need strong data consistency, it may be not the best approach;
Higher complexity since the DB Modeling is delegated to the Developer;
Data Conflicts can be a pain if you need to sync the nodes around the clusters that you have.

Now we know more about a few properties that a Database can have generally have, so it's time to decide based on our project.

3.3 Concluding a Good Decision

ACID vs BASE? Who wins? The type of project decides! Often you can use multiple databases in a project, so it’s not a problem at all. But if you need to choose just one, choose wisely. To be clear:

ACID should be chosen when you NEED TO HAVE Data Consistency with Transactions and performance isn’t a key consideration.
BASE should be chosen when you have a higher demand for IOPS and are sure about your Data Modeling.

For our project, we're going to receive a huge amount of of messages from Twitch.tv. We need to store them quickly and to handle all those messages. So, of course we're going to abandon the ACID 'Safety Guard' and jump into BASE 'Do Whatever Wisely' xD

Thinking about that, I decided to use CQL and ScyllaDB since it handles our idea of receiving millions of messages per second and at the same time has the Consistency and support for ACID. If Discord uses ScyllaDB it for storing messages, why not use it with Twitch? :D

4. Modeling our Ideas into Queries

Screenshot of my Twitch chat saying "hi" to you. lol

When you're using ScyllaDB, your main focus needs to be on which query you want to run. Thinking on that, we need to:

Store messages and read them when necessary.
Store and read often from a streamer list.
Count all messages sent by chat users in a specific stream;

So our data modeling should be like:

No big deal here, it's supposed to be simple. We need the fastest throughput possible and complex queries don't allow us to have such a performance.

Here are a few queries that we can run with the data retrieved from this model:

4.1 Number of messages per user (top 5)

SELECT
    chatter_id,
    chatter_username,
    count(*) AS msg_count
FROM
    dev_sentinel.messages
GROUP BY
    chatter_id,
    chatter_username
ORDER BY
    msg_count DESC
LIMIT 5

4.2 Number of unique users per stream(er)

SELECT
    streamer_id,
    COUNT(DISTINCT chatter_id) AS unique_chatters
FROM
    dev_sentinel.messages
GROUP BY
    streamer_id
ORDER BY
    unique_chatters DESC

4.3 Earliest and latest message of a given user

SELECT
    min(sent_at) AS earliest_msg,
    max(sent_at) AS latest_msg
FROM
    dev_sentinel.messages
WHERE
    chatter_username = 'danielhe4rt'

This is very similar to the modeling I discussed in the post. There, I have a more detailed explanation of a few pieces of the code. Feel free to take a look :p

5. Twitch as our payload!

Ok, now we have the Database concept modeled and now we need the "real world" payload. I don't know if you like these tutorials that just mock all the data just to show you a huge number that doesn't even mean anything at the end... I simply don't – that's and that's why I wanted to bring something real for you to explore.

On Twitch's streaming platform, they have a bunch of APIs that can interact with the streamer chat. The most notorious is called 'TMI' - Twitch Messaging Interface - which is a client that connects directly to any Twitch Streamer chat that you want. Here's a list of clients for you to check it out:

tmi.js - Interface for NodeJS
tmi.php - Interface for PHP (Easily integrated to Laravel)
pytmi - Interface for Python
twitch-irc-rs Interface for Rust

Anyway, the idea is the same for all of these clients: you need to choose a channel and connect to it. The code looks like this:


$client = new Client(new ClientOptions([
    'connection' => [
        'secure' => true,
        'reconnect' => true,
        'rejoin' => true,
    ],
    'channels' => ['danielhe4rt']
]));

$client->on(MessageEvent::class, function (MessageEvent $e) {
    print "{$e->tags['display-name']}: {$e->message}";
});

$client->connect();

Each Twitch payload has the "tags" array, that brings us a JSON with all the data related to that specific message:

{
    "badge-info": {
        "subscriber": "58"
    },
    "badges": {
        "broadcaster": "1",
        "subscriber": "3036",
        "partner": "1"
    },
    "client-nonce": "3e00905ed814fb4d846e8b9ba6a9c1da",
    "color": "#8A2BE2",
    "display-name": "danielhe4rt",
    "emotes": null,
    "first-msg": false,
    "flags": null,
    "id": "b40513ae-efed-472b-9863-db34cf0baa98",
    "mod": false,
    "returning-chatter": false,
    "room-id": "227168488",
    "subscriber": true,
    "tmi-sent-ts": "1686770892358",
    "turbo": false,
    "user-id": "227168488",
    "user-type": null,
    "emotes-raw": null,
    "badge-info-raw": "subscriber/58",
    "badges-raw": "broadcaster/1,subscriber/3036,partner/1",
    "username": "danielhe4rt",
    "message-type": "chat"
}

On this payload, we gonna need only:

room-id: Identifier related to the specific broadcaster channel.
user-id: Identifier related to the user who sent the message
tmi-sent-at: message timestamp

On the Message interface, you will also receive a string with the message

This is a simple project, but seriously, try to abstract more ideas from that and let me know! I'll gladly help you to create something bigger!

6. Make it BURN!

As I told you at the beginning of this article, my goal with this project was to build a highly scalable application with a really cool database that handles our needs by receiving a huge chunk of payload per second.

So, we connected on the ~20k most accessed chats on Twitch.tv and got an average of 1,700 ~ 2,000 messages per second. This gives us an average of 6 MILLION messages per hour. Have you ever coded something that had such a high data ingestion?

While the application is receiving all this data and posting it to ScyllaDB, here are some statistics of a T3-Micro Cluster, the cheapest instance at AWS.

It handles 1k requests/s like it's nothing with latency sub millisecond P99 latency. Also, the load of this lightweight machine for 1k/s is just 8%, so you can do something monstrously faster if you want.

Most of the time, it depends on how many streamers will be connected to your bot and how many messages viewers send per second.

7. Final Considerations

This project taught me a lot about how important it is to choose the right tool for the right job. In this specific case, the database needs to be something that you will use while thinking at a higher scale.

Just remember that it is totally fine to have more than one database inside a project. Each one resolves a generic or a specific problem inside the development environment. Always do proper research and a PoC with as many tools as possible if you have time!

If you want to build by yourself, check this tutorial on Killercoda and don't forget to follow me at socials!

Stay hydrated and see you soon.

Twitter DanielHe4rt PT-BR
Twitter Danielhe4rt EN
Twitch Channel (Daily LiveCoding Sessions)
Instagram
YouTube

Database 101: How social media “likes” are stored in a database

Daniel Reis — Mon, 08 May 2023 17:17:03 +0000

Did you ever think about how Instagram, Twitter, Facebook or any social media platforms track who liked your posts? Let's figure it out in this post!

If you’re just starting working with databases, you might want to start off by reading my initial post, Database 101: Data Consistency for Beginners. That captures my own exploration of how many database paradigms exist as I look far beyond my previous experience with just SQL and MySQL. I’m keeping track on my studies in this Database 101 series.

1. Prologue

Recently, I was invited to speak at an event called "CityJS." But here's the thing: I'm the PHP guy. I don't do JS at all, but I accepted the challenge. To pull it off, I needed to find a good example to show how a highly scalable and low latency database works.

So, I asked one of my coworkers for examples. He told me to look for high numbers inside any platform, like counters or something like that. At that point I realized that any type of metrics can fit this example. Likes, views, comments, follows, etc. could be queried as counters. In this article, you will find my studies of how do proper data modeling for these using ScyllaDB.

2. Let's Research

First things first, right? After deciding what to cover in my talk, I needed to understand how to build this data model.

We'll need a posts table and also a post_likes table that relates who liked each post. So far, it seems enough to do our likes counter.

My first bet for a query to count all likes was something like:

Ok and if I just do a query with SELECT count(*) FROM social.post_likes it can work, right?

Well, it worked but it was not as performant as expected when I did a test with a couple thousands of likes in a post. As the number of likes grows, the query becomes slower and slower...

"But ScyllaDB can handle thousands of rows easily… why isn’t it performant?" That's probably what you're thinking right now (or maybe not).

ScyllaDB – even as a cool database with cool features – will not solve the problem of bad data modeling. We need to think about how to make things faster.

3. Researching Data Types

Ok, let's think straight: the data needs to be stored and we need the relation between who liked our post, but we can't use it for count. So what if I create a new row as integer in the posts table and increment/decrement it every time?

Well, that seems like a good idea, but there's a problem: we need to keep track of every change on the posts table and if we start to INSERT or UPDATE data there, we'll probably create a bunch of nonsense records in our database.

Using ScyllaDB, every time that you need to update something, you actually create new data.

scylla@cqlsh:socials> INSERT INTO socials.posts (id, user_id, description, image_url, created_at, likes) VALUES (4d18bb8c-9c57-44fe-827a-4a2d65f331e5, 3edd5f1d-67e9-4a3e-af1a-9adbb41e2129, 'Such a cool event P99 Conf!', 'https://i.imgur.com/Xp8gi7t.jpg', '2023-04-23 15:02:49', 1);

scylla@cqlsh:socials> INSERT INTO socials.posts (id, user_id, description, image_url, created_at, likes) VALUES (4d18bb8c-9c57-44fe-827a-4a2d65f331e5, 3edd5f1d-67e9-4a3e-af1a-9adbb41e2129, 'Such a cool event P99 Conf!', 'https://i.imgur.com/Xp8gi7t.jpg', '2023-04-23 15:02:50', 2);

scylla@cqlsh:socials> INSERT INTO socials.posts (id, user_id, description, image_url, created_at, likes) VALUES (4d18bb8c-9c57-44fe-827a-4a2d65f331e5, 3edd5f1d-67e9-4a3e-af1a-9adbb41e2129, 'Such a cool event P99 Conf!', 'https://i.imgur.com/Xp8gi7t.jpg', '2023-04-23 15:02:51', 3);

scylla@cqlsh:socials> SELECT * from posts;

 id                                 | user_id                           | created_at                    | description               | image_url                     | likes
--------------------------------------+--------------------------------------+---------------------------------+-----------------------------+---------------------------------+-------
 4d18bb8c-9c57-44fe-827a-4a2d65f331e5 | 3edd5f1d-67e9-4a3e-af1a-9adbb41e2129 | 2023-04-23 15:02:48.000000+0000 | Such a cool event P99 Conf! | https://i.imgur.com/Xp8gi7t.jpg |    1
 4d18bb8c-9c57-44fe-827a-4a2d65f331e5 | 3edd5f1d-67e9-4a3e-af1a-9adbb41e2129 | 2023-04-23 15:02:50.000000+0000 | Such a cool event P99 Conf! | https://i.imgur.com/Xp8gi7t.jpg |    2
 4d18bb8c-9c57-44fe-827a-4a2d65f331e5 | 3edd5f1d-67e9-4a3e-af1a-9adbb41e2129 | 2023-04-23 15:02:51.000000+0000 | Such a cool event P99 Conf! | https://i.imgur.com/Xp8gi7t.jpg |    3

You will have to track everything that changes in your data. So, for each increase, there will be one more row unless you don't change your clustering keys or don't care about timestamps (a really stupid idea).

After that, I went into ScyllaDB docs and found out that there's a type called counter that fit our needs and is also ATOMIC!

Ok, it fit our needs but not our data modeling. To use this type, we have to follow a few rules but let's focus on the ones that are causing trouble for us right now:

The only other columns in a table with a counter column can be columns of the primary key (which cannot be updated).
No other kinds of columns can be included.
You need to use UPDATE queries to handle tables that own a counter data type.
You only can INCREMENT or DECREMENT values, setting a specific value is not permitted.

This limitation safeguards correct handling of counter and non-counter updates by not allowing them in the same operation.

So, we can use this counter but not on the posts table... Ok then, it seems that we're finding a way to get it done.

4. Properly Modeling

With the information that counter type should not be "mixed" with other data types in a table, the only option that is left to us is create a NEW TABLE and store this type of data.

So, I made a new table called post_analytics that will hold only counter types. For the moment, let's work with only likes since we have a Many to Many relation (post_likes) created already.

These next queries are what you probably will run for this example that we created:

## Social when you like a post

UPDATE socials.post_analytics SET likes = likes + 1 WHERE post_id = 4d18bb8c-9c57-44fe-827a-4a2d65f331e5;

INSERT INTO socials.post_likes (post_id, user_id, liked_at) VALUES (4d18bb8c-9c57-44fe-827a-4a2d65f331e5, 3edd5f1d-67e9-4a3e-af1a-9adbb41e2129, '2023-04-23 15:02:50');

# Social when you dislike a post

DELETE FROM socials.post_likes WHERE post_id = 4d18bb8c-9c57-44fe-827a-4a2d65f331e5 AND user_id = 3edd5f1d-67e9-4a3e-af1a-9adbb41e2129;

UPDATE socials.post_analytics SET likes = likes - 1 WHERE post_id = 4d18bb8c-9c57-44fe-827a-4a2d65f331e5;

Now you might have new unanswered questions in your mind like: "So every time that I need a new counter related to some data, I'll need a new table?" Well, it depends on your use case. In the social media case, if you want to store who saw the post, you will probably need a post_viewers table with session_id and a bunch of other stuff.

Having these simple queries that can be done without joins can be way faster than having count(*) queries.

5. Final Considerations

Me at CityJS stage saying a bunch of nonse data modeling using TS

I learned a lot not only by studying new ways of data modeling but also having to learn TypeScript to create the CityJS presentation and build this use case.

As everything is brand new for me, I'll do my best to keep sharing my studies. Please feel free to correct me in the comments! Discussing is the best way to learn new things.

Don't forget to like this post, follow me on the socials and fill your water bottle xD

Twitter DanielHe4rt PT-BR
Twitter DanielHe4rt EN
Twitch Channel

DEV Community: ScyllaDB

Database 101: SSL/TLS for beginners

Table of Contents

1. Prologue

2. What is SSL and TLS?

3. OpenSSL for Noobs

4. Database Authentication

4.1 No Authentication

4.2 Username/Password Authentication

4.3 Creating Users and Roles

4.4 Certificated / Role Certificated Authentication

4.4.1 Change the Authentication Type

4.4.2 Enable the Encryption Port

4.4.3 Enable the Client Encryption Port

5. Testing Encrypted Connections

6. Conclusion

How to Build a Low-Latency Video Streaming App with ScyllaDB & NextJS

Video streaming app features

Technology stack

Using ScyllaDB for low-latency video streaming applications

Page: Continue Watching

Page: Watch Video

Page: Most Recent Videos

Clone the repo and get started!

Wrapping up

Performance e elegância! Escrevendo uma CLI CRUD utilizando ScyllaDB e Ruby

Table of Contents

1. Iniciando o projeto

1.1 Resolvendo bibliotecas de sistema para instalar o driver

1.2 Iniciando o projeto

1.3 Instalando dependências de projeto

1.4 Definindo um REPL

2. Definindo a camada de injeção de dependências

2.1 Criando o container principal

2.2 Criando o provider de banco de dados

Definindo as constantes para nosso projeto

Criando uma classe utilitária para criar nosso banco

Checando se um keyspace ou tabela existe

Criando os keyspaces e tabelas

2.3 Carregando o novo provider na nossa aplicação

3. Definindo o boilerplate para nossa CLI

4. Implementando nossos comandos

4.1 Implementando o primeiro comando Add

4.2 Implementando o comando List

4.3 Implementando o comando Delete

4.4 Implementando o comando Update

Conclusão

Database 101: How does migration between databases work in the real world?

Table Of Contents

1. Prologue

2. Database Migrations: How to Start

3. Why are we doing this migration?

4. Proof of Concept: where to start?

4.1 PoC: Same Paradigm

4.2 PoC: Different Paradigm

5. Testing it before it's too late! (seriously)

6. Migration Strategies

6.1 Strategy: Cold Migration

6.2 Strategy: Hot Migration

7. Final Considerations

Database 101: Twitch Bot in a higher level for beginners.

Table Of Contents

1. Prologue

2. The Project: Twitch Sentinel

3. Making Good Decisions

3.1 ACID Acronym

3.2 BASE Acronym

3.3 Concluding a Good Decision

4. Modeling our Ideas into Queries

4.1 Number of messages per user (top 5)

4.2 Number of unique users per stream(er)

4.3 Earliest and latest message of a given user

5. Twitch as our payload!

6. Make it BURN!

7. Final Considerations

Database 101: How social media “likes” are stored in a database

Table of Contents

1. Prologue

2. Let's Research

3. Researching Data Types