Stream and Display Data in Realtime with Materialize and Adonis

In this tutorial, we are going to build a web application using and integrate it with to create a real-time dashboard based on streaming data using standard SQL. AdonisJS Materialize Materialize lets you define views you want to maintain on your data, just as you would with any SQL table, and then get the results in real-time, unlike traditional databases which frequently act as if they've never been asked that question before. Prerequisites You need to have the following things installed before getting started: Install Docker Install Docker Compose Install Node.js What is Materialize that takes data coming from sources like Kafka, PostgreSQL, S3 buckets, and more and allows you to effectively transform it in real-time using SQL. is a streaming database Materialize Unlike a traditional database, Materialize is able to incrementally maintain views on top of streaming data, providing fresh and correct results as new data arrives. This means that, instead of recomputing the view from scratch every time it needs to be updated, it only does work proportional to the changes in its inputs, so it is fast and efficient. In the context of web development, Materialize can be used as a backend to power real-time applications (as we'll see in this demo)! Running a Materialize Demo For the sake of this tutorial we are going to run the following Materialize Demo: Materialize - Log Parsing Demo The setup of the demo is the following: We would not get into too much details here, but if you have not gone through this demo before, make sure to read it! To run the demo, follow these steps: First things first, before you could run the demo, you need to clone the repository: Clone the repository: git clone https://github.com/bobbyiliev/mz-http-logs.git Once that is done, switch to the repository directory: cd mz-http-logs Then start all services: docker-compose up -d With that, you would have your Materialize instance up and running. Next we will prepare our AdonisJS installation and use AdonisJS to create our Materialize sources and views! What is AdonisJS? AdonisJS is a web framework for Node.js. It includes everything that you would need to create a fully functional web application or an API. AdonisJS has been inspired by Laravel and it has its own ORM, Auth support, and a CLI tool called Ace which is very similar to Artisan. At the end we would extend the Log parsing Materialize demo and have the following setup: Install AdonisJS Let's start by installing AdonisJS. To do so, you would need to run the following command: npm init adonis-ts-app@latest hello-materialize Once you run that, you will be asked to select a project structure. You will be able to choose between an API, Web App, and a minimal possible AdonisJS app: CUSTOMIZE PROJECT ❯ Select the project structure … Press to select api (Tailored for creating a REST API server) ❯ web (Traditional web application with server-rendered templates) slim (A smallest possible AdonisJS application) For this tutorial let's go with the app! Using your arrow keys select and hit enter. web web After that you will be asked to choose a name for the project, I will leave this as but feel free to choose a different name. hello-materialize I will then press enter and say yes to the rest of the settings: ❯ Enter the project name · hello-materialize ❯ Setup eslint? (y/N) · y ❯ Configure webpack encore for compiling frontend assets? (y/N) › y This will instantiate the project and might take up to a minute to complete: Once ready, you can into the new project directory: cd cd hello-materialize And then start the webserver: node ace serve --watch If you are coming from the Laravel world, this would be just like running . The CLI tool is just like and comes with a lot of the same functionalities. php artisan serve ace artisan To check all of the commands, you can run: . ace node ace Installing Lucid Lucid is the AdonisJS ORM. It is quite similar to Laravel Eloquent. Lucid comes with an Active Record ORM, Query Builder, Migrations, Seeds, and Factories. Let's go ahead and install it! To do so, just run the following command: npm i @adonisjs/lucid Once done, you would need to do a quick configuration. Configuring Lucid In order to configure Lucid, you need to run the following command: ace node ace configure @adonisjs/lucid You will be asked to select the database driver that you want to use. As Materialize is wire-compatible with PostgreSQL, you can connect to it using any driver; here, make sure to select PostgreSQL! pg ![AdonisJS lucid configuration](https://cdn.hackernoon.com/images/ckwysasxk-004-m-0-as-6-d-20-z-32-q-0.jpg) Next, you will be asked to select where you want to display the configuration instructions. I chose , which prints out the necessary environment variables that you have to add to your file. In the terminal .env Configure the Materialize env variables In order to let our AdonisJS application connect to Materialize, we need to change the details in the file. PG_* .env With your favorite text editor, open the file and update the environment variables to: .env PG_ DB_CONNECTION=pg PG_HOST=localhost PG_PORT=6875 PG_USER=materialize PG_PASSWORD= PG_DB_NAME=materialize This will allow AdonisJS to connect to Materialize just as it would when connecting to PostgreSQL. One thing to keep in mind is that Materialize doesn’t yet support the full system catalog of PostgreSQL (we're working on it!), which means that ORMs like Lucid, Prisma, Sequelize, or TypeORM might fail during some attempts to interact with Materialize. As we work to broaden coverage, the integration with these tools will gradually improve! pg_catalog Creating a Controller Let's create a controller where we will add the functionality that would allow us to connect to Materialize! As the Materialize demo is simulating an application log with a lot of visitors, let's call our AdonisJS controller : VisitorsController node ace make:controller VisitorsController This will create a controller file at: app/Controllers/Http/VisitorsController.ts Next, let's create the routes that we would need! Creating the AdonisJS routes Your routes file is stored at . In there we can specify our application URLs and map them to different controllers and methods! start/routes.ts We do not yet have the methods ready, but we know that we would need the following routes: : When visited, this route would create a Materialize /source source : When visited, this route would create a /view materialized view : This route would return an event stream with all of the latest changes to our materialized view /visitors : This will be the landing page where we will display the streaming data that we are getting from the endpoint and Materialize / /visitors Open your routes file at and update it so that it has the following content: start/routes.ts import Route from '@ioc:Adonis/Core/Route'

Route.get('/', 'VisitorsController.index') Route.get('/visitors', 'VisitorsController.visitors') Route.get('/source', 'VisitorsController.source') Route.get('/view', 'VisitorsController.view') Next, let's add a method that would allow us to create a Materialize source as described in the ! Materialize Log Parsing Demo Creating a Materialize Source from logs If you were accessing Materialize directly via a SQL client (like ), in order to access data from a continuously produced log file, you would execute the following statement: psql CREATE SOURCE requests FROM FILE '/log/requests' WITH (tail = true) FORMAT REGEX '(?P \d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}) - - \[(?P [^]]+)\] "(?P (?:GET /search/\?kw=(?P [^ ]*) HTTP/\d\.\d)|(?:GET /detail/(?P [a-zA-Z0-9]+) HTTP/\d\.\d)|(?:[^"]+))" (?P \d{3}) -'; Let's see how we could do that via AdonisJS! First, open the file with your favorite text editor. app/Controllers/Http/VisitorsController.ts The file would have the following content initially: // import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'

export default class VisitorsController {} There are a few things that we would want to do: Import Lucid: import Database from '@ioc:Adonis/Lucid/Database' Then inside the VisitorsController class, let's create a method called and source // import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext' import Database from '@ioc:Adonis/Lucid/Database'

export default class VisitorsController {

public async source({request, response}) {

//Using Ludic to connect to Materialize, we are executing a CREATE SOURCE statement const res = await Database.rawQuery( `CREATE SOURCE requests FROM FILE '/log/requests' WITH (tail = true) FORMAT REGEX '(\\?P \d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}) - - \[(\\?P [^]]+)\] "(\\?P (\\?:GET /search/\\\?kw=(\\?P [^ ]*) HTTP/\d\.\d)|(\\?:GET /detail/(\\?P [a-zA-Z0-9]+) HTTP/\d\.\d)|(\\?:[^"]+))" (\\?P \d{3}) -';` ); return res;

}

} Now, if you were to visit the URL via your browser ( ) it would create your Materialize source: /source http://127.0.0.1:3333/source Creating a Materialize View You may be familiar with materialized views from the world of traditional databases like PostgreSQL, which are essentially cached queries. The unique feature here is the materialized view we are about to create is automatically kept up-to-date. Let's do the same thing as before, but to create a materialized view based on our file source! To do that, let's create a method called with the following content: view Add this right after the end of the method source public async view({request, response}) {

//Using Ludic to connect to Materialize, we are executing a CREATE VIEW statement const res = await Database.rawQuery( `CREATE OR REPLACE MATERIALIZED VIEW unique_visitors AS SELECT count(DISTINCT ip) FROM requests;` ); return res;

} Our materialized view would show the count of the unique visitors flowing through our demo application. To create the view, visit the URL via your browser (eg. ). /view http://127.0.0.1:3333/view With that, our view will be created and we can move on to the next step! Creating an event stream You can query the new materialized view, that we've just created, as usual with a standard statement: SELECT SELECT * FROM unique_visitors; However, in order to take full advantage of the incrementally updated materialized view right from our AdonisJS app, rather than querying Materialize with a standard to get the state of the view at a point in time, we will use a statement to request a stream of updates as the view changes. SELECT TAIL public async visitors({request, response}) {

// First we set a header to identify that this would be an event stream response.response.setHeader('Content-Type', 'text/event-stream');

// Then we declare a TAIL cursor await Database.rawQuery('BEGIN'); await Database.rawQuery('DECLARE visitors_c CURSOR FOR TAIL unique_visitors');

// Finally we use FETCH in a loop to retrieve each batch of results as soon as it is ready while (true) { const res = await Database.rawQuery('FETCH ALL visitors_c'); response.response.write(`data: ${JSON.stringify(res.rows)}\n\n`) } } For more information about , make sure to check out the official documentation here: TAIL statement. Materialize TAIL If you were to now visit the URL via your browser, you would see the following output: /visitors Next, let's create a view where we would use the endpoint as an event source and continuously update our web page. /visitors Displaying the number of unique visitors on the frontend First, before we get started, make sure that you've executed the following command to configure Encore which is used to compile and serve the frontend assets for your AdonisJS app: node ace configure encore Then create a new file at: resources/views/visitors.edge And add the following content: Materialize and AdonisJS @entryPointStyles('app') @entryPointScripts('app')