database

343 notes in this subject, read out of this brain and free to use. This is page 2 of 6.

Supabase uses Postgres, not NoSQL

Supabase is built on PostgreSQL as its core database rather than a NoSQL store. This choice was deliberate, as Postgres offers the functionality required to compete with Firebase while maintaining the scalability to go beyond it.

Supavisor is a cloud-native Postgres connection pooler

Supavisor is a cloud-native, multi-tenant Postgres connection pooler. Source code is at github.com/supabase/supavisor, written in Elixir, and licensed under Apache 2.0.

Supabase core architecture components

Each Supabase project consists of multiple services fronted by an Envoy API gateway: Postgres (database), Studio (dashboard), GoTrue (Auth), PostgREST (API), Realtime (WebSocket engine), Storage API (S3-compatible object storage), Deno (Edge Functions), postgres-meta (database management API), Supavisor (connection pooler), and Envoy (API gateway).

Supabase core database features

Every Supabase project includes a full Postgres database with vector database support for storing vector embeddings alongside regular data.

Database webhooks feature

Database webhooks allow you to send database changes to any external service. This feature is in beta status.

Secrets and encryption with Supabase Vault

Supabase provides a Postgres extension called Supabase Vault for encrypting sensitive data and storing secrets. This feature is in public alpha status.

Database replication with Supabase Pipelines

Supabase Pipelines enable automatic replication of your database to destination systems like data warehouses and analytics platforms. This feature is in public alpha status and is not available on self-hosted deployments.

Query data from Supabase in Expo React Native component

Use useEffect hook to fetch data when the component mounts. Call supabase.from('table_name').select() to query data and store in state. Use FlatList component from react-native to render the data with keyExtractor and renderItem props.

Flask route querying Supabase table

Use the Supabase client to query a table with supabase.table('tablename').select('*').execute(). The response object contains a data attribute with the query results. This example shows querying an 'instruments' table and iterating through results to build HTML.

pg_net call reading secret key from Vault

headers := jsonb_build_object( 'Content-Type', 'application/json', 'apikey', (select decrypted_secret from vault.decrypted_secrets where name = 'secret_key') )

pg_net call with secret key on apikey header

select net.http_post( url := 'https://your-project.supabase.co/functions/v1/your-function', headers := jsonb_build_object( 'Content-Type', 'application/json', 'apikey', 'sb_secret_...' ), body := jsonb_build_object('event', 'ping') );

Database Webhooks and pg_net secret key migration

Calls from Postgres with pg_net (including Database Webhooks) must be updated to send the secret key on the apikey header instead of Authorization Bearer header. For Database Webhooks created in the Dashboard, remove the Authorization header holding the key and add an apikey header with the secret key instead.

Store secret keys in Vault to avoid hardcoding

Do not hardcode secret keys in SQL or webhook configuration where they are stored in plain text. Instead, store secret keys in Vault and read them at call time using SELECT decrypted_secret from vault.decrypted_secrets.

Secret key header format for database calls

Secret keys are not JWTs and must be sent on the apikey header instead of the Authorization Bearer header. The new secret keys are rejected when sent as Authorization Bearer headers because they cannot be parsed as JWTs.

Create decodable struct for database rows in Swift

Create a decodable struct to deserialize data from the database. For example, an Instrument struct should conform to Decodable and Identifiable, with properties matching database columns: struct Instrument: Decodable, Identifiable { let id: Int; let name: String }

Query data from iOS app using supabase-swift

Use a task modifier to fetch data from the database. The pattern is: instruments = try await supabase.from("table_name").select().execute().value. This executes the query asynchronously and returns the deserialized data.

Query data from Flutter using FutureBuilder

Use FutureBuilder to fetch data from Supabase when a page loads. The example queries an 'instruments' table using Supabase.instance.client.from('instruments').select() and displays results in a ListView.

Flutter Supabase data query and display code example

class _HomePageState extends State<HomePage> { final _future = Supabase.instance.client .from('instruments') .select(); @override Widget build(BuildContext context) { return Scaffold( body: FutureBuilder( future: _future, builder: (context, snapshot) { if (!snapshot.hasData) { return const Center(child: CircularProgressIndicator()); } final instruments = snapshot.data!; return ListView.builder( itemCount: instruments.length, itemBuilder: ((context, index) { final instrument = instruments[index]; return ListTile( title: Text(instrument['name']), ); }), ); }, ), ); } }

Create Kotlin data class for database model

Define a serializable data class to represent database table data. Example: @Serializable data class Instrument(val id: Int, val name: String). This allows the Supabase client to deserialize JSON data into Kotlin objects.

Prisma seed script example for RedwoodJS

Create a seed script in scripts/seed.ts using Prisma's create operations. The example shows seeding an Instrument table with multiple records using db.instrument.createMany({ data }).

RedwoodJS with Supabase database setup - connection pooler modes

To connect RedwoodJS to Supabase, you need two connection strings: Transaction mode is used for application queries and runs on port 6543, while Session mode is used for running Prisma migrations and runs on port 5432. Transaction mode requires pgbouncer=true parameter to disable Prisma prepared statements, and connection_limit=1 parameter if using Prisma from a serverless environment.

RedwoodJS Prisma environment variables configuration

Set DATABASE_URL to the Transaction mode connection string for Prisma Client app queries, and DIRECT_URL to the Session mode connection string for Prisma Migrate. Both are required in the .env file.

RedwoodJS Prisma datasource configuration for Supabase

In api/db/schema.prisma, configure the datasource with provider 'postgresql' and set url to env("DATABASE_URL") and directUrl to env("DIRECT_URL").

Prisma migration command in RedwoodJS

Run 'yarn rw prisma migrate dev' to apply schema migrations in RedwoodJS. To reset the database and run seed scripts, use 'yarn rw prisma db reset'.

Spring Boot CommandLineRunner bean for seed data

Add a `@Bean` method in the main application class that returns a `CommandLineRunner`. The method receives an `InstrumentRepository` through dependency injection. Inside the runner, check if the repository is empty and seed sample data. This runs once when the app starts.

Spring Data JpaRepository interface example

Create an `InstrumentRepository` interface in `src/main/java/com/example/instruments/InstrumentRepository.java` that extends `JpaRepository<Instrument, Long>`. This provides query methods like `findAll()`, `save()`, and others without writing any implementation.

Spring Boot JPA entity example with Instrument

Create a Spring Data JPA entity in `src/main/java/com/example/instruments/Instrument.java`. With `spring.jpa.hibernate.ddl-auto=update`, Hibernate creates the table automatically when the app starts. The entity uses `@Entity`, `@Table(name = "instruments")`, `@Id`, and `@GeneratedValue(strategy = GenerationType.IDENTITY)` annotations. Include a no-arg constructor for JPA, a constructor taking the name parameter, and getter/setter methods for all fields.

Spring Boot change default Hibernate schema

By default Hibernate creates tables in the `public` schema, which Supabase exposes as a data API. Create a new schema (such as `app`) from the Table Editor in the Supabase dashboard first. Then configure Hibernate to use it in `application.properties` with: `spring.jpa.properties.hibernate.default_schema=app`

Spring Boot Hibernate dialect connection error

If the app fails to start with the error `Unable to determine Dialect without JDBC metadata`, Hibernate could not open a connection. Check the logs above that line for the real cause, most commonly `password authentication failed`.

Spring Boot application.properties datasource configuration

Configure the datasource in `src/main/resources/application.properties` with the following properties: `spring.datasource.url=${SUPABASE_DB_URL}`, `spring.datasource.driver-class-name=org.postgresql.Driver`, and `spring.jpa.hibernate.ddl-auto=update`. Reference the SUPABASE_DB_URL environment variable instead of hardcoding the connection string.

Spring Boot JDBC connection string configuration

Use the Session pooler (port 5432) and select the JDBC tab to copy the connection string. Replace the password placeholder with your database password and percent-encode any reserved characters (such as `&`, `#`, `?`, or spaces). Add `sslmode=require` to the connection string. Set the connection string as an environment variable: `export SUPABASE_DB_URL='jdbc:postgresql://aws-[REGION].pooler.supabase.com:5432/postgres?user=postgres.[PROJECT-REF]&password=[YOUR-PASSWORD]&sslmode=require'`

Spring Boot database connection pool warning

The Transaction pooler (port 6543) does not work as Spring Boot's main data source because Spring Data JPA uses Hibernate, which relies on server-side prepared statements. Use the Session pooler (port 5432) or the direct connection string instead.

Row Level Security is enabled by default

Supabase has Row Level Security enabled by default on all tables, making it safe to expose API credentials in browser-side code.

Flutter upsert profile data

Update or insert user profile data using `await supabase.from('profiles').upsert(updates)` where updates is a map containing at least the 'id' field and the fields to update. The timestamp should be set with `DateTime.now().toIso8601String()`. Catch `PostgrestException` for database errors.

Flutter fetch user profile from database

Retrieve the authenticated user's profile using `final userId = supabase.auth.currentSession!.user.id;` to get the user ID, then query the profiles table with `await supabase.from('profiles').select().eq('id', userId).single();`. Access user-specific data by matching the user ID. Catch `PostgrestException` for database errors.

Database upsert with minimal return option

Use supabase.from('profiles').upsert(updates, { returning: 'minimal' }) to insert or update records without returning the data. This reduces bandwidth when you don't need the response.

Swift: Query database and deserialize single record

Use the from() method to select a table, then call .single() to fetch a single record. Call .execute() and access .value to get the decoded result. Example: `let profile: Profile = try await supabase.from("profiles").select().eq("id", value: currentUser.id).single().execute().value`

Swift: Update database record

Use the from() method to select a table, then call .update() with an encodable struct. Chain .eq() to specify conditions, then .execute() to apply the update. Example: `try await supabase.from("profiles").update(updatedProfile).eq("id", value: currentUser.id).execute()`

Swift: Map database column names to struct properties

Use CodingKeys enum with String raw values to map snake_case database column names to camelCase Swift property names. Example: `enum CodingKeys: String, CodingKey { case fullName = "full_name" }`

Database linting features with plpgsql_check

The plpgsql_check linter provides: validation of correct types for function parameters, identification of unused variables and function arguments, detection of dead code after RETURN commands, detection of missing RETURN commands in Postgres functions, identification of unwanted hidden casts that can be performance issues, and checks for SQL injection vulnerability in EXECUTE statements.

Basejump Database Test Helpers for pgTAP

Basejump has created a useful set of Database Test Helpers for pgTAP testing on Supabase, available at https://github.com/usebasejump/supabase-test-helpers with an accompanying blog post.

Best practices for database test data setup

When setting up test data: use begin and rollback to ensure test isolation, create realistic test data that covers edge cases, and use different user roles and permissions in tests.

RLS policy testing best practices

When testing RLS policies: test Create, Read, Update, Delete operations; test with different user roles (anonymous and authenticated); test edge cases and potential security bypasses; always test negative cases to verify what users should not be able to do.

Troubleshooting RLS test failures

Common RLS test failures and solutions: (1) Ensure correct role is set with 'set local role authenticated;'; (2) Verify JWT claims are set with 'set local "request.jwt.claims"'; (3) Check that policy definitions match test assumptions.

Application-level testing cannot use transactions for isolation

Application-level tests cannot use database transactions for isolation like pgTAP tests do. Instead, tests should be designed to be independent by using unique identifiers (like unique user IDs) for each test case to avoid conflicts.

pgTAP test setup with pgtap extension

To set up pgTAP testing, create the pgtap extension with 'create extension if not exists pgtap with schema extensions;' and declare the number of expected test cases using 'select plan(N);'.

Supabase admin client for test setup

Use the admin Supabase client (initialized with SUPABASE_SECRET_KEY) to create test users with createUser() method and to insert test data, rather than using the public client. The admin client bypasses RLS policies for setup.

Set database role and JWT claims for pgTAP testing

In pgTAP tests, simulate authenticated requests by setting 'set local role authenticated;' and setting the JWT subject claim with 'set local request.jwt.claim.sub = <user_id>;'.

TypeScript RLS testing example with Vitest

Example application-level RLS test using TypeScript and Vitest: generate unique user IDs in beforeAll hook, create test users using admin client with email_confirm: true, insert initial todos, then write tests that sign in as each user and verify they can only access their own data.

pgTAP RLS testing example for todos table

Example pgTAP test demonstrating RLS policy testing: create todos table with user_id, enable RLS, create policy restricting access to own todos, insert test data for two users, set role and JWT claims for each user, then use results_eq to verify each user sees only their own todos and lives_ok to verify authorized operations.

pgTAP database unit testing framework for Postgres

pgTAP is a unit testing framework for Postgres that allows testing database structure (tables, columns, constraints), Row Level Security (RLS) policies, functions and procedures, and data integrity.

Test isolation strategies for application-level tests

Three approaches for test isolation in application-level testing: (1) Unique Identifiers - generate unique IDs for each test suite to prevent data conflicts; (2) Cleanup After Tests - clean up created data in afterAll or afterEach hooks if necessary; (3) Isolated Data Sets - use prefixes or namespaces in data to separate test cases.

Configure multiple seed files in config.toml

To manage multiple seed files or organize them across different folders, configure additional paths or glob patterns in `supabase/config.toml`. Example with explicit paths: ```toml [db.seed] enabled = true sql_paths = ['./countries.sql', './cities.sql'] ``` Or using a glob pattern to include all `.sql` files under a folder: ```toml [db.seed] enabled = true sql_paths = ['./seeds/*.sql'] ```

Seed file example with SQL insert

Example of a basic seed file inserting country records: ```sql insert into countries (name, code) values ('United States', 'US'), ('Canada', 'CA'), ('Mexico', 'MX'); ```

Seed files processing order and behavior

The CLI processes seed files in the order they are declared in the `sql_paths` array. If a glob pattern is used and matches multiple files, those files are sorted in lexicographic order to ensure consistent execution. The base folder for pattern matching is `supabase`, so `./countries.sql` will search for `supabase/countries.sql`. Files matched by multiple patterns will be deduplicated to prevent redundant seeding. If a pattern does not match any files, a warning will be logged.

Seed file best practices

As a best practice, only include data insertions in seed files and avoid adding schema statements.

What is database seeding

Seeding is the process of populating a database with initial data, typically used to provide sample or default records for testing and development purposes. Seeding is used to create reproducible environments for local development, staging, and production.

Seed files execution timing

Seed files are executed the first time you run `supabase start` and every time you run `supabase db reset`. Seeding occurs after all database migrations have been completed.

Default seed file location and pattern

By default, if no specific configuration is provided, the system will look for a seed file matching the pattern `supabase/seed.sql`. This maintains backward compatibility with earlier versions, where the seed file was placed in the `supabase` folder.

Snaplet Seed for generating large volumes of seed data

For most projects, a hand-written `supabase/seed.sql` is the simplest and most reliable approach. If you need large volumes of realistic data, you can generate it with Snaplet Seed. Snaplet wound down as a company in 2024 and open-sourced its tooling. `@snaplet/seed` is now community-maintained at supabase-community/seed and receives only occasional fixes, so treat it as an optional convenience rather than a required part of the workflow.

Give your agent this brain