First things first, let's get the Supabase client library into your app. You can use npm or yarn for this.

npm install @supabase/supabase-js
# or
yarn add @supabase/supabase-js

Next up, we need to create a Supabase client instance. You'll need your API URL and Key, which you can find in your Supabase project settings.

import { createClient } from '@supabase/supabase-js';

const supabaseUrl = 'https://xyzcompany.supabase.co';
const supabaseKey = 'public-anon-key';
const supabase = createClient(supabaseUrl, supabaseKey);

Let's make sure we're managing our database connections efficiently. Supabase has built-in connection pooling for this. Head over to the Supabase Dashboard and do the following:

1. Go to the "Database" section.
2. Click on "Settings" > "Connection Pooling."
3. Enable Connection Pooling and set the max number of connections based on what your app needs.

Now, let's talk about writing optimized SQL queries. You want to minimize the load on your database. Use the right indexes and avoid those SELECT * queries. Only fetch the data you really need.

const { data, error } = await supabase
  .from('table_name')
  .select('column1, column2') // Specify the columns you need
  .eq('filter_column', 'value');

Caching is your friend. It helps reduce the load on your database. You can use in-memory caches like Redis or local caches in your client-side code.

1. Store frequently accessed data in a cache.
2. If the data isn't in the cache, fetch it from Supabase and then store it in the cache for next time.

Supabase enforces rate limits, so you need to handle them to avoid overwhelming the server. Implement retry mechanisms in your code.

async function fetchDataWithRetry() {
  let attempts = 0;
  const MAX_ATTEMPTS = 3;

  while (attempts < MAX_ATTEMPTS) {
    const { data, error } = await supabase.from('table_name').select('*');

    if (!error) {
      return data;
    }

    if (attempts < MAX_ATTEMPTS - 1) {
      await new Promise(resolve => setTimeout(resolve, 1000)); // wait 1 second before retrying
    }

    attempts++;
  }

  throw new Error('Failed to fetch data after multiple attempts.');
}

Keep an eye on your database's performance through the Supabase dashboard. Use the stats and logs to spot and fix performance issues.

1. Go to the "Database" section in the Supabase Dashboard.
2. Check the "Usage" tab for insights on query performance.
3. Look into "Logs" to troubleshoot issues and optimize queries.

If your app needs real-time data, use Supabase's real-time capabilities through WebSockets. This way, you won't need to keep polling with SQL queries.

const mySubscription = supabase
  .from('table_name')
  .on('INSERT', payload => {
    console.log('New row added!', payload.new);
  })
  .subscribe();

Don't forget to clean up inactive connections to free up resources. Unsubscribe from real-time subscriptions when you don't need them anymore.

mySubscription.unsubscribe();

Keep your Supabase URL and Key secure by storing them in environment variables. This also helps manage different environments like development, staging, and production.

// .env
REACT_APP_SUPABASE_URL=https://xyzcompany.supabase.co
REACT_APP_SUPABASE_KEY=public-anon-key

// Use in code
const supabaseUrl = process.env.REACT_APP_SUPABASE_URL;
const supabaseKey = process.env.REACT_APP_SUPABASE_KEY;
const supabase = createClient(supabaseUrl, supabaseKey);

Following these steps will help you handle database connections in Supabase efficiently, optimizing performance and resource usage.