Exploring Supabase: Self-Hosting & GraphQL Integration
When I first looked into Supabase, it seemed like a pretty badass tool that is both simple and powerful. The core of Supabase is open source and the licensing on it is Apache 2, so it’s not merely “source open.” If you look at the Supabase DB repo, you can see that the PostgreSQL database comes with a ton of open-source extensions. So, the database is based on open-source standards.
One of the coolest extensions I found was pg_graphql. This allows you to run GraphQL queries and mutations on an existing database schema using the Supabase HTTP API, SQL, or raw SQL queries. I thought this was really cool because we can essentially use Supabase as a backend as a service without needing to write any API endpoints. And with built-in authentication, real-time notifications, row-level security, and storage (which can connect with S3 or Minio), Supabase offers a comprehensive solution.
Self-Hosting Supabase with Docker
I prefer not to use services hosted by providers other than cloud providers that I trust. So, I opted to run Supabase locally. I followed the instructions in the Supabase documentation for self-hosting with Docker Self-Hosting with Docker | Supabase Docs, but with a slight modification. Instead of the standard Docker commands, I ran:
docker compose -f docker-compose.yml -f docker-compose.s3.yml pull
docker compose -f docker-compose.yml -f docker-compose.s3.yml upFirst Impressions of the Supabase UI
Upon exploring the UI, it’s impressive how much you can do initially. However, one thing became very clear: you can’t create a new project within the UI! This is aggravating because, yes, Supabase is open source, but if you want to host another project, you need to recreate all the infrastructure. Technically, you could use a single PostgreSQL cluster, but with the components of Supabase being microservices, the toll on your infrastructure will quickly stack up.
Running GraphQL Queries
Despite this annoyance, I wanted to see how easy it would be to run GraphQL queries through the GraphiQL UI in the dashboard. Let’s just say it wasn’t as smooth as I hoped. When running the first query, I got a “does not have access” error. I realized that I needed to add a JSON object to the header section at the bottom of the page. However, there’s no obvious way to create a JSON web token in the dashboard besides using the SDK. This made me wonder why I couldn’t just run a query directly in the UI without all this hassle.
Nevertheless, I was able to achieve the same results in the SQL editor by running queries like this using the sql editor:
SELECT graphql.resolve($$
{
accountCollection {
edges {
node {
id,
email,
created_at,
updated_at,
blogCollection {
edges {
node {
id,
name,
description,
created_at,
updated_at,
blog_postCollection {
edges {
node {
id,
title,
body,
status,
created_at,
updated_at
}
}
}
}
}
}
}
}
}
}
$$);For a schema that looks like this:
CREATE TABLE account(
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL
);
CREATE TABLE blog(
id SERIAL PRIMARY KEY,
owner_id INTEGER NOT NULL REFERENCES account(id),
name VARCHAR(255) NOT NULL,
description VARCHAR(255),
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL
);
CREATE TYPE blog_post_status AS ENUM ('PENDING', 'RELEASED');
CREATE TABLE blog_post(
id UUID NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
blog_id INTEGER NOT NULL REFERENCES blog(id),
title VARCHAR(255) NOT NULL,
body VARCHAR(10000),
status blog_post_status NOT NULL,
created_at TIMESTAMP NOT NULL,
updated_at TIMESTAMP NOT NULL
);Integrating Supabase with .NET Aspire
So, what is the point of all of this? Well, now we are going to combine Supabase with .NET Aspire to create a seamless integration for database migrations and querying.
I created a git repository to show how you can use .NET Aspire with Supabase. Here is the link: josephaw1022/SupabaseSpike.
Program.cs for the App Host
Here is the Program.cs file for the app host:
var builder = DistributedApplication.CreateBuilder(args);
var existingSupabaseDb = builder.AddConnectionString("Supabase");
var dbMigration = builder.AddProject<Projects.SupabaseSpike_DatabaseMigrations>("dbmigration")
.WithReference(existingSupabaseDb);
builder.AddProject<Projects.SupabaseSpike_Web>("webfrontend")
.WithExternalHttpEndpoints()
.WithReference(existingSupabaseDb);
builder.Build().Run();We have a database migration project that runs migrations on the PostgreSQL database and defines our schema. All it has is an EF Core context that has a migration that is run when the app host starts up.
The UI project uses the PostgreSQL client to run GraphQL queries on the database. This strategy requires you to serve your Blazor application through server rendering, as accessing the database cannot be done on the client side.
Blazor Application Home Page
Here is the home page for our Blazor application, showing how we can use GraphQL to skip writing any API endpoints
@page "/"
@using Dapper
@using Npgsql
@using System.Text.Json
@inject NpgsqlConnection DbConnection
@inject ILogger<Home> Logger
<PageTitle>Home</PageTitle>
<h1>Hello, world!</h1>
Welcome to your new app.
<h3>
Here is the GraphQL response
</h3>
<br />
@if (jsonDocument != null)
{
<div style="border-bottom: 10px;">
@foreach (var account in jsonDocument.RootElement.GetProperty("data").GetProperty("accountCollection").GetProperty("edges").EnumerateArray())
{
var node = account.GetProperty("node");
<div>
<h4>Account: @node.GetProperty("email").GetString()</h4>
<p>Created At: @node.GetProperty("created_at").GetDateTime()</p>
<p>Updated At: @node.GetProperty("updated_at").GetDateTime()</p>
@if (node.TryGetProperty("blogCollection", out JsonElement blogCollection))
{
@foreach (var blog in blogCollection.GetProperty("edges").EnumerateArray())
{
var blogNode = blog.GetProperty("node");
<div style="margin-left:20px;">
<h5>Blog: @blogNode.GetProperty("name").GetString()</h5>
<p>Description: @blogNode.GetProperty("description").GetString()</p>
<p>Created At: @blogNode.GetProperty("created_at").GetDateTime()</p>
<p>Updated At: @blogNode.GetProperty("updated_at").GetDateTime()</p>
@if (blogNode.TryGetProperty("blog_postCollection", out JsonElement blogPostCollection))
{
@foreach (var post in blogPostCollection.GetProperty("edges").EnumerateArray())
{
var postNode = post.GetProperty("node");
<div style="margin-left:40px;">
<h6>Post: @postNode.GetProperty("title").GetString()</h6>
<p>Status: @postNode.GetProperty("status").GetString()</p>
<p>Created At: @postNode.GetProperty("created_at").GetDateTime()</p>
<p>Updated At: @postNode.GetProperty("updated_at").GetDateTime()</p>
<p>@postNode.GetProperty("body").GetString()</p>
</div>
}
}
</div>
}
}
</div>
}
</div>
}
else
{
<p>Loading...</p>
}
@code {
private JsonDocument jsonDocument;
protected override async Task OnInitializedAsync()
{
var sqlQuery = @"
SELECT graphql.resolve($$
{
accountCollection {
edges {
node {
id,
email,
created_at,
updated_at,
blogCollection {
edges {
node {
id,
name,
description,
created_at,
updated_at,
blog_postCollection {
edges {
node {
id,
title,
body,
status,
created_at,
updated_at
}
}
}
}
}
}
}
}
}
}
$$);
";
var result = await DbConnection.QueryFirstOrDefaultAsync<string>(sqlQuery);
if (result != null)
{
jsonDocument = JsonDocument.Parse(result);
}
}
}Final Thoughts
What are my thoughts around this strategy?
- Simplifies Database Querying: This approach simplifies querying the database as it allows you to write GraphQL queries directly, skipping the need to write complex joins.
- No API Endpoints Needed: I like that there’s no need to write any API endpoints.
- Learning Curve: While I know a decent amount of GraphQL, there are nuances and details to learn and figure out. Therefore, I’m not sure I’d use this strategy in a production application. However, it is a quick go-to-market implementation plan for those very familiar with GraphQL and comfortable using PostgreSQL.
Ending Thoughts on Supabase
There is a lot more to Supabase that went unmentioned in this post, but these are the aspects that piqued my interest. Supabase offers a wide array of features that can be incredibly powerful when utilized fully. To form your own conclusion, I highly recommend experimenting with the self-hosting option or using the Supabase managed service. By doing so, you can gain a firsthand understanding of its capabilities and limitations, allowing you to determine how it best fits into your development workflow.
In conclusion, Supabase offers a powerful, open-source backend solution with impressive features. However, the limitations on self-hosted instances and some usability issues with the UI can be frustrating. Despite these challenges, the potential for integrating GraphQL and developing a smooth workflow keeps Supabase a compelling choice for backend development.
