Pick a type or review a query pattern before writing your migration. Postgres and Supabase examples — free, no sign-up.
Loading the tool…
Educational examples, not an applied migration or security audit. The owned-table policy snippets are one set; adapt schema and grants to your installation.
Reviewed 2026-10-09 against PostgreSQL and Supabase documentation. SQL placeholders require a parameterized driver.
| Pattern | Scope | SQL | Source |
|---|---|---|---|
| uuid: primary and foreign keys | 128-bit identifiers. A random UUID is not an authorization rule. Referencing auth.users(id) ties a row to a Supabase user. | id uuid primary key default gen_random_uuid(), owner_id uuid not null references auth.users(id) | Official source ↗ |
| timestamptz: instants | Stores an instant, normalized internally to UTC, displayed in the session time zone. The original zone name is not retained. | created_at timestamptz not null default now(); select now() at time zone 'America/Montevideo'; | Official source ↗ |
| timestamp and date: local calendar values | timestamp without time zone is a wall-clock value, not an instant. date is a calendar day. Store a named time zone separately for local recurring schedules. | select date '2026-10-09', timestamp '2026-10-09 09:00'; | Official source ↗ |
| jsonb: structured metadata | Binary JSON supports containment and indexing. It does not retain whitespace, object key order or duplicate keys. Enforce critical fields in typed columns. | select '{"plan":"pro","seats":3}'::jsonb ->> 'plan'; | Official source ↗ |
| text and varchar | text stores variable-length strings. varchar(n) adds a length limit, with no general speed benefit over text. | name text not null check (length(name) > 0), country_code varchar(2) | Official source ↗ |
| numeric: money and exact decimals | numeric is exact decimal arithmetic. Choose precision and scale for your domain. Floating point types are approximate; do not use them for exact money. | amount numeric(12,2) not null check (amount >= 0) | Official source ↗ |
| integer and bigint | Whole numbers. bigint covers a larger range than integer; JavaScript cannot represent every bigint safely as a Number. | quantity integer not null check (quantity >= 0), sequence_number bigint generated always as identity | Official source ↗ |
| boolean, arrays and enum | boolean models true/false/null. Arrays hold one element type. Enums constrain values but need deliberate migrations when the set evolves. | create type public.task_state as enum ('open', 'done'); select true::boolean, array['api', 'billing']::text[]; | Official source ↗ |
| Create an owned SaaS table | Run the RLS and policy examples too before exposing this table. These are educational SQL snippets, not migrations applied by this tool. | create table public.projects ( id uuid primary key default gen_random_uuid(), owner_id uuid not null references auth.users(id), name text not null, metadata jsonb not null default '{}'::jsonb, created_at timestamptz not null default now() ); | Official source ↗ |
| Enable RLS and minimum grants | RLS and SQL grants are separate checks. RLS without policies denies ordinary role access. Table owners and privileged roles can bypass RLS; keep service credentials on the server. | alter table public.projects enable row level security; grant select, insert, update, delete on public.projects to authenticated; revoke all on public.projects from anon; | Official source ↗ |
| SELECT policy: read your own rows | auth.uid() is null without a signed-in user. USING filters existing rows. Restrict the policy to authenticated; users cannot authorize themselves with user_metadata. | create policy "Read own projects" on public.projects for select to authenticated using ((select auth.uid()) = owner_id); | Official source ↗ |
| INSERT policy: assign the correct owner | WITH CHECK validates the new row. A SELECT policy alone never authorizes inserts. | create policy "Insert own projects" on public.projects for insert to authenticated with check ((select auth.uid()) = owner_id); | Official source ↗ |
| UPDATE policy: preserve row ownership | USING limits old rows; WITH CHECK constrains the updated row and prevents owner transfer. Supabase also requires a matching SELECT policy for updates. | create policy "Update own projects" on public.projects for update to authenticated using ((select auth.uid()) = owner_id) with check ((select auth.uid()) = owner_id); | Official source ↗ |
| DELETE policy: remove your own rows | USING constrains deletion. Avoid a policy querying the same protected table recursively. Test with ordinary authenticated identities, not a service role. | create policy "Delete own projects" on public.projects for delete to authenticated using ((select auth.uid()) = owner_id); | Official source ↗ |
| Functions: security invoker and search_path | SECURITY INVOKER uses the caller permissions and RLS. Empty search_path with qualified names reduces name resolution risks. Functions default to public execution: revoke and grant intentionally. | create function public.my_projects() returns setof public.projects language sql stable security invoker set search_path = '' as $$ select * from public.projects where owner_id = (select auth.uid()); $$; revoke all on function public.my_projects() from public, anon; grant execute on function public.my_projects() to authenticated; | Official source ↗ |
| B-tree and partial indexes | An index on owner_id helps RLS lookups. A partial index only helps compatible predicates. Indexes cost write time and storage; inspect real plans. | create index projects_owner_created_idx on public.projects (owner_id, created_at desc); create index projects_open_idx on public.projects (owner_id) where metadata ->> 'state' = 'open'; | Official source ↗ |
| GIN index and jsonb containment | The default GIN jsonb operator class supports containment and key existence. jsonb_path_ops is smaller for supported containment/path queries but lacks key-existence operators. | create index projects_metadata_idx on public.projects using gin (metadata); select * from public.projects where metadata @> '{"plan":"pro"}'::jsonb; | Official source ↗ |
| Upsert with an intentional conflict key | ON CONFLICT needs a matching unique constraint or index. An upsert may require SELECT, INSERT and UPDATE policies. Random UUIDs alone do not deduplicate retries. | insert into public.projects (id, owner_id, name) values ('11111111-1111-4111-8111-111111111111', auth.uid(), 'Demo') on conflict (id) do update set name = excluded.name returning id; | Official source ↗ |
| Keyset pagination and parameterized queries | Use placeholders with a server-side driver, never concatenate untrusted SQL. A composite cursor resolves tied timestamps. Add LIMIT and a supporting index. | select id, name, created_at from public.projects where owner_id = $1::uuid and (created_at, id) < ($2::timestamptz, $3::uuid) order by created_at desc, id desc limit 25; -- Supporting index: (owner_id, created_at desc, id desc) | Official source ↗ |
| Transactions and EXPLAIN | BEGIN/COMMIT group changes; ROLLBACK abandons them. EXPLAIN ANALYZE executes the query, including writes: use a test database or a rollback transaction. | begin; explain select * from public.projects where owner_id = auth.uid(); rollback; | Official source ↗ |
Free, no sign-up. Inputs and results stay in your browser. Analytics record only a tool identifier and action, never your input.
No. It is a searchable reference. No SQL is sent to a server or database; snippets are copied locally for review.
Use timestamptz for instants such as creation and billing timestamps. Use date for calendar days and timestamp without time zone for deliberate local wall-clock values. A timestamptz does not preserve the original time zone name.
RLS and SQL grants must both be configured. With RLS enabled and no policies, ordinary roles cannot access rows. Owners and privileged service roles may bypass RLS, so test as an actual authenticated user.
USING controls which existing rows can be updated. WITH CHECK constrains the new row and can prevent changing owner_id to a different user. A matching SELECT policy is also needed in Supabase.
No. They execute with owner privileges and need careful access design. These examples use SECURITY INVOKER, qualified object names and an empty search_path; execution permissions are explicitly scoped.
No. jsonb is useful for flexible structured values, while typed columns suit constraints and frequent filters. Indexes help compatible queries but add write and storage cost. Inspect real execution plans.
Decode a JWT locally and inspect Supabase role, aal, amr, metadata and expiry. Understand auth.uid() and auth.jwt() in RLS. Free, no sign-up.
Format PostgreSQL queries, Supabase policies and PL/pgSQL function definitions locally. Check basic syntax without running SQL. Free, no sign-up.
Generate browser-random secrets and a Next.js env template for Supabase and Stripe. Separate NEXT_PUBLIC_ values from server secrets. Free, no sign-up.
20 matching entries.
Data types
128-bit identifiers. A random UUID is not an authorization rule. Referencing auth.users(id) ties a row to a Supabase user.
id uuid primary key default gen_random_uuid(), owner_id uuid not null references auth.users(id)
Data types
Stores an instant, normalized internally to UTC, displayed in the session time zone. The original zone name is not retained.
created_at timestamptz not null default now(); select now() at time zone 'America/Montevideo';
Data types
timestamp without time zone is a wall-clock value, not an instant. date is a calendar day. Store a named time zone separately for local recurring schedules.
select date '2026-10-09', timestamp '2026-10-09 09:00';
Data types
Binary JSON supports containment and indexing. It does not retain whitespace, object key order or duplicate keys. Enforce critical fields in typed columns.
select '{"plan":"pro","seats":3}'::jsonb ->> 'plan';Data types
text stores variable-length strings. varchar(n) adds a length limit, with no general speed benefit over text.
name text not null check (length(name) > 0), country_code varchar(2)
Data types
numeric is exact decimal arithmetic. Choose precision and scale for your domain. Floating point types are approximate; do not use them for exact money.
amount numeric(12,2) not null check (amount >= 0)
Data types
Whole numbers. bigint covers a larger range than integer; JavaScript cannot represent every bigint safely as a Number.
quantity integer not null check (quantity >= 0), sequence_number bigint generated always as identity
Data types
boolean models true/false/null. Arrays hold one element type. Enums constrain values but need deliberate migrations when the set evolves.
create type public.task_state as enum ('open', 'done');
select true::boolean, array['api', 'billing']::text[];Schema
Run the RLS and policy examples too before exposing this table. These are educational SQL snippets, not migrations applied by this tool.
create table public.projects (
id uuid primary key default gen_random_uuid(),
owner_id uuid not null references auth.users(id),
name text not null,
metadata jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now()
);RLS
RLS and SQL grants are separate checks. RLS without policies denies ordinary role access. Table owners and privileged roles can bypass RLS; keep service credentials on the server.
alter table public.projects enable row level security; grant select, insert, update, delete on public.projects to authenticated; revoke all on public.projects from anon;
RLS
auth.uid() is null without a signed-in user. USING filters existing rows. Restrict the policy to authenticated; users cannot authorize themselves with user_metadata.
create policy "Read own projects" on public.projects for select to authenticated using ((select auth.uid()) = owner_id);
RLS
WITH CHECK validates the new row. A SELECT policy alone never authorizes inserts.
create policy "Insert own projects" on public.projects for insert to authenticated with check ((select auth.uid()) = owner_id);
RLS
USING limits old rows; WITH CHECK constrains the updated row and prevents owner transfer. Supabase also requires a matching SELECT policy for updates.
create policy "Update own projects" on public.projects for update to authenticated using ((select auth.uid()) = owner_id) with check ((select auth.uid()) = owner_id);
RLS
USING constrains deletion. Avoid a policy querying the same protected table recursively. Test with ordinary authenticated identities, not a service role.
create policy "Delete own projects" on public.projects for delete to authenticated using ((select auth.uid()) = owner_id);
Functions
SECURITY INVOKER uses the caller permissions and RLS. Empty search_path with qualified names reduces name resolution risks. Functions default to public execution: revoke and grant intentionally.
create function public.my_projects() returns setof public.projects language sql stable security invoker set search_path = '' as $$ select * from public.projects where owner_id = (select auth.uid()); $$; revoke all on function public.my_projects() from public, anon; grant execute on function public.my_projects() to authenticated;
Indexes
An index on owner_id helps RLS lookups. A partial index only helps compatible predicates. Indexes cost write time and storage; inspect real plans.
create index projects_owner_created_idx on public.projects (owner_id, created_at desc); create index projects_open_idx on public.projects (owner_id) where metadata ->> 'state' = 'open';
Indexes
The default GIN jsonb operator class supports containment and key existence. jsonb_path_ops is smaller for supported containment/path queries but lacks key-existence operators.
create index projects_metadata_idx on public.projects using gin (metadata);
select * from public.projects where metadata @> '{"plan":"pro"}'::jsonb;Queries
ON CONFLICT needs a matching unique constraint or index. An upsert may require SELECT, INSERT and UPDATE policies. Random UUIDs alone do not deduplicate retries.
insert into public.projects (id, owner_id, name)
values ('11111111-1111-4111-8111-111111111111', auth.uid(), 'Demo')
on conflict (id) do update set name = excluded.name
returning id;Queries
Use placeholders with a server-side driver, never concatenate untrusted SQL. A composite cursor resolves tied timestamps. Add LIMIT and a supporting index.
select id, name, created_at from public.projects where owner_id = $1::uuid and (created_at, id) < ($2::timestamptz, $3::uuid) order by created_at desc, id desc limit 25; -- Supporting index: (owner_id, created_at desc, id desc)
Queries
BEGIN/COMMIT group changes; ROLLBACK abandons them. EXPLAIN ANALYZE executes the query, including writes: use a test database or a rollback transaction.
begin; explain select * from public.projects where owner_id = auth.uid(); rollback;