Use this to lock down the schema if you have JSON or JSONB columns

pg_jsonschema lets you validate schema for your JSON and JSONB columns. In this video, Tyler Shukert walks you through how to restrict the schema of your JSON and JSONB columns to ensure the integrity of the database is kept.
Learn more ways to lock down your schema: json-schema.org/learn/getting...
JSON and JSONB columns allow you to be flexible with what type of data you want to store, but you might still want to restrict the schema of the JSON to some degree. pg_jsonschema is a light-weight Postgres extension that allows you to define a schema for your JSON and JSONB columns to ensure database integrity.
00:00 Intro
00:34 Go over the table
01:15 How to use pg_jsonschema
04:35 Apply constraints on table
06:02 Outro
💻 Videos to watch next:
▶ How to support unstructured data in Postgres with JSON columns: • How to store and query...
▶ FASTEST way to host a Postgres database (2024):
• FASTEST way to host a ...
▶ Create PostgreSQL Functions with Supabase: • Create PostgreSQL Func...
👇 Learn more about Supabase 👇
🕸 Website: supabase.com/
🏁 Get started: app.supabase.com/
📄 Docs: supabase.com/docs
🔔 Subscribe for more tutorials and feature updates from Supabase: / @supabase
📱 Connect with Us:
🐙 Github: www.github.com/supabase
💬 Discord: www.discord.supabase.com/
🐦 Twitter: / supabase
▶ Instagram (follow for memes): / supabasecom
ABOUT SUPABASE:
Supabase is the open source Firebase alternative. Supabase provides a full Postgres database for every project with pgvector, backups, realtime, and more. Add and manage email and password, passwordless, OAuth, and mobile logins to your project through a suite of identity providers and APIs.
Build in a weekend, scale to millions.
#Supabase #AppDevelopment #RealtimeApps #DeveloperTools

[@Supabase]

Thanks for tuning in! JSON Schema has an amazing docs that walk you through various examples of how you can use it. json-schema.org/learn/getting-started-step-by-step

[@skrobonja32]

Robert Downey Jr. always has killer tips! 💪Great tutorial!

[@rembautimes8808]

Wow - thanks for sharing. Was looking for this video and as I have used RXDB before I can see how it works. Thanks a lot.

[@EmielvanGoor]

Exactly what I needed, keep the tips coming! Loving Supabase (and PostgreSQL) everyday more and more! Thanks!

[@dshukertjr]

Thanks for the positive comment! More Postgres content coming on this channel!

[@luisandrade1291]

Please tell me it has support for your typescript types codegen.

[@tylershukert3621]

At the moment no, but that is an awesome suggestion! Will pass the feedback to the team.

[@ipranay]

This is super helpful!! Any performance concerns with this extension? (esp if you have a large object schema)

[@Supabase]

Generally you shouldn't notice much difference as the extension is very performant, but of course this depends on a lot of factors. You can find some benchmarks here: github.com/supabase/pg_jsonschema?tab=readme-ov-file#benchmark

[@andylee5969]

Great feature and tutorial :) Anyone think it’d be useful to have a tool that can convert these schemas into zod object schemas?

[@mrbjjackson]

Have you any tips for having types in our code when processing jsonb data?

[@yarapolana]

Adding types support for the json in the javascript sdk anytime soon?

[@dshukertjr]

That is an excelent idea. We don't yet have type support for the JSON/ JSONB columns from the pg_jsonschema definition, but will pass the feedback to the JS SDK team.

[@williamliu796]

What is the use case for this? If your schema needs to be strict, isnt it better to create other tables?

[@dshukertjr]

There is no single right or wrong in development. If someone chooses to use JSON or JSONB column, they still might want to restrict what data comes in, as by default JSON and JSONB type columns accept a very wide range of schema. Even a plain string or integers are valid JSON types, so this extension helps the developers to ensure those values do not come in.

[@takeshikriang]

Thank you for sharing this. As a self-taught developer, I have learned a lot with Supabase. Can you create a Postgres tutorial as well?

[@Supabase]

Thanks for the kind words! What type of content would you like to see in the Postgres tutorial you wish?

[@takeshikriang]

@@Supabase For example, something related to multi-tenancy in Postgres. It is tough to find such quality content.

[@AymericRabot]

How do you handle evolving schemas? migrations?

[@Supabase]

You can simply update the check constraint to match the new schema.

[@AymericRabot]

@@Supabase how do you handle the existing records and upgrade them to the new schema?

[@AmitKapadiaUK]

Great! How do you do this via the dashboard gui?

[@dshukertjr]

Defining JSON schema is currently not supported in the GUI, so you would have to define it from the SQL editor. I will pass the feedback to the frontend team!

[@980107105843]

wow you are still updating this series , cute , good luck , i started this whole project through the login profile videos 😆!

[@dshukertjr]

Is it the Flutter video you are talking about? Thanks for handing out in our KZbin channel for such a long time!

[@980107105843]

@@dshukertjr Yes its the flutter ones, i think its like 3/6 months ago, idk how much info i can give about this but i am recently creating a private POS , so i went thru ur github pages and look through how u handle errors and handle page transitions, routes etc. Just wanted to let u know was great help :)