Supabase DB in Python and JS/TS
Introduction
blablabla yada-yada-yada, you know the drill!
Supabase - the Open Source Firebase alternative!
An open-source, PostgreSQL-based alternative to Firebase most famous for its Supabase Auth.
Implementation in Python
Ahh yes! My favorite part of writing articles: coding section. As you can see below the code is straight forward, no funny stuff and they works! Simple and easy!
import os
from typing import List, Dict, Any
from supabase import create_client, Client
from dotenv import load_dotenv
load_dotenv()
url1: str = os.environ.get("SUPABASE_URL")
key1: str = os.environ.get("SUPABASE_KEY")
url2: str = os.getenv("SUPABASE_URL")
key2: str = os.getenv("SUPABASE_KEY")
supabase: Client = create_client(url1, key1)
def create_table() -> None:
'''Function to create table'''
query = """
CREATE TABLE IF NOT EXISTS employees (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INT NOT NULL,
department TEXT NOT NULL
);
"""
response = supabase.rpc('execute_sql', {'query': query})
print("Table created: ", response)
def insert_multiple_data() -> None:
'''Function to insert multiple data into the table'''
data: List[Dict[str, Any]] = [
{"name": "John Doe", "age": 30, "department": "Engineering"},
{"name": "Jane Smith", "age": 28, "department": "Marketing"},
{"name": "Alice Johnson", "age": 35, "department": "Sales"},
{"name": "Bob Brown", "age": 40, "department": "HR"}
]
response = supabase.table("employees").insert(data).execute()
print("Data inserted:", response)
def fetch_data() -> None:
'''Fetching all data in the employees table'''
response = supabase.table("employees").select("*").execute()
print("Fetched data: ", response.data)
def query_data() -> None:
'''query result: only employees with the age more than 30 years old'''
response = (
supabase.table("employees")
.select("*")
.gt("age", 30)
.execute()
)
print("Employees > 30 years old: ", response.data)
def main():
create_table()
insert_multiple_data()
fetch_data()
query_data()
main()
Implementation in JS/TS
Similar to the previous post about Xata.io database, the JS/TS implementation requires more steps compared to Python (or I haven't figured it out). Note the code structure is the same between Python and JS/TS, however I commented out createTable function in main coz it's not working here. On the other hand the create_table function in Python works well.
import { createClient } from '@supabase/supabase-js';
import dotenv from 'dotenv';
dotenv.config();
const url = process.env.SUPABASE_URL;
const key = process.env.SUPABASE_KEY;
if (!url || !key) {
throw new Error('SUPABASE_URL and SUPABASE_KEY must be set in the environment');
}
const supabase = createClient(url, key);
interface EmployeeTS {
name: string;
age: number;
department: string;
}
async function createTable() {
const query = `
CREATE TABLE IF NOT EXISTS employeesTS (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INT NOT NULL,
department TEXT NOT NULL
);
`;
const response = await supabase.rpc('execute_sql', { 'query': query });
console.log('Table created:', response);
}
async function insertMultipleData() {
const data: EmployeeTS[] = [
{ name: 'Jahn Due', age: 33, department: 'Engineering' },
{ name: 'Jine Smoth', age: 22, department: 'Marketing' },
{ name: 'Alicu Jihnsun', age: 45, department: 'Sales' },
{ name: 'Bab Brewn', age: 44, department: 'HR' },
];
const response = await supabase.from('employeesTS').insert(data);
console.log('Data inserted:', response);
}
async function fetchData() {
const response = await supabase.from('employeesTS').select('*');
console.log('Fetched data:', response.data);
}
async function queryData() {
const response = await supabase.from('employeesTS').select('*').gt('age', 30);
console.log('Employees > 30 years old:', response.data);
}
async function main() {
// await createTable();
await insertMultipleData();
await fetchData();
await queryData();
}
main();
Manual table creation via web dashboard
For JS/TS implementation, I end up created the table via Supabase dashboard. Good thing the dashboard is fast and table creation was super easy! Just make sure you thoroughly check available options, for example: the default settings is to allow nullable on every columns which I find odd and I changed the settings to not allow nulls.
Post doing the manual table creation, you can do whatever database operations as usual (insert, fetch, filter, etc) from the JS/TS code. I find this as a minor hassle, overall I like the experience when using Supabase.