Supabase is an open source Firebase alternative. Start your project with a Postgres database, Authentication, instant APIs, Edge Functions, Realtime subscriptions, and Storage.

Supabase

To track visits without using google analytics should I just make a table for visitors and increment to it on every page load?

I literally went to sleep and now when I woke up the table isn't there anymore.

It ain't that big of an issue as long as it doesn't happen again.

How can I query random row from a table? Is there any built in method to do that?

https://github.com/supabase/supabase/discussions/2299

is the postgres database connection available, if yes

can i build my own express app using the database connection , to create my own api for managing the database

and create my own backend using the database url from superbase postgres

how can we do server side login validation via Supabase ? Any guide or tutorials out there ? I'm using Framer + Supabase.

Has anyone set up geo location, distance from location, with supabase query?

It depends what you're trying to achieve.

If you just want to know the distance from a location, there's libs like `geolib` (for JS).

If you've got a set of data in Postgres and want to search for e.g. nearest points from the user location, there's a few things to setup. I've done this for things like bus stops, since users will usually want to know where bus stops are within an area as opposed to remembering a street name.

1. Ensure that each row has a `latitude` and `longitude` property and that all rows have valid data for these columns.

2. Ensure that you've enabled (or installed, if self-hosting) the `postgis` extensions

3. Update your table so that it has a `geometry` column of type `extensions.geometry`

4. Update every row in the table to create the geometry data (in this example, the table is called `some_table`:
```sql
UPDATE public.some_table SET geometry = extensions.ST_SetSRID(extensions.ST_MakePoint(longitude, latitude), 4326);
```

5. Create a function in Postgres with the following content:

```sql
CREATE OR REPLACE FUNCTION public.search_locations_by_distance(
    in_latitude real,
    in_longitude real,
    max_distance_in_meters integer,
    result_limit integer DEFAULT 20)
    RETURNS TABLE(
        atco_code text,
        naptan text,
        common_name text,
        short_common_name text,
        landmark text,
        street text,
        crossing text,
        bearing text,
        longitude real,
        latitude real,
        geographic_area text,
        distance double precision
    ) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000
AS $BODY$
BEGIN RETURN QUERY
SELECT *
FROM public.some_table as stops,
    extensions.ST_Distancesphere(
        stops.geometry,
        extensions.ST_MakePoint(in_longitude, in_latitude)
    ) as distance
WHERE extensions.ST_Distancesphere(
        stops.geometry,
        extensions.ST_MakePoint(in_longitude, in_latitude)
    ) < LEAST (max_distance_in_meters, 5000)
ORDER BY distance ASC
LIMIT LEAST (result_limit, 100);
END;
$BODY$;
```

In that example, it allows me to specify a latitude, a longitude, a maximum distance, and a result limit. It also enforces a maximum distance of 5000 meters and 100 results using the `LEAST` function.

After you've gone through that setup, you're able to call it with the JS library like this:

```js
SupabaseClient.rpc("search_locations_by_distance", {
  in_latitude: <user latitude>,
  in_longitude: <user longitude>,
  max_distance_in_meters: 800,
})
```

`result_limit` will default to 20 if not specified.

In terms of performance, with ~37 million rows which have indexes and partitions setup, querying data takes around 1400ms on a self-hosted instance (with 4GB RAM and 2 VCPU cores). It's certainly not the fastest query out there, but I deem it to be acceptable considering the size of the dataset.

Hi Scott, Thank you so much for this explanation. The more involved set up is what I'm looking for. 

I appreciate the detail. And this is exciting. We ought to set up a Medium for official supabase tutorials, this being one. Or some sort of curated list of tutorials by the Supabase team. 

Question, what do you think the query performance would be on a Supabase hosted instance?

I'm honestly not sure what the performance would be like on a hosted instance - my dataset actually exceeded what was available on the $25 plan so I couldn't test anything of this scale. However, I'd expect the performance to be very similar. Without indexes and partitions, I found the queries to take anywhere between 3-5x longer to complete.

<@!845250139706753044> can you share your postgres geolocation function? I figured I'd try and do this.

`WHERE
    earth_box(ll_to_earth(${lat}, ${lng}), ${radius}) @> ll_to_earth("Latitude", "Longitude") 

    AND earth_distance(ll_to_earth(${lat}, ${lng}), 
              ll_to_earth("Latitude", "Longitude")) < ${radius} 

ORDER BY distance LIMIT 200`

You pass it:  `lat` and `lng`, the fields in the database are named `Latitude` and `Longitude`, and you also pass it `radius` which is in meters, which would be the max search radius.

Be sure to turn on the `EARTHDISTANCE` extension!

How can I update a user's metadata as an admin? I don't see anything about this in the docs.

Presumably, the supabase.auth.update() only works when the user is logged in: https://supabase.io/docs/reference/javascript/auth-update

you can create a new table, maybe something like `profiles` which can contain whatever user metadata you like.

could also add a enum type for the user_status column

```sql

CREATE TYPE user_status AS ENUM ('trialing', 'active', 'cancelled');

CREATE TABLE profiles (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  -- foreign key that uses auth.users, so the UUID here has to match one of your users
  user_id uuid REFERENCES auth.users NOT NULL,
  inserted_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at timestamp with time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
  -- column for user_status, using the enum type made above
  user_status user_status DEFAULT 'active'
)
```

but doing this requires two steps in order to check the status of the user

the first calls supabase to fetch the user, but then a second step is required to fetch the info from the other profiles table

I was hoping that I could include this information in the initial auth response, such as by having it in the metadata, but then I need a way to update this data programmatically

Not sure then - I originally thought you could expose the `auth` schema in your api, but I think that schema is protected. Then you could of queried a foreign table at the same time as `auth.users`

I think there was some good reasons, mostly security, as to why the auth.users table is locked up and not exposed though - and possibly some reasoning why we encourage you to keep user meta data in another table - i'll ask around. its an interesting use case 🙂

hi <@!290237132310839297> ! wanting to do the exact same thing ... did you figure this out?

looked into the GoTrue repo and there is an admin API you can use ... this worked for me:

```import { NextApiRequest, NextApiResponse } from "next"

export default async function handler(req: NextApiRequest, res: NextApiResponse) {
  try {
    if (req.method === "PATCH") {
      const { userId } = req.query
      const { appMetadata } = req.body

      if (!appMetadata) {
        res.status(400).json({ error: "Missing appMetadata" })
        return
      }

      const url = `${process.env.NEXT_PUBLIC_SUPABASE_URL}/auth/v1/admin/users/${userId}`
      const options = {
        method: 'PUT',
        body: JSON.stringify({ app_metadata: appMetadata }),
        headers: {
          'Content-Type': 'application/json',
          'Authorization': `Bearer ${process.env.SUPABASE_SERVICE_KEY}`,
          'apikey': process.env.SUPABASE_SERVICE_KEY,
        }
      }
      const result = await fetch(url, options)
        .then(res => res.json())
      
      res.status(200).json({ user: result })
    } else {
      res.status(405).json({ error: 'Method not allowed' })
    }
  } catch (error) {
    res.status(500).json({ error: error.message || JSON.stringify(error) })
  }
}```

basically hosting that on my app: PATCH /api/private-protected-admin-endpoints/users/{id}

it's kind of weird how it needs two headers for auth, but one is for supabase's API wrapper and the oher one for actual GoTrue's api

needless to say this has to be called from a protected endpoint since it's using supabase's service api key

proceed with caution with the above. I'm not 100% certain but the above could possibly break some supabase auth features depending what is in the body of that fetch

cc <@!377023879983661067> - some interesting use cases here for updating user meta data on auth schema

For me, each user has a status -- like trialing, active, cancelled -- and I want to be able to receive this status when the person authenticates

but I need a way to update this status programmatically in response to webhooks

[Help With] Server-side authentication validation

I do not know how to code, I'd like to get help from someone with setting this up together with me, step by step. I am using:

-Framer for the frontend, which is basically using ReactJs overrides which can be easily applied to visual components and so on.

-Supabase (for the DB itself)

1. First we’ll need to prepare the server which is going to handle the validation request from the client (my app). This means setting it up, writing the required code and connecting it to the DB.
2. Setting up Supabase (I already have it connected with Framer and there’s already a working table) but we may need to do additional work (your call).
3. Setting up the front-end through React overrides will be needed as well. This validation request needs to be happening when the log-in component [button] is tapped by an user
4. Making sure the validation happens on both client & server side
5. Testing for errors

How long does a user stay logged in for after logging in with a magic link?

Until they log out or until their token expires (token gets automatically renewed within an hour if the tab/window is active)

does that mean that if the token expires when they are not on the site, they need to log in again next time they visit?

I'm not able to sign into Supabase today - after clicking 'Continue with GitHub' it redirects to https://api.supabase.io/ and shows `{"code":401,"message":"Unauthorized"}` 

Have cleared cookies and tried in different browsers.

I can confirm your problem. Meanwhile i tryed to sign in with my github account. It doesnt work

We've recently made changes to our auth. Can you try again?

Still not working. I used inkognito for sign in with github

I received an internal server error when creating a new project. Has anyone had the same problem today?

We're aware of an issue with login and creating projects. We are looking into this now

Things will be coming back to normal now.

Sounds good! Thanks for fixing this issue so fast 🙂