Database

ShipNow supports Supabase as the database solution with built-in user management features.

Overview

The main features of Supabase Database include:

🔄 Unlimited API requests
🚀 Run on Edge Runtime
💻 Browser querying data support
🔐 Built-in file storage

Setup Supabase Environment Variables

Create a account on Supabase and create a project

Copy Supabase project url, anon key and service role key from the project API Settings

Copy Supabase project url, anon key and service role key

Add project url, anon key and service role key to .env.local file:

.env.local

NEXT_PUBLIC_SUPABASE_URL="https://your-project-id.supabase.co"
NEXT_PUBLIC_SUPABASE_ANON_KEY="eyJxxx"
SUPABASE_SERVICE_ROLE_KEY="eyJxxx"

Create Supabase Database Tables

Copy SQL schema from apps/web/migrations/1000_init.sql

apps/web/migrations/1000_init.sql

--
-- Name: next_auth; Type: SCHEMA;
--
CREATE SCHEMA next_auth;
 
GRANT USAGE ON SCHEMA next_auth TO service_role;
GRANT ALL ON SCHEMA next_auth TO postgres;
 
--
-- Create users table
--
CREATE TABLE IF NOT EXISTS next_auth.users
(
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    name text,
    email text,
    "emailVerified" timestamp with time zone,
    image text,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT email_unique UNIQUE (email)
);
 
GRANT ALL ON TABLE next_auth.users TO postgres;
GRANT ALL ON TABLE next_auth.users TO service_role;
 
--- uid() function to be used in RLS policies
CREATE FUNCTION next_auth.uid() RETURNS uuid
    LANGUAGE sql STABLE
    AS $$
  select
  	coalesce(
		nullif(current_setting('request.jwt.claim.sub', true), ''),
		(nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub')
	)::uuid
$$;
 
--
-- Create sessions table
--
CREATE TABLE IF NOT EXISTS  next_auth.sessions
(
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    expires timestamp with time zone NOT NULL,
    "sessionToken" text NOT NULL,
    "userId" uuid,
    CONSTRAINT sessions_pkey PRIMARY KEY (id),
    CONSTRAINT sessionToken_unique UNIQUE ("sessionToken"),
    CONSTRAINT "sessions_userId_fkey" FOREIGN KEY ("userId")
        REFERENCES  next_auth.users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);
 
GRANT ALL ON TABLE next_auth.sessions TO postgres;
GRANT ALL ON TABLE next_auth.sessions TO service_role;
 
--
-- Create accounts table
--
CREATE TABLE IF NOT EXISTS  next_auth.accounts
(
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    type text NOT NULL,
    provider text NOT NULL,
    "providerAccountId" text NOT NULL,
    refresh_token text,
    access_token text,
    expires_at bigint,
    token_type text,
    scope text,
    id_token text,
    session_state text,
    oauth_token_secret text,
    oauth_token text,
    "userId" uuid,
    CONSTRAINT accounts_pkey PRIMARY KEY (id),
    CONSTRAINT provider_unique UNIQUE (provider, "providerAccountId"),
    CONSTRAINT "accounts_userId_fkey" FOREIGN KEY ("userId")
        REFERENCES  next_auth.users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);
 
GRANT ALL ON TABLE next_auth.accounts TO postgres;
GRANT ALL ON TABLE next_auth.accounts TO service_role;
 
--
-- Create verification_tokens table
--
CREATE TABLE IF NOT EXISTS  next_auth.verification_tokens
(
    identifier text,
    token text,
    expires timestamp with time zone NOT NULL,
    CONSTRAINT verification_tokens_pkey PRIMARY KEY (token),
    CONSTRAINT token_unique UNIQUE (token),
    CONSTRAINT token_identifier_unique UNIQUE (token, identifier)
);
 
GRANT ALL ON TABLE next_auth.verification_tokens TO postgres;
GRANT ALL ON TABLE next_auth.verification_tokens TO service_role;
 
CREATE TABLE IF NOT EXISTS public.users
(
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    name text,
    email text,
    "emailVerified" timestamp with time zone,
    image text,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT email_unique UNIQUE (email)
);
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
 
-- Inserts a row into public.users
CREATE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = ''
AS $$
BEGIN
  INSERT INTO public.users (id, name, email, "emailVerified", image)
  VALUES (new.id, new.raw_user_meta_data ->> 'name', new.email, new.email_confirmed_at, new.raw_user_meta_data ->> 'avatar_url');
  RETURN new;
END;
$$;
 
-- Trigger the function every time a user is created
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

Go to Supabase SQL Editor, paste the SQL schema and run it.

Run SQL schema to your database

Go to Supabase API Settings, add next_auth to the "Exposed schemas" list.

Expose the NextAuth schema in Supabase

Database CRUD Operations

Read One User

import { createAdminClient } from '@/utils/supabase/server';
 
async function readUser(id: string) {
  const supabase = createAdminClient();
  const { data, error } = await supabase
    .from('users')
    .select()
    .eq('id', id);
 
  if (error) throw error;
  return data?.[0];
}
 
const user = await readUser('user-uuid');

Read User List

import { createAdminClient } from '@/utils/supabase/server';
 
async function readUsers() {
  const supabase = createAdminClient();
  const { data, error } = await supabase
    .from('users')
    .select();
 
  if (error) throw error;
  return data;
}
 
const users = await readUsers();

Create User

import { createAdminClient } from '@/utils/supabase/server';
 
async function createUser(userData: {
  email: string;
  name?: string;
}) {
  const supabase = createAdminClient();
  const { data, error } = await supabase
    .from('users')
    .insert([userData])
    .select();
 
  if (error) throw error;
  return data?.[0];
}
 
// Usage
const newUser = await createUser({
  email: '[email protected]',
  name: 'John Doe'
});

Update User

import { createAdminClient } from '@/utils/supabase/server';
 
async function updateUser(id: string, updates: {
  name?: string;
  email?: string;
}) {
  const supabase = createAdminClient();
  const { data, error } = await supabase
    .from('users')
    .update(updates)
    .eq('id', id)
    .select();
 
  if (error) throw error;
  return data?.[0];
}
 
// Usage
const updatedUser = await updateUser('user-uuid', {
  name: 'New Name'
});

Delete User

import { createAdminClient } from '@/utils/supabase/server';
 
async function deleteUser(id: string) {
  const supabase = createAdminClient();
  const { error } = await supabase
    .from('users')
    .delete()
    .eq('id', id);
 
  if (error) throw error;
  return true;
}
 
// Usage
await deleteUser('user-uuid');

Using Supabase Client

Server Components

For server-side operations to access the database, use the server client:

import { createAdminClient } from '@/utils/supabase/server';
 
export async function MyServerComponent() {
  const supabase = await createAdminClient();
  
  const { data, error } = await supabase
    .from('users')
    .select('*')
    .single();
    
  if (error) throw error;
  
  return <div>User: {data.email}</div>;
}

Client Components

For client-side operations, use the client-side client:

'use client';
 
import { createClient } from '@/utils/supabase/client';
 
export function MyClientComponent() {
  const supabase = createClient();
  
  async function fetchUser() {
    const { data, error } = await supabase
      .from('users')
      .select('*')
      .single();
 
    if (error) throw error;
  }
  
  return <button onClick={fetchUser}>Fetch User</button>;
}

In the browser, you need to configure RLS policies to read data.

Need Help?

Check the Supabase documentation
Join our Discord community
Open an issue on GitHub

On this page