Android Example [B4X] Supabase - Database CRUD


This is a very simple tutorial on how to use the CRUD options. A more detailed tutorial is coming soon.

CREATE
One Row:
Dim Insert As Supabase_DatabaseInsert = xSupabase.Database.InsertData
Insert.From("dt_Tasks")
Dim InsertMap As Map = CreateMap("Tasks_Name":"Task 01","Tasks_Checked":False,"Tasks_CreatedAt":DateUtils.TicksToString(DateTime.Now),"Tasks_UpdatedAt":DateUtils.TicksToString(DateTime.Now))
Wait For (Insert.Insert(InsertMap).Execute) Complete (Result As SupabaseDatabaseResult)
Bulk Insert:
Dim Insert As Supabase_DatabaseInsert = xSupabase.Database.InsertData
Insert.From("dt_Tasks")
Dim lst_BulkInsert As List
lst_BulkInsert.Initialize
lst_BulkInsert.Add(CreateMap("Tasks_Name":"Task 02","Tasks_Checked":True,"Tasks_CreatedAt":DateUtils.TicksToString(DateTime.Now),"Tasks_UpdatedAt":DateUtils.TicksToString(DateTime.Now)))
lst_BulkInsert.Add(CreateMap("Tasks_Name":"Task 03","Tasks_Checked":True,"Tasks_CreatedAt":DateUtils.TicksToString(DateTime.Now),"Tasks_UpdatedAt":DateUtils.TicksToString(DateTime.Now)))
Wait For (Insert.InsertBulk(lst_BulkInsert).Execute) Complete (Result As SupabaseDatabaseResult)
READ
B4X:
    Dim Query As Supabase_DatabaseSelect = xSupabase.Database.SelectData
    Query.Columns("*").From("dt_Tasks")
    Query.Filter_Equal(CreateMap("Tasks_Name":"Task 02"))
    Wait For (Query.Execute) Complete (DatabaseResult As SupabaseDatabaseResult)

    xSupabase.Database.PrintTable(DatabaseResult)

    For Each Row As Map In DatabaseResult.Rows
        Log(Row.Get("Tasks_Name"))
    Next
UPDATE
B4X:
Dim Update As Supabase_DatabaseUpdate = xSupabase.Database.UpdateData
Update.From("dt_Tasks")
Update.Update(CreateMap("Tasks_Name":"Task 02"))
Update.Eq(CreateMap("Tasks_Id":1))
Wait For (Update.Execute) Complete (Result As SupabaseDatabaseResult)
DELETE
B4X:
Dim Delete As Supabase_DatabaseDelete = xSupabase.Database.DeleteData
Delete.From("dt_Tasks")
Delete.Eq(CreateMap("Tasks_Id":1))
Wait For (Delete.Execute) Complete (Result As SupabaseError)
 
Last edited:

GJREDITOR

Member
Thank you for the library. Could you please explain why I am getting this error?
ResponseError. Reason: , Response: {"code":"PGRST301","details":null,"hint":null,"message":"JWSError (CompactDecodeError Invalid number of parts: Expected 3 parts; got 1)"}
Tag: java.lang.Object@355f501d, Columns: 0, Rows: 0
My B4J page code is:
supabase B4J Code:
Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
    Private xSupabase As Supabase
End Sub

Public Sub Initialize
'    B4XPages.GetManager.LogEvents = True
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("MainPage")
    
    xSupabase.Initialize("https://iyisqcxlgqdmgpvsvgkr.supabase.co","<anonkeyhere>")' I rechecked anon key again.It is correct'
End Sub

'You can see the list of page related events in the B4XPagesManager object. The event name is B4XPage.

Private Sub Button1_Click
    Dim Query As Supabase_DatabaseSelect = xSupabase.Database.SelectData
    Query.Columns("*").From("NEETtlb")
    Query.Filter_Equal(CreateMap("RANK":"10"))
    Wait For (Query.Execute) Complete (DatabaseResult As SupabaseDatabaseResult)
    xSupabase.Database.PrintTable(DatabaseResult)
End Sub
 

GJREDITOR

Member
No, the ilike is not the same as like. If you want the like filter then set it to Filter_Like
The ilike filter I have not yet tested
Thank you. I have been using 'like' filter since posting to this thread . The 'ilike' filter works if I add a "*" :
B4X:
Query.Filter_Ilike(CreateMap("NAME":"shri*"))
for the case insensitive search.
 

pliroforikos

Active Member
Licensed User
Hello, thank you for the library. I've created a small database for my school to test and it works wonderfull.
But, i can't figure how to make complicated queries.

For example for the database bellow i'm trying to get all students where they belong to a school with name "2nd High School". Or all students where the belong to CLASS with class_name "B". Or i need to get all data from CLASS, SUBCLASS, STUDENT where the belong to a certain school.
The relationship between tables are One to Many.
Can you please make such examples?

er1.png

Thank you very much again
 
Last edited:

pliroforikos

Active Member
Licensed User
I mean is it supported from library?

As i found the way of writing such queries please let me answer the question in case someone intersted. So:
1697444429524.png

For the above database schema some queries.

1.
Select all students from all schools:
Query.Columns("*,  CLASS(id, SUBCLASSES(id, STUDENTS(*)))").From("SCHOOLS")

2.
Select all students from a certain school:
Query.Columns("*,  CLASS(id, SUBCLASSES(id, STUDENTS(*)))").From("SCHOOLS")
Query.Filter_Equal(CreateMap("school_name":"2nd High School"))

3.
Select all students from a certain subclass:
Query.Columns("*, STUDENTS(*)").From("SUBCLASSES")
Query.Filter_Equal(CreateMap("subclass_name":"C1"))

A small donation made. Thank you for your effort.
 
Last edited:

amorosik

Expert
Licensed User
Hi Alexander, very compliment for this work
One of the main reason for choosing Firebase is the possibility of 'subscribing' to changes on the databssde records and therefopre having a method to ensure thet the client is informed of the changes made by other users on the data
For Supabase, how to 'see' the notifications that come from the db server?
 

Alexander Stolte

Expert
Licensed User
Longtime User
One of the main reason for choosing Firebase is the possibility of 'subscribing' to changes on the databssde records and therefopre having a method to ensure thet the client is informed of the changes made by other users on the data
For Supabase, how to 'see' the notifications that come from the db server?
Supabase supports websockets, but unfortunately I haven't managed to get it to work yet. Discussed here yesterday:
The other option are firebase notifications, but here, too, i have only a theoretical solution so far.
Create a edge function in supabase with the following code:
B4X:
// Follow this setup guide to integrate the Deno language server with your editor:
// https://deno.land/manual/getting_started/setup_your_environment
// This enables autocomplete, go to definition, etc.

import { default as axiod } from "https://deno.land/x/[email protected]/mod.ts";
import * as djwt from "https://deno.land/x/[email protected]/mod.ts";

const FIREBASE_PROJECT_ID="your-project-id"
const FIREBASE_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n"
const FIREBASE_CLIENT_EMAIL="[email protected]"

export async function sendFirebaseMessageToDevice(
  now: Date,
  deviceToken: string,
  payload: MessagingPayload
) {
  const url = "https://fcm.googleapis.com/v1/projects/:project/messages:send".replace(
    ":project",
    assertDefined(Deno.env.get("FIREBASE_PROJECT_ID"), "FIREBASE_PROJECT_ID")
  );

  const authToken = await getGoogleAccessToken(now);

  return axiod.post(
    url,
    {
      message: {
        ...payload,
        token: deviceToken,
      },
    },
    {
      headers: {
        "Content-Type": "application/json",
        Authorization: `Bearer ${authToken}`,
      },
    }
  );
}

async function getGoogleAccessToken(now: Date) {
  const jwt = await djwt.create(
    { alg: "RS256", typ: "JWT" },
    {
      iss: Deno.env.get("FIREBASE_CLIENT_EMAIL"),
      scope: "https://www.googleapis.com/auth/firebase.messaging",
      aud: "https://oauth2.googleapis.com/token",
      exp: now.setSeconds(3600) / 1000,
      iat: now.getTime() / 1000,
    },
    assertDefined(Deno.env.get("FIREBASE_PRIVATE_KEY"), "FIREBASE_PRIVATE_KEY")
  );

  const token = await getOAuthToken(jwt);

  return token.access_token;
}

async function getOAuthToken(jwt: string) {
  const response = await axiod.post<{
    access_token: string;
    expires_in: number;
    token_type: string;
  }>(
    "https://oauth2.googleapis.com/token",
    `grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer&assertion=${jwt}`,
    {
      headers: { "Content-Type": "application/x-www-form-urlencoded" },
    }
  );

  return response.data;
}

export function isDefined<T>(value: T | undefined): value is NonNullable<T> {
  return value !== undefined && value !== null;
}

export function assertDefined<T>(value: T | null | undefined, description: string): T {
  if (isDefined(value)) {
    return value;
  }

  throw new Error(`Expected defined value for "${description}", received ${value} instead.`);
}

export interface MessagingPayload {
  /**
   * The data message payload.
   */
  data?: DataMessagePayload;
  /**
   * The notification message payload.
   */
  notification?: NotificationMessagePayload;
}

export interface DataMessagePayload {
  [key: string]: string;
}
 
/**
 * Interface representing an FCM legacy API notification message payload.
 * Notification messages let developers send up to 4KB of predefined
 * key-value pairs. Accepted keys are outlined below.
 *
 * See {@link https://firebase.google.com/docs/cloud-messaging/send-message | Build send requests}
 * for code samples and detailed documentation.
 */
export interface NotificationMessagePayload {
  /**
   * Identifier used to replace existing notifications in the notification drawer.
   *
   * If not specified, each request creates a new notification.
   *
   * If specified and a notification with the same tag is already being shown,
   * the new notification replaces the existing one in the notification drawer.
   *
   * **Platforms:** Android
   */
  tag?: string;
  /**
   * The notification's body text.
   *
   * **Platforms:** iOS, Android, Web
   */
  body?: string;
  /**
   * The notification's icon.
   *
   * **Android:** Sets the notification icon to `myicon` for drawable resource
   * `myicon`. If you don't send this key in the request, FCM displays the
   * launcher icon specified in your app manifest.
   *
   * **Web:** The URL to use for the notification's icon.
   *
   * **Platforms:** Android, Web
   */
  icon?: string;
  /**
   * The value of the badge on the home screen app icon.
   *
   * If not specified, the badge is not changed.
   *
   * If set to `0`, the badge is removed.
   *
   * **Platforms:** iOS
   */
  badge?: string;
  /**
   * The notification icon's color, expressed in `#rrggbb` format.
   *
   * **Platforms:** Android
   */
  color?: string;
  /**
   * The sound to be played when the device receives a notification. Supports
   * "default" for the default notification sound of the device or the filename of a
   * sound resource bundled in the app.
   * Sound files must reside in `/res/raw/`.
   *
   * **Platforms:** Android
   */
  sound?: string;
  /**
   * The notification's title.
   *
   * **Platforms:** iOS, Android, Web
   */
  title?: string;
  /**
   * The key to the body string in the app's string resources to use to localize
   * the body text to the user's current localization.
   *
   * **iOS:** Corresponds to `loc-key` in the APNs payload. See
   * {@link https://developer.apple.com/library/content/documentation/NetworkingInternet/Conceptual/RemoteNotificationsPG/PayloadKeyReference.html |
   * Payload Key Reference} and
   * {@link https://developer.apple.com/library/content/documentation/NetworkingInternet/Conceptual/RemoteNotificationsPG/CreatingtheNotificationPayload.html#//apple_ref/doc/uid/TP40008194-CH10-SW9 |
   * Localizing the Content of Your Remote Notifications} for more information.
   *
   * **Android:** See
   * {@link http://developer.android.com/guide/topics/resources/string-resource.html | String Resources}
   * for more information.
   *
   * **Platforms:** iOS, Android
   */
  bodyLocKey?: string;
  /**
   * Variable string values to be used in place of the format specifiers in
   * `body_loc_key` to use to localize the body text to the user's current
   * localization.
   *
   * The value should be a stringified JSON array.
   *
   * **iOS:** Corresponds to `loc-args` in the APNs payload. See
   * {@link https://developer.apple.com/library/content/documentation/NetworkingInternet/Conceptual/RemoteNotificationsPG/PayloadKeyReference.html |
   * Payload Key Reference} and
   * {@link https://developer.apple.com/library/content/documentation/NetworkingInternet/Conceptual/RemoteNotificationsPG/CreatingtheNotificationPayload.html#//apple_ref/doc/uid/TP40008194-CH10-SW9 |
   * Localizing the Content of Your Remote Notifications} for more information.
   *
   * **Android:** See
   * {@link http://developer.android.com/guide/topics/resources/string-resource.html#FormattingAndStyling |
   * Formatting and Styling} for more information.
   *
   * **Platforms:** iOS, Android
   */
  bodyLocArgs?: string;
  /**
   * Action associated with a user click on the notification. If specified, an
   * activity with a matching Intent Filter is launched when a user clicks on the
   * notification.
   *
   *   * **Platforms:** Android
   */
  clickAction?: string;
  /**
   * The key to the title string in the app's string resources to use to localize
   * the title text to the user's current localization.
   *
   * **iOS:** Corresponds to `title-loc-key` in the APNs payload. See
   * {@link https://developer.apple.com/library/content/documentation/NetworkingInternet/Conceptual/RemoteNotificationsPG/PayloadKeyReference.html |
   * Payload Key Reference} and
   * {@link https://developer.apple.com/library/content/documentation/NetworkingInternet/Conceptual/RemoteNotificationsPG/CreatingtheNotificationPayload.html#//apple_ref/doc/uid/TP40008194-CH10-SW9 |
   * Localizing the Content of Your Remote Notifications} for more information.
   *
   * **Android:** See
   * {@link http://developer.android.com/guide/topics/resources/string-resource.html | String Resources}
   * for more information.
   *
   * **Platforms:** iOS, Android
   */
  titleLocKey?: string;
  /**
   * Variable string values to be used in place of the format specifiers in
   * `title_loc_key` to use to localize the title text to the user's current
   * localization.
   *
   * The value should be a stringified JSON array.
   *
   * **iOS:** Corresponds to `title-loc-args` in the APNs payload. See
   * {@link https://developer.apple.com/library/content/documentation/NetworkingInternet/Conceptual/RemoteNotificationsPG/PayloadKeyReference.html |
   * Payload Key Reference} and
   * {@link https://developer.apple.com/library/content/documentation/NetworkingInternet/Conceptual/RemoteNotificationsPG/CreatingtheNotificationPayload.html#//apple_ref/doc/uid/TP40008194-CH10-SW9 |
   * Localizing the Content of Your Remote Notifications} for more information.
   *
   * **Android:** See
   * {@link http://developer.android.com/guide/topics/resources/string-resource.html#FormattingAndStyling |
   * Formatting and Styling} for more information.
   *
   * **Platforms:** iOS, Android
   */
  titleLocArgs?: string;
  [key: string]: string | undefined;
}

// To invoke:
// curl -i --location --request POST 'http://localhost:54321/functions/v1/' \
//   --header 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6ImFub24iLCJleHAiOjE5ODM4MTI5OTZ9.CRXP1A7WOeoJeXxjNni43kdQwgnWNReilDMblYTn_I0' \
//   --header 'Content-Type: application/json' \
//   --data '{"name":"Functions"}'
and then run this edge function on database changes.

I appreciate any help, if I do it on my own I think it will take a few more months as I am working with stuff here that I have never worked with before and I am just doing it on the side.

for further questions please open a new thread. Thanks
 

Jondie

New Member
Hello Alexander I'm newbie here and your work is excellent but i got some errors.

ResponseError. Reason: , Response: {"error":"invalid_request","error_description":"refresh_token required"}
Token received. Expires: 01/01/1970 00:00:00


Whenever i try to CREATE data this is always error that shows up. What are the possible solutions for this. Thanks for the response in advanced :>
 

Alexander Stolte

Expert
Licensed User
Longtime User
Have a look at the authentification thread. You need to authentificate your user, before you can access the database.
For further questions, please make a new thread. Thanks.
 
Top