Supabase to SQL: Your Guide to Advanced Data Analytics

Supabase has quickly become a favorite platform among developers thanks to its ease of use, powerful real-time features, and integrated database management. While Supabase provides a robust set of built-in functions for common database tasks, many users often find themselves needing deeper insights through custom analytics queries.

In this blog, we'll explore how some common Supabase functions translate directly into SQL queries, highlighting how understanding this equivalence can unlock greater analytical power and flexibility. We'll also introduce you to Draxlr, a powerful analytics platform that seamlessly integrates with your Supabase database, empowering you with both visual query building and raw SQL capabilities.

From Supabase Functions to Raw SQL

Here's a quick comparison using a bookstore database:

1. Fetching Data (Select)

• Supabase:

supabase.from("book").select("title, isbn13")

• SQL:

SELECT title, isbn13 FROM book;

2. Filtering Data

• Supabase:

supabase.from("book").select("*").eq("publisher_id", 10)

• SQL:

SELECT * FROM book WHERE publisher_id = 10;

3. Aggregations

• Supabase:

supabase.from("order_line").select("SUM(price)").gte("price", 15)

• SQL:

SELECT SUM(price) FROM order_line WHERE price >= 15;

4. Ordering and Limits

• Supabase:

supabase
  .from("book")
  .select("*")
  .order("publication_date", { ascending: false })
  .limit(5)

• SQL:

SELECT * FROM book ORDER BY publication_date DESC LIMIT 5;

Complex Queries with Supabase and SQL

Consider some advanced examples from a bookstore database:

1. Join Multiple Tables

• Supabase:

supabase
  .from("cust_order")
  .select(`order_id, order_date, customer(customer_id, customer_address(city))`)
  .eq("customer.customer_id", 5)

• SQL:

SELECT
  cust_order.order_id, cust_order.order_date, address.city
FROM cust_order
  INNER JOIN customer ON cust_order.customer_id = customer.customer_id
  INNER JOIN customer_address ON customer.customer_id = customer_address.customer_id
  INNER JOIN address ON customer_address.address_id = address.address_id
WHERE customer.customer_id = 5

2. Aggregating and Grouping Data

• Supabase:

supabase.from("order_line").select("book_id, sum(price)").group("book_id")

• SQL:

SELECT
  book_id, SUM(price) AS total_sales
FROM order_line
  GROUP BY book_id;

3. Total orders count

• Supabase:

supabase.from("cust_order").select("*", { count: "exact", head: true })

• SQL:

SELECT COUNT(*) AS total_orders FROM cust_order;

4. Top 5 best selling books

• Supabase:

supabase
  .from("order_line")
  .select("book_id, sum(price)")
  .order("sum(price)", { ascending: false })
  .limit(5)

• SQL:

SELECT
  book_id, SUM(price) AS total_sales
FROM order_line
  GROUP BY book_id
  ORDER BY total_sales DESC
LIMIT 5;

5. Nested Subqueries

• Supabase (through RPC or Raw SQL):

Since Supabase doesn't directly support raw SQL queries through its standard client methods, you can create a custom Postgres function and invoke it via RPC:

Step 1: Create a custom function in Supabase

CREATE OR REPLACE FUNCTION fetch_books_by_author(name text)
RETURNS TABLE(title varchar) AS $$
BEGIN
  RETURN QUERY
  SELECT book.title
  FROM book
  JOIN book_author ON book.book_id = book_author.book_id
  JOIN author ON book_author.author_id = author.author_id
  WHERE author.author_name = name;
END;
$$ LANGUAGE plpgsql;

Step 2: Invoke the function using Supabase client

supabase.rpc('fetch_books_by_author', { author_name: 'J.R.R. Tolkien' });

Why Go Beyond Supabase Functions?

Supabase functions are excellent for quick queries and basic database operations. However, for advanced analytics, custom reports, and in-depth insights, mastering raw SQL or leveraging a visual query builder can significantly enhance your capabilities.

Unlock Advanced Analytics with Draxlr

With Draxlr, you can:

• Directly connect to your Supabase database in just a few clicks.
• Visually build complex SQL queries without manually writing code.
• Seamlessly switch between visual queries and raw SQL, perfect for both beginners and experts.
• Instantly perform advanced analytics such as joins, nested queries, and complex aggregations.
• Create stunning dashboards with a wide range of visualization options.
• Set up automated alerts to monitor key metrics and data changes.

Whether you're a developer looking to streamline analytics workflows or a data analyst diving deeper into Supabase data, Draxlr empowers you to achieve more with ease. It helps you explore and visualize your data, monitor key metrics, and share insights across your organization.

Here is a quick video walkthrough of how to build a dashboard from Supabase data in Draxlr:

Ready to supercharge your Supabase analytics?

- Jaskaran Singh