Supabase Makes Extensions Easier for Developers with Trusted Language Extensions for PostgreSQL

One of the reasons developers love PostgreSQL, beyond being an open source relational database, is that you can add new capabilities to support your applications. This is possible because of the extensible design of PostgreSQL, which allows for implementing new functionality such as data types or complex data processing without changes to the core database engine. For example, applications with maps and geographic information can use the PostGIS extension to add spatial data type support.

AWS partner Supabase is familiar with the extensibility of PostgreSQL and uses this to deliver features in its offering. Supabase is an open source Firebase alternative that provides the backend features developers need to build web and mobile apps. The Supabase platform is implemented as a thin usability layer on top of PostgreSQL. As a company built on open source, Supabase aims to provide its community with a self-hosting option that is cloud-provider agnostic. They are using Trusted Language Extensions for PostgreSQL to make the Supabase platform easier to deploy across multiple cloud providers and to make it easier for developers to add extensions in Supabase.

PostgreSQL extensions and the cloud

Within the PostgreSQL community more than 1,000 known open source extensions are available, in addition to an unknown number of commercial extensions that support applications from independent software vendors (ISVs). Amazon Relational Database Service (RDS) for PostgreSQL and Amazon Aurora PostgreSQL-Compatible Edition support a curated set of 90+ extensions. Why the curation?

Traditionally, PostgreSQL extensions, particularly those written in C, have involved some risk of unsafe access to the database that are mitigated through how extensions are managed and deployed. For example, extensions require filesystem access to be installed, may involve privileged access to the database, access to shared memory, or network access. Under the AWS Shared Responsibility Model, managing the security risks of each of these 90+ extensions to infrastructure is the responsibility of AWS. Managing these security risks takes time. Other providers of managed PostgreSQL, like Supabase, often follow a similar model. With customers asking for support of more extensions on PostgreSQL, it is time to innovate on the extension model.

Trusted Language Extensions (TLE) for PostgreSQL is an open source development kit that lets developers create extensions in their preferred language with built-in safety guardrails. A PostgreSQL trusted language, such as JavaScript, Perl, or PL/pgSQL, provides security boundaries so that an unprivileged user can safely run code in the database. TLE is available as the open source PostgreSQL extension pg_tle and can be used with Amazon RDS and Amazon Aurora.

PostgreSQL extensions in Supabase

The Supabase development team recognized that TLE could make it easier for them to support more extensions for app developers using the Supabase platform. Because TLE is available under the Apache 2.0 license it also aligns with Supabase’s open source approach.

The Supabase platform includes more than 50 PostgreSQL extensions. For example, when an app developer using Supabase creates a project, a dedicated PostgreSQL database is included with row level security to control what data the app users can access. Row level security is integrated with JSON Web Tokens (JWT) authentication using the pgjwt extension, which is licensed under the MIT License, and written in the trusted languages SQL and PL/pgSQL.

However, managing and supporting 50 extensions was becoming a burden for the Supabase team. An important consideration for the Supabase development team is the customer experience when upgrading to a new version of the platform. They require a supported migration path for the code in the database that is in sync with the Supabase platform code. With the PostgreSQL extension packaging mechanism and the TLE management functionality, the Supabase team can install and upgrade both Supabase platform code and extension code following software development lifecycle (SDLC) best practices. The result is a successful upgrade experience for Supabase customers.

The Supabase platform now includes pg_tle to install and manage a number of trusted language extensions such as pgjwt. For app developers using Supabase, you can more easily add PostgreSQL extensions while skipping the Pull Request and the wait for Supabase to add an extension to your database. Tooling advances to make it easier to discover and install TLEs are underway with dbdev: A Database Package Manager.

Installing pgjwt on Amazon RDS

Here is an example of installing a TLE developed by a third party — in this case pgjwt developed by Supabase — on Amazon RDS.

As a prerequisite, you need to have TLE installed and created on your Amazon RDS or Amazon Aurora database instance. See “Working with Trusted Language Extensions for PostgreSQL” in the Amazon RDS User Guide or Amazon Aurora User Guide.

1.     In your SQL query tool of choice, install the pgjwt extension so that it is available in your database by providing the extension name, version, description, extension contents, and dependencies.

SELECT pgtle.install_extension(
‘pgjwt’, ‘0.2.0’, ‘JSON Web Token API for Postgresql’,
— NOTE: Copy the contents of
— in here and remove the first line that contains “echo”

2.     Now that pgjwt is installed in your database, you can address the dependency on the pgcrypto extension by creating it if it has not already been made available to database users. pgcrypto is installed as an Amazon RDS supported extension.


3.     With pgjwt installed in your database, and the pgcrypto dependency available, you can now create the pgjwt extension so that its functionality is available to database users.


You can now work with JSON Web Tokens, see how in the pgjwt README.

If you are no longer are using pgjwt, the following steps can be used to remove the extensions:

1.     From the database you installed pgjwt, remove user access to the extension functionality.


2.     Because you no longer need the dependent pgcrypto, its functionality can be removed from user access. It will remain if other database objects are dependent on pgcrypto.


3.     With the functionality of both extensions removed from access by database users, remove the pgjwt extension from the database.

SELECT pgtle.uninstall_extension(‘pgjwt’, ‘0.2.0’);

The above steps also apply if you are running PostgreSQL on your local machine. For details see the pg_tle documentation in GitHub.

A community solution

As more applications are built and run in the cloud, developers need the ability to control their destiny and add PostgreSQL extensions without waiting for the cloud provider of choice to add support. Trusted Language Extensions for PostgreSQL provides the built-in safety guardrails to run PostgreSQL in managed and self-managed environments. Together with Supabase and other contributors, we are building a community and look forward to advancing features, tooling, and trusted languages that make it easier for everyone to safely run PostgreSQL extensions.

To learn more, see Trusted Language Extensions for PostgreSQL on Amazon Aurora and Amazon RDS. You can get started by launching a new Amazon RDS database instance directly from the AWS Console.

Flatlogic Admin Templates banner

Better Exception Handling With EntityFrameworkCore Exceptions

I cannot tell you how many times I’ve had the following conversation

“Hey I’m getting an error”

“What’s the error?”


“OK, what’s the message though, that could be anything”

“ahhh.. I didn’t see…..”

Frustratingly, When doing almost anything with Entity Framework including updates, deletes and inserts, if something goes wrong you’ll be left with the generic exception of :

Microsoft.EntityFrameworkCore.DbUpdateException: ‘An error occurred while saving the entity changes. See the inner exception for details.’

It can be extremely annoying if you’re wanting to catch a particular database exception (e.g. It’s to be expected that duplicates might be inserted), and handle them in a different way than something like being unable to connect to the database full stop. Let’s work up a quick example to illustrate what I mean.

Let’s assume I have a simple database model like so :

class BlogPost
public int Id { get; set; }
public string PostName { get; set; }

And I have configured my entity to have a unique constaint meaning that every BlogPost must have a unique name :

.HasIndex(x => x.PostName)

If I do something as simple as :

context.Add(new BlogPost
PostName = “Post 1”

context.Add(new BlogPost
PostName = “Post 1”


The *full* exception would be along the lines of :

Microsoft.EntityFrameworkCore.DbUpdateException: ‘An error occurred while saving the entity changes. See the inner exception for details.’
Inner Exception
SqlException: Cannot insert duplicate key row in object ‘dbo.BlogPosts’ with unique index ‘IX_BlogPosts_PostName’. The duplicate key value is (Post 1).

Let’s say that we want to handle this exception in a very specific way, for us to do this we would have to have a bit of a messy try/catch statement :

}catch(DbUpdateException exception) when (exception?.InnerException?.Message.Contains(“Cannot insert duplicate key row in object”) ?? false)
//We know that the actual exception was a duplicate key row

Very ugly and there isn’t much reusability here. If we want to catch a similar exception elsewhere in our code, we’re going to be copy and pasting this long catch statement everywhere.

And that’s where I came across the EntityFrameworkCore.Exceptions library!

Using EntityFrameworkCore.Exceptions

The EntityFrameworkCore.Exceptions library is extremely easy to use and I’m actually somewhat surprised that it hasn’t made it’s way into the core EntityFramework libraries already.

To use it, all we have to do is run the following on our Package Manager Console :

Install-Package EntityFrameworkCore.Exceptions.SqlServer

And note that there are packages for things like Postgres and MySQL if that’s your thing!

Then with a single line for our DB Context we can set up better error handling :

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

If we run our example code from above, instead of our generic DbUpdateException we get :

EntityFramework.Exceptions.Common.UniqueConstraintException: ‘Unique constraint violation’

Meaning we can change our Try/Catch to be :

}catch(UniqueConstraintException ex)
//We know that the actual exception was a duplicate key row

Much cleaner, much tidier, and far more reusable!

The post Better Exception Handling With EntityFrameworkCore Exceptions appeared first on .NET Core Tutorials.