openref: 0.1 info: id: reference/supabase-py title: Supabase Python Client description: | Supabase Python definition: spec/enrichments/tsdoc_v2/combined.json specUrl: https://github.com/supabase/supabase/edit/master/apps/docs/spec/supabase_py_v2.yml slugPrefix: '/' libraries: - name: 'Python' id: 'py' version: '0.0.1' functions: - id: initializing description: | You can initialize a new Supabase client using the `create_client()` method. The Supabase client is your entrypoint to the rest of the Supabase functionality and is the easiest way to interact with everything we offer within the Supabase ecosystem. params: - name: supabase_url isOptional: false type: string description: The unique Supabase URL which is supplied when you create a new project in your project dashboard. - name: supabase_key isOptional: false type: string description: The unique Supabase Key which is supplied when you create a new project in your project dashboard. - name: options isOptional: true type: ClientOptions description: Options to change the Auth behaviors. subContent: - name: schema isOptional: true type: string description: The Postgres schema which your tables belong to. Must be on the list of exposed schemas in Supabase. Defaults to 'public'. - name: headers isOptional: true type: dictionary description: Optional headers for initializing the client. - name: auto_refresh_token isOptional: true type: bool description: Whether to automatically refresh the token when it expires. Defaults to `true`. - name: persist_session isOptional: true type: bool description: Whether to persist a logged in session to storage. - name: storage isOptional: true type: SyncSupportedStorage description: A storage provider. Used to store the logged in session. - name: realtime isOptional: true type: string description: Options passed to the realtime-py instance. - name: postgrest_client_timeout isOptional: true type: int, float, Timeout description: Timeout passed to the SyncPostgrestClient instance. - name: storage_client_timeout isOptional: true type: int, float, Timeout description: Timeout passed to the SyncStorageClient instance. - name: flow_type isOptional: true type: AuthFlowType description: flow type to use for authentication. examples: - id: create-client name: create_client() code: | ``` import os from supabase import create_client, Client url: str = os.environ.get("SUPABASE_URL") key: str = os.environ.get("SUPABASE_KEY") supabase: Client = create_client(url, key) ``` - id: with-timeout-option name: With timeout option code: | ``` import os from supabase import create_client, Client from supabase.client import ClientOptions url: str = os.environ.get("SUPABASE_URL") key: str = os.environ.get("SUPABASE_KEY") supabase: Client = create_client(url, key, options=ClientOptions( postgrest_client_timeout=10, storage_client_timeout=10, schema="public", )) ``` - id: sign-up title: 'sign_up()' params: - name: credentials isOptional: false type: SignUpWithPasswordCredentials subContent: - name: email isOptional: true type: string description: One of `email` or `phone` must be provided. - name: phone isOptional: true type: string description: One of `email` or `phone` must be provided. - name: password type: string - name: options isOptional: true type: object subContent: - name: email_redirect_to isOptional: true type: string description: > Only for email signups. The redirect URL embedded in the email link. Must be a configured redirect URL for your Supabase instance. - name: data isOptional: true type: object description: > A custom data object to store additional user metadata. - name: captcha_token isOptional: true type: string - name: channel isOptional: true type: sms | whatsapp description: > The channel to use for sending messages. Only for phone signups. notes: | - By default, the user needs to verify their email address before logging in. To turn this off, disable **Confirm email** in [your project](https://supabase.com/dashboard/project/_/auth/providers). - **Confirm email** determines if users need to confirm their email address after signing up. - If **Confirm email** is enabled, a `user` is returned but `session` is null. - If **Confirm email** is disabled, both a `user` and a `session` are returned. - By default, when the user confirms their email address, they are redirected to the [`SITE_URL`](https://supabase.com/docs/guides/auth/redirect-urls). You can modify your `SITE_URL` or add additional redirect URLs in [your project](https://supabase.com/dashboard/project/_/auth/url-configuration). - If sign_up() is called for an existing confirmed user: - When both **Confirm email** and **Confirm phone** (even when phone provider is disabled) are enabled in [your project](/dashboard/project/_/auth/providers), an obfuscated/fake user object is returned. - When either **Confirm email** or **Confirm phone** (even when phone provider is disabled) is disabled, the error message, `User already registered` is returned. - To fetch the currently logged-in user, refer to [`get_user()`](/docs/reference/python/auth-getuser). examples: - id: signup name: Sign up with an email and password code: | ```python response = supabase.auth.sign_up( {"email": "email@example.com", "password": "password"} ) ``` response: | ```json { "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [ "email" ] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": null, "recovery_sent_at": null, "email_change_sent_at": null, "new_email": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2024-06-17T00:19:25.760110Z", "confirmed_at": null, "email_confirmed_at": "2024-06-17T00:19:25.779181Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-06-17T00:19:25.785489Z", "role": "authenticated", "updated_at": "2024-06-17T00:19:25.794650Z", "identities": [ { "id": "11111111-1111-1111-1111-111111111111", "user_id": "11111111-1111-1111-1111-111111111111", "identity_data": { "email": "email@example.com", "sub": "11111111-1111-1111-1111-111111111111" }, "provider": "email", "created_at": "2024-06-17T00:19:25.774522Z", "last_sign_in_at": "2024-06-17T00:19:25.774498Z", "updated_at": "2024-06-17T00:19:25.774522Z" } ], "factors": null }, "session": { "provider_token": null, "provider_refresh_token": null, "access_token": "", "refresh_token": "", "expires_in": 3600, "expires_at": 1700000000, "token_type": "bearer", "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [ "email" ] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": null, "recovery_sent_at": null, "email_change_sent_at": null, "new_email": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2024-06-17T00:19:25.760110Z", "confirmed_at": null, "email_confirmed_at": "2024-06-17T00:19:25.779181Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-06-17T00:19:25.785489Z", "role": "authenticated", "updated_at": "2024-06-17T00:19:25.794650Z", "identities": [ { "id": "11111111-1111-1111-1111-111111111111", "user_id": "11111111-1111-1111-1111-111111111111", "identity_data": { "email": "email@example.com", "sub": "11111111-1111-1111-1111-111111111111" }, "provider": "email", "created_at": "2024-06-17T00:19:25.774522Z", "last_sign_in_at": "2024-06-17T00:19:25.774498Z", "updated_at": "2024-06-17T00:19:25.774522Z" } ], "factors": null } } } ``` - id: sign-up-phone name: Sign up with a phone number and password (SMS) isSpotlight: true code: | ```python response = supabase.auth.sign_up( {"phone": "123456789", "password": "password"} ) ``` - id: sign-up-phone-whatsapp name: Sign up with a phone number and password (whatsapp) isSpotlight: true description: | The user will be sent a WhatsApp message which contains a OTP. By default, a given user can only request a OTP once every 60 seconds. Note that a user will need to have a valid WhatsApp account that is linked to Twilio in order to use this feature. code: | ```python response = supabase.auth.sign_up( { "phone": "123456789", "password": "password", "options": {"channel": "whatsapp"}, } ) ``` - id: sign-up-with-additional-user-metadata name: Sign up with additional user metadata code: | ```python response = supabase.auth.sign_up( { "email": "email@example.com", "password": "password", "options": {"data": {"first_name": "John", "age": 27}}, } ) ``` - id: sign-up-with-redirect name: Sign up with a redirect URL description: | - See [redirect URLs and wildcards](/docs/guides/auth/redirect-urls) to add additional redirect URLs to your project. code: | ```python response = supabase.auth.sign_up( { "email": "hello1@example.com", "password": "password", "options": { "email_redirect_to": "https://example.com/welcome", }, } ) ``` - id: sign-in-anonymously title: 'sign_in_anonymously()' params: - name: credentials isOptional: false type: SignInAnonymouslyCredentials subContent: - name: options isOptional: true type: object subContent: - name: data isOptional: true type: object description: A custom data object to store the user's metadata. This maps to the `auth.users.raw_user_meta_data` column. The `data` should be a JSON object that includes user-specific info, such as their first and last name. - name: captcha_token isOptional: true type: string description: Verification token received when the user completes the captcha on the site. notes: | - Returns an anonymous user - It is recommended to set up captcha for anonymous sign-ins to prevent abuse. You can pass in the captcha token in the `options` param. examples: - id: sign-in-anonymously name: Create an anonymous user isSpotlight: true code: | ```python response = supabase.auth.sign_in_anonymously( {"options": {"captcha_token": ""}} ) ``` response: | ```json { "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": {}, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": null, "recovery_sent_at": null, "email_change_sent_at": null, "new_email": null, "invited_at": null, "action_link": null, "email": "", "phone": "", "created_at": "2024-06-25T19:57:25.870777Z", "confirmed_at": null, "email_confirmed_at": null, "phone_confirmed_at": null, "last_sign_in_at": "2024-06-25T19:57:25.878874Z", "role": "authenticated", "updated_at": "2024-06-25T19:57:25.880971Z", "identities": [], "factors": null, "is_anonymous": true }, "session": { "provider_token": null, "provider_refresh_token": null, "access_token": "", "refresh_token": "", "expires_in": 3600, "expires_at": 1719349045, "token_type": "bearer", "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": {}, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": null, "recovery_sent_at": null, "email_change_sent_at": null, "new_email": null, "invited_at": null, "action_link": null, "email": "", "phone": "", "created_at": "2024-06-25T19:57:25.870777Z", "confirmed_at": null, "email_confirmed_at": null, "phone_confirmed_at": null, "last_sign_in_at": "2024-06-25T19:57:25.878874Z", "role": "authenticated", "updated_at": "2024-06-25T19:57:25.880971Z", "identities": [], "factors": null, "is_anonymous": true } } } ``` - id: sign-in-anonymously-with-user-metadata name: Create an anonymous user with custom user metadata isSpotlight: false code: | ```python response = supabase.auth.sign_in_anonymously( {"options": {"data": {}}} ) ``` - id: sign-in-with-password title: 'sign_in_with_password' params: - name: credentials type: SignInWithPasswordCredentials subContent: - name: email isOptional: true type: string description: One of `email` or `phone` must be provided. - name: phone isOptional: true type: string description: One of `email` or `phone` must be provided. - name: password type: string - name: options isOptional: true type: object subContent: - name: captcha_token isOptional: true type: string notes: | Log in an existing user with an email and password or phone and password. - Requires either an email and password or a phone number and password. examples: - id: sign-in-with-email-and-password name: Sign in with email and password isSpotlight: true code: | ```python response = supabase.auth.sign_in_with_password( {"email": "email@example.com", "password": "example-password"} ) ``` response: | ```json { "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [ "email" ] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": "2023-02-19T00:01:51.147035Z", "recovery_sent_at": null, "email_change_sent_at": null, "new_email": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2023-02-19T00:01:51.142802Z", "confirmed_at": "2023-02-19T00:01:51.351735Z", "email_confirmed_at": "2023-02-19T00:01:51.351735Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-06-25T19:50:31.744823Z", "role": "authenticated", "updated_at": "2024-06-25T19:50:31.757491Z", "identities": [], "factors": null, "is_anonymous": false }, "session": { "provider_token": null, "provider_refresh_token": null, "access_token": "", "refresh_token": "", "expires_in": 3600, "expires_at": 1719348631, "token_type": "bearer", "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [ "email" ] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": "2023-02-19T00:01:51.147035Z", "recovery_sent_at": null, "email_change_sent_at": null, "new_email": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2023-02-19T00:01:51.142802Z", "confirmed_at": "2023-02-19T00:01:51.351735Z", "email_confirmed_at": "2023-02-19T00:01:51.351735Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-06-25T19:50:31.744823Z", "role": "authenticated", "updated_at": "2024-06-25T19:50:31.757491Z", "identities": [], "factors": null, "is_anonymous": false } } } ``` - id: sign-in-with-phone-and-password name: Sign in with phone and password isSpotlight: false code: | ```python response = supabase.auth.sign_in_with_password( {"phone": "+13334445555", "password": "some-password"} ) ``` - id: sign-in-with-otp title: 'sign_in_with_otp' params: - name: credentials type: SignInWithPasswordCredentials subContent: - name: email isOptional: true type: string description: One of `email` or `phone` must be provided. - name: phone isOptional: true type: string description: One of `email` or `phone` must be provided. - name: options isOptional: true type: object subContent: - name: email_redirect_to isOptional: true type: string description: > Only for email signups. The redirect URL embedded in the email link. Must be a configured redirect URL for your Supabase instance. - name: should_create_user isOptional: true type: boolean description: > Whether to create the user if they don't already exist. Defaults to true. - name: data isOptional: true type: object description: > A custom data object to store additional user metadata. - name: captcha_token isOptional: true type: string - name: channel isOptional: true type: sms | whatsapp description: > The channel to use for sending messages. Only for phone signups. notes: | - Requires either an email or phone number. - This method is used for passwordless sign-ins where a OTP is sent to the user's email or phone number. - If the user doesn't exist, `sign_in_with_otp()` will signup the user instead. To restrict this behavior, you can set `should_create_user` in `SignInWithPasswordlessCredentials.options` to `false`. - If you're using an email, you can configure whether you want the user to receive a magiclink or a OTP. - If you're using phone, you can configure whether you want the user to receive a OTP. - The magic link's destination URL is determined by the [`SITE_URL`](/docs/guides/auth/redirect-urls). - See [redirect URLs and wildcards](/docs/guides/auth/overview#redirect-urls-and-wildcards) to add additional redirect URLs to your project. - Magic links and OTPs share the same implementation. To send users a one-time code instead of a magic link, [modify the magic link email template](https://supabase.com/dashboard/project/_/auth/templates) to include `{{ .Token }}` instead of `{{ .ConfirmationURL }}`. examples: - id: sign-in-with-email name: Sign in with email isSpotlight: true description: The user will be sent an email which contains either a magiclink or a OTP or both. By default, a given user can only request a OTP once every 60 seconds. code: | ```python response = supabase.auth.sign_in_with_otp( { "email": "email@example.com", "options": {"email_redirect_to": "https://example.com/welcome"}, } ) ``` response: | ```json { "message_id": null, "user": null, "session": null } ``` - id: sign-in-with-sms-otp name: Sign in with SMS OTP isSpotlight: false description: The user will be sent a SMS which contains a OTP. By default, a given user can only request a OTP once every 60 seconds. code: | ```python response = supabase.auth.sign_in_with_otp({ "phone": '+13334445555', }) ``` - id: sign-in-with-whatsapp-otp name: Sign in with WhatsApp OTP isSpotlight: false description: The user will be sent a WhatsApp message which contains a OTP. By default, a given user can only request a OTP once every 60 seconds. Note that a user will need to have a valid WhatsApp account that is linked to Twilio in order to use this feature. code: | ```python supabase.auth.sign_in_with_otp({ "phone": "+13334445555", "options": { "channel": "whatsapp", } }) ``` - id: sign-in-with-oauth title: 'sign_in_with_oauth' params: - name: credentials isOptional: false type: SignInWithOAuthCredentials subContent: - name: provider isOptional: false type: boolean description: One of the providers supported by GoTrue. - name: options isOptional: true type: object subContent: - name: scopes isOptional: true type: string description: A space-separated list of scopes granted to the OAuth application. - name: redirect_to isOptional: true type: string description: A URL to send the user to after they are confirmed. - name: query_params isOptional: true type: string description: An object of query params notes: | - This method is used for signing in using a third-party provider. - Supabase supports many different [third-party providers](/docs/guides/auth#configure-third-party-providers). examples: - id: sign-in-using-a-third-party-provider name: Sign in using a third-party provider isSpotlight: true code: | ``` response = supabase.auth.sign_in_with_oauth({ "provider": 'github' }) ``` - id: sign-in-using-a-third-party-provider-with-redirect name: Sign in using a third-party provider with redirect isSpotlight: false description: | - When the third-party provider successfully authenticates the user, the provider redirects the user to the URL specified in the `redirectTo` parameter. This parameter defaults to the [`SITE_URL`](/docs/guides/auth/redirect-urls). It does not redirect the user immediately after invoking this method. - See [redirect URLs and wildcards](/docs/guides/auth/overview#redirect-urls-and-wildcards) to add additional redirect URLs to your project. code: | ``` response = supabase.auth.sign_in_with_oauth({ "provider": 'github', "options": { "redirect_to": 'https://example.com/welcome' } }) ``` - id: sign-in-with-scopes name: Sign in with scopes isSpotlight: false description: | If you need additional data from an OAuth provider, you can include a space-separated list of scopes in your request to get back an OAuth provider token. You may also need to specify the scopes in the provider's OAuth app settings, depending on the provider. The list of scopes will be documented by the third-party provider you are using and specifying scopes will enable you to use the OAuth provider token to call additional APIs supported by the third-party provider to get more information. code: | ``` response = supabase.auth.sign_in_with_oauth({ "provider": 'github', "options": { "scopes": 'repo gist notifications' } }) ``` - id: sign-in-with-id-token title: 'sign_in_with_id_token' params: - name: credentials type: SignInWithIdTokenCredentials subContent: - name: provider isOptional: false type: '"google" | "apple" | "azure" | "facebook" | "kakao" | string' description: | Provider name or OIDC `iss` value identifying which provider should be used to verify the provided token. Supported names: `google`, `apple`, `azure`, `facebook`, `kakao`, `keycloak` (deprecated). - name: token isOptional: false type: string description: | OIDC ID token issued by the specified provider. The `iss` claim in the ID token must match the supplied provider. Some ID tokens contain an `at_hash` which require that you provide an `access_token` value to be accepted properly. If the token contains a `nonce` claim you must supply the nonce used to obtain the ID token. - name: options isOptional: true type: object subContent: - name: captcha_token isOptional: true type: string - name: nonce isOptional: true type: string description: | If the ID token contains a `nonce` claim, then the hash of this value is compared to the value in the ID token. - name: access_token isOptional: true type: string description: | If the ID token contains an `at_hash` claim, then the hash of this value is compared to the value in the ID token. notes: | Allows signing in with an OIDC ID token. The authentication provider used should be enabled and configured. examples: - id: sign-in-with-id-token name: 'Sign In using ID Token' code: | ```python response = supabase.auth.sign_in_with_id_token( {"provider": "google", "token": "your-id-token"} ) ``` response: | ```json { "session": { "access_token": "", "expires_at": 1721236789, "expires_in": 60, "provider_refresh_token": null, "provider_token": null, "refresh_token": "", "token_type": "bearer", "user": { "action_link": null, "app_metadata": { ... }, "aud": "authenticated", "confirmation_sent_at": null, "confirmed_at": "Wed, 17 Jul 2024 16:34:24 GMT", "created_at": "Wed, 17 Jul 2024 16:34:24 GMT", "email": "email@example.com", "email_change_sent_at": null, "email_confirmed_at": "Wed, 17 Jul 2024 16:34:24 GMT", "factors": null, "id": "11111111-1111-1111-1111-111111111111", "identities": [ { "identity_id": "22222222-2222-2222-2222-222222222222", "provider": "google", "user_id": "11111111-1111-1111-1111-111111111111" } ], "invited_at": null, "last_sign_in_at": "Wed, 17 Jul 2024 17:18:49 GMT", "new_email": null, "phone": "", "phone_confirmed_at": null, "recovery_sent_at": null, "role": "authenticated", "updated_at": "Wed, 17 Jul 2024 17:18:49 GMT", "user_metadata": { ... } } }, "user": { "action_link": null, "app_metadata": { ... }, "aud": "authenticated", "confirmation_sent_at": null, "confirmed_at": "Wed, 17 Jul 2024 16:34:24 GMT", "created_at": "Wed, 17 Jul 2024 16:34:24 GMT", "email": "email@example.com", "email_change_sent_at": null, "email_confirmed_at": "Wed, 17 Jul 2024 16:34:24 GMT", "factors": null, "id": "11111111-1111-1111-1111-111111111111", "identities": [ { "identity_id": "22222222-2222-2222-2222-222222222222", "provider": "google", "user_id": "11111111-1111-1111-1111-111111111111" } ], "invited_at": null, "last_sign_in_at": "Wed, 17 Jul 2024 17:18:49 GMT", "new_email": null, "phone": "", "phone_confirmed_at": null, "recovery_sent_at": null, "role": "authenticated", "updated_at": "Wed, 17 Jul 2024 17:18:49 GMT", "user_metadata": { ... } } } ``` - id: sign-in-with-sso title: 'sign_in_with_sso()' overwriteParams: - name: params type: SignInWithSSOCredentials subContent: - name: provider_id isOptional: true type: string description: > UUID of the SSO provider. One of `provider_id` or `domain` is required. - name: domain isOptional: true type: string description: > Domain name of the organization to use SSO with. One of `provider_id` or `domain` is required. - name: options isOptional: true type: object subContent: - name: redirect_to type: string description: > The URL to redirect the user to after they have signed in. Must be a configured redirect URL for your Supabase instance. - name: captcha_token type: string notes: | - Before you can call this method you need to [establish a connection](/docs/guides/auth/sso/auth-sso-saml#managing-saml-20-connections) to an identity provider. Use the [CLI commands](/docs/reference/cli/supabase-sso) to do this. - If you've associated an email domain to the identity provider, you can use the `domain` property to start a sign-in flow. - In case you need to use a different way to start the authentication flow with an identity provider, you can use the `provider_id` property. For example: - Mapping specific user email addresses with an identity provider. - Using different hints to identity the identity provider to be used by the user, like a company-specific page, IP address or other tracking information. examples: - id: sign-in-with-domain name: Sign in with email domain isSpotlight: true description: | You can extract the user's email domain and use it to trigger the authentication flow with the correct identity provider. code: | ```python response = supabase.auth.sign_in_with_sso({"domain": "company.com"}) ``` response: | ```json { "url": "" } ``` - id: sign-in-with-provider-uuid name: Sign in with provider UUID isSpotlight: true description: | Useful when you need to map a user's sign in request according to different rules that can't use email domains. code: | ```python response = supabase.auth.sign_in_with_sso( {"provider_id": "21648a9d-8d5a-4555-a9d1-d6375dc14e92"} ) ``` - id: sign-out title: 'sign_out()' params: - name: options isOptional: true type: SignOutOptions subContent: - name: scope isOptional: true type: '"global" | "local" | "others"' description: | Determines which sessions should be logged out. Global means all sessions by this account. Local means only this session. Others means all other sessions except the current one. When using others, there is no sign-out event fired on the current session! notes: | - In order to use the `sign_out()` method, the user needs to be signed in first. - By default, `sign_out()` uses the global scope, which signs out all other sessions that the user is logged into as well. - Since Supabase Auth uses JWTs for authentication, the access token JWT will be valid until it's expired. When the user signs out, Supabase revokes the refresh token and deletes the JWT from the client-side. This does not revoke the JWT and it will still be valid until it expires. examples: - id: sign-out name: Sign out code: | ```python response = supabase.auth.sign_out() ``` - id: verify-otp title: 'verify_otp' params: - name: params type: VerifyOtpParams subContent: - name: phone isOptional: true type: string description: One of `phone`, `email`, or `token_hash` must be provided. - name: email isOptional: true type: string description: One of `phone`, `email`, or `token_hash` must be provided. - name: token_hash isOptional: true type: string description: > The token hash from the user's email link. One of `phone`, `email`, or `token_hash` must be provided. - name: type type: sms | phone_change | signup | invite | magiclink | recovery | email_change | email - name: token isOptional: true type: string description: The OTP sent to the user. Required if using `phone` or `email`. - name: options isOptional: true type: object subContent: - name: redirect_to isOptional: true type: string description: > A URL to redirect the user to after they are confirmed. Must be in your configured redirect URLs. - name: captcha_token isOptional: true type: string description: Deprecated. notes: | - The `verify_otp` method takes in different verification types. If a phone number is used, the type can either be `sms` or `phone_change`. If an email address is used, the type can be one of the following: `email`, `recovery`, `invite` or `email_change` (`signup` and `magiclink` types are deprecated). - The verification type used should be determined based on the corresponding auth method called before `verify_otp` to sign up / sign-in a user. - The `TokenHash` is contained in the [email templates](/docs/guides/auth/auth-email-templates) and can be used to sign in. You may wish to use the hash with Magic Links for the PKCE flow for Server Side Auth. See [this guide](/docs/guides/auth/server-side/email-based-auth-with-pkce-flow-for-ssr) for more details. examples: - id: verify-signup-one-time-password(otp) name: Verify Signup One-Time Password (OTP) isSpotlight: false code: | ```python response = supabase.auth.verify_otp( {"email": "email@example.com", "token": "123456", "type": "email"} ) ``` response: | ```json { "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": "2023-02-19T00:01:51.147035Z", "recovery_sent_at": "2024-07-21T22:20:00.366959Z", "email_change_sent_at": null, "new_email": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2023-02-19T00:01:51.142802Z", "confirmed_at": "2023-02-19T00:01:51.351735Z", "email_confirmed_at": "2023-02-19T00:01:51.351735Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-07-21T22:21:35.199451Z", "role": "authenticated", "updated_at": "2024-07-21T22:21:35.206985Z", "identities": [], "factors": null, "is_anonymous": false }, "session": { "provider_token": null, "provider_refresh_token": null, "access_token": "", "refresh_token": "", "expires_in": 3600, "expires_at": 1700000000, "token_type": "bearer", "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": "2023-02-19T00:01:51.147035Z", "recovery_sent_at": "2024-07-21T22:20:00.366959Z", "email_change_sent_at": null, "new_email": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2023-02-19T00:01:51.142802Z", "confirmed_at": "2023-02-19T00:01:51.351735Z", "email_confirmed_at": "2023-02-19T00:01:51.351735Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-07-21T22:21:35.199451Z", "role": "authenticated", "updated_at": "2024-07-21T22:21:35.206985Z", "identities": [], "factors": null, "is_anonymous": false } } } ``` - id: verify-sms-one-time-password name: Verify SMS One-Time Password (OTP) isSpotlight: true code: | ```python response = supabase.auth.verify_otp( {"phone": "+13334445555", "token": "123456", "type": "sms"} ) ``` - id: verify-email-auth(tokenhash) name: Verify Email Auth (Token Hash) isSpotlight: false code: | ```python response = supabase.auth.verify_otp( {"email": "email@example.com", "token_hash": "", "type": "email"} ) ``` - id: auth-mfa-api title: 'Overview' notes: | This section contains methods commonly used for Multi-Factor Authentication (MFA) and are invoked behind the `supabase.auth.mfa` namespace. Currently, we only support time-based one-time password (TOTP) as the 2nd factor. We don't support recovery codes but we allow users to enroll more than 1 TOTP factor, with an upper limit of 10. Having a 2nd TOTP factor for recovery frees the user of the burden of having to store their recovery codes somewhere. It also reduces the attack surface since multiple recovery codes are usually generated compared to just having 1 backup TOTP factor. - id: mfa-enroll title: 'mfa.enroll()' notes: | - Currently, `totp` is the only supported `factor_type`. The returned `id` should be used to create a challenge. - To create a challenge, see [`mfa.challenge()`](/docs/reference/python/auth-mfa-challenge). - To verify a challenge, see [`mfa.verify()`](/docs/reference/python/auth-mfa-verify). - To create and verify a challenge in a single step, see [`mfa.challenge_and_verify()`](/docs/reference/python/auth-mfa-challengeandverify). examples: - id: enroll-totp-factor name: Enroll a time-based, one-time password (TOTP) factor isSpotlight: true code: | ``` res = supabase.auth.mfa.enroll({ "factor_type": "totp", "friendly_name": "your_friendly_name" }) ``` - id: mfa-challenge title: 'mfa.challenge()' notes: | - An [enrolled factor](/docs/reference/python/auth-mfa-enroll) is required before creating a challenge. - To verify a challenge, see [`mfa.verify()`](/docs/reference/python/auth-mfa-verify). examples: - id: create-mfa-challenge name: Create a challenge for a factor isSpotlight: true code: | ``` res = supabase.auth.mfa.challenge({ "factor_id": '34e770dd-9ff9-416c-87fa-43b31d7ef225' }) ``` - id: mfa-verify title: 'mfa.verify()' notes: | - To verify a challenge, please [create a challenge](/docs/reference/python/auth-mfa-challenge) first. examples: - id: verify-challenge name: Verify a challenge for a factor isSpotlight: true code: | ``` res = supabase.auth.mfa.verify({ "factor_id": '34e770dd-9ff9-416c-87fa-43b31d7ef225', "challenge_id": '4034ae6f-a8ce-4fb5-8ee5-69a5863a7c15', "code": '123456' }) ``` - id: mfa-challenge-and-verify title: 'mfa.challenge_and_verify()' notes: | - An [enrolled factor](/docs/reference/python/auth-mfa-enroll) is required before invoking `challengeAndVerify()`. - Executes [`mfa.challenge()`](/docs/reference/python/auth-mfa-challenge) and [`mfa.verify()`](/docs/reference/python/auth-mfa-verify) in a single step. examples: - id: challenge-and-verify name: Create and verify a challenge for a factor isSpotlight: true code: | ``` res = supabase.auth.mfa.challenge_and_verify({ "factor_id": '34e770dd-9ff9-416c-87fa-43b31d7ef225', "code": '123456' }) ``` - id: mfa-unenroll title: 'mfa.unenroll()' examples: - id: unenroll-a-factor name: Unenroll a factor isSpotlight: true code: | ``` res = supabase.auth.mfa.unenroll({ "factor_id": '34e770dd-9ff9-416c-87fa-43b31d7ef225', }) ``` - id: mfa-get-authenticator-assurance-level title: 'mfa.get_authenticator_assurance_level()' notes: | - Authenticator Assurance Level (AAL) is the measure of the strength of an authentication mechanism. - In Supabase, having an AAL of `aal1` refers to having the 1st factor of authentication such as an email and password or OAuth sign-in while `aal2` refers to the 2nd factor of authentication such as a time-based, one-time-password (TOTP). - If the user has a verified factor, the `next_level` field will return `aal2`, else, it will return `aal1`. examples: - id: get-aal name: Get the AAL details of a session isSpotlight: true code: | ``` res = supabase.auth.mfa.get_authenticator_assurance_level() ``` - id: get-user title: 'get_user' params: - name: jwt isOptional: true type: string description: Takes in an optional access token JWT. If no JWT is provided, the JWT from the current session is used. notes: | - This method fetches the user object from the database instead of local session. - This method is useful for checking if the user is authorized because it validates the user's access token JWT on the server. examples: - id: get-the-logged-in-user-with-the-current-existing-session name: Get the logged in user with the current existing session isSpotlight: true code: | ``` response = supabase.auth.get_user() ``` response: | ```json { "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": "2023-02-19T00:01:51.147035Z", "recovery_sent_at": "2024-07-21T22:20:00.366959Z", "email_change_sent_at": null, "new_email": null, "new_phone": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2023-02-19T00:01:51.142802Z", "confirmed_at": "2023-02-19T00:01:51.351735Z", "email_confirmed_at": "2023-02-19T00:01:51.351735Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-07-24T22:24:57.642878Z", "role": "authenticated", "updated_at": "2024-07-24T22:24:57.650021Z", "identities": [], "is_anonymous": false, "factors": null } } ``` - id: get-the-logged-in-user-with-a-custom-access-token-jwt name: Get the logged in user with a custom access token jwt isSpotlight: false code: | ``` response = supabase.auth.get_user(jwt) ``` - id: get-session title: 'get_session' notes: | - This method retrieves the current local session (i.e in memory). - The session contains a signed JWT and unencoded session data. - Since the unencoded session data is retrieved from the local storage medium, **do not** rely on it as a source of trusted data on the server. It could be tampered with by the sender. If you need verified, trustworthy user data, call [`get_user`](/docs/reference/python/auth-getuser) instead. - If the session has an expired access token, this method will use the refresh token to get a new session. examples: - id: get-the-session-data name: Get the session data isSpotlight: true code: | ```python response = supabase.auth.get_session() ``` response: | ```json { "provider_token": null, "provider_refresh_token": null, "access_token": "", "refresh_token": "", "expires_in": 3600, "expires_at": 1700000000, "token_type": "bearer", "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": "2023-02-19T00:01:51.147035Z", "recovery_sent_at": "2024-07-21T22:20:00.366959Z", "email_change_sent_at": null, "new_email": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2023-02-19T00:01:51.142802Z", "confirmed_at": "2023-02-19T00:01:51.351735Z", "email_confirmed_at": "2023-02-19T00:01:51.351735Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-07-21T22:36:45.194120Z", "role": "authenticated", "updated_at": "2024-07-21T22:36:45.196044Z", "identities": [], "factors": null, "is_anonymous": false } } ``` - id: set-session title: 'set_session()' params: - name: access_token isOptional: false type: string - name: refresh_token isOptional: false type: string description: | Sets the session data from the current session. If the current session is expired, setSession will take care of refreshing it to obtain a new session. If the refresh token or access token in the current session is invalid, an error will be thrown. notes: | - This method sets the session using an `access_token` and `refresh_token`. - If successful, a `SIGNED_IN` event is emitted. examples: - id: set-session name: Refresh the session description: Sets the session data from refresh_token and returns current session or raise an error if the refresh_token is invalid. code: | ```python response = supabase.auth.set_session(access_token, refresh_token) ``` response: | ```json { "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": "2023-02-19T00:01:51.147035Z", "recovery_sent_at": "2024-07-21T22:20:00.366959Z", "email_change_sent_at": null, "new_email": null, "new_phone": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2023-02-19T00:01:51.142802Z", "confirmed_at": "2023-02-19T00:01:51.351735Z", "email_confirmed_at": "2023-02-19T00:01:51.351735Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-07-24T22:24:57.642878Z", "role": "authenticated", "updated_at": "2024-07-24T22:24:57.650021Z", "identities": [], "is_anonymous": false, "factors": null }, "session": { "provider_token": null, "provider_refresh_token": null, "access_token": "", "refresh_token": "", "expires_in": 89, "expires_at": 1721863497, "token_type": "bearer", "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": "2023-02-19T00:01:51.147035Z", "recovery_sent_at": "2024-07-21T22:20:00.366959Z", "email_change_sent_at": null, "new_email": null, "new_phone": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2023-02-19T00:01:51.142802Z", "confirmed_at": "2023-02-19T00:01:51.351735Z", "email_confirmed_at": "2023-02-19T00:01:51.351735Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-07-24T22:24:57.642878Z", "role": "authenticated", "updated_at": "2024-07-24T22:24:57.650021Z", "identities": [], "is_anonymous": false, "factors": null } } } ``` - id: refresh-session title: 'refresh_session()' params: - name: refresh_token isOptional: true type: string description: | Returns a new session, regardless of expiry status. Takes in an optional refresh token. If not passed in, then refresh_session() will attempt to retrieve it from get_session(). If the current session's refresh token is invalid, an error will be thrown. notes: | - This method will refresh the session whether the current one is expired or not. examples: - id: refresh-session name: Refresh session using the current session code: | ``` response = supabase.auth.refresh_session() ``` response: | ```json { "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": "2023-02-19T00:01:51.147035Z", "recovery_sent_at": "2024-07-21T22:20:00.366959Z", "email_change_sent_at": null, "new_email": null, "new_phone": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2023-02-19T00:01:51.142802Z", "confirmed_at": "2023-02-19T00:01:51.351735Z", "email_confirmed_at": "2023-02-19T00:01:51.351735Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-07-24T22:24:57.642878Z", "role": "authenticated", "updated_at": "2024-07-24T22:24:57.650021Z", "identities": [], "is_anonymous": false, "factors": null }, "session": { "provider_token": null, "provider_refresh_token": null, "access_token": "", "refresh_token": "", "expires_in": 89, "expires_at": 1721863497, "token_type": "bearer", "user": { "id": "11111111-1111-1111-1111-111111111111", "app_metadata": { "provider": "email", "providers": [] }, "user_metadata": {}, "aud": "authenticated", "confirmation_sent_at": "2023-02-19T00:01:51.147035Z", "recovery_sent_at": "2024-07-21T22:20:00.366959Z", "email_change_sent_at": null, "new_email": null, "new_phone": null, "invited_at": null, "action_link": null, "email": "email@example.com", "phone": "", "created_at": "2023-02-19T00:01:51.142802Z", "confirmed_at": "2023-02-19T00:01:51.351735Z", "email_confirmed_at": "2023-02-19T00:01:51.351735Z", "phone_confirmed_at": null, "last_sign_in_at": "2024-07-24T22:24:57.642878Z", "role": "authenticated", "updated_at": "2024-07-24T22:24:57.650021Z", "identities": [], "is_anonymous": false, "factors": null } } } ``` - id: select title: 'Fetch data: select()' notes: | - By default, Supabase projects return a maximum of 1,000 rows. This setting can be changed in your project's [API settings](/dashboard/project/_/settings/api). It's recommended that you keep it low to limit the payload size of accidental or malicious requests. You can use `range()` queries to paginate through your data. - `select()` can be combined with [Filters](/docs/reference/python/using-filters) - `select()` can be combined with [Modifiers](/docs/reference/python/using-modifiers) - `apikey` is a reserved keyword if you're using the [Supabase Platform](/docs/guides/platform) and [should be avoided as a column name](https://github.com/supabase/supabase/issues/5465). params: - name: columns isOptional: true type: string description: The columns to retrieve, defaults to `*`. - name: count isOptional: true type: CountMethod description: The property to use to get the count of rows returned. examples: - id: getting-your-data name: Getting your data code: | ```python response = supabase.table("countries").select("*").execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 1, "name": "Afghanistan" }, { "id": 2, "name": "Albania" }, { "id": 3, "name": "Algeria" } ], "count": null } ``` - id: selecting-specific-columns name: Selecting specific columns code: | ``` response = supabase.table("countries").select("name").execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "name": "Afghanistan" }, { "name": "Albania" }, { "name": "Algeria" } ], "count": null } ``` - id: query-referenced-tables name: Query referenced tables description: | If your database has foreign key relationships, you can query related tables too. code: | ```python response = supabase.table("countries").select("name, cities(name)").execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text ); insert into countries (id, name) values (1, 'Germany'), (2, 'Indonesia'); insert into cities (id, country_id, name) values (1, 2, 'Bali'), (2, 1, 'Munich'); ``` response: | ```json { "data": [ { "name": "Germany", "cities": [ { "name": "Munich" } ] }, { "name": "Indonesia", "cities": [ { "name": "Bali" } ] } ], "count": null } ``` - id: query-referenced-tables-through-a-join-table name: Query referenced tables through a join table code: | ```python response = supabase.table("users").select("name, teams(name)").execute() ``` data: sql: | ```sql create table users ( id int8 primary key, name text ); create table teams ( id int8 primary key, name text ); -- join table create table users_teams ( user_id int8 not null references users, team_id int8 not null references teams, -- both foreign keys must be part of a composite primary key primary key (user_id, team_id) ); insert into users (id, name) values (1, 'Kiran'), (2, 'Evan'); insert into teams (id, name) values (1, 'Green'), (2, 'Blue'); insert into users_teams (user_id, team_id) values (1, 1), (1, 2), (2, 2); ``` response: | ```json { "data": [ { "name": "Kiran", "teams": [ { "name": "Green" }, { "name": "Blue" } ] }, { "name": "Evan", "teams": [ { "name": "Blue" } ] } ], "count": null } ``` description: | If you're in a situation where your tables are **NOT** directly related, but instead are joined by a _join table_, you can still use the `select()` method to query the related data. The join table needs to have the foreign keys as part of its composite primary key. hideCodeBlock: true - id: query-the-same-referenced-table-multiple-times name: Query the same referenced table multiple times code: | ```python response = ( supabase.table("messages") .select("content,from:sender_id(name),to:receiver_id(name)") .execute() ) ``` data: sql: | ```sql create table users (id int8 primary key, name text); create table messages ( sender_id int8 not null references users, receiver_id int8 not null references users, content text ); insert into users (id, name) values (1, 'Kiran'), (2, 'Evan'); insert into messages (sender_id, receiver_id, content) values (1, 2, '👋'); ``` response: | ```json { "data": [ { "content": "👋", "from": { "name": "Kiran" }, "to": { "name": "Evan" } } ], "count": null } ``` description: | If you need to query the same referenced table twice, use the name of the joined column to identify which join to use. You can also give each column an alias. hideCodeBlock: true - id: filtering-through-referenced-tables name: Filtering through referenced tables code: | ```python response = ( supabase.table("cities") .select("name, countries(*)") .eq("countries.name", "Estonia") .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text ); insert into countries (id, name) values (1, 'Germany'), (2, 'Indonesia'); insert into cities (id, country_id, name) values (1, 2, 'Bali'), (2, 1, 'Munich'); ``` response: | ```json { "data": [ { "name": "Bali", "countries": null }, { "name": "Munich", "countries": null } ], "count": null } ``` description: | If the filter on a referenced table's column is not satisfied, the referenced table returns `[]` or `null` but the parent table is not filtered out. If you want to filter out the parent table rows, use the `!inner` hint hideCodeBlock: true - id: querying-referenced-table-with-count name: Querying referenced table with count code: | ```python response = supabase.table("countries").select("*, cities(count)").execute() ``` data: sql: | ```sql create table countries ( "id" "uuid" primary key default "extensions"."uuid_generate_v4"() not null, "name" text ); create table cities ( "id" "uuid" primary key default "extensions"."uuid_generate_v4"() not null, "name" text, "country_id" "uuid" references public.countries on delete cascade ); with country as ( insert into countries (name) values ('united kingdom') returning id ) insert into cities (name, country_id) values ('London', (select id from country)), ('Manchester', (select id from country)), ('Liverpool', (select id from country)), ('Bristol', (select id from country)); ``` response: | ```json { "data": [ { "id": "c31e7151-5a6f-453c-915b-caf31ec9a0a0", "name": "united kingdom", "cities": [ { "count": 4 } ] } ], "count": null } ``` description: | You can get the number of rows in a related table by using the **count** property. hideCodeBlock: true - id: querying-with-count-option name: Querying with count option code: | ```python response = supabase.table("countries").select("*", count="exact").execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 1, "name": "Afghanistan" }, { "id": 2, "name": "Albania" }, { "id": 3, "name": "Algeria" } ], "count": 3 } ``` description: | You can get the number of rows by using the *count* parameter in the select query. hideCodeBlock: true - id: querying-json-data name: Querying JSON data code: | ```python response = supabase.table("users").select("id, name, address->city").execute() ``` data: sql: | ```sql create table users ( id int8 primary key, name text, address jsonb ); insert into users (id, name, address) values (1, 'Avdotya', '{"city":"Saint Petersburg"}'); ``` response: | ```json { "data": [ { "id": 1, "name": "Avdotya", "city": "Saint Petersburg" } ], "count": null } ``` description: | You can select and filter data inside of [JSON](/docs/guides/database/json) columns. Postgres offers some [operators](/docs/guides/database/json#query-the-jsonb-data) for querying JSON data. hideCodeBlock: true - id: querying-referenced-table-with-inner-join name: Querying referenced table with inner join code: | ```python response = ( supabase.table("cities") .select("name, countries!inner(name)") .eq("countries.name", "Indonesia") .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text ); insert into countries (id, name) values (1, 'Germany'), (2, 'Indonesia'); insert into cities (id, country_id, name) values (1, 2, 'Bali'), (2, 1, 'Munich'); ``` response: | ```json { "data": [ { "name": "Bali", "countries": { "name": "Indonesia" } } ], "count": null } ``` description: | If you don't want to return the referenced table contents, you can leave the parenthesis empty. Like `.select('name, countries!inner()')`. hideCodeBlock: true - id: switching-schemas-per-query name: Switching schemas per query code: | ```python response = supabase.schema("myschema").table("mytable").select("*").execute() ``` data: sql: | ```sql create schema myschema; create table myschema.mytable ( id uuid primary key default gen_random_uuid(), data text ); insert into myschema.mytable (data) values ('mydata'); ``` response: | ```json { "data": [ { "id": "b1a8c0b5-bdf0-46f1-95a5-f0fb9cb2f410", "data": "mydata" } ], "count": null } ``` description: | In addition to setting the schema during initialization, you can also switch schemas on a per-query basis. Make sure you've set up your [database privileges and API settings](/docs/guides/api/using-custom-schemas). hideCodeBlock: true - id: insert title: 'Create data: insert()' params: - name: json isOptional: false type: dict, list description: The values to insert. Pass an dict to insert a single row or an list to insert multiple rows. - name: count isOptional: true type: CountMethod description: The property to use to get the count of rows returned. - name: returning isOptional: true type: ReturnMethod description: Either 'minimal' or 'representation'. Defaults to 'representation'. - name: default_to_null isOptional: true type: bool description: Make missing fields default to `null`. Otherwise, use the default value for the column. Only applies for bulk inserts. examples: - id: create-a-record name: Create a record code: | ```python response = ( supabase.table("countries") .insert({"id": 1, "name": "Denmark"}) .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); ``` response: | ```json { "data": [ { "id": 1, "name": "Denmark" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: bulk-create name: Bulk create code: | ```python try: response = supabase.table("countries") .insert([ { "id": 1, "name": "Nepal" }, { "id": 1, "name": "Vietnam" }, ]) .execute() return response except Exception as exception: return exception ``` data: sql: | ```sql create table countries (id int8 primary key, name text); ``` response: | ```json { "_raw_error": { "code": "23505", "details": "Key (id)=(1) already exists.", "hint": null, "message": "duplicate key value violates unique constraint \"countries_pkey\"" }, "message": "duplicate key value violates unique constraint \"countries_pkey\"", "code": "23505", "hint": null, "details": "Key (id)=(1) already exists." } ``` description: | A bulk create operation is handled in a single transaction. If any of the inserts fail, none of the rows are inserted. hideCodeBlock: true - id: update title: 'Modify data: update()' notes: | - `update()` should always be combined with [Filters](/docs/reference/python/using-filters) to target the item(s) you wish to update. params: - name: json isOptional: false type: dict, list description: The values to insert. Pass an dict to insert a single row or an list to insert multiple rows. - name: count isOptional: true type: CountMethod description: The property to use to get the count of rows returned. examples: - id: updating-your-data name: Updating your data code: | ```python response = ( supabase.table("countries") .update({"name": "Australia"}) .eq("id", 1) .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Denmark'); ``` response: | ```json { "data": [ { "id": 1, "name": "Australia" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: updating-json-data name: Updating JSON data code: | ```python response = ( supabase.table("users") .update({"address": {"street": "Melrose Place", "postcode": 90210}}) .eq("address->postcode", 90210) .execute() ) ``` data: sql: | ```sql create table users ( id int8 primary key, name text, address jsonb ); insert into users (id, name, address) values (1, 'Michael', '{ "postcode": 90210 }'); ``` response: | ```json { "data": [ { "id": 1, "name": "Michael", "address": { "street": "Melrose Place", "postcode": 90210 } } ], "count": null } ``` description: | Postgres offers some [operators](/docs/guides/database/json#query-the-jsonb-data) for working with JSON data. Currently, it is only possible to update the entire JSON document. hideCodeBlock: true - id: upsert title: 'Upsert data: upsert()' notes: | - Primary keys must be included in the `values` dict to use upsert. params: - name: json isOptional: false type: dict, list description: The values to insert. Pass an dict to insert a single row or an list to insert multiple rows. - name: count isOptional: true type: CountMethod description: The property to use to get the count of rows returned. - name: returning isOptional: true type: ReturnMethod description: Either 'minimal' or 'representation'. Defaults to 'representation'. - name: ignore_duplicates isOptional: true type: bool description: Whether duplicate rows should be ignored. - name: on_conflict isOptional: true type: string description: Specified columns to be made to work with UNIQUE constraint. - name: default_to_null isOptional: true type: bool description: Make missing fields default to `null`. Otherwise, use the default value for the column. Only applies for bulk inserts. examples: - id: upsert-your-data name: Upsert your data code: | ```python response = ( supabase.table("countries") .upsert({"id": 1, "name": "Australia"}) .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'); ``` response: | ```json { "data": [ { "id": 1, "name": "Australia" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: bulk-upsert-your-data name: Bulk Upsert your data code: | ```python response = ( supabase.table("countries") .upsert([{"id": 1, "name": "Albania"}, {"id": 2, "name": "Algeria"}]) .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'); ``` response: | ```json { "data": [ { "id": 1, "name": "Albania" }, { "id": 2, "name": "Algeria" } ], "count": null } ``` hideCodeBlock: true - id: upserting-into-tables-with-constraints name: Upserting into tables with constraints code: | ```python response = ( supabase.table("users") .upsert( {"id": 42, "handle": "saoirse", "display_name": "Saoirse"}, on_conflict="handle", ) .execute() ) ``` data: sql: | ```sql create table users ( id int8 generated by default as identity primary key, handle text not null unique, display_name text ); insert into users (id, handle, display_name) values (1, 'saoirse', null); ``` response: | ```json { "_raw_error": { "code": "23505", "details": "Key (handle)=(saoirse) already exists.", "hint": null, "message": "duplicate key value violates unique constraint \"users_handle_key\"" }, "message": "duplicate key value violates unique constraint \"users_handle_key\"", "code": "23505", "hint": null, "details": "Key (handle)=(saoirse) already exists." } ``` description: | In the following query, `upsert()` implicitly uses the `id`(primary key) column to determine conflicts. If there is no existing row with the same `id`, `upsert()` inserts a new row, which will fail in this case as there is already a row with `handle` `"saoirse"`. Using the `on_conflict` option, you can instruct `upsert()` to use another column with a unique constraint to determine conflicts. hideCodeBlock: true - id: delete title: 'Delete data: delete()' notes: | - `delete()` should always be combined with [filters](/docs/reference/python/using-filters) to target the item(s) you wish to delete. - If you use `delete()` with filters and you have [RLS](/docs/learn/auth-deep-dive/auth-row-level-security) enabled, only rows visible through `SELECT` policies are deleted. Note that by default no rows are visible, so you need at least one `SELECT`/`ALL` policy that makes the rows visible. - When using `delete().in_()`, specify an array of values to target multiple rows with a single query. This is particularly useful for batch deleting entries that share common criteria, such as deleting users by their IDs. Ensure that the array you provide accurately represents all records you intend to delete to avoid unintended data removal. params: - name: count isOptional: true type: CountMethod description: The property to use to get the count of rows returned. - name: returning isOptional: true type: ReturnMethod description: Either 'minimal' or 'representation'. Defaults to 'representation'. examples: - id: delete-records name: Delete records code: | ```python response = supabase.table('countries').delete().eq('id', 1).execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Spain'); ``` response: | ``` { "data": [ { "id": 1, "name": "Spain" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: delete-multiple-records name: Delete multiple records code: | ```python response = supabase.table("countries").delete().in_("id", [1, 2, 3]).execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Spain'), (2, 'France'), (3, 'Germany'); ``` response: | ```json { "data": [ { "id": 1, "name": "Spain" }, { "id": 2, "name": "France" }, { "id": 3, "name": "Germany" } ], "count": null } ``` hideCodeBlock: false isSpotlight: false - id: rpc title: 'Postgres functions: rpc()' description: | You can call Postgres functions as _Remote Procedure Calls_, logic in your database that you can execute from anywhere. Functions are useful when the logic rarely changes—like for password resets and updates. ```sql create or replace function hello_world() returns text as $$ select 'Hello world'; $$ language sql; ``` params: - name: fn isOptional: false type: callable description: The stored procedure call to be executed. - name: params isOptional: true type: dict of any description: Parameters passed into the stored procedure call. - name: get isOptional: true type: dict of any description: When set to `true`, `data` will not be returned. Useful if you only need the count. - name: head isOptional: true type: dict of any description: When set to `true`, the function will be called with read-only access mode. - name: count isOptional: true type: CountMethod description: | Count algorithm to use to count rows returned by the function. Only applicable for [set-returning functions](https://www.postgresql.org/docs/current/functions-srf.html). `"exact"`: Exact but slow count algorithm. Performs a `COUNT(*)` under the hood. `"planned"`: Approximated but fast count algorithm. Uses the Postgres statistics under the hood. `"estimated"`: Uses exact count for low numbers and planned count for high numbers. examples: - id: call-a-postgres-function-without-arguments name: Call a Postgres function without arguments code: | ```python response = supabase.rpc("hello_world").execute() ``` data: sql: | ```sql create function hello_world() returns text as $$ select 'Hello world'; $$ language sql; ``` response: | ```json { "data": "Hello world", "count": null } ``` hideCodeBlock: true isSpotlight: true - id: call-a-postgres-function-with-arguments name: Call a Postgres function with arguments code: | ```python response = supabase.rpc("echo", { "say": "👋" }).execute() ``` data: sql: | ```sql create function echo(say text) returns text as $$ select say; $$ language sql; ``` response: | ```json { "data": "👋", "count": null } ``` hideCodeBlock: true - id: bulk-processing name: Bulk processing code: | ```python response = supabase.rpc("add_one_each", {"arr": [1, 2, 3]}).execute() ``` data: sql: | ```sql create function add_one_each(arr int[]) returns int[] as $$ select array_agg(n + 1) from unnest(arr) as n; $$ language sql; ``` response: | ```json { "data": [ 2, 3, 4 ], "count": null } ``` description: | You can process large payloads by passing in an array as an argument. hideCodeBlock: true - id: call-a-postgres-function-with-filters name: Call a Postgres function with filters code: | ```python response = supabase.rpc("list_stored_countries").eq("id", 1).single().execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'France'), (2, 'United Kingdom'); create function list_stored_countries() returns setof countries as $$ select * from countries; $$ language sql; ``` response: | ```json { "data": { "id": 1, "name": "France" }, "count": null } ``` description: | Postgres functions that return tables can also be combined with [Filters](/docs/reference/javascript/using-filters) and [Modifiers](/docs/reference/javascript/using-modifiers). hideCodeBlock: true - id: call-a-read-only-postgres-function name: Call a read-only Postgres function code: | ```python response = supabase.rpc('hello_world', get=True).execute() ``` data: sql: | ```sql create function hello_world() returns text as $$ select 'Hello world'; $$ language sql; ``` response: | ```json { "data": "Hello world", "count": null } ``` hideCodeBlock: true - id: using-filters title: Using Filters description: | Filters allow you to only return rows that match certain conditions. Filters can be used on `select()`, `update()`, `upsert()`, and `delete()` queries. If a Postgres function returns a table response, you can also apply filters. examples: - id: applying-filters name: Applying Filters description: | Filters must be applied after any of `select()`, `update()`, `upsert()`, `delete()`, and `rpc()` and before [modifiers](/docs/reference/python/using-modifiers). code: | ```python # Correct response = ( supabase.table("cities") .select("name, country_id") .eq("name", "Bali") .execute() ) # Incorrect response = ( supabase.table("cities") .eq("name", "Bali") .select("name, country_id") .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text ); insert into countries (id, name) values (1, 'Germany'), (2, 'Indonesia'); insert into cities (id, country_id, name) values (1, 2, 'Bali'), (2, 1, 'Munich'); ``` - id: chaining-filters name: Chaining description: | Filters can be chained together to produce advanced queries. For example, to query cities with population between 1,000 and 10,000. code: | ```python response = ( supabase.table("cities") .select("name, country_id") .gte("population", 1000) .lt("population", 10000) .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text, population int8 ); insert into countries (id, name) values (1, 'Germany'), (2, 'Indonesia'); insert into cities (id, country_id, name, population) values (1, 2, 'Bali', 9980), (2, 1, 'Munich', 250000); ``` response: | ```json { "data": [ { "name": "Bali", "country_id": 2 } ], "count": null } ``` - id: conditional-chaining name: Conditional chaining description: | Filters can be built up one step at a time and then executed. code: | ```python filterByName = None filterPopLow = 1000 filterPopHigh = 10000 query = supabase.table("cities").select("name, country_id") if filterByName: query = query.eq("name", filterByName) if filterPopLow: query = query.gte("population", filterPopLow) if filterPopHigh: query = query.lt("population", filterPopHigh) response = query.execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text, population int8 ); insert into countries (id, name) values (1, 'Germany'), (2, 'Indonesia'); insert into cities (id, country_id, name, population) values (1, 2, 'Bali', 9980), (2, 1, 'Munich', 250000); ``` response: | ```json { "data": [ { "name": "Bali", "country_id": 2 } ], "count": null } ``` - id: filter-by-value-within-json-column name: Filter by values within JSON column code: | ```python response = ( supabase.table("users") .select("*") .eq("address->postcode", 90210) .execute() ) ``` data: sql: | ```sql create table users ( id int8 primary key, name text, address jsonb ); insert into users (id, name, address) values (1, 'Michael', '{ "postcode": 90210 }'), (2, 'Jane', null); ``` response: | ```json { "data": [ { "id": 1, "name": "Michael", "address": { "postcode": 90210 } } ], "count": null } ``` - id: filter-foreign-tables name: Filter Foreign Tables code: | ```python response = ( supabase.table("countries") .select("name, cities!inner(name)") .eq("cities.name", "Bali") .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text ); insert into countries (id, name) values (1, 'Germany'), (2, 'Indonesia'); insert into cities (id, country_id, name) values (1, 2, 'Bali'), (2, 1, 'Munich'); ``` response: | ```json { "data": [ { "name": "Indonesia", "cities": [ { "name": "Bali" } ] } ], "count": null } ``` description: | You can filter on foreign tables in your `select()` query using dot notation. - id: eq title: eq() description: | Match only rows where `column` is equal to `value`. params: - name: column isOptional: false type: string description: The column to filter on - name: value isOptional: false type: any description: The value to filter by examples: - id: with-select name: With `select()` code: | ```python response = supabase.table("countries").select("*").eq("name", "Albania").execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 2, "name": "Albania" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: neq title: neq() description: | Match only rows where `column` is not equal to `value`. params: - name: column isOptional: false type: string description: The column to filter on - name: value isOptional: false type: any description: The value to filter by examples: - id: with-select name: With `select()` code: | ```python response = supabase.table("countries").select("*").neq("name", "Albania").execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 1, "name": "Afghanistan" }, { "id": 3, "name": "Algeria" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: gt title: gt() description: | Match only rows where `column` is greather than `value`. params: - name: column isOptional: false type: string description: The column to filter on - name: value isOptional: false type: any description: The value to filter by examples: - id: with-select name: With `select()` code: | ```python response = supabase.table("countries").select("*").gt("id", 2).execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 3, "name": "Algeria" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true description: | When using [reserved words](https://www.postgresql.org/docs/current/sql-keywords-appendix.html) for column names you need to add double quotes e.g. `.gt('"order"', 2)` - id: gte title: gte() description: | Match only rows where `column` is greater than or equal to `value`. params: - name: column isOptional: false type: string description: The column to filter on - name: value isOptional: false type: any description: The value to filter by examples: - id: with-select name: With `select()` code: | ```python response = supabase.table("countries").select("*").gte("id", 2).execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 2, "name": "Albania" }, { "id": 3, "name": "Algeria" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: lt title: lt() description: | Match only rows where `column` is less than `value`. params: - name: column isOptional: false type: string description: The column to filter on - name: value isOptional: false type: any description: The value to filter by examples: - id: with-select name: With `select()` code: | ```python response = supabase.table("countries").select("*").lt("id", 2).execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 1, "name": "Afghanistan" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: lte title: lte() description: | Match only rows where `column` is less than or equal to `value`. params: - name: column isOptional: false type: string description: The column to filter on - name: value isOptional: false type: any description: The value to filter by examples: - id: with-select name: With `select()` code: | ```python response = supabase.table("countries").select("*").lte("id", 2).execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 1, "name": "Afghanistan" }, { "id": 2, "name": "Albania" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: like title: like() description: | Match only rows where `column` matches `pattern` case-sensitively. params: - name: column isOptional: false type: string description: The name of the column to apply a filter on - name: pattern isOptional: false type: string description: The pattern to match by examples: - id: with-select name: With `select()` code: | ```python response = supabase.table("countries").select("*").like("name", "%Alba%").execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 2, "name": "Albania" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: ilike title: ilike() description: | Match only rows where `column` matches `pattern` case-insensitively. params: - name: column isOptional: false type: string description: The name of the column to apply a filter on - name: pattern isOptional: false type: string description: The pattern to match by examples: - id: with-select name: With `select()` code: | ```python response = supabase.table("countries").select("*").ilike("name", "%alba%").execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 2, "name": "Albania" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: is title: is_() description: | Match only rows where `column` IS `value`. params: - name: column isOptional: false type: string description: The name of the column to apply a filter on - name: value isOptional: false type: null | boolean description: The value to match by examples: - id: checking-nullness name: Checking for nullness, True or False code: | ```python response = supabase.table("countries").select("*").is_("name", "null").execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Germany'), (2, null); ``` response: | ```json { "data": [ { "id": 2, "name": null } ], "count": null } ``` description: | Using the `eq()` filter doesn't work when filtering for `null`. Instead, you need to use `is_()`. To query for null values in python use the string 'null' instead of the python `None` value. Note that `is` is a reserved word in Python, so the underscore is added to avoid a syntax error. hideCodeBlock: true isSpotlight: true - id: in title: in_() description: | Match only rows where `column` is included in the `values` array. params: - name: column isOptional: false type: string description: The column to filter on - name: values isOptional: false type: array description: The values to filter by examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("countries") .select("*") .in_("name", ["Albania", "Algeria"]) .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 2, "name": "Albania" }, { "id": 3, "name": "Algeria" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: contains title: contains() description: | Only relevant for jsonb, array, and range columns. Match only rows where `column` contains every element appearing in `value`. params: - name: column isOptional: false type: string description: The column to filter on - name: values isOptional: false type: object description: The jsonb, array, or range value to filter with examples: - id: on-array-columns name: On array columns code: | ```python response = ( supabase.table("issues") .select("*") .contains("tags", ["is:open", "priority:low"]) .execute() ) ``` data: sql: | ```sql create table users ( id int8 primary key, name text, tags text[] ); insert into issues (id, title, tags) values (1, 'Cache invalidation is not working', array['is:open', 'severity:high', 'priority:low']), (2, 'Use better names', array['is:open', 'severity:low', 'priority:medium']); ``` response: | ```json { "data": [ { "id": 1, "title": "Cache invalidation is not working", "tags": [ "is:open", "severity:high", "priority:low" ] } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: on-range-columns name: On range columns code: | ```python response = ( supabase.table("reservations") .select("*") .contains("during", "[2000-01-01 13:00, 2000-01-01 13:30)") .execute() ) ``` data: sql: | ```sql create table reservations ( id int8 primary key, room_name text, during tsrange ); insert into reservations (id, room_name, during) values (1, 'Emerald', '[2000-01-01 13:00, 2000-01-01 15:00)'), (2, 'Topaz', '[2000-01-02 09:00, 2000-01-02 10:00)'); ``` response: | ```json { "data": [ { "id": 1, "room_name": "Emerald", "during": "[\"2000-01-01 13:00:00\",\"2000-01-01 15:00:00\")" } ], "count": null } ``` description: | Postgres supports a number of [range types](https://www.postgresql.org/docs/current/rangetypes.html). You can filter on range columns using the string representation of range values. hideCodeBlock: true - id: on-jsonb-columns name: On `jsonb` columns code: | ```python response = ( supabase.table("users") .select("*") .contains("address", {"postcode": 90210}) .execute() ) ``` data: sql: | ```sql create table users ( id int8 primary key, name text, address jsonb ); insert into users (id, name, address) values (1, 'Michael', '{ "postcode": 90210, "street": "Melrose Place" }'), (2, 'Jane', '{}'); ``` response: | ```json { "data": [ { "id": 1, "name": "Michael", "address": { "street": "Melrose Place", "postcode": 90210 } } ], "count": null } ``` hideCodeBlock: true - id: contained-by title: contained_by() description: | Only relevant for jsonb, array, and range columns. Match only rows where every element appearing in `column` is contained by `value`. params: - name: column isOptional: false type: string description: The jsonb, array, or range column to filter on - name: value isOptional: false type: object description: The jsonb, array, or range value to filter with examples: - id: on-array-columns name: On array columns code: | ```python response = ( supabase.table("classes") .select("name") .contained_by("days", ["monday", "tuesday", "wednesday", "friday"]) .execute() ) ``` data: sql: | ```sql create table classes ( id int8 primary key, name text, days text[] ); insert into classes (id, name, days) values (1, 'Chemistry', array['monday', 'friday']), (2, 'History', array['monday', 'wednesday', 'thursday']); ``` response: | ```json { "data": [ { "name": "Chemistry" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: on-range-columns name: On range columns code: | ```python response = ( supabase.table("reservations") .select("*") .contained_by("during", "[2000-01-01 00:00, 2000-01-01 23:59)") .execute() ) ``` data: sql: | ```sql create table reservations ( id int8 primary key, room_name text, during tsrange ); insert into reservations (id, room_name, during) values (1, 'Emerald', '[2000-01-01 13:00, 2000-01-01 15:00)'), (2, 'Topaz', '[2000-01-02 09:00, 2000-01-02 10:00)'); ``` response: | ```json { "data": [ { "id": 1, "room_name": "Emerald", "during": "[\"2000-01-01 13:00:00\",\"2000-01-01 15:00:00\")" } ], "count": null } ``` description: | Postgres supports a number of [range types](https://www.postgresql.org/docs/current/rangetypes.html). You can filter on range columns using the string representation of range values. hideCodeBlock: true - id: on-jsonb-columns name: On `jsonb` columns code: | ```python response = ( supabase.table("users") .select("name") .contained_by("address", {}) .execute() ) ``` data: sql: | ```sql create table users ( id int8 primary key, name text, address jsonb ); insert into users (id, name, address) values (1, 'Michael', '{ "postcode": 90210, "street": "Melrose Place" }'), (2, 'Jane', '{}'); ``` response: | ```json { "data": [ { "name": "Jane" } ], "count": null } ``` hideCodeBlock: true - id: range-gt title: range_gt() description: | Only relevant for range columns. Match only rows where every element in `column` is greater than any element in `range`. params: - name: column isOptional: false type: string description: The range column to filter on - name: range isOptional: false type: array description: The range to filter with examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("reservations") .select("*") .range_gt("during", ["2000-01-02 08:00", "2000-01-02 09:00"]) .execute() ) ``` data: sql: | ```sql create table reservations ( id int8 primary key, room_name text, during tsrange ); insert into reservations (id, room_name, during) values (1, 'Emerald', '[2000-01-01 13:00, 2000-01-01 15:00)'), (2, 'Topaz', '[2000-01-02 09:00, 2000-01-02 10:00)'); ``` response: | ```json { "data": [ { "id": 2, "room_name": "Topaz", "during": "[\"2000-01-02 09:00:00\",\"2000-01-02 10:00:00\")" } ], "count": null } ``` description: | Postgres supports a number of [range types](https://www.postgresql.org/docs/current/rangetypes.html). You can filter on range columns using the string representation of range values. hideCodeBlock: true isSpotlight: true - id: range-gte title: range_gte() description: | Only relevant for range columns. Match only rows where every element in `column` is either contained in `range` or greater than any element in `range`. params: - name: column isOptional: false type: string description: The range column to filter on - name: range isOptional: false type: string description: The range to filter with examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("reservations") .select("*") .range_gte("during", ["2000-01-02 08:30", "2000-01-02 09:30"]) .execute() ) ``` data: sql: | ```sql create table reservations ( id int8 primary key, room_name text, during tsrange ); insert into reservations (id, room_name, during) values (1, 'Emerald', '[2000-01-01 13:00, 2000-01-01 15:00)'), (2, 'Topaz', '[2000-01-02 09:00, 2000-01-02 10:00)'); ``` response: | ```json { "data": [ { "id": 2, "room_name": "Topaz", "during": "[\"2000-01-02 09:00:00\",\"2000-01-02 10:00:00\")" } ], "count": null } ``` description: | Postgres supports a number of [range types](https://www.postgresql.org/docs/current/rangetypes.html). You can filter on range columns using the string representation of range values. hideCodeBlock: true isSpotlight: true - id: range-lt title: range_lt() description: | Only relevant for range columns. Match only rows where every element in `column` is less than any element in `range`. params: - name: column isOptional: false type: string description: The range column to filter on - name: range isOptional: false type: array description: The range to filter with examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("reservations") .select("*") .range_lt("during", ["2000-01-01 15:00", "2000-01-01 16:00"]) .execute() ) ``` data: sql: | ```sql create table reservations ( id int8 primary key, room_name text, during tsrange ); insert into reservations (id, room_name, during) values (1, 'Emerald', '[2000-01-01 13:00, 2000-01-01 15:00)'), (2, 'Topaz', '[2000-01-02 09:00, 2000-01-02 10:00)'); ``` response: | ```json { "data": [ { "id": 1, "room_name": "Emerald", "during": "[\"2000-01-01 13:00:00\",\"2000-01-01 15:00:00\")" } ], "count": null } ``` description: | Postgres supports a number of [range types](https://www.postgresql.org/docs/current/rangetypes.html). You can filter on range columns using the string representation of range values. hideCodeBlock: true isSpotlight: true - id: range-lte title: range_lte() description: | Only relevant for range columns. Match only rows where every element in `column` is less than any element in `range`. params: - name: column isOptional: false type: string description: The range column to filter on - name: range isOptional: false type: array description: The range to filter with examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("reservations") .select("*") .range_lte("during", ["2000-01-01 14:00", "2000-01-01 16:00"]) .execute() ) ``` data: sql: | ```sql create table reservations ( id int8 primary key, room_name text, during tsrange ); insert into reservations (id, room_name, during) values (1, 'Emerald', '[2000-01-01 13:00, 2000-01-01 15:00)'), (2, 'Topaz', '[2000-01-02 09:00, 2000-01-02 10:00)'); ``` response: | ```json { "data": [ { "id": 1, "room_name": "Emerald", "during": "[\"2000-01-01 13:00:00\",\"2000-01-01 15:00:00\")" } ], "count": null } ``` description: | Postgres supports a number of [range types](https://www.postgresql.org/docs/current/rangetypes.html). You can filter on range columns using the string representation of range values. hideCodeBlock: true isSpotlight: true - id: range-adjacent title: range_adjacent() description: | Only relevant for range columns. Match only rows where `column` is mutually exclusive to `range` and there can be no element between the two ranges. params: - name: column isOptional: false type: string description: The range column to filter on - name: range isOptional: false type: array description: The range to filter with examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("reservations") .select("*") .range_adjacent("during", ["2000-01-01 12:00", "2000-01-01 13:00"]) .execute() ) ``` data: sql: | ```sql create table reservations ( id int8 primary key, room_name text, during tsrange ); insert into reservations (id, room_name, during) values (1, 'Emerald', '[2000-01-01 13:00, 2000-01-01 15:00)'), (2, 'Topaz', '[2000-01-02 09:00, 2000-01-02 10:00)'); ``` response: | ```json { "data": [ { "id": 1, "room_name": "Emerald", "during": "[\"2000-01-01 13:00:00\",\"2000-01-01 15:00:00\")" } ], "count": null } ``` description: | Postgres supports a number of [range types](https://www.postgresql.org/docs/current/rangetypes.html). You can filter on range columns using the string representation of range values. hideCodeBlock: true isSpotlight: true - id: overlaps title: overlaps() description: | Only relevant for array and range columns. Match only rows where `column` and `value` have an element in common. params: - name: column isOptional: false type: string description: The array or range column to filter on - name: value isOptional: false type: Iterable[Any] description: The array or range value to filter with examples: - id: on-array-columns name: On array columns code: | ```python response = ( supabase.table("issues") .select("title") .overlaps("tags", ["is:closed", "severity:high"]) .execute() ) ``` data: sql: | ```sql create table issues ( id int8 primary key, title text, tags text[] ); insert into issues (id, title, tags) values (1, 'Cache invalidation is not working', array['is:open', 'severity:high', 'priority:low']), (2, 'Use better names', array['is:open', 'severity:low', 'priority:medium']); ``` response: | ```json { "data": [ { "title": "Cache invalidation is not working" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: on-range-columns name: On range columns code: | ```python response = ( supabase.table("reservations") .select("*") .overlaps("during", "[2000-01-01 12:45, 2000-01-01 13:15)") .execute() ) ``` data: sql: | ```sql create table reservations ( id int8 primary key, room_name text, during tsrange ); insert into reservations (id, room_name, during) values (1, 'Emerald', '[2000-01-01 13:00, 2000-01-01 15:00)'), (2, 'Topaz', '[2000-01-02 09:00, 2000-01-02 10:00)'); ``` response: | ```json { "data": [ { "id": 1, "room_name": "Emerald", "during": "[\"2000-01-01 13:00:00\",\"2000-01-01 15:00:00\")" } ], "status": 200, "statusText": "OK" } ``` description: | Postgres supports a number of [range types](https://www.postgresql.org/docs/current/rangetypes.html). You can filter on range columns using the string representation of range values. hideCodeBlock: true - id: text-search title: text_search() description: | Only relevant for text and tsvector columns. Match only rows where `column` matches the query string in `query`. params: - name: column isOptional: false type: string description: The text or tsvector column to filter on - name: query isOptional: false type: string description: The query text to match with - name: options isOptional: true type: object description: Named parameters subContent: - name: type isOptional: true type: '"plain" | "phrase" | "websearch"' description: Change how the `query` text is interpreted - name: config isOptional: true type: string description: The text search configuration to use notes: | - For more information, see [Postgres full text search](/docs/guides/database/full-text-search). examples: - id: text-search name: Text search code: | ```python response = ( supabase.table("texts") .select("content") .text_search("content", "'eggs' & 'ham'", options={"config": "english"}) .execute() ) ``` data: sql: | ```sql create table texts ( id bigint primary key generated always as identity, content text ); insert into texts (content) values ('Four score and seven years ago'), ('The road goes ever on and on'), ('Green eggs and ham') ; ``` response: | ```json { "data": [ { "content": "Green eggs and ham" } ], "count": null } ``` - id: basic-normalization name: Basic normalization description: Uses PostgreSQL's `plainto_tsquery` function. code: | ```python response = ( supabase.table("quotes") .select("catchphrase") .text_search( "catchphrase", "'fat' & 'cat'", options={"type": "plain", "config": "english"}, ) .execute() ) ``` - id: full-normalization name: Full normalization description: Uses PostgreSQL's `phraseto_tsquery` function. code: | ```python response = ( supabase.table("quotes") .select("catchphrase") .text_search( "catchphrase", "'fat' & 'cat'", options={"type": "phrase", "config": "english"}, ) .execute() ) ``` - id: web-search name: Websearch description: | Uses PostgreSQL's `websearch_to_tsquery` function. This function will never raise syntax errors, which makes it possible to use raw user-supplied input for search, and can be used with advanced operators. - `unquoted text`: text not inside quote marks will be converted to terms separated by & operators, as if processed by plainto_tsquery. - `"quoted text"`: text inside quote marks will be converted to terms separated by `<->` operators, as if processed by phraseto_tsquery. - `OR`: the word “or” will be converted to the | operator. - `-`: a dash will be converted to the ! operator. code: | ```python response = ( supabase.table("quotes") .select("catchphrase") .text_search( "catchphrase", "'fat or cat'", options={"type": "websearch", "config": "english"}, ) .execute() ) ``` - id: match title: match() description: | Match only rows where each column in `query` keys is equal to its associated value. Shorthand for multiple `.eq()`s. params: - name: query isOptional: false type: dict description: The object to filter with, with column names as keys mapped to their filter values examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("countries") .select("*") .match({"id": 2, "name": "Albania"}) .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 2, "name": "Albania" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: not title: not_() description: | Match only rows which doesn't satisfy the filter. `not_` expects you to use the raw PostgREST syntax for the filter values. notes: | ```python .not_.in_('id', '(5,6,7)') # Use `()` for `in` filter .not_.contains('arraycol', '{"a","b"}') # Use `{}` for array values ``` examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("countries") .select("*") .not_.is_("name", "null") .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Albania'), (2, null); ``` response: | ```json { "data": [ { "id": 1, "name": "Albania" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: or title: or_() params: - name: filters isOptional: false type: string description: The filters to use, following PostgREST syntax - name: reference_table isOptional: true type: string description: Set this to filter on referenced tables instead of the parent table notes: | or_() expects you to use the raw PostgREST syntax for the filter names and values. ```python .or_('id.in.(5,6,7), arraycol.cs.{"a","b"}') # Use `()` for `in` filter, `{}` for array values and `cs` for `contains()`. .or_('id.in.(5,6,7), arraycol.cd.{"a","b"}') # Use `cd` for `containedBy()` ``` examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("countries") .select("name") .or_("id.eq.2,name.eq.Algeria") .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "name": "Albania" }, { "name": "Algeria" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: use-or-with-and name: Use `or` with `and` code: | ```python response = ( supabase.table("countries") .select("name") .or_("id.gt.3,and(id.eq.1,name.eq.Afghanistan)") .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` reponse: | ```json { "data": [ { "name": "Afghanistan" } ], "count": null } ``` hideCodeBlock: true - id: use-or-on-referenced-tables name: Use `or` on referenced tables code: | ```python response = ( supabase.table("countries") .select("name, cities!inner(name)") .or_("country_id.eq.1,name.eq.Beijing", reference_table="cities") .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text ); insert into countries (id, name) values (1, 'Germany'), (2, 'Indonesia'); insert into cities (id, country_id, name) values (1, 2, 'Bali'), (2, 1, 'Munich'); ``` response: | ```json { "data": [ { "name": "Germany", "cities": [ { "name": "Munich" } ] } ], "count": null } ``` hideCodeBlock: true - id: filter title: filter() params: - name: column isOptional: false type: string description: The column to filter on - name: operator isOptional: true type: string description: The operator to filter with, following PostgREST syntax - name: value isOptional: true type: any description: The value to filter with, following PostgREST syntax notes: | filter() expects you to use the raw PostgREST syntax for the filter values. ```python .filter('id', 'in', '(5,6,7)') # Use `()` for `in` filter .filter('arraycol', 'cs', '{"a","b"}') # Use `cs` for `contains()`, `{}` for array values ``` examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("countries") .select("*") .filter("name", "in", '("Algeria","Japan")') .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 3, "name": "Algeria" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: on-a-foreign-table name: On a foreign table code: | ```python response = ( supabase.table("countries") .select("name, cities!inner(name)") .filter("cities.name", "eq", "Bali") .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text ); insert into countries (id, name) values (1, 'Germany'), (2, 'Indonesia'); insert into cities (id, country_id, name) values (1, 2, 'Bali'), (2, 1, 'Munich'); ``` response: | ```json { "data": [ { "name": "Indonesia", "cities": [ { "name": "Bali" } ] } ], "count": null } ``` hideCodeBlock: true - id: using-modifiers title: Using Modifiers description: | Filters work on the row level—they allow you to return rows that only match certain conditions without changing the shape of the rows. Modifiers are everything that don't fit that definition—allowing you to change the format of the response (e.g., returning a CSV string). Modifiers must be specified after filters. Some modifiers only apply for queries that return rows (e.g., `select()` or `rpc()` on a function that returns a table response). - id: order title: order() description: Order the query result by `column`. params: - name: column isOptional: false type: string description: The column to order by - name: desc isOptional: true type: bool description: Whether the rows should be ordered in descending order or not. - name: foreign_table isOptional: true type: string description: Foreign table name whose results are to be ordered. - name: nullsfirst isOptional: true type: bool description: Order by showing nulls first examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("countries") .select("*") .order("name", desc=True) .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "id": 1, "name": "Afghanistan" }, { "id": 2, "name": "Albania" }, { "id": 3, "name": "Algeria" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: on-a-foreign-table name: On a foreign table code: | ```python response = ( supabase.table("countries") .select("name, cities(name)") .order("name", desc=True, foreign_table="cities") .execute() ) ``` data: sql: | create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text ); insert into countries (id, name) values (1, 'United States'), (2, 'Vanuatu'); insert into cities (id, country_id, name) values (1, 1, 'Atlanta'), (2, 1, 'New York City'); response: | ```json { "data": [ { "name": "United States", "cities": [ { "name": "New York City" }, { "name": "Atlanta" } ] }, { "name": "Vanuatu", "cities": [] } ], "count": null } ``` description: | Ordering on foreign tables doesn't affect the ordering of the parent table. hideCodeBlock: true - id: limit title: limit() params: - name: size isOptional: false type: number description: The maximum number of rows to return - name: foreign_table isOptional: true type: string description: Set this to limit rows of foreign tables instead of the parent table. examples: - id: with-select name: With `select()` code: | ```python response = supabase.table("countries").select("name").limit(1).execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "name": "Afghanistan" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: on-a-foreign-table name: On a foreign table code: | ```python response = ( supabase.table("countries") .select("name, cities(name)") .limit(1, foreign_table="cities") .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text ); insert into countries (id, name) values (1, 'United States'); insert into cities (id, country_id, name) values (1, 1, 'Atlanta'), (2, 1, 'New York City'); ``` response: | ```json { "data": [ { "name": "United States", "cities": [ { "name": "Atlanta" } ] } ], "count": null } ``` hideCodeBlock: true - id: range title: range() params: - name: start isOptional: false type: number description: The starting index from which to limit the result. - name: end isOptional: false type: number description: The last index to which to limit the result. - name: foreign_table isOptional: true type: string description: Set this to limit rows of foreign tables instead of the parent table. notes: | Limit the query result by starting at an offset (`from`) and ending at the offset (`from + to`). Only records within this range are returned. This respects the query order and if there is no order clause the range could behave unexpectedly. The `from` and `to` values are 0-based and inclusive: `range(1, 3)` will include the second, third and fourth rows of the query. examples: - id: with-select name: With `select()` code: | ```python response = supabase.table("countries").select("name").range(0, 1).execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": [ { "name": "Afghanistan" }, { "name": "Albania" } ], "count": null } ``` hideCodeBlock: true isSpotlight: true - id: on-a-foreign-table name: On a foreign table code: | ```python response = ( supabase.table("countries") .select("name, cities(name)") .range(0, 1, foreign_table="cities") .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); create table cities ( id int8 primary key, country_id int8 not null references countries, name text ); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); insert into cities (id, country_id, name) values (1, 1, 'Kabul'), (2, 1, 'Herat'), (3, 2, 'Berat'); ``` response: | ```json { "data": [ { "name": "Afghanistan", "cities": [ { "name": "Kabul" }, { "name": "Herat" } ] }, { "name": "Albania", "cities": [ { "name": "Berat" } ] }, { "name": "Algeria", "cities": [] } ], "count": null } ``` hideCodeBlock: true - id: single title: single() notes: Return `data` as a single object instead of an array of objects. examples: - id: with-select() name: With `select()` code: | ```python response = supabase.table("countries").select("name").limit(1).single().execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": { "name": "Afghanistan" }, "count": null } ``` hideCodeBlock: true isSpotlight: true - id: maybe-single title: maybe_single() notes: Return `data` as a single object instead of an array of objects. examples: - id: with-select name: With `select()` code: | ```python response = ( supabase.table("countries") .select("*") .eq("name", "Albania") .maybe_single() .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": { "id": 2, "name": "Albania" }, "count": null } ``` hideCodeBlock: true isSpotlight: true - id: csv title: csv() notes: Return `data` as a string in CSV format. examples: - id: return-data-as-csv name: Return data as CSV code: | ```python response = supabase.table("countries").select("*").csv().execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ```json { "data": "id,name\n1,Afghanistan\n2,Albania\n3,Algeria", "count": null } ``` description: | By default, the data is returned in JSON format, but can also be returned as Comma Separated Values. hideCodeBlock: true isSpotlight: true - id: explain title: Using Explain params: - name: wal isOptional: true type: boolean description: If `true`, include information on WAL record generation. - name: verbose isOptional: true type: boolean description: If `true`, the query identifier will be returned and `data` will include the output columns of the query. - name: settings isOptional: true type: boolean description: If `true`, include information on configuration parameters that affect query planning. - name: format isOptional: true type: boolean description: The format of the output, can be `"text"` (default) or `"json"`. - name: format isOptional: true type: '"text" | "json"' description: The format of the output, can be `"text"` (default) or `"json"`. - name: buffers isOptional: true type: boolean description: If `true`, include information on buffer usage. - name: analyze isOptional: true type: boolean description: If `true`, the query will be executed and the actual run time will be returned. description: | For debugging slow queries, you can get the [Postgres `EXPLAIN` execution plan](https://www.postgresql.org/docs/current/sql-explain.html) of a query using the `explain()` method. This works on any query, even for `rpc()` or writes. Explain is not enabled by default as it can reveal sensitive information about your database. It's best to only enable this for testing environments but if you wish to enable it for production you can provide additional protection by using a `pre-request` function. Follow the [Performance Debugging Guide](/docs/guides/database/debugging-performance) to enable the functionality on your project. examples: - id: get-execution-plan name: Get the execution plan code: | ```python response = supabase.table("countries").select("*").explain().execute() ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ``` Aggregate (cost=33.34..33.36 rows=1 width=112) -> Limit (cost=0.00..18.33 rows=1000 width=40) -> Seq Scan on countries (cost=0.00..22.00 rows=1200 width=40) ``` description: | By default, the data is returned in TEXT format, but can also be returned as JSON by using the `format` parameter. hideCodeBlock: true isSpotlight: true - id: get-execution-plan-with-analyze-and-verbose name: Get the execution plan with analyze and verbose code: | ```python response = ( supabase.table("countries") .select("*") .explain(analyze=True, verbose=True) .execute() ) ``` data: sql: | ```sql create table countries (id int8 primary key, name text); insert into countries (id, name) values (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'); ``` response: | ``` Aggregate (cost=33.34..33.36 rows=1 width=112) (actual time=0.041..0.041 rows=1 loops=1) Output: NULL::bigint, count(ROW(countries.id, countries.name)), COALESCE(json_agg(ROW(countries.id, countries.name)), '[]'::json), NULLIF(current_setting('response.headers'::text, true), ''::text), NULLIF(current_setting('response.status'::text, true), ''::text) -> Limit (cost=0.00..18.33 rows=1000 width=40) (actual time=0.005..0.006 rows=3 loops=1) Output: countries.id, countries.name -> Seq Scan on public.countries (cost=0.00..22.00 rows=1200 width=40) (actual time=0.004..0.005 rows=3 loops=1) Output: countries.id, countries.name Query Identifier: -4730654291623321173 Planning Time: 0.407 ms Execution Time: 0.119 ms ``` description: | By default, the data is returned in TEXT format, but can also be returned as JSON by using the `format` parameter. hideCodeBlock: true isSpotlight: false - id: invoke title: 'invoke()' description: | Invoke a Supabase Function. notes: | - Requires an Authorization header. - When you pass in a body to your function, we automatically attach the Content-Type header for `Blob`, `ArrayBuffer`, `File`, `FormData` and `String`. If it doesn't match any of these types we assume the payload is `json`, serialise it and attach the `Content-Type` header as `application/json`. You can override this behaviour by passing in a `Content-Type` header of your own. examples: - id: invoke-function name: Basic invocation description: code: | ```python response = supabase.functions.invoke( "hello-world", invoke_options={"body": {"name": "Functions"}} ) ``` - id: error-handling name: Error handling description: | Returns one of the following errors: - `FunctionsHttpError`: if your function throws an error - `FunctionsRelayError`: if the Supabase Relay encounters an error processing your function isSpotlight: true code: | ```python from supafunc.errors import FunctionsRelayError, FunctionsHttpError try: response = supabase.functions.invoke( "hello-world", invoke_options={ "body": {"foo": "bar"}, "headers": {"my-custom-header": "my-custom-header-value"}, }, ) except FunctionsHttpError as exception: err = exception.to_dict() print(f"Function returned an error {err.get("message")}") except FunctionsRelayError as exception: err = exception.to_dict() print(f"Relay error: {err.get("message")}") ``` - id: passing-custom-headers name: Passing custom headers description: | The library accepts custom headers via the `headers` option. Note: `supabase-py` automatically populates the `Authorization` header if there is a signed in user. isSpotlight: true code: | ```python response = supabase.functions.invoke( "hello-world", invoke_options={ "headers": { "my-custom-header": "my-custom-header-value" }, "body": { "foo": "bar" } } ) ``` - id: subscribe title: on().subscribe() notes: | - By default, Broadcast and Presence are enabled for all projects. - By default, listening to database changes is disabled for new projects due to database performance and security concerns. You can turn it on by managing Realtime's [replication](/docs/guides/api#realtime-api-overview). - You can receive the "previous" data for updates and deletes by setting the table's `REPLICA IDENTITY` to `FULL` (e.g., `ALTER TABLE your_table REPLICA IDENTITY FULL;`). - Row level security is not applied to delete statements. When RLS is enabled and replica identity is set to full, only the primary key is sent to clients. examples: - id: listen-to-broadcast name: Listen to broadcast messages isSpotlight: true code: | ```python channel = supabase.channel("room1") def on_subscribe(status, err): if status == RealtimeSubscribeStates.SUBSCRIBED: channel.send_broadcast('cursor-pos', { "x": random.random(), "y": random.random() }) def handle_broadcast(payload): print("Cursor position received!", payload) channel.on_broadcast(event="cursor-pos", callback=handle_broadcast).subscribe(on_subscribe) ``` - id: listen-to-presence-sync name: Listen to presence sync isSpotlight: true code: | ```python channel = supabase.channel('room1') def on_subscribe(status, err): if status == RealtimeSubscribeStates.SUBSCRIBED: channel.track({ "online_at": datetime.datetime.now().isoformat() }) def handle_presence_sync(): print('Synced presence state: ', channel.presence.state) channel.on_presence_sync(callback=handle_presence_sync).subscribe(on_subscribe) ``` - id: listen-to-presence-join name: Listen to presence join isSpotlight: true code: | ```python channel = supabase.channel('room1') def handle_presence_join(key, current_presence, new_presence): print('Newly joined presences: ', new_presence) def on_subscribe(status, err): if status == RealtimeSubscribeStates.SUBSCRIBED: channel.track({ "online_at": datetime.datetime.now().isoformat() }) channel.on_presence_join(callback=handle_presence_join).subscribe(on_subscribe) ``` - id: listen-to-presence-leave name: Listen to presence leave isSpotlight: true code: | ```python channel = supabase.channel('room1') def handle_presence_leave(key, current_presence, left_presence): print('Newly left presences: ', left_presence) def on_subscribe(status, err): if status == RealtimeSubscribeStates.SUBSCRIBED: channel.track({ "online_at": datetime.datetime.now().isoformat() }) channel.untrack() channel.on_presence_leave(callback=handle_presence_leave).subscribe(on_subscribe) ``` - id: listen-to-all-database-changes name: Listen to all database changes isSpotlight: true code: | ```python supabase .channel('room1') .on_postgres_changes("*", schema="*", callback=handle_record_updated) .subscribe() ``` - id: listen-to-a-specific-table name: Listen to a specific table code: | ```python supabase .channel('room1') .on_postgres_changes("*", schema="public", table="countries", callback=handle_record_updated) .subscribe() ``` - id: listen-to-inserts name: Listen to inserts code: | ```python supabase .channel('room1') .on_postgres_changes("INSERT", schema="public", table="countries", callback=handle_record_inserted) .subscribe() ``` - id: listen-to-updates name: Listen to updates description: | By default, Supabase will send only the updated record. If you want to receive the previous values as well you can enable full replication for the table you are listening to: ```sql alter table "your_table" replica identity full; ``` code: | ```python supabase .channel('room1') .on_postgres_changes("UPDATE", schema="public", table="countries", callback=handle_record_updated) .subscribe() ``` - id: listen-to-deletes name: Listen to deletes description: | By default, Supabase does not send deleted records. If you want to receive the deleted record you can enable full replication for the table you are listening too: ```sql alter table "your_table" replica identity full; ``` code: | ```python supabase .channel('room1') .on_postgres_changes("DELETE", schema="public", table="countries", callback=handle_record_deleted) .subscribe() ``` - id: listen-to-multiple-events name: Listen to multiple events description: You can chain listeners if you want to listen to multiple events for each table. code: | ```python supabase .channel('room1') .on_postgres_changes("INSERT", schema="public", table="countries", callback=handle_record_inserted) .on_postgres_changes("DELETE", schema="public", table="countries", callback=handle_record_deleted) .subscribe() ``` - id: listening-to-row-level-changes name: Listen to row level changes description: You can listen to individual rows using the format `{table}:{col}=eq.{val}` - where `{col}` is the column name, and `{val}` is the value which you want to match. notes: | - ``eq`` filter works with all database types as under the hood, it's casting both the filter value and the database value to the correct type and then comparing them. code: | ```python supabase .channel('room1') .on_postgres_changes("UPDATE", schema="public", table="countries", filter="id=eq.200", callback=handle_record_updated) .subscribe() ``` - id: broadcast-message title: broadcastMessage() description: | Broadcast a message to all connected clients to a channel. examples: - id: send-a-message name: Send a message via websocket isSpotlight: true code: | ```python channel = supabase.channel('room1') def on_subscribe(status, err): if status == RealtimeSubscribeStates.SUBSCRIBED: channel.send_broadcast('cursor-pos', { "x": random.random(), "y": random.random() }) channel.subscribe(on_subscribe) ``` response: | ``` ok | timed out | error ``` - id: get-channels title: getChannels() examples: - id: get-all-channels name: Get all channels isSpotlight: true code: | ```python channels = supabase.get_channels() ``` - id: remove-channel title: removeChannel() notes: | - Removing a channel is a great way to maintain the performance of your project's Realtime service as well as your database if you're listening to Postgres changes. Supabase will automatically handle cleanup 30 seconds after a client is disconnected, but unused channels may cause degradation as more clients are simultaneously subscribed. examples: - id: removes-a-channel name: Removes a channel isSpotlight: true code: | ```python supabase.remove_channel(myChannel) ``` - id: remove-all-channels title: removeAllChannels() notes: | - Removing channels is a great way to maintain the performance of your project's Realtime service as well as your database if you're listening to Postgres changes. Supabase will automatically handle cleanup 30 seconds after a client is disconnected, but unused channels may cause degradation as more clients are simultaneously subscribed. examples: - id: remove-all-channels name: Remove all channels isSpotlight: true code: | ```python supabase.remove_all_channels() ``` - id: list-buckets title: 'list_buckets()' notes: | - RLS policy permissions required: - `buckets` table permissions: `select` - `objects` table permissions: none - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: list-buckets name: List buckets code: | ``` res = supabase.storage.list_buckets() ``` - id: get-bucket title: 'get_bucket()' notes: | - RLS policy permissions required: - `buckets` table permissions: `select` - `objects` table permissions: none - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: get-bucket name: Get bucket code: | ``` res = supabase.storage.get_bucket(name) ``` - id: create-bucket title: 'create_bucket()' notes: | - RLS policy permissions required: - `buckets` table permissions: `insert` - `objects` table permissions: none - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: create-bucket name: Create bucket code: | ``` res = supabase.storage.create_bucket(name) ``` - id: empty-bucket title: 'empty_bucket()' notes: | - RLS policy permissions required: - `buckets` table permissions: `select` - `objects` table permissions: `select` and `delete` - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: empty-bucket name: Empty bucket code: | ``` res = supabase.storage.empty_bucket(name) ``` - id: delete-bucket title: 'delete_bucket()' notes: | - RLS policy permissions required: - `buckets` table permissions: `select` and `delete` - `objects` table permissions: none - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: delete-bucket name: Delete bucket code: | ``` res = supabase.storage.delete_bucket(name) ``` - id: from-upload title: 'from_.upload()' notes: | - RLS policy permissions required: - `buckets` table permissions: none - `objects` table permissions: `insert` - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works - Please specify the appropriate content [MIME type](https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/MIME_types) if you are uploading images or audio. If no `file_options` are specified, the MIME type defaults to `text/html`. examples: - id: upload-file name: Upload file using filepath code: | ```py with open(filepath, 'rb') as f: supabase.storage.from_("testbucket").upload(file=f,path=path_on_supastorage, file_options={"content-type": "audio/mpeg"}) ``` - id: from-update title: from_.update() notes: | - RLS policy permissions required: - `buckets` table permissions: none - `objects` table permissions: `update` and `select` - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: update-file name: Update file code: | ```python with open(filepath, 'rb') as f: supabase.storage.from_("bucket_name").update(file=f, path=path_on_supastorage, file_options={"cache-control": "3600", "upsert": "true"}) ``` - id: from-move title: 'from_.move()' notes: | - RLS policy permissions required: - `buckets` table permissions: none - `objects` table permissions: `update` and `select` - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: move-file name: Move file code: | ``` res = supabase.storage.from_('bucket_name').move('public/avatar1.png', 'private/avatar2.png') ``` - id: from-create-signed-url title: 'from_.create_signed_url()' notes: | - RLS policy permissions required: - `buckets` table permissions: none - `objects` table permissions: `select` - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: create-signed-url name: Create Signed URL code: | ``` res = supabase.storage.from_('bucket_name').create_signed_url(filepath, expiry_duration) ``` - id: from-get-public-url title: 'from_.get_public_url()' notes: | - The bucket needs to be set to public, either via [updateBucket()](/docs/reference/python/storage-updatebucket) or by going to Storage on [supabase.com/dashboard](https://supabase.com/dashboard), clicking the overflow menu on a bucket and choosing "Make public" - RLS policy permissions required: - `buckets` table permissions: none - `objects` table permissions: none - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: get-public-url name: Returns the URL for an asset in a public bucket code: | ``` res = supabase.storage.from_('bucket_name').get_public_url('test/avatar1.jpg') ``` - id: from-download title: 'from_.download()' notes: | - RLS policy permissions required: - `buckets` table permissions: none - `objects` table permissions: `select` - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: download-file name: Download file code: | ``` with open(destination, 'wb+') as f: res = supabase.storage.from_('bucket_name').download(source) f.write(res) ``` - id: from-remove title: 'from_.remove()' notes: | - RLS policy permissions required: - `buckets` table permissions: none - `objects` table permissions: `delete` and `select` - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: delete-file name: Delete file code: | ``` res = supabase.storage.from_('bucket_name').remove('test.jpg') ``` - id: from-list title: 'from_.list()' notes: | - RLS policy permissions required: - `buckets` table permissions: none - `objects` table permissions: `select` - Refer to the [Storage guide](/docs/guides/storage/security/access-control) on how access control works examples: - id: list-files name: List files in a bucket code: | ``` res = supabase.storage.from_('bucket_name').list() ```