Supabase Unleashed: Advanced Features for TypeScript, Frameworks, and Direct Database Connections

This article is an excerpt from the book, "Building Production-Grade Web Applications with Supabase", by David Lorenz. Supabase supercharges web development with scalable backend solutions. With this book, you'll build secure, real-time apps of any size by leveraging Supabase's powerful Row Level Security and eliminating the need for separate backend development.

Introduction

Supabase is a powerful platform that integrates Postgres databases with modern developer tools to simplify backend development. While the Supabase client is the recommended approach for interacting with its database, understanding how to establish a direct database connection can expand your options and offer greater flexibility. This section explores the scenarios in which direct access might be necessary, how to configure such connections, and their implications in different project setups. By mastering this complementary skill, you'll unlock additional possibilities for extending and optimizing your applications.

Connecting directly to the database

Note: Building a raw database connection is helpful but complementary knowledge. In this book’s project, we will use the Supabase client and not a direct database connection.

At the  end of the day, Supabase comes down to just being a Postgres database with additional services surrounding it like a galaxy. Hence, you can also directly access the database. But why would you ever want to do this?

When you work with platforms such as Supabase that make your life easier by providing data storage, file storage, authentication, and more, you often don’t get direct access to the underlying database or your access is extremely limited. The reason is that providers of such platforms often want to safeguard you and themselves from scrapping the project in a way that will break it irrevocably.

Having no or limited direct access to your database also means that you cannot extend it with additional features or use libraries of any kind that need direct access (such as sequelize, drizzle, or pg_dump). But with Supabase, you can. So, let’s have a look at how we can connect directly.

On a supabase.com project, within the Dashboard (Studio) area, you’ll find the database connection URI of the Postgres database in the Project Settings | Database section. In your local instance, the complete connection URI is shown in the Terminal after running npx supabase start or, for a running instance, when calling npx supabase status. It already contains the username and password, separated with a colon (on your local instance, this is usually postgresql://postgres:postgres@localhost:54322/postgres).

Then, you can connect to it with whichever tool you like – for example, via GUIs for databases such as  DBeaver (https://dbeaver.io/).

To test if the connection to the database works, I prefer the psql command-line tool. For my local instance, I can simply use one of the following commands:

• The most minimal way to test a connection is by calling plsql with the connection string in postgresql://username:password@host:port/postgres format, like so:

psql 'postgresql://postgres:postgres@localhost:54322/postgres'

• For real connections (not just local ones), you should prefer a more verbose form that doesn’t keep the password in cleartext in the Terminal and prompts you for the password:

psql -h localhost -p 54322 -d postgres -U postgres

• This is equal to the longest form where the parameter meanings become self-explanatory:

psql --host=localhost --port=54322 --dbname=postgres --username=postgres

With that, you know how to connect to the database if needed. Please be aware that connecting to the database directly and changing data there can be dangerous if you don’t know what you’re doing as there’s no protection layer in between.

Next, you’ll learn what you need to do to get immediate TypeScript support with Supabase.

Using Supabase with TypeScript

Many projects nowadays use TypeScript instead of JavaScript. In this book, we’ll focus on using Supabase with JavaScript instead of TypeScript. But still, I want to show you how easily it can be used in combination with the Supabase JavaScript clients, and which benefits it brings.

Supabase’s npm library comes with TypeScript support out of the box. However, with TypeScript, Supabase can also tell you that the expected data from your database doesn’t exist or help you find the correct table name for your database via autocompletion in your editor.

All you need for this is a specific TypeScript file that is generated specifically for your Supabase project. The following steps show how to trigger the Supabase CLI so that it creates such a supabase.ts file containing the needed types for TypeScript – depending on  whether you want the types from a supabase.com project, a local instance, or an instance hosted somewhere else than supabase.com:

• If you want types for a project based on supabase.com, follow these steps to get a supabase.ts file:

I.  Go to https://supabase.com/dashboard/account/tokens and create an access token.

II.  Run npx supabase login. You’ll be asked for the access token you just generated.

After pressing Enter, it will tell you that the login process has succeeded.

III.  Now, open your project via supabase.com; you’ll see a link in your browser that looks like https://supabase.com/dashboard/project/YOUR_PROJECT_

ID/.... You’ll also find the same project ID as part of your API URL in the Settings | API section. Copy this project ID.

IV.  Generate your custom supabase.ts file by running 

npx supabase gen types typescript --schema public --project-id YOUR_PROJECT_ID > supabase.ts

• If you’re running a local instance, which you should have by now, and want to grab the types from there, you don’t need an access key. You only need to run the following command in your project folder (this is where we ran npx supabase init previously in this chapter):

npx supabase gen types typescript --schema public --local  > supabase.ts

Note that if you run it outside of the project folder, it won’t know which local instance you’re referring to and fail.

• If you have a n instance that’s self-hosted on a remote server or running with a provider other than supabase.com, then the previous steps won’t work and you’ll need the generalized variant of fetching types with a direct database connection. To do that, you must generate the supabase.ts file, as follows:

I.  Find your database URL (see the Connecting directly to the database section). For example, in your local instance, you’ll find it in the Terminal output after starting Supabase with

npx supabase start. It will be in the following format: postgresql:// USER:PASSWORD@DB_HOST:PORT/postgres.

II.  Run npx supabase gen types typescript --schema public --db-url postgresql://USER:PASSWORD @DB_HOST:PORT/postgres > supabase. ts. You’ll receive the file.

With this supabase.ts file, it’s easy to make your client type-safe and get proper type hints – simply import the Database type from supabase.ts and pass it to the client creation process. For example, if you want to make the createReqResSupabase({req,res}) function type-safe, you just pass the <Database> type when creating the client:

import type { Database } from './supabase';
export const getSupabaseReqResClient = ({ req, res }) => {
 return createServerClient<Database>(...);
};

With that, your Supabase client is type-safe. But let’s understand what that means and what it implies. Say, for example, you’re fetching data from a specific table of your database: the Supabase client will exactly know which columns to fetch and provide proper type support for the returned data.

But what happens when I change anything in my instance? Won’t it be outdated immediately as my supabase.ts fi le contains outdated types?

Let me try to answer this question with another question: How can you use a new feature on your smartphone if the new feature is only available in a newer software version? The simple answer is that you update the software version.

The same goes for the Supabase types. Anytime you change something in your Supabase project and it doesn’t give you the proper TypeScript hints, run npx supabase gen types typescript ... again and you’ll be all set.

With this, you can use Supabase in a TypeScript-based project. Before finishing up this chapter, we’ll have a look at some samples of how a Supabase client can be used with other frameworks so that you’re familiar with Supabase’s flexibility.

Connecting Supabase to other frameworks

Imagine that you’ve set up an awesome project with Next.js and Supabase. However, one day, you want to add another feature to your project – an extremely fast API that does complex calculations based on data from your Supabase instance. You notice that JavaScript won’t be the best choice and decide to build a small Python server for this feature that can be called from your primary project.

This is what I did in one of my projects at Wahnsinn Design GmbH where the web application, with Supabase at its heart, was built with Next.js. However, a new feature was added using another project with Python. Since there is a Python library for Supabase, the connection was seamless.

Since Supabase is not framework-dependent, since it’s just REST APIs, the options for integrations are endless, from C#, Swift, and Kotlin, to JavaScript-based frameworks such as Nuxt or refi ne (you’ll find the most recent list at https://supabase.com/docs).

Although we will focus on JavaScript with Next.js in this book, you can use most samples, especially in the upcoming chapters, and translate them into other languages or frameworks with ease. This is because using the Supabase client for the different languages will have similar syntax (as far as the language allows).

Let’s have a brief look at how to connect Supabase in Nuxt and Python.

Nuxt 3

Nuxt is the Vue-based full-stack competitor to Next.js. Connecting with Nuxt comes down to installing the @nuxtjs/supabase package – which, again, is just a convenient wrapper for the @supabase/ supabase-js package.

Once installed with npm install @nuxtjs/supabase, add the module to your Nuxt configuration, like so:

export default defineNuxtConfig({
 modules: ['@nuxtjs/supabase'],
})

Similar to our Next.js application, add the anon key as SUPABASE_KEY and your API URL as SUPABASE_URL to the .env file of your Nuxt project.

Now, you can use the client in Vue composables, like so:

<script setup lang="ts">
 const supabase = useSupabaseClient();
</script>

Alternatively, you can use proper TypeScript types, as we’ve already learned, like so:

<script setup lang="ts">
 import type { Database } from '~/supabase';
 const client = useSupabaseClient<Database>();
</script>

You can find a detailed explanation of Nuxt 3 at https://supabase.nuxtjs.org/get-started.

Python

Python is fast and has become more popular than ever with many AI applications. This is because it is convenient to use for scientific calculations.

The Python Supabase package is one of the easiest to use:

1. Install the Supabase package and the dotenv package with pip install supabase and pip install python-dotenv, respectively.

2. Create a .env file with two lines, one being your SUPABASE_ANON_KEY=... value and the other being your SUPABASE_URL=... value.

3. Initialize the Supabase client in a file such as supabase_client.py, as follows:

import os
from dotenv import load_dotenv
from supabase import create_client, Client
load_dotenv()
supabase_url: str = os.getenv("SUPABASE_URL")
supabase_anon_key: str = os.getenv("SUPABASE_ANON_KEY")
my_supabase: Client = create_client(supabase_url, supabase_anon_ key)

4. Use it in any file via import:

from supabase_client import my_supabase
...

You can find the full Python documentation here: https://supabase.com/docs/reference/ python.

I’d be lying if I said all frameworks and languages are equal concerning updates and support within the Supabase community. On the web, there is a general trend toward JavaScript-based environments (Vue, Next, React, Nuxt, Remix, Svelte, Deno, you name it) and at the time of writing this book, several client libraries exist, including JavaScript, Flutter, Python, C#, Swift, and Kotlin.

However, it is extremely important to keep in mind that Supabase can be used in any framework or language due to its REST-based nature and that Supabase is also very keen on contributions. Lastly, you can always just use the direct database connection – but with that, you’d be bypassing all authentication and permissions.

With this at hand, you are well-positioned to tackle any project with Supabase, no matter if you are using a framework-specific client, the RESTful API, or the direct database connection.

Conclusion

In this article, we explored the fundamentals of connecting directly to a Supabase database and the practical use cases it enables. While the Supabase client provides a robust and secure interface, direct access empowers you to extend functionality, integrate with various libraries, and handle advanced operations. We also discussed integrating Supabase with TypeScript and other frameworks like Nuxt and Python, demonstrating its versatility across languages and ecosystems. With these tools and insights, you're equipped to harness Supabase's full potential, whether working within its client or venturing into direct database interactions.

Author Bio

David Lorenz is a web software architect and lecturer who began programming at age 11. Before completing university in 2014, he had built a CRM system that automated an entire company and worked with numerous agencies through his own company. In 2015, he secured his first employment as a senior web developer, where he played a pioneering role in using cutting-edge technology and was an early adopter of progressive web apps. In 2017, he became the leading frontend architect and team lead for one of the largest projects at Mercedes-Benz.io, involving massive-scale architecture. Today, David provides valuable insights and guidance to clients across various industries, using his extensive experience and exceptional problem-solving abilities.