Use Range to make sure your reservations don't overlap in Postgres!
HTML-код
- Опубликовано: 5 авг 2024
- In apps involving start and end times, such as reservation or calendar apps, using range types in Postgres can bring many benefits, such as querying overlapping events or preventing overlaps under certain conditions.
Query ranges using the Supabase client library: supabase.com/docs/reference/j...
Range columns in Postgres represent the start and end of quantifiable data such as numbers, dates, or timestamps. Using ranges, you are introduced to advanced query operators along with exclude constraints that ensure no ranges overlap with each other under certain conditions, ensuring that reservations are protected at the database level.
CHAPTERS:
00:00 Intro
00:26: Range column introduction
01:45 Range type operations
02:37 Adding a constraint to prevent overlaps
04:00 Expanding the constraint to prevent overlaps only under certain condition
05:57 Outro
💻 Videos to watch next:
▶ • How to store and query...
▶ • Make your queries 43,2...
▶ • Self-host Maps with Pr...
👇 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 
Наука
I think I will go back to creating Flutter content for the next video. Any other Flutter fans watching this video? What content would you like to see?
Yes please, any idea for implementing local first apps
❤ Complex RLS or RBAC for flutter app (e.g admin - mod - etc)
I like the Postgres stuff
@@cogulad1067 Sounds good. Will explorer the options!
@@AmmarQ. Love it! Will add it to my list!
The best thing to ever happen to postgres is supabase. Without supabase a lof of developer would've missed postgres.
elite level of pg sql. nice. thanks learning a lot looks like not everything needs to be code. put as much as possible in the db. ( should be faster. )
Yup! The power of Posgres 💪
Perfect timing. I was just about to add some starts_at and ends_at columns! This is a nice feature to learn about!
Awesome!
consider it twice my friend. you may need these columns more then you think
@@LawZist can you elaborate?
@@LawZist Why is that? Genuine question
Im working at a saas company that provide orders delivery optimizations. we work with times A LOT. many times we need to do some logic only on the start time or the end time, do timezones manipulation add or substract times, etc.. im not sure that the range can fit for all these use cases. Im not saying not to use range, im just saying that you need to make sure it is the right solution for your needs, moreover many times we need to do the overlapping check in the backend and it is a very simple check to do
I’ve struggled with this for a long time. Thanks
I'm glad to hear that you found it helpful! Let me know if there are any other things that you would like us to turn into a video!
Perfect for my project
This was so helpful, thanks a lot!
Glad it was helpful!
Man, the more I get into Supabase, the more I realize Firebase is just not good enough.
Supabase is self contained and it's for all seasons.
Power of Posgres🎉
noSQL blows. the less optimised your DB is with firebase the more you are going to get charged. not to mention if you were using the postgres geo points extension, you can calculate all your distances without paying for google maps api access.
@@mrrolandlawrenceI don’t mind nosql in general like mongo, but firestore just has way too many limitations that you need to work around. It’s fine for tools or simple apps but anything bigger it is a pain
Awesome! In the recent videos you published you talk about constraints, which I think are great to be implemented at database layer. Do you have any advise to handle properly exceptions raised by constraint on client side? How client side can recognize the violated constraint and display the correct friendly message? (with javascript sdk would be perfect 😇)
The exception raised on the client will contain the name of the constraint that triggered the exception, so looking for the constraint name in the exception and showing different error message depending on the message might be the best way!
what id love to see is more on the realtime. how to keep your client side JS object the same as your postgres view. including what happens if you miss a channel broadcast because of say network connection issue. from what i understand from the docs if you miss a broadcast message - too bad. there is no queuing for subscribed clients to catch up on?
Correct. Currently if you miss a broadcast message there is no way of re-fetching it. If it's a data that needs to be re-fetched, you might need to store the information on the DB so that clients that go offline could re-fetch it.
i'm with Swift and SwiftUI. I have created my brand new project with Supabase as my backend, i'm on week 2 and i love it. I find the Supabase SDK very lightweight compared with Firebase spyware, also very performant and my Previews work wow!... The Supabase Dashboard is brilliant and easy to understand, the only part i need to work on is to learn the basics of SQL as i have never interacted with this programming language and looks like is a must. But even without any SQL knowledge, the default templates does the job with a little of twist here and there. Is .range also used for pagination? For example in SwiftUI we have a List of items and by default it returns 1000 rows from Supabase. Can this be paginated with .range ? thank you
The `.range()` method in the client SDK and the range data-type that I'm talking about are actually two different unrelated things! The `.range()` method is indeed used for pagination!
What would the tstzrange constraint look like if you want to allow maximum overlaps at any certain time.
I had to implement this about 15 years ago in Java bizlogic. The use case was to have max 3 top ads active on a webpage at any time for the best sales. They were not assigned to a spot / table only limited by the maximum amount of overlaps at any point in time.
Would that be possible to achieve with a postgres db-constraint? - Surely the constraint introduced in the video could be used in a work around / good enough manner by having kind of 3 spots for the max 3 sales but that wouldn't produce a the same UX.
Great question. Without going into too much details, I think something like this could be achieved using a trigger. Set a trigger on the table when a new row is inserted (or updated if range does get updated), and trigger a function that checks for maximum of 3 overlaps. You can raise an exception from the triggered function to rollback the isnert if condition is not met.
supabase.com/docs/guides/database/postgres/triggers
👍🏻
When can we expect to see support and documentation for these column types in the JS API?
The JS and other client library already supports them, and you can find docs for them in the reference docs! supabase.com/docs/reference/javascript/overlaps
It's cool, to enforce a business rule that should be on domain layer. But is that really useful to put those kind of constraints on the database ? It blurs the lines between data physical integrity and business logic.
It's not putting business logic. It's just placing guard rails so that the data stays clean. You should still do validations on your application before inserting the data into the database.
It’s good to have this protection in your DB layer as a complement for your application logic. This means if you see a constraint error in your logs, someone screwed with your application code or might by trying to do a manual update in a wrong way.
I have never heard of those fields, thanks for that! I’d like to use those features, but postgres syntax is so strange to me.
@@dshukertjr Actually it is. Data integrity has nothing to do with business logic, it's 2 different aspects. Imagine that your business logic change, you introduce an external flag, should you relax physical constraints on data ? It does not make sense to me. The DBMS should only throw errors if physical integrity is compromised by a transaction. Focus on building and testing your product, not adding more validation layers.
@@BonBaisers Yup, data integrity and business logic are two completely different things. Different applications have different requirements, so there is no single "correct" answer here, but it's always a good idea to start with more constraints if you are just getting started. One could also argue that there could be multiple applications talking to the database, and it is hard to ensure that all of those applications comply to the same set of rules, where as if the data is secured at the database layer, someone could make a mistake at the application layer and still your data is safe!