For one of my .NET projects, I use Supabase to store my data. Supabase is an open-source alternative to Firebase. It is based on PostgreSQL and provides a REST API and websockets to interact with the database.

To connect my .NET project to Supabase, I use Supabase Sharp and you can also find the documentation on the official Supabase website.

The ease of use of the library is really interesting and it also allows me to use the Supabase website directly to manage my data without having to write code to do it in my own applications.

However, the documentation is not explicit when you have tables linked by foreign keys (FK). Here is how I managed to get the data from my linked tables while using C# objects and some attributes from the library.

Supabase Data Model

Here is my data model in Supabase:

Table Book

The AuthorId field is a foreign key to the Author table.

Table Author

C# Code

Base Class

The simple code to define the C# objects is as follows:

public class Book
{
    public string Title { get; set; }
    public Author Author { get; set; }
}

public class Author
{
    public string Name { get; set; }
}

Using Supabase Sharp

Defining Attributes

For the library to understand the relationships between the tables, we need to add attributes to our classes.

• Table: To indicate the name of the table in the database
• PrimaryKey: To indicate the primary key of the table
• Column: To indicate the name of the column in the database
• Reference: To indicate that it is a foreign key
  • includeInQuery: To indicate if the related table should be included in the query

[Table("Books")]
public class Book
{
    [PrimaryKey("Id")]
    public int Id { get; set; }
    [Column("Title")]
    public string Title { get; set; }
    [Reference(typeof(Author), includeInQuery:true)]
    public Author Author { get; set; }
}

[Table("Authors")]
public class Author
{    
    [PrimaryKey("Id")]
    public int Id { get; set; }
    [Column("Name")]
    public string Name { get; set; }
}

Fetching Data

To fetch the data, simply use the Get method of the library.

var books = await supabase
    .From<Book>()
    .Get();

Since we used the Reference attribute with includeInQuery:true, the library will automatically fetch the data from the Author table and include it in the Book object. If we had set includeInQuery:false, the library would have returned an empty Author object, but with the correct Id attribute. To fetch the data from the Author table, simply call the Get method of the library using Select and specify the Authors table.

var books = await supabase
    .From<Book>()
    .Select("*, Author(*)")
    .Get();

Relationship with Optional Foreign Keys

If the relationship between the tables is optional, simply declare the object as nullable and add the Reference attribute with joinType: ReferenceAttribute.JoinType.Left to retrieve the objects that do not have a relationship. The code to fetch the data remains the same.

[Table("Books")]
public class Book
{
    [PrimaryKey("Id")]
    public int Id { get; set; }
    [Column("Title")]
    public string Title { get; set; }
    [Reference(typeof(Author), joinType: ReferenceAttribute.JoinType.Left, includeInQuery:true)]
    public Author? Author { get; set; }
}

Conclusion

Supabase Sharp is a simple and effective library for interacting with Supabase. The Table, PrimaryKey, Column, and Reference attributes allow you to define relationships between tables and easily retrieve data. The library is constantly evolving and I encourage you to check the documentation for more information.