Snaplet Seed requirements
To use Snaplet Seed, you need to have Node.js and npm installed. You can add Node.js to your project by running `npm init -y` in your project directory.
database
343 notes in this subject, read out of this brain and free to use. This is page 3 of 6.
Initialize Snaplet Seed with init command
To use Snaplet Seed for the first time, set it up with the command `npx @snaplet/seed init`. This command will analyze your database and its structure, and then generate a JavaScript client which can be used to define exactly how your data should be generated using code. The `init` command generates a configuration file, `seed.config.ts` and an example script, `seed.ts`, as a starting point.
Snaplet ORM adapter selection
During `npx @snaplet/seed init`, if you are not using an Object Relational Mapper (ORM) or your ORM is not in the supported list, choose `node-postgres`.
Snaplet Seed configuration example with select filter
Example `seed.config.ts` that configures Snaplet to only generate data for the public schema: ```ts export default defineConfig({ adapter: async () => { const client = new Client({ connectionString: 'postgresql://postgres:postgres@localhost:54322/postgres', }) await client.connect() return new SeedPg(client) }, select: ['!*', 'public.*'], }) ```
Snaplet Seed script example with relationships
Example `seed.ts` that defines seed data generation for related tables with specific constraints: ```ts import { copycat } from '@snaplet/copycat' import { createSeedClient } from '@snaplet/seed' async function main() { const seed = await createSeedClient({ dryRun: true }) await seed.Post([ { title: 'There is a lot of snow around here!', createdBy: { email: (ctx) => copycat.email(ctx.seed, { domain: 'acme.org', }), }, Comment: (x) => x(3), }, ]) process.exit() } main() ``` This example generates a Post with the specified title, a User as the creator with an email ending in @acme.org, and three Comments from three different users on that post.
Generate SQL from Snaplet Seed TypeScript script
To generate SQL statements from a Snaplet Seed TypeScript script and write them to `supabase/seed.sql`, run: `npx tsx seed.ts > supabase/seed.sql`
Sync Snaplet Seed with database structure changes
Whenever your database structure changes, regenerate `@snaplet/seed` to keep it in sync with the new structure by running: `npx @snaplet/seed sync`
Snaplet Seed with LLM integration for realistic data
You can enhance your Snaplet Seed script by using Large Language Models to generate more realistic data. To enable this feature, set one of the following environment variables in your `.env` file: `OPENAI_API_KEY=<your_openai_api_key>` or `GROQ_API_KEY=<your_groq_api_key>`. After setting the environment variables, run `npx @snaplet/seed sync` and then `npx tsx seed.ts > supabase/seed.sql` to sync and generate the seed data.
database.dev setup requirements
To use database development tools, install these prerequisites: the http extension in the extensions schema, and the pg_tle extension. These enable installation of the supabase-dbdev package manager.
Install supabase-dbdev package manager
First create extensions http and pg_tle. Then uninstall supabase-dbdev if it exists. Install it using pgtle.install_extension() with parameters fetched from https://api.database.dev/rest/v1/package_versions. After installation, create the extension and run select dbdev.install('supabase-dbdev'). Finally drop and recreate the extension for a clean installation.
Install basejump test helpers
Run select dbdev.install('basejump-supabase_test_helpers') followed by create extension if not exists "basejump-supabase_test_helpers" version '0.0.6' to install the test helpers package.
Test helpers functions for user management
The basejump-supabase_test_helpers package provides these user management functions: tests.create_supabase_user() to create test users, tests.authenticate_as() to switch contexts, and tests.get_supabase_uid() to retrieve user IDs.
Test helpers functions for RLS testing
The basejump-supabase_test_helpers package provides tests.rls_enabled() to verify RLS status, and supports testing policy enforcement and simulating different user contexts.
Test helpers reduce boilerplate compared to raw pgTAP
Test helpers eliminate the need to manually insert auth.users, simplify JWT claim management, and provide clean test setup and cleanup compared to writing raw pgTAP tests.
Verify RLS enabled schema-wide
To verify RLS is enabled across an entire schema, use select tests.rls_enabled('public') within a pgTAP begin/rollback block with plan(1).
pgTAP test files execute in alphabetical order
Test files are executed in alphabetical order, so create a setup file with a naming convention like 000-setup-tests-hooks.sql to ensure it runs first and handles global environment setup.
Pre-test hook file structure
A pre-test hook file should contain all shared extensions and dependencies, common test utilities, and a basic always-green test to verify setup. Use naming like 000-setup-tests-hooks.sql to ensure it runs first via alphabetical ordering.
Security definer functions prevent RLS recursion
Use security definer functions when they call tables with RLS enabled to avoid RLS checking recursion issues. Define these functions in a private schema that is not exposed through the API.
Private schema for security definer functions
Create a private schema to store security definer functions. These functions should never be in API-exposed schemas and are used to avoid RLS checking recursion when functions call RLS-protected tables.
pgTAP test assertion functions
Common pgTAP assertion functions include: results_eq() to compare query results, results_ne() to verify results are not equal, lives_ok() to verify a query executes without error, throws_ok() to verify a query throws a specific error, and ok() for simple boolean assertions.
Authenticate as service role in tests
Use select tests.authenticate_as_service_role() to bypass RLS during test setup when you need to insert initial test data.
Clear authentication in tests
Use select tests.clear_authentication() to test behavior for unauthenticated users.
Test time-based conditions with freeze and unfreeze
Use tests.freeze_time('2024-01-01 12:00:00+00'::timestamptz) to set a specific time for testing time-based logic, and tests.unfreeze_time() to resume normal time.
Test helpers benefits for RLS testing
Test helpers reduce boilerplate by eliminating manual auth.users insertion, simplify JWT claim management, provide clean test setup and cleanup, and allow easy context switching between test users.
Database (Postgres) errors troubleshooting table
Symptom: statement timeout; duplicate key or sequence error; trigger errors; slow ALTER; blocked queries; disk/memory/swap pressure; index size. Layer: Database (Postgres) → postgres_logs. Troubleshooting guides: Statement timeout; Duplicate key/sequence; Blocked queries; Disk not shrinking; Autovacuum stalled; High CPU.
Connections and pooler errors troubleshooting table
Symptom: too many connections; remaining connection slots; CONNECT_TIMEOUT; pooler vs. direct connection; read-only transaction; prepared statement already exists; no pg_hba.conf entry; IPv4/IPv6; SASL/SCRAM. Layer: Connections & pooler → supavisor_logs, postgres_logs. Troubleshooting guides: Too many connections; Remaining slots; Prepared statement exists; Read-only transaction; CONNECT_TIMEOUT; Supavisor terminology.
Database jobs errors troubleshooting table
Symptom: Webhook not firing; pg_cron job not running; pg_net queue stuck; 42501 ... http_request_queue. Layer: Database jobs → postgres_logs. Troubleshooting guides: Webhook debugging; pg_cron debugging; 42501 http_request_queue.
Product CRUD operations using Postgrest in Android
Implement ProductRepository interface with methods: createProduct(product), getProducts(), getProduct(id), deleteProduct(id), and updateProduct(id, name, price, imageName, imageFile). Use postgrest.from("products").insert(productDto) to create, .select().decodeList<ProductDto>() to read all, .select { filter { eq("id", id) } }.decodeSingle<ProductDto>() to read one, .delete { filter { eq("id", id) } } to delete, and .update({ set(key, value) }) { filter { eq("id", id) } } to update.
Data transfer object (DTO) serialization in Android Supabase
Create serializable data classes using @Serializable annotation and @SerialName to map Kotlin field names to database column names. Example: @Serializable data class ProductDto(@SerialName("name") val name: String, @SerialName("price") val price: Double, @SerialName("image") val image: String?, @SerialName("id") val id: String). Use these DTOs with Postgrest's decodeList<ProductDto>() and decodeSingle<ProductDto>() methods.
Metrics API data types
The Metrics API surfaces Postgres performance and health metrics including database CPU usage, IO statistics, WAL (Write-Ahead Logging) data, connection counts, and query statistics.
Postgres logs with Read Replicas
Postgres logs show queries and activity for your database. When Read Replicas are enabled, logs are automatically filtered between databases. Logs for a specific database can be toggled with the Source button on the Logs dashboard.
Postgres connection logging disabled by default
By default, Supabase sets log_connections to off for new projects. Connection logging must be enabled first to log connection lifecycle events such as when a client connects or authenticates.
Enable pgAudit extension for query logging
To enable query logs for categories of statements: enable the pgAudit extension, configure pgaudit.log, and optionally perform a fast reboot. Query logs can then be viewed under Logs > Postgres Logs.
Temporary pgAudit configuration for single session
To enable logging for function calls, writes, and DDL statements for a single session, execute: set pgaudit.log = 'function, write, ddl';
Permanent pgAudit configuration
To permanently set a logging configuration beyond a single session, execute: alter role postgres set pgaudit.log to 'function, write, ddl'; then perform a fast reboot. For API-related logs, set the configuration for the authenticator role: alter role authenticator set pgaudit.log to 'write';
pgAudit log level configuration
The log level defaults to 'log'. To adjust log levels, run: alter role postgres set pgaudit.log_level to 'info'; or alter role postgres set pgaudit.log_level to 'debug5'; Error, fatal, and panic log levels are not allowed per pgAudit documentation.
Reset pgAudit system-wide settings
To reset system-wide settings, execute: alter role postgres reset pgaudit.log then perform a fast reboot.
RAISE log messages in Postgres
Messages logged via RAISE INFO, RAISE NOTICE, RAISE WARNING, and RAISE LOG are shown in Postgres Logs. Only messages at or above your logging level are shown. Syncing of messages to Postgres Logs may take a few minutes. Check your logging level with: show log_min_messages;
Postgres log level hierarchy
LOG is a higher level than WARNING and ERROR. If log level is set to LOG, WARNING and ERROR messages will not be shown.
Postgres log character limit
Postgres log events on the Supabase Platform are limited to 100,000 characters. If a log event exceeds this limit, it will be truncated. This limit does not apply to self-hosted deployments.
Internal Postgres connection logs not recorded
Internal connection logs to Postgres within the Supabase Platform by internal services are not logged. This does not apply to self-hosting.
Query Postgres SQLSTATE codes example
To find a specific Postgres SQLSTATE (e.g., 42501 permission denied, 42P01 relation missing, 23505 duplicate key), use: select timestamp, log_attributes['parsed.user_name'] as role, event_message from logs where source = 'postgres_logs' and log_attributes['parsed.sql_state_code'] = '42501' order by timestamp desc limit 100;
Database report charts for Free and Pro plans
Free and Pro plans include these Database report charts: Memory usage (RAM usage percentage by the database, shows memory pressure and resource utilization), CPU usage (average CPU usage percentage, shows CPU-intensive query identification), Disk IOPS (read/write operations per second with limits, shows IO bottleneck detection and workload analysis), Database connections (number of pooler connections to the database, shows connection pool monitoring), Disk usage (disk space consumption breakdown, shows storage capacity planning), Database size (total database size and growth trends, shows space consumption monitoring including list of largest tables).
Database report charts available on all plans
All Supabase plans include these Database report charts: Dedicated Pooler connections (client connections to PgBouncer, shows dedicated pooler connection monitoring), Shared Pooler connections (client connections to the shared pooler, shows shared pooler usage patterns).
Advanced telemetry database charts for Team, Enterprise, and Platform plans
Team, Enterprise, and Platform plans have access to advanced telemetry database charts including Memory commitment, Disk throughput, Dedicated Pooler (PgBouncer) Client Connections, and Shared Pooler (Supavisor) Client Connections.
Memory usage chart components
The Memory usage chart displays: Used (RAM actively used by Postgres and the operating system), Cache + buffers (memory used for page cache and OS buffers), Free (available unallocated memory), Swap (disk overflow used when physical RAM is exhausted). The Swap series only appears when the system is swapping. Sustained swap activity indicates memory pressure and can significantly degrade database performance.
Memory commitment chart purpose and interpretation
The Memory commitment chart shows how much memory the Linux kernel has promised to processes (Committed_AS) against the maximum it is willing to promise (CommitLimit). It is a leading indicator of out-of-memory risk not visible on the Memory usage chart. Committed memory is the sum of every outstanding promise across every process, regardless of whether those pages have been touched yet. A flat Committed well below the Commit limit indicates healthy status. Committed gradually rising over days or weeks suggests organic growth or a memory leak. Committed spiking near or above the Commit limit is dangerous and usually indicates a connection storm or several large concurrent queries. Committed sustained above the Commit limit means the instance is on borrowed time and upgrade or workload fixes are needed.
CPU usage chart categories
The CPU usage chart displays: System (CPU time for kernel operations), User (CPU time for database queries and user processes), IOWait (CPU time waiting for disk/network IO), IRQs (CPU time handling interrupts), Other (CPU time for miscellaneous tasks).
Disk IOPS chart purpose and limits
The Disk IOPS chart displays read and write IOPS with a reference line showing the compute size's maximum IOPS capacity. It helps identify disk IO bottlenecks, distinguish between read-heavy vs write-heavy operations, and spot disk activity spikes that correlate with performance issues.
Disk throughput chart availability
The Disk throughput chart is available on Team and Enterprise plans only. It displays read and write throughput in bytes per second with a reference line showing the compute size's maximum disk throughput.
Disk size chart components
The Disk size chart displays: Database (space used by actual database data including tables and indexes), WAL (space used by Write-Ahead Logging), System (reserved space for system operations).
Database connections chart connection types
The Database connections chart displays connection types: Postgres (direct connections from your application), PostgREST (connections from the PostgREST API layer), Reserved (administrative connections for Supabase services), Auth (connections from Supabase Auth service), Storage (connections from Supabase Storage service), Other roles (miscellaneous database connections).
Dedicated Pooler (PgBouncer) Client Connections chart availability
The Dedicated Pooler (PgBouncer) Client Connections chart is available on Team and Enterprise plans. It displays the number of PgBouncer connections over time.
Shared Pooler (Supavisor) Client Connections chart availability
The Shared Pooler (Supavisor) Client Connections chart is available on Team and Enterprise plans. It displays the number of Supavisor connections over time.
Replication slots, WAL senders, and connection limits by compute instance
Maximum limits vary by compute instance: Nano/Micro (5 replication slots, 5 WAL senders, 60 database connections, 200 pooler clients), Small (5 slots, 5 senders, 90 connections, 400 clients), Medium (5 slots, 5 senders, 120 connections, 600 clients), Large (8 slots, 8 senders, 160 connections, 800 clients), XL (24 slots, 24 senders, 240 connections, 1,000 clients), 2XL (80 slots, 80 senders, 380 connections, 1,500 clients), 4XL (80 slots, 80 senders, 480 connections, 3,000 clients), 8XL (80 slots, 80 senders, 490 connections, 6,000 clients), 12XL (80 slots, 80 senders, 500 connections, 9,000 clients), 16XL (80 slots, 80 senders, 500 connections, 12,000 clients).
Downgrading compute requires fewer replication slots than new max
When downgrading your compute instance, ensure that you are using fewer replication slots than the maximum number of replication slots available for the new compute instance. Setting max_replication_slots to a lower value than the current number of replication slots will prevent the server from starting.
Disable read-only mode with SQL
To manually override read-only mode, run these commands in the SQL Editor: set session characteristics as transaction read write; to allow deletion within the session, then vacuum; to reclaim space, then set default_transaction_read_only = 'off'; to disable read-only mode.
Database size vs disk size definitions
Database size displays the actual size of data within the Postgres database, found on the Database Reports page. Disk size shows overall disk space usage, which includes both database size and additional files required for Postgres to function like the Write Ahead Log (WAL) and other system log files, found on the Database Settings page.
Query to check database size
Run this SQL query to show the size of all databases in your Postgres cluster: select pg_size_pretty(sum(pg_database_size(pg_database.datname))) from pg_database;
Database size components
Database size is consumed primarily by data, indexes, and materialized views. You can reduce database size by removing any of these and running a Vacuum operation.