DEV Community: leibole

Evaluating Supabase

leibole — Wed, 21 Oct 2020 08:23:36 +0000

Introduction

I'm a freelance web developer working on various projects regularly. I use Firebase a lot when I need to get up and running quickly. I recently heard about "Supabase - the open source Firebase" (in a great podcast episode), and thought I'd try it to see how it stands to the promise. Tl;dr: in a lot of ways it's already better :)
The nickname "Open source firebase" does it a bit of injustice. Unlike firebase, Supabase is based on an SQL database - Postgresql (with all the pros and cons). Supabase also offers a great hosted version of its open source project, with a decent management UI including many features, as well as real time capabilities, on top of a ready made javascript library (more clients to come).

My Use Case

I tested Supabase with one of my existing projects (built with Firebase's Firestore). It is a software for managing zoos, used for keeping track of all the animals in a given zoo. The main entities in the db are "Animals" and "Events". An animal can have many events, and each event can be reported for exactly one animal.
The scale of the project is not big in total, however each zoo has quite a lot of data. There are hundreds of zoos in the system, and each zoo can have thousands of animals and tens of thousands of events.

Supabase Evaluation

To test Supabase, I focused on a number of important criterias: setup, project integration and management UI. Here are my conclusions on there:

Setup

Setting up a Supabase database is very quick and easy. I got a database up and running in about 5 minutes. You also get auto generated docs with the details of the new project. It took a few more minutes to set up my tables from the UI, and configure the schema for my two tables (animals and events).

Integration

The integration into my existing project was really easy, and copied directly from the generated docs. It looks like this:



const supabaseUrl = "https://edvkppzqwycrasvjykbo.supabase.co";
const supabaseKey = "<LONG_KEY>";
const supabase = createClient(supabaseUrl, supabaseKey);

const { body } = await supabase
     .from("events")
     .select("*")

Unlike most of the projects I work on, this code "just worked" on the first attempt. I was very surprised. There are still some ways for Supabase to go in terms of authentication - the key used here is only suitable for server environments, but still the integration is easy.
User Interface
The user interface offered by Supabase is very useful. A user can create, edit and manage tables directly from the UI, as well as run queries and see results. It is still very glitchy, I met a lot of bugs in just my short usage. Nonetheless, its usability is already far broader than that of Firebase or Firestore.

The supabase UI

Performance Evaluation
The main reason that led me to look for a Firebase alternative is the lacking performance. At the moment I'm sometimes querying for thousands of records at once, which can take a few seconds in Firebase. This hurts user experience and leads to compromises in the UI I implement, to keep these performance issues from showing.
I tested the performance in a few stages:
Migrate the data:
I chose a single zoo and transferred its data. I wrote a script to read the data from Firebase, and write it to Supabase.
All it took to write 31,666 rows of data to Supabase was this one row (I wrote a few more lines of code for preparing the data):



   await supabase.from("animals").insert(animalsToWrite);

It also worked super fast, around 10-15 seconds for the write to complete.
Compare reading the data in Supabase and Firebase:
Here is the code for reading the events rows from Firebase:



const readFirebaseData = async () => {
 console.time("test");
 const events = await db
   .collection("zoos")
   .doc("example")
   .collection("events")
   .get();

 console.log(events.docs);
 console.timeEnd("test");
};

And The result - 28 seconds for reading 16,753 documents from firebase:

Similarly, the code for testing Supabase was:



const readData = async () => {
 console.time("test");
 try {
   const events = await supabase.from("events").select("*");
   console.log(events.length);
 } catch (e) {
   console.error(e);
 }

 console.timeEnd("test");
};

And result - a whooping 31,666 rows read in the 1.5 seconds:

Bonus: Easy BI Integration

As part of my project the web application is connected to a BI system - Google Datastudio. To use it with Firebase, I need to first copy the entire DB into a structured DB, like Big Query. I use a process that runs once a day, and copies all the Firebase data into BigQuery.
When using Supabase, copying the data is not needed. Supabase provides each project with a dedicated DB. The DB URL is easily found in the management UI. I just passed this URL to the BI system and Violla! The system is connected to a great BI.

The Downsides

Like anything, Supabase has its downsides:

The real-time functionality still cannot be securely enough used from client code.
Authentication still has a ways to go for it to be possible.
The UI is very glitchy and very raw. I found numerous annoying bugs just by using it for about half an hour. I had to connect with my local psql client to get around them.
The pricing is free for now, which seems weird. I worry that when I get to larger amounts of data I might be limited. Another worry is that they will begin to charge large sums when I'm seriously locked in.
I didn't see a parallel to Firebase Functions, where I could extend the app's functionality with custom serverless code, triggered by events from the Firebase database.

Conclusions

Supabase looks very promising. Offering an easy to setup and use Postgresql DB, with great client libraries seems like a no brainer. The performance is great, and ease of use is as good as it gets.
Nonetheless, the product is still in alpha, and it shows. I will wait a couple of months for some of the issues to be sorted out. After that, I will definitely attempt to migrate my app to Supabase.
The performance enhancements could be gained just by moving to a standard managed postgres DB, but combining the ease of use Supabase offers drives it over the edge for me.

React (injected) Chrome extension

leibole — Sat, 18 Apr 2020 15:04:54 +0000

Intro

In this post, I'll quickly run through some very useful information on how to inject a React app into an existing web page. I've used it to extend a specific web app that had no other way of extending, but it can be useful for many other scenarios. There's also a double bonus:

I'll show how to run the extension in a dev environment.
We'll see how to auto-reload the extension once the code is changed.

Stage 1: Create React App

It seems like every React how to tutorial starts with this line, and so does this one. Create a new react app using Create React App. I have actually created a react app with typescript enabled:

npx create-react-app my-app --template typescript

Now we have a basic react app, with the react default content. Let's replace the contents off App.tsx with the most basic content to inject:


import React from 'react';

const App = () => {
  return <div>Some injected content</div>
}

export default App;

Stage 2: Extension manifest file

Each extension needs a manifest file (see extension manifest file). Our file should be located in the public folder, and should look something like this:

{
  "name": "Extension name",
  "version": "1.0",
  "manifest_version": 2,
  "browser_action": {
    "default_popup": "index.html"
  },
  "content_security_policy": "script-src 'self' 'sha256-<the extension hash>'; object-src 'self'",
  "background": { "scripts": ["hot-reload.js"] }, // see bonus :)
  "content_scripts": [
    {
      "matches": ["<all_urls>"],
      "css": ["/static/css/main.css"],
      "js": ["/static/js/main.js"]
    }
  ]
}

Stage 3: Eject Create React App

I always prefer to avoid ejecting a Create React App (CRA) project, but we have to in this case. We want the output files to always be named main.js and main.css and avoid the random hash in the file name that's used by default in CRA. So let's run

npm run eject

We need to edit the webpack.config.js file: we need to remove the "chunkhash" from the output file names, both main.js and main.css.
We can now run

npm run build

and get the built files output. One thing is still missing: the actual injection code.

Stage 4: Injecting the React App

Now usually in a normal React App, we'll create a

<div id="root></div>

inside the index.html file, and then call

ReactDOM.render(<App />, document.getElementById("root"));

to insert the app.
The injection code is very similar: we choose where to inject the app (for example - the body element), and append a div to it with the id "root":

const app = document.createElement("div");
app.id = "root";
document.body.append(app);
ReactDOM.render(
  <App />,
  document.getElementById("root")
);

And just like that, the React App is appended to the body.

Bonus #1: Run in Dev mode

The basic usage of the app is now as an injected div. But in that case, whenever we make a change we have to reload the extension, and even worse, the code is the built code, uglified and minified and unreadable. What can we do in development?
Just have the app inject itself as a normal React app. include the normal root code in the index.html file, and in index.tsx check if the environment is development, and if so attach the React app to the root element:

if (process.env.NODE_ENV === "development") {
  ReactDOM.render(
    <App />,
    document.getElementById("root")
  );
} else {
  const app = document.createElement("div");
  app.id = "root";
  document.body.append(app);
  ReactDOM.render(
    <App />,
    document.getElementById("root")
  );
}

Bunos #2: Auto reload the extension on file changes

To avoid having to manually reload the extension on each rebuild, I've used a hot-reload code snippet I found in this repo https://github.com/xpl/crx-hotreload. Just copy the file hot-reload.js from this repo into your public folder, and include this line in the manifest file (already included in the example manifest file above):

"background": { "scripts": ["hot-reload.js"] }

The file from the repo needs a small change to work well with the React ecosystem: a short timeout (~10 seconds) needs to be added before reloading the extension, to allow for the build to complete.

Good luck, you're welcome to comment if you have any questions.

Working with BigQuery Analytic Functions

leibole — Mon, 21 Oct 2019 08:05:10 +0000

1. Introduction

Most of the day-to-day database users can make do with the standard database functionality. Want to get all the friends of a certain user? Just join the users table with the friends table and there you have it (btw — it’s never that simple). If you’re a more advanced user, you might use aggregations and ordering.

However, sometimes you can’t get the insights you want with just joins and aggregations. When I faced such a problem, my first instinct was to load all the data into a simple python/javascript/ruby/(favorite dynamic programming language) and calculate it there. But it meant loading a big amount of data into the script, while losing the parallel computation power BigQuery has to offer. Also, it meant more moving parts in the system and more deployment details.

That’s when a simple google search (I think it was “BigQuery calculate row based on other rows”) led me to BigQuery Analytic Functions (the concept is not specific to BigQuery).

In this post, I will describe what these functions are, what was the problem I was trying to solve, how I solved it with Analytic functions and my conclusions from the process.

Ok then…

2. The problem at hand (simplified)

The dataset I was working with is local rain data. It basically has three columns: date, location and amount. Each row represents the amount of rain (in mm) that poured on each date in a certain location.

What I wanted to extract was an aggregation by “rain events” in a location. A “rain event” is defined as one or more consecutive days of rain. Once there’s a rain-free day, the event is over. That means I would like to aggregate together consecutive rainy days and get the average and total rain for each “rain event”.

Let’s take a look at a quick example. The dataset:

Original rain days table

We can see three different rain events (consecutive days of rain):

2019–01–01 to 2019–01–03 in New York
2019–03–31 to 2019–04–01 in New York
2019–05–25 to 2019–05–27 in LA

And the resulting table I wished to get from the input dataset should look something like:

Required results table

How can this be calculated with an SQL query? Grouping by month or week won’t work. As far as I saw, the best choice was to use Analytic Functions.

3. Analytic functions: description and syntax

Analytic functions perform operations on groups of rows, same as aggregations (GROUP BY and the likes). There are two major differences:

a. Analytic functions return a single value for each of the input rows.

b. The group of rows for the operation is defined using a window.

Analytic functions are added as part of the SELECT clause, and with them, one has to specify three parameters:

a. The partition (breaks input table into different groups).

b. The order (orders the rows in each partition); Order influences the operation’s result, as we’ll see later.

c. The window — each row is operated on with a group of other rows, which are defined by the window parameter.

Let’s have a look at a simple example and explain the syntax:

SELECT
SUM(amount) OVER (PARTITION BY location ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM rain_data

The partition and order parts are pretty clear, just mention the column names like ordinary GROUP BY and ORDER BY inputs.

The window part needs some explaining: it states that for each row, the operation should look at a window which includes the row itself, one row before it (1 PRECEDING), and one row after it (1 FOLLOWING). So the operation calculates the sum of three rows, but it does that for every row in the input table and gives the fitting result for each row.

The flow (leaving out the order part) is depicted in the following chart:

More information on the syntax can be found in the BigQuery documentation (it’s not that bad): https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts

The example query did not solve my problem. What did solve my problem? On to the next section.

4. Solving the problem

So how can Analytic functions help? In my case, I wanted to partition by location, order by date, and use a window of two rows.

Why two rows? For each row we would calculate the date difference between the current row and the previous one. If the difference is larger than a single day, then it belongs to a different rain event.

Lucky for me, BigQuery has a built-in Analytic function for working with the previous row: the LAG function (for some reason it is called a navigation function). The LAG function references the previous row, according to a provided Partition and Order (a window is not necessary!).

So Let’s see how the initial query looks like:

SELECT * FROM (
SELECT *,
DATE_DIFF(date, LAG(date) OVER(PARTITION BY location ORDER BY date), day) AS days_from_last_rain
FROM rain_example
) AS t
WHERE
days_from_last_rain > 1
OR days_from_last_rain IS NULL

The result of this query (on the example table above):

This is the set of rainy days that were the first in a rain event. We got it by selecting the diff between the date of the previous row and the date of the current row. We then filtered all those with a diff of 1 day, as those have preceding rainy days.

Now we can connect each rainy day from the original dataset to the correct rain event, and aggregate each group to get the average and sum

5. Conclusions

There’s a bunch of ‘hidden’ functionality when you dive into the BigQuery documentation (or any other database for that matter). So next time before you load data into a script and start hacking, give the documentation a look (or just google it).

Repost medium article

leibole — Fri, 21 Sep 2018 22:08:18 +0000

How do I repost an article I wrote on medium to dev.to?

Real world data processing with Google Cloud Platform

leibole — Fri, 21 Sep 2018 22:00:25 +0000

How I built a data pipeline from an end user’s legacy software to a modern cloud infrastructure using Google Cloud Platform and very little money

Introduction

My name is Ido, and I’ve been in the tech industry for quite a while. My latest project is aimed at bringing some modern cloud and data technology into the world of farming. I’ve started with a simple enough goal: deliver the data from present-day (out dated) farm management software into the cloud. There we can conduct advanced analysis and help farmers better manage their farms.

I am on a very low budget, without outside investors or any spare money. Also, as it’s only me working on this project, I will need to keep any solution as simple as possible, and beware of wasting my time on unnecessary optimisations/features.

To incorporate for these requirements I’ve used Google Cloud Platform’s set of tools. I’ve found that they have a good approach to data processing and analysis. Moreover, they really believe in managed services, which should save me a lot of time. They do have some shortcomings, which I’ll also go into details about.

On to the real stuff…

High Level Description

This is what we set out to do in the project:

Data files are collected on each farm’s local computer. The computers are connected to the internet and the files are stored locally in .csv format.
Send these files to the cloud.
Write these files’ contents into a cloud based database, after (deep) parsing and cleansing.
Visualise and display the aggregated data on a web, mobile friendly, page.
Make the data accessible for further analysis and whatever data science magic we may want to apply to it.

Architecture

I’ll start with a diagram that describes the full flow the data makes from farm to its end goal (be it dashboards or data science stuff). Following it I’ll describe each stage and how it’s implemented on google cloud:

Data pipeline architecture diagram

Local computer to cloud storage: local .csv files are uploaded to Google Cloud Storage, a normal cloud object storage. Done once a day, and fairly simple.
Pub/Sub job: a Pub/Sub topic gets a job on each new Google storage object uploaded. Very easy to configure this with google’s CLI tool. You can just specify a path prefix for queueing, as well as an HTTP endpoint that will be called each time a new file is created. And of course google Pub/Sub itself is managed and infinitely scalable.
I did have a couple of (self-inflicted) messes with it: at my first usage, I didn’t specify a path prefix, and on my triggered HTTP endpoint I copied the uploaded file to a new location. This of course triggered a new job, and so on, causing an infinite loop. Oops. Another thing is that you have to make sure to return status 200 OK to the PubSub service, otherwise is will keep bombing you with requests. It’s part of that ‘deliver at least once’ promise you receive from the service.
App Engine: this is where I implemented the HTTP endpoint. It copies the new data file to another cloud storage bucket according to its type (a few types of .csv files can be uploaded) and source (which farm it came from). I used the python language for the implementation.
I found App Engine to be an interesting solution for simple work. It’s easier to set up than real servers, but has a lot of pitfalls that if you’re not careful can (and did) cost a lot of time. I’ll go over some quick points:
- There are two App Engine environments: Standard and Flexible. Basically standard is like a sandbox version of the programming language you’re using, but it’s supposed to be easier to deploy (it’s not really easier). The biggest difference I saw: the flexible environment is much more expensive than the standard.
- When using the standard environment to interact with other Google Cloud services, and running your program locally, the standard environment will work with ‘mock’ services on your local machine. When I worked with google’s Datastore service (a managed cloud DB), I expected to see the changes from my local environment reflected in the cloud DB, but instead it was reflected in a local mock that was hard to find.
- Logging and versioning and a lot of other stuff are right out of the box, which is very convenient I have to say.
Data parse and cleanse: I did this on a new and cool tool called Google Dataprep (by Trifacta). It’s very useful and simple for parsing and cleaning data, I’ll go over it in the next section (recommended tools). It has a native write mechanism to Google BigQuery, which is next in the pipeline.
Write to cloud DB: I used Google’s BigQuery as my cloud DB. It’s a really awesome tool, and provides very fast data queries. See more in the recommended tools section.
Visualise the data: I’ve used google’s Data Studio for connecting to BigQuery and visualising the data. It is a fairly new BI tool. It’s very lean compared to classic BI tools (Tableau, QlikView etc.), but also very clean and intuitive. It’s still in beta, and that was definitely felt with many minor bugs and annoying pitfalls. Being a simple tool is also good: It is very easy to learn and get by with, and it looks very good and clear. It also has native and direct connectors to BigQuery and integrates very well with it. The data is displayed fast, and very easy to configure.
Research the data: I haven’t done much of this, but it’s a very small step to make. Google offers managed Jupiter notebooks that are directly connected to BigQuery, for researching the data, and creating production machine learning models.

Recommended Tools

Dataprep: “An intelligent cloud data service to visually explore, clean, and prepare data for analysis.” (from the product page). It is basically a UI based parsing and cleansing tool, and it is very good at that. You can define parsing and cleaning using a very comprehensive UI. It saved me a ton of time on writing regex’s and applying rules on different types of encodings, deleting empty rows etc.
Some more advantages: it integrates well with Cloud Storage and BigQuery, it has a convenient job scheduling mechanism, and it looks great. The worst thing about it (it could be an advantage for some): it runs the parsing and cleaning job created on the UI with a very big overhead. I think this happens because it’s aimed at much larger scale data than what I’m using (I won’t go into any deeper details here), but the bottom line is that even the smallest parse job takes about 5 minutes. I considered moving from it because of this, but the usability and integration to other services are so great the I stuck with it.
BigQuery: a cloud hosted (and managed) analytics database. It is relational, and integrates really well with all of Google’s other services. Also, most BI and machine learning tools have good integrations for it, as it has become really popular tool. It has a good web based UI for querying and understanding the data. There’s no need to set indexes (it’s supposed to be indexed on every column). I really enjoyed using it and found that it has mostly advantages. Most queries I’ve used just returned really quickly with the results. However, on the odd case that they don’t, it’s a little hard to optimise, as a lot of the database details are abstracted away.

Billing/Costs

The billing has been very reasonable, although my customer base is still relatively small (I have tens of farms I work with). The entire testing and setting up of the environment were all within the free tier, so no need to commit for any payment before launching.

At the moment I’m supporting tens of farms with a monthly cost of 10–15$. A big part part of this cost can be ‘optimised away’, but as mentioned I try to avoid too early optimisations.

Conclusions

Google Cloud Platform helped me a lot in getting up and running with the project quickly and efficiently. Their managed services approach is great for these purposes. All their services work very well together, and offer full end to end solution for web app development and data pipelines.

Nonetheless, it has its good share of downsides. The documentation isn’t always comprehensive enough, and just googling for a solution doesn’t work that well with a lot of the services being new and not that common yet. Moreover, issues in managed services are harder to solve, as a lot of the details are abstracted away.

I left all of the technical hard core details out, but be sure that I faced many pitfalls and nitpicks. Feel free to leave comments and questions, and I can dig deep into a lot of the tools I’ve used if you’re interested in anything specific.

Service-ception: how and why we’ve built a service inside a service

leibole — Thu, 31 May 2018 13:48:38 +0000

The road to micro-services architecture: creating a service within a service

Introduction

Here at Yotpo, we run a very high scale (tens of millions of requests per day) web system. We ship our product to eCommerce web sites, each with its own scale of shoppers, all of which we serve.

One of the products we offer our customers is User Generated Images (images created by shoppers). We recently started working on a new product offering — an Album Widget. In this widget, shop owners can choose images which their shoppers uploaded, and show these images on an onsite widget. Moreover, we will allow them to moderate and add images from instagram to the album.

The images, the album, and the album creation experience will all be developed and served by Yotpo. Obviously this product requires a lot of backend entities and code, which will live and engage with the rest of our existing code base.

In the following post we will try to describe how we kept a service oriented approach in the development process, while using the same code base and the same process. We will discuss what we did, why we did it, and the advantages and disadvantages we found for our approach along the way.

The problem

No one can ignore the crazy hype going around ‘micro-services’ architecture these days. Describing the architectural merits is beyond the scope of this post. Suffice to say that the few services we currently own have grown to massive proportions, and our goal as developers is to avoid adding any new code to them. Instead, it is preferred to write new, small services that are as decoupled as possible from existing services.

However, creating a new service has a considerable overhead effort for anyone. It means creating a whole new deployment process, repository, database, tooling chain and more. The time constraints on R&D projects are always very pressing, and product teams often have to surrender a lot of feature content to meet them.

Developers constantly seek to find a way to keep the long term service oriented strategy, while still maintaining a tight schedule.

How it should look one day

The proposed solution

So how can this problem be confronted? For us, a mid-way solution was the way to go: expanding one of the existing services, the “Social media service” (used for fetching media from instagram).

But, this cannot be achieved effectively just by adding new controllers and models the good old way we know. It is important to make sure all the new behaviour is in a whole separate flow, as well as a separate folder structure. The new code addition should get a new name, and treated as a complete separate service. This separation might seem ‘semantic’ and imposed, but keeping it helps keep the new ‘service within a service’ as decoupled as possible from the existing service.

This solution keeps the service oriented architecture as its guiding principle, while avoiding most of the overhead effort tied with creating a new micro service.

Challenges

During the development process we obviously faced some unforeseen challenges. The most interesting ones were:

Inter-service communication

The new (albums) service and the old (social media) service still need to communicate with each other.

For example, an album can contain images from the social service. Whenever there’s a request for an album, it will need to return data for its social images. That data exists in the social images service.

At this point it will be very tempting to mix the services. For example, use a model from the social media service inside one of the albums service’s controllers. To avoid that, we introduced a new entity which will prevent the coupling — SocialImagesProvider.

This entity will be the only point of communication from the albums service to the social images service. If and when we decide to extract one of the services into a separate microservice, the SocialImagesProvider will be the only entity needed to be reimplemented.

Outer-Service communication

Communication to external services was also difficult. Just as an album can contain a social image (from instagram), it can contain a user generated image. So again, when there’s a request for an album containing a user generated image, the album service will need to fetch the image’s data. Only this time, the data isn’t in its brother-service, it’s in one of our other services.

This basically requires to perform an inter-service join, so as to get all the image’s details. This join is complicated, as it contains two services, and is not very reliable. We will need to reconsider this implementation very soon as we continue to scale our application.

Advantages

There are many advantages to writing the new service inside the old one, amongst them:

Using a single database: this saves the time of deploying and maintaining a new database. The tables for the new service are new tables that do not relate at all to the existing ones.
Known and familiar code: the code base, architecture, database and more are all familiar to all the developers on the project, so it’s still possible to move quickly without learning a new programming languages/architecture/database.
Inner-service communication: the two brother-services can communicate efficiently with other: they basically run on the same process.
Easy to extract micro-service in the future: the new service is well defined and separated from the existing one, so it is (relatively) easy to extract it to a separate micro-service in the future.
Decoupled: the new service is decoupled from the old one, so the two services can advance independently. Changes to one can be made without worrying to the other. Moreover, the service’s boundaries are very clear, so it’s kept small enough, and easier to fully grasp by developers.
No ‘new service overhead’.

Disadvantages

Services have to be deployed together: even though a change to one service is independent of the other, deployment is still joint. If one of the services breaks, the other will break as well.
Some coupling is inevitable.
Project still grows larger: the original service still grows, the code base becomes larger and harder to fully grasp, and the database grows significantly.

Conclusions

In the post we have described our approach to developing a new meaningful feature, while trying to maintain a balance between a tight schedule and a new architectural approach — micro-services.

The path we chose — a service with a service — has advantages and disadvantages, just like any other path. The development and deployment went very smoothly, and the product is up and running to the satisfaction of our customers.

In our near future road map, the new service will be extracted, and given a life of its own, with a new database, deployment, and everything else that accompanies a decoupled micro-service.