DEV Community: Tilman Vogel

From scraping in the cloud to streams of insights

Tilman Vogel — Fri, 19 Jan 2024 14:54:02 +0000

Any questions? Please comment and let me know what you think!

Before going into story-telling, this post touches on the following topics - please, jump ahead:

Caching data
Corner-cases on Wikipedia
Missing elevation data on Wikidata
Extensible storage of facts
Dual-axis weather plots
Running circles around airports
Gift ribbon for Google cloud-functions
Hiding input in automatic reports from Jupyter notebooks
Results
I did not do it

Or, just read it front-to-back:

I am currently enrolled in a WBS Coding School Data Science Boot Camp and the past two weeks, we were learning the data-engineering skills of ETL, i.e. extraction, transformation and loading of data into storage for comprehensive analysis.

As learning scenario, we thought of a fictitious E-scooter rental company called Gans which is interested in up-to-date information on the cities they operate in such that they can plan their work better. Two points were of particular interest:

weather data in order to anticipate less busy times for maintenance
flight data in order to accommodate for higher demand in certain parts of the city

Caching data

In this project, we were using the two most common methods of acquiring data:

scraping information from unstructured web-pages
requesting structured data from APIs

Both of those are relatively slow operations that can limit development turnaround when you are still working on the details of post-processing and storing the data in the framework of a Jupyter notebook. Also, APIs usually put limits on the number of requests, you can do, and web-sites might get annoyed and ban you for too frequent requests.

To address this, it is quite easy to implement caching logic. I used a custom attribute on my data-retrieval function which seems to be the closest Python-analogy to function-static variables that I know from C++. We were using Beautiful Soup for parsing the HTML and it looks like this:

import requests
from bs4 import BeautifulSoup
from datetime import datetime

scrape_list = []
use_caching = True

def get_soup(url: str):
  """
  Retrieve HTML soup from URL using a local soup cache.

  If the URL was previously retrieved, the cached soup is 
  served instead of making a web request. If the URL is 
  requested from the web, the soup is stored in the cache.

  :param url: the URL to retrieve from cache or web
  :return: tuple(soup, scrape_index) 
    where scrape_index refers to the scrape_list
  """

  if use_caching:
    try:
      if cached := get_soup.cache.get(url):
        return cached
    except:
      get_soup.cache = {}

  dt = datetime.now()
  response = requests.get(url)
  soup = BeautifulSoup(response.content, 'html.parser')
  scrape_index = len(scrape_list)
  scrape_list.append([url, dt, None])
  if use_caching:
    get_soup.cache[url] = (soup, scrape_index)
  return (soup, scrape_index)

As you can see, caching is made optional because in production, when the script is run in the cloud only once per instance of time and not multiple times inside a Jupyter notebook for development purposes, it is a waste to keep all retrieved documents in memory even if they will not be used a second time.

Also, the requested URL and current time is saved in a scrape_list such that this information can be later saved to the database along with the extracted information. The third None entry in each record there is later used to hold the database ID once the scrape is stored.

I also wrote a very similar function for retrieving and caching JSON data from APIs:

def get_json(url, headers = None):
  """
  Retrieve JSON from URL using a local cache.

  If the URL was previously retrieved, the cached JSON is 
  served instead of making a web request. If the URL is 
  requested from the web, the JSON is stored in the cache.

  :param url: the URL to retrieve from cache or web
  :param headers: the HTTP request headers to pass 
    (for authorization)
  :return: tuple (json, scrape_index) 
    where scrape_index refers to the scrape_list
  """
  if use_caching:
    try:
      if cached := get_json.cache.get(url):
        return cached
    except:
      get_json.cache = {}

  dt = datetime.now()
  response = requests.get(url, headers=headers)
  response.raise_for_status()
  if response.status_code == 204: # No Content
    json = None
  else:
    json = response.json()

  scrape_index = len(scrape_list)
  scrape_list.append([url, dt, None])
  if use_caching:
    get_json.cache[url] = (json, scrape_index)
  return (json, scrape_index)

The main difference apart from parsing JSON instead of HTML and passing HTTP request headers for authorization is handling of HTTP request result states such as failures like 404 via exceptions and the special 204 No Content reply.

Corner-cases on Wikipedia

For the exercise, we extracted geographical location information and population numbers from https://en.wikipedia.org . As to be expected from unstructured data, and even though Wikipedia has some guidelines and tries to follow common patterns across the articles for different cities, the scraping function for processing Wikipedia had to be successively extended to handle certain corner-cases as I extended the list of cities, e.g. different labels or slightly different formatting of the population numbers.

Missing elevation data on Wikidata

In contrast, https://wikidata.org follows strict rules on data-structure and Wikipedia links to the corresponding Wikidata pages. This makes it quite easy to scrape data because every type of information has unique identifiers. However, not many cities actually have peak elevation data, so my initial idea on elevation data failed which was to retrieve average and highest elevation in order to estimate hilliness which is in general relevant for E-scooter use.

Extensible storage of facts

The common approach to store information like population data would be to make it a column in the city database table. In order to allow tracking change of facts over time, one could in contrast employ a separate table which stores the time of the scrape, population and other facts in multiple columns linked to the respective city such that a time-series of such information may evolve.

However, I wanted to try an approach that allows you to later on add new types of facts without changing the database table schema. Even though, some consider it an anti-pattern, I used the entity-attribute-value model to store the population data. This allows to later on add a new type of information (e.g. GDP per capita) to the measure table and then just store the new information in a fact table.

As a counter-argument to bashing this as an anti-pattern: Each time, you do an evaluation of your facts based on a current set of measures, it is quite easy to write a common table expression that transforms your fact table into a classical table with individual columns for each of your measures just as you had designed it otherwise but without having to update the database schema each time you add a new measure. You just need to update your evaluation code.

As an example how to assemble the fact data into a classical table, see this and the SQL schema for reference:

WITH latest_fact AS (
  SELECT * FROM fact f1
  WHERE NOT EXISTS( -- a newer value for the same fact:
    SELECT 1 FROM fact f2
    WHERE f2.city = f1.city
    AND f2.measure = f1.measure
    AND f2.scrape > f1.scrape
  )
),
city_facts AS (
  SELECT name AS city, 
    -- convert string to number:
    population.value + 0 AS population,
    population.meta AS population_meta, 
    population_scrape.timestamp AS population_scrape_timestamp
  FROM city
  LEFT JOIN latest_fact population 
    ON population.measure = (
      SELECT id FROM measure WHERE name = 'population'
    )
    AND population.city = city.id
  LEFT JOIN scrape population_scrape
    ON population_scrape.id = population.scrape
)
SELECT city, 
  population, 
  population_meta->"$.date" AS census_date
FROM city_facts
ORDER BY population DESC
;

Here city_facts is a classical table-representation of the latest known facts. The population_meta is a JSON-column for case-dependent meta-data about that fact (census-date of reference in the example of population data). The city_facts can then be queried to give a result like this:

city	population	census_date
New York City	8804190	2020
London	8799800	2021
Sydney	5297089	2022
Berlin	3850809	2021
Paris	2102650	2023
Vienna	2002821
Hamburg	1945532	2022-12-31

Dual-axis weather plots

Retrieving weather data from the OpenWeather API was straight-forward. We could use the previously retrieved latitude/longitude coordinates for the cities. I focused on saving values that seemed relevant for E-scooter use like felt temperature, rain, snow, wind and wind gusts. My primary visualization for this would be per-city plots showing temperature and precipitation which requires two different y-axes in the same plot and cannot be achieved with seaborn's convenient but opinionated "figure-level" API. So, I had to dive a bit deeper to come up with figures like this:

I quite like how you can see the transition between rain and snow in some cases. Placing the legend was a bit of a challenge.

Running circles around airports

For flight-data, we were using the AeroDataBox API. There were a few peculiarities to take into account here:

the API would only give you flight data for a maximum span of 12h, so covering a full day and night would take two requests
the API would expect times given only in the airport's local time-zone, so aligning the data in common UTC would require time-zone conversion

Fortunately, using the timezonefinder package made it quite easy to identify the timezone from the airport coordinates reported by AeroDataBox.

Because airports tend to be far out of the city center where E-scooter business is going on, I assumed that commuting to the airport would be done by car, cab or public transport and the effect on E-scooter use would happen with a time-shift w.r.t. the arrival and departure times of flights. These time-shifts would depend on estimated quantities like how early people tend to arrive at the airport ahead of flights and how long it takes them to pick up baggage, clear immigration and walk out of the building but also the commuting time to and from the airport. To estimate this, I used an average city travel speed and the distance between airport and city center which I calculated using the GeoPy ellipsoidal distance calculation module.

Also, some cities have multiple airports in their vicinity and some airports have multiple cities in their vicinity which makes the calculations necessary for each pair and then to accumulate the results.

Again, I was too demanding for poor seaborn's convenient but opinionated "figure-level" API that I had to counter-act a bit to get back to well-formatted x-axis ticks on all and not only the bottom sub-figures because, you know, when there are lots of figures, you won't see the bottom row and some x-axis visible is quite helpful, really.

Sorry, for the tiny bit of ranting but this is what the results look like:

I like that you can easily see that E-scooter use for departures of course is expected to happen earlier than for arrivals in general and how different the distribution is for the cities of Tübingen and Stuttgart which are both served by Stuttgart airport but have different commute times. The individual shape differences are caused by the different per-hour binning.

The counts mean expected users based on an average number of passengers per flight and a global fraction of E-scooter users per flight.

Gift ribbon for Google cloud-functions

As I was happy with having all the extraction-transformation-load logic being refactored into self-containing units and a central orchestrating function, it was time to wrap this logic into a Google cloud-function container and have it run at periodic intervals.

The main challenge was to come up with a comprehensive list of utilized Python packages in requirements.txt. As helpers, I found pipreqs and the recommendation to use it along with pip-compile from pip-tools. And, yes, that's a nice start but there are dynamic things that are not picked up by these tools like the actually used SQL driver in SQLAlchemy (pymysql) or the IPython kernel itself for running Jupyter notebooks (see below).

Anyway, to deserve it, you need to work a little. So, no complaints here!

Hiding input in automatic reports from Jupyter notebooks

Why stop here? Having the ETL automation in place, it's just a consequence to also think about creating automatic reports whenever the database is updated.

For that, I already had separated the ETL from the reporting on the database-content into a separate Jupyter notebook and the amazing Python world of course already has nbconvert to run notebooks automatically and to export them to HTML, PDF, Markdown and much more. This works as a command-line tool or from Python itself which could easily be put into the existing Google cloud-function.

The resulting report must go somewhere and Google cloud allows to host static pages in their storage buckets. Setting that up and API access to it were fairly easy. I did not go the way through the load-balancer mentioned there because I expect very low load and Google Cloud buckets meanwhile also support HTTPS and I am not going to tie that to my own domain which requires SSL certificate setup.

With the new functionality, my cloud-function now took several python files and a Jupyter notebook as template for the reports, so I switched to deploying my function using the gcloud CLI which is as easy as running this deploy.sh in the local folder containing the cloud-function sources:

#!/bin/bash
gcloud functions deploy wbscs-cities-update \
--gen2 \
--runtime=python312 \
--region=europe-west1 \
--source=. \
--entry-point=run_etl_all \
--trigger-http

For some reason, that even builds faster than via the web console.

The final challenge was to hide most of the lengthy input cells which would be easy to do using nbconvert if only I wouldn't have desired, one of them to still be shown, namely the one defining the model parameters for calculating the flight-related use peaks.

After quite some confusion about nbconvert output templates, I found an easy way to remove all input cells that are currently collapsed in Jupyter lab which is also a convenient way to define that without having to assign custom cell-tags which some suggest. The conversion code then looks like this:

def remove_collapsed_input(notebook):
  for cell in notebook.cells:
    if (cell['metadata']
        .get('jupyter',{})
        .get('source_hidden', False)
    ):
      cell.transient = {"remove_source": True}

def make_report():
  dotenv.load_dotenv()

  notebook = nbformat.read(
    'docs/gans_cities_display.ipynb', 
    as_version=4)

  ep = ExecutePreprocessor(timeout=600, kernel_name='python3')
  ep.preprocess(notebook, {'metadata': {'path': 'docs/'}})

  remove_collapsed_input(notebook)

  html_exporter = HTMLExporter(template_name="lab")
  output, resources = \
    html_exporter.from_notebook_node(notebook)

  upload_blob(
    'wbscs_gans_cities_report', 
    output, 
    'text/html', 
    'index.html')

Results

One of the results of this project is the SQL database schema which ended up like this and shows what data and what relations were acquired by the ETL:

Another result is the reports that were automatically generated, an example of which you can see here!

If you are reading this early enough, and I have not yet torn down the automation to save CPU, API and Google credits, you could see the actual page here!

And of course, finally, all the nice code and development notebooks:

gans_cities_scraping_and_api.ipynb: notebook for developing the ETL and initial reporting
gans_cities_scraping_and_api.py: refactored ETL code ready for use in the Google cloud-function
main.py: the entry-point for the Google cloud-function driving ETL and reporting
gans_update_report.py: code for making a new report from a Jupyter notebook template
gans_cities_display.ipynb: Jupyter notebook used as the template for automatic reports

I did not do it

It would be cool to combine the flight data with the weather data. My idea would be to define sigmoidal functions that define the relative likelihood that people use E-scooters under different weather parameters, e.g. below a certain temperature, when it rains, snows or if the wind blows too strongly. With those, I could attenuate the usage predictions that I derived from the flight data or just use them individually to predict the flight-independent scooter activity. The missing piece here would be the "perfect weather curve" for usage over the day.

These were two packed weeks and even though, I have many years of software development experience, I learned a lot of new infrastructure and Python packages. I am also amazed at how my fellow boot campers are doing, most with a lot less prior experience!

run-mariabackup makeover

Tilman Vogel — Tue, 16 May 2023 19:38:33 +0000

On our busy server, we were recently hitting the roof using our previous mysqldump/bzip2 based MariaDB backup strategy.

So, I started looking for alternatives and quickly found Percona XtraBackup and its descendant mariabackup mentioned. Being physical instead of logical backup approaches which basically copy the server storage instead of dumping the database content into replicating SQL statements, they follow a very different strategy. The big advantage is much shorter database locking caused by the backup process. Also, they both offer incremental backups which further shortens execution time on successive runs.

Of course, any backup strategy is only as good as the corresponding recovery process. With logical mysqldump backups, the process is straight-forward and the data can even be inspected before being restored. With physical backups, the standard approach is to restore the full set of databases served by the server instance as a whole. Single-database recovery is described but surprisingly complicated and for example involves dropping foreign-key constraints before importing backup data and reestablishing them afterwards.

Of course, all these operations should be prepared and at hand before you adopt a new backup strategy. After more searching, I came about a script package called run-mariabackup which exists in multiple evolution steps maintained by different people. While it already looked promising as a starting point, the current state left multiple features to be desired. In particular a single-database restoration script.

Before I tell you the link to the repository, please be warned: This is very new code with little testing! So, treat it as experimental code which means that you need to thoroughly review it and test it on your own before putting it into any kind of production environment. Do independent backups if your data has any worth to you! Make sure that backup and recovery works before relying on it!

So, this is the result of a few hours of bash scripting and trying various scenarios:

tvogel / run-mariabackup

Fork and rewrite of YoSiJo/run-mariabackup

README

forked from YoSiJo/run-mariabackup
- forked from omegazeng/run-mariabackup
  - forked from jmfederico/run-xtrabackup.sh

Note: I have tested this on openSUSE Tumbleweed 20230512 with MariaDB 10.11.2.

Fat Warning

This new fork (as of 2023-05-15) has seen only very limited testing and use. In particular, only with MyISAM and InnoDB tables In these limited cases, it worked fine but there is no guarantee at all that it will fulfill any purpose in your specific environment. As it is acting on complete database server setups with possibly many databases and complexities which I have never seen before, there is a high probability that this script will break in your scenario and can possibly have catastrophic effects. Just as a non-exclusive single example, I have not tried it on databases with stored procedures or triggers.

So, use these scripts only after your own educated review and on your own risk!

Also, please share any concerns and observations…

View on GitHub

Backup

After adjusting some settings to fit your environment in a config.sh file, backing up is as easy as:

# ~/git/run-mariabackup/backup.sh

which will run full base-backups at specified intervals and incremental backups in between. You can also request a new full base-backup by placing a base-backup.request file into your backup target directory which is convenient if you want to trigger a full base-backup from cron every Sunday night.

The command will also discard of backups that exceed a specified age.

Restore

In order to restore all databases, you go to the backup data directory representing the state which you want to restore and:

# ~/git/run-mariabackup/restore.sh

This will move your old data-directory out of the way (and keep it) and move the complete backup back in place.

If you are interested in only one database, use:

# ~/git/run-mariabackup/restore.sh <original> <restored>

In this case, the restored data is not directly put back into place but instead into a new database named <restored> which must not exist before. After that, you can use a simple mysqldump/mysql pipe to put the data back into place after inspection. This can also be used to restore only individual tables (if this makes sense at all).

Cron

I am a big fan of https://habilis.net/cronic/, so I would recommend something like this for your /etc/crontab:

# run base mariabackup once a week (Sunday night) and incremental every 12 hours                                                                                                                                                                                              
54 00    * * 1  root  cronic touch /var/backup/mariabackup/base-backup.request                                                                                                                                                                                             
55 00,12 * * *  root  cronic /root/git/run-mariabackup/backup.sh

At this point in time, I am interested in reviews and feedback. So, I am glad to hear back from you!

Getting started with Supabase and Quasar v2

Tilman Vogel — Fri, 12 May 2023 14:28:19 +0000

This tutorial is based on the common supabase tutorials and demonstrates how to build a basic user management app. The app authenticates and identifies the user, stores their profile information in the database, and allows the user to log in, update their profile details, and upload a profile photo. The app uses:

Supabase Database - a Postgres database for storing your user data and Row Level Security so data is protected and users can only access their own information.
Supabase Auth - users log in through magic links sent to their email (without having to set up passwords).
Supabase Storage - users can upload a profile photo.

NOTE

If you get stuck while working through this guide, refer to the full example on GitHub.

Project setup

Before we start building, we're going to set up our Database and API. This is as simple as starting a new Project in Supabase and then creating a "schema" inside the database.

Create a project

Create a new project in the Supabase Dashboard.
Enter your project details.
Wait for the new database to launch.

Set up the database schema

Now we are going to set up the database schema. We can use the "User Management Starter" quickstart in the SQL Editor, or you can just copy/paste the SQL from below and run it yourself.

Using the dashboard

Go to the SQL Editor page in the Dashboard.
Click User Management Starter.
Click Run.

Using SQL



-- Create a table for public profiles
create table profiles (
  id uuid references auth.users not null primary key,
  updated_at timestamp with time zone,
  username text unique,
  full_name text,
  avatar_url text,
  website text,

  constraint username_length check (char_length(username) >= 3)
);
-- Set up Row Level Security (RLS)
-- See https://supabase.com/docs/guides/auth/row-level-security for more details.
alter table profiles
  enable row level security;

create policy "Public profiles are viewable by everyone." on profiles
  for select using (true);

create policy "Users can insert their own profile." on profiles
  for insert with check (auth.uid() = id);

create policy "Users can update own profile." on profiles
  for update using (auth.uid() = id);

-- This trigger automatically creates a profile entry when a new user signs up via Supabase Auth.
-- See https://supabase.com/docs/guides/auth/managing-user-data#using-triggers for more details.
create function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, full_name, avatar_url)
  values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
  return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- Set up Storage!
insert into storage.buckets (id, name)
  values ('avatars', 'avatars');

-- Set up access controls for storage.
-- See https://supabase.com/docs/guides/storage#policy-examples for more details.
create policy "Avatar images are publicly accessible." on storage.objects
  for select using (bucket_id = 'avatars');

create policy "Anyone can upload an avatar." on storage.objects
  for insert with check (bucket_id = 'avatars');

create policy "Anyone can update their own avatar." on storage.objects
  for update using (auth.uid() = owner) with check (bucket_id = 'avatars');

Get the API Keys

Now that you've created some database tables, you are ready to insert data using the auto-generated API. We just need to get the Project URL and anon key from the API settings.

Go to the API Settings page in the Dashboard.
Find your Project URL, anon, and service_role keys on this page.

Building the App

Let's start building the Quasar v2 app from scratch.

Initialize a Quasar v2 app

We can quickly use Quasar CLI to initialize an app called supabase-quasar:



yarn global add @quasar/cli
yarn create quasar

✔ What would you like to build? › App with Quasar CLI, let's go!
✔ Project folder: … supabase-quasar
✔ Pick Quasar version: › Quasar v2 (Vue 3 | latest and greatest)
✔ Pick script type: › Typescript
✔ Pick Quasar App CLI variant: › Quasar App CLI with Vite
✔ Package name: … supabase-quasar
✔ Project product name: (must start with letter if building mobile apps) … Supabase Quasar App
✔ Project description: … A Quasar Project using Supabase
✔ Author: … Me <me@supabase.io>
✔ Pick a Vue component style: › Composition API with <script setup>
✔ Pick your CSS preprocessor: › Sass with SCSS syntax
✔ Check the features needed for your project: › ESLint
✔ Pick an ESLint preset: › Prettier

? Install project dependencies? (recommended) › - Use arrow-keys. Return to submit.
❯   Yes, use yarn

Then let's install the only additional dependency: supabase-js



cd supabase-quasar
yarn add @supabase/supabase-js

And finally, we want to save the environment variables in a .env.
All we need are the API URL and the anon key that you copied earlier.



VITE_SUPABASE_URL=YOUR_SUPABASE_URL
VITE_SUPABASE_ANON_KEY=YOUR_SUPABASE_ANON_KEY

With the API credentials in place, create an src/boot/supabase.js helper file to initialize the Supabase client. These variables are exposed on the browser, and that's completely fine since we have Row Level Security enabled on our Database.



import { boot } from 'quasar/wrappers';
import { createClient } from '@supabase/supabase-js';
import { ref } from 'vue';

// declare module '@vue/runtime-core' {
//   interface ComponentCustomProperties {
//     $supabase: SupabaseClient<Database>;
//   }
// }

const supabaseUrl = import.meta.env.VITE_SUPABASE_URL;
const supabaseAnonKey = import.meta.env.VITE_SUPABASE_ANON_KEY;

type Session = Awaited<
  ReturnType<typeof supabase.auth.getSession>
>['data']['session'];

const session = ref<Session>();
let setPasswordRequested: 'initial' | 'reset' | false = false;

// Hack to process the #access_token and #error before vue-router "cleans up",
// breaking supabase.auth token processing later on
let indexSupabaseHash = window.location.hash.indexOf('#access_token');
if (indexSupabaseHash < 0)
  indexSupabaseHash = window.location.hash.indexOf('#error');
const routerHash = window.location.hash.substring(
  0,
  indexSupabaseHash >= 0 ? indexSupabaseHash : undefined
);

const supabase = createClient(supabaseUrl, supabaseAnonKey);
// must be registered immediately before returning to the event loop because
// otherwise changes triggered by the #access_token, e.g. PASSWORD_RECOVERY,
// would be missed if the **async** supabase.auth._initialize() is processed
// before the callback is registered.
supabase.auth.onAuthStateChange(async (_event, _session) => {
  // console.log('supabase.auth.onAuthStateChange', _event, _session);
  session.value = _session;
  if (_event === 'PASSWORD_RECOVERY') setPasswordRequested = 'reset';

  const userMetaData = session.value?.user.user_metadata;
  if (!userMetaData?.passwordSetup) {
    setPasswordRequested = 'initial';
  }
});

/**
 * Finalize supabase init
 *
 * Shall be called from the router initialization before it is instantiated
 * and mangles the hash parts of the window.location. This function also removes
 * the supabase hash parts and reinstates the vue-router hash parts which are
 * otherwise cleared out by supabase.auth.initialize().
 */
async function init() {
  // this waits for the async init to finish:
  const { error } = await supabase.auth.initialize();
  if (error) window.alert(error.message);
  window.location.hash = routerHash;
}

export default boot(({ router }) => {
  // for use inside Vue files (Options API) through this.$supabase
  // app.config.globalProperties.$supabase = supabase;
  // ^ ^ ^ this will allow you to use this.$supabase (for Vue Options API form)
  //       so you won't necessarily have to import supabase in each vue file
  router.beforeEach((to) => {
    if (setPasswordRequested) {
      to.query.setPassword = setPasswordRequested;
      setPasswordRequested = false;
      return to;
    }
  });
  router.afterEach((to) => {
    if (to.query.setPassword ?? false) {
      // to be implemented if you use password login:
      // execChangePassword(
      //   to.query.setPassword === 'initial'
      //     ? 'Choose password'
      //     : 'Forgot password'
      // ).then(() => {
      //   if (to.query.setPassword === 'initial')
      //     supabase.auth.updateUser({ data: { passwordSetup: true } });
      //   router.replace({ query: { setPassword: undefined } });
      // });
    }
  });
});

export { init, supabase, session };

Also, create an src/boot/router-auth.js helper file to limit access to pages by authentication status.



import { boot } from 'quasar/wrappers';
import { session } from './supabase';

export default boot(({ router }) => {
  router.beforeEach((to) => {
    if (to.matched.some((record) => record.meta.requiresAuth ?? false)) {
      // console.log('needs auth: ', to);
      if (!session.value) {
        // console.log('no session!');
        return { name: 'auth', query: { redirect: to.fullPath } };
      }
    }
  });
});

Add these two files to the boot section of quasar.config.js:



    boot: [
      'supabase',
      'router-auth'
    ],

Also, replace router/index.ts with the following in order to make sure, supabase can process the URL before the router works on it:



import { route } from 'quasar/wrappers';
import {
  createMemoryHistory,
  createRouter,
  createWebHashHistory,
  createWebHistory,
} from 'vue-router';

import routes from './routes';

import { init as supabaseInit } from 'boot/supabase';

/*
 * If not building with SSR mode, you can
 * directly export the Router instantiation;
 *
 * The function below can be async too; either use
 * async/await or return a Promise which resolves
 * with the Router instance.
 */

export default route(async function (/* { store, ssrContext } */) {
  await supabaseInit();

  const createHistory = process.env.SERVER
    ? createMemoryHistory
    : (process.env.VUE_ROUTER_MODE === 'history' ? createWebHistory : createWebHashHistory);

  const Router = createRouter({
    scrollBehavior: () => ({ left: 0, top: 0 }),
    routes,

    // Leave this as is and make changes in quasar.conf.js instead!
    // quasar.conf.js -> build -> vueRouterMode
    // quasar.conf.js -> build -> publicPath
    history: createHistory(process.env.VUE_ROUTER_BASE),
  });

  return Router;
});

Set up a Login page

Set up an src/pages/AuthPage.vue page to manage logins and sign ups. We'll use Magic Links, so users can sign in with their email without using passwords.



<script setup>
import { ref } from 'vue';
import { supabase } from 'boot/supabase';

const loading = ref(false);
const email = ref('');

const handleLogin = async () => {
  try {
    loading.value = true;
    const { error } = await supabase.auth.signInWithOtp({
      email: email.value,
    });
    if (error) throw error;
    alert('Check your email for the login link!');
  } catch (error) {
    if (error instanceof Error) {
      alert(error.message);
    }
  } finally {
    loading.value = false;
  }
};
</script>

<template>
  <form class="q-pa-md column flex-center flex" @submit.prevent="handleLogin">
    <q-card class="col-6 form-widget">
      <q-card-section class="q-gutter-md">
        <div class="text-h1">Supabase + Quasar</div>
        <div>Sign in via magic link with your email below</div>
        <q-input
          lazy-rules
          :rules="[
            (val, rules) =>
              rules.email(val) || 'Please enter a valid email address',
          ]"
          type="email"
          label="Your email"
          v-model="email"
        />
      </q-card-section>
      <q-card-actions class="q-gutter-md">
        <q-btn
          type="submit"
          color="primary"
          :label="loading ? 'Loading' : 'Send magic link'"
          :disabled="loading"
        />
      </q-card-actions>
    </q-card>
  </form>
</template>

Account page

After a user is signed in, we can allow them to edit their profile details and manage their account. Create a new src/pages/AccountPage.vue page to handle this.



<script setup>
import { session, supabase } from 'boot/supabase';
import { onMounted, ref } from 'vue';
import { useRouter } from 'vue-router';

const loading = ref(true);
const username = ref('');
const website = ref('');
const avatar_url = ref('');

const router = useRouter();

onMounted(() => {
  getProfile();
});

async function getProfile() {
  try {
    loading.value = true;
    const { user } = session.value;

    let { data, error, status } = await supabase
      .from('profiles')
      .select('username, website, avatar_url')
      .eq('id', user.id)
      .single();

    if (error && status !== 406) throw error;

    if (data) {
      username.value = data.username;
      website.value = data.website;
      avatar_url.value = data.avatar_url;
    }
  } catch (error) {
    alert(error.message);
  } finally {
    loading.value = false;
  }
}

async function updateProfile() {
  try {
    loading.value = true;
    const { user } = session.value;

    const updates = {
      id: user.id,
      username: username.value,
      website: website.value,
      avatar_url: avatar_url.value,
      updated_at: new Date(),
    };

    let { error } = await supabase.from('profiles').upsert(updates);

    if (error) throw error;
  } catch (error) {
    alert(error.message);
  } finally {
    loading.value = false;
  }
}

async function signOut() {
  try {
    loading.value = true;
    let { error } = await supabase.auth.signOut();
    if (error) throw error;
  } catch (error) {
    alert(error.message);
  } finally {
    loading.value = false;
  }
  router.go(0);
}
</script>

<template>
  <form class="q-pa-md column flex-center flex" @submit.prevent="updateProfile">
    <q-card class="col-6 form-widget">
      <q-card-section>
        <div class="text-h2">Account</div>
        <q-input
          id="email"
          label="Email"
          type="text"
          :model-value="session.user.email"
          disable
        />
        <q-input id="username" label="Name" type="text" v-model="username" />
        <q-input
          id="website"
          label="Website"
          type="website"
          v-model="website"
        />
      </q-card-section>
      <q-card-actions class="q-gutter-md" align="right">
        <q-btn
          type="submit"
          color="primary"
          :label="loading ? 'Loading ...' : 'Update'"
          :disabled="loading"
        />
        <q-btn flat label="Sign Out" @click="signOut" :disabled="loading" />
      </q-card-actions>
    </q-card>
  </form>
</template>

Router

Now, use those pages in the src/router/routes.ts route definitions:



import { RouteRecordRaw } from 'vue-router';

const routes: RouteRecordRaw[] = [
  {
    path: '/',
    component: () => import('layouts/MainLayout.vue'),
    children: [{ path: '', component: () => import('pages/AccountPage.vue') }],
    meta: {
      requiresAuth: true,
    },
  },
  {
    path: '/auth',
    name: 'auth',
    component: () => import('pages/AuthPage.vue'),
  },

  // Always leave this as last one,
  // but you can also remove it
  {
    path: '/:catchAll(.*)*',
    component: () => import('pages/ErrorNotFound.vue'),
  },
];

export default routes;

Launch!

Now that we have all the components in place, we can straight away run the app in a terminal window:



quasar dev

which will open the completed app in your browser.

Bonus: Profile photos

Every Supabase project is configured with Storage for managing large files like photos and videos.

Create an upload widget

Create a new src/components/AvatarUploader.vue component that allows users to upload profile photos:



<script setup>
import { ref, toRefs, watch } from 'vue';
import { supabase } from 'boot/supabase';

const prop = defineProps(['path', 'size']);
const { path, size } = toRefs(prop);

const emit = defineEmits(['upload', 'update:path']);
const uploading = ref(false);
const src = ref('');
const files = ref();
const fileselector = ref();

const downloadImage = async () => {
  try {
    const { data, error } = await supabase.storage
      .from('avatars')
      .download(path.value);
    if (error) throw error;
    src.value = URL.createObjectURL(data);
  } catch (error) {
    console.error('Error downloading image: ', error.message);
  }
};

const uploadAvatar = async (evt) => {
  files.value = evt.target.files;
  try {
    uploading.value = true;
    if (!files.value || files.value.length === 0) {
      throw new Error('You must select an image to upload.');
    }

    const file = files.value[0];
    const fileExt = file.name.split('.').pop();
    const filePath = `${Math.random()}.${fileExt}`;

    let { error: uploadError } = await supabase.storage
      .from('avatars')
      .upload(filePath, file);

    if (uploadError) throw uploadError;
    emit('update:path', filePath);
    emit('upload');
  } catch (error) {
    alert(error.message);
  } finally {
    uploading.value = false;
  }
};

watch(path, () => {
  if (path.value) downloadImage();
});
</script>

<template>
  <div>
    <q-img
      v-if="src"
      :src="src"
      alt="Avatar"
      class="avatar image"
      :style="{ height: size + 'em', width: size + 'em' }"
    />
    <div
      v-else
      class="non-selectable row avatar no-image justify-center items-center"
      :style="{ height: size + 'em', width: size + 'em' }"
      @click="fileselector.click()"
    >
      Photo
    </div>

    <div :style="{ width: size + 'em' }">
      <q-btn
        style="width: 100%"
        :label="uploading ? 'Uploading ...' : 'Pick Photo'"
        color="primary"
        :disable="uploading"
        @click="fileselector.click()"
      />
      <input
        style="visibility: hidden; position: absolute; width: 0px"
        type="file"
        id="fileselector"
        ref="fileselector"
        accept="image/*"
        @change="uploadAvatar"
        :disabled="uploading"
      />
    </div>
  </div>
</template>

<style>
.avatar {
  border-radius: var(--custom-border-radius);
  overflow: hidden;
  max-width: 100%;
}
.avatar.image {
  object-fit: cover;
}
.avatar.no-image {
  color: #eee;
  background-color: #333;
  border: 1px solid rgb(200, 200, 200);
  border-radius: var(--custom-border-radius);
}
</style>

Add the new widget

And then, we can add the widget to the Account page in src/pages/AccountPage.vue:



<script>
// Import the new component
import AvatarUploader from 'components/AvatarUploader.vue';
</script>

<template>
  <form class="form-widget" @submit.prevent="updateProfile">
    <!-- Add to body -->
    <avatar-uploader v-model:path="avatar_url" @upload="updateProfile" size="10" />

    <!-- Other form elements -->
  </form>
</template>

Storage management

If you upload additional profile photos, they'll accumulate in the avatars bucket because of their random names with only the latest being referenced from public.profiles and the older versions getting orphaned.

To automatically remove obsolete storage objects, extend the database triggers. Note that it is not sufficient to delete the objects from the storage.objects table because that would orphan and leak the actual storage objects in the S3 backend. Instead, invoke the storage API within Postgres via the HTTP extension.

Enable the HTTP extension for the extensions schema in the Dashboard. Then, define the following SQL functions in the SQL Editor to delete storage objects via the API:



create or replace function delete_storage_object(bucket text, object text, out status int, out content text)
returns record
language 'plpgsql'
security definer
as $$
declare
  project_url text := '<YOURPROJECTURL>';
  service_role_key text := '<YOURSERVICEROLEKEY>'; --  full access needed
  url text := project_url||'/storage/v1/object/'||bucket||'/'||object;
begin
  select
      into status, content
           result.status::int, result.content::text
      FROM extensions.http((
    'DELETE',
    url,
    ARRAY[extensions.http_header('authorization','Bearer '||service_role_key)],
    NULL,
    NULL)::extensions.http_request) as result;
end;
$$;

create or replace function delete_avatar(avatar_url text, out status int, out content text)
returns record
language 'plpgsql'
security definer
as $$
begin
  select
      into status, content
           result.status, result.content
      from public.delete_storage_object('avatars', avatar_url) as result;
end;
$$;

Next, add a trigger that removes any obsolete avatar whenever the profile is updated or deleted:



create or replace function delete_old_avatar()
returns trigger
language 'plpgsql'
security definer
as $$
declare
  status int;
  content text;
begin
  if coalesce(old.avatar_url, '') <> ''
      and (tg_op = 'DELETE' or (old.avatar_url <> new.avatar_url)) then
    select
      into status, content
      result.status, result.content
      from public.delete_avatar(old.avatar_url) as result;
    if status <> 200 then
      raise warning 'Could not delete avatar: % %', status, content;
    end if;
  end if;
  if tg_op = 'DELETE' then
    return old;
  end if;
  return new;
end;
$$;

create trigger before_profile_changes
  before update of avatar_url or delete on public.profiles
  for each row execute function public.delete_old_avatar();

Finally, delete the public.profile row before a user is deleted. If this step is omitted, you won't be able to delete users without first manually deleting their avatar image.



create or replace function delete_old_profile()
returns trigger
language 'plpgsql'
security definer
as $$
begin
  delete from public.profiles where id = old.id;
  return old;
end;
$$;

create trigger before_delete_user
  before delete on auth.users
  for each row execute function public.delete_old_profile();

At this stage you have a fully functional application!