modern web development sucks we have amazing tools that solve every problem and technically it's never been easier to build a billion dooll failed app idea the problem is that by the time you deploy it you end up paying 20 different ycb startups to use their fancy shovels you think you need an in-memory cache database to handle your low to five users you need a cron service you need authentication performance monitoring analytics serverless functions and the list goes on it's no coincidence that America's homeless population started to rise immediately after the release of nextjs in 2016
but what if I told you you could throw all of these shovels in the incinerator and use nothing but postgressql yes the relational database to achieve 90% of your web development needs in today's video we'll look at 11 useful unorthodox and just plain weird ways to use postgress by the end of it we will have built a full stack application using nothing but an open source database but first of all what makes postgress special and why not use something like MySQL or SQL light well out of the box postgress provides Advanced Data types like binary
Json arrays key value stores and even geometric types to Define shapes but more importantly it's extensible and you can even create your own custom data types over the years this has led to a massive ecosystem of extensions where developers have given it all kinds of crazy new features it's just like modding a game where you take something that sucks like Civilization 7 and install a bunch of mods to make it playable now before we get into the first example I first want to warn you that just because you can do something in postgress doesn't mean
you should before you go and cancel all your shovel subscriptions don't forget to think critically and make sure you use the right tool for the job one of the great debates among web developers is SQL versus nosql and one of the key selling points for no SQL is that you can work with unstructured data like objects with Dynamic data that might be different for each record you can't do that in SQL because every table has a fixed schema well actually that's not true because postgress supports binary Json in this query you'll notice how we create
a table with a Json B type and then insert some values into it as raw Json strings so pretty cool and believe it or not we can actually query this data now we can use a select statement followed by a wear clause and then this syntax to filter and access Fields inside this unstructured data that feature is built right into postgress but now we're going to implement some really crazy stuff thanks to extensions first though you might be wondering where do I get a postgress database it's free and open source to host locally but an
even easier option is neon the sponsor of today's video they may not condone all the crazy stuff I'm showing you in this video but they provide postgress on a serverless platform that makes it really easy to Kickstart and scale your projects not only do they have an awesome free tier but there's also developer friendly guides for all the biggest Frameworks and OMS developers already love postgress and neon makes it even easier to work with by providing features like branching which allows you to easily create different data workflows for development testing and production and will automatically
scale up with zero effort as your app grows give neon a try for free right now using the link below to create a postgress database then take your connection details to a VSS code extension like SQL tools and connect to it locally and now we're ready to do some really crazy stuff with extensions developers only want one thing and it's disgusting a Chron job which is code that runs on a schedule now normally you would have to edit the cron tab file on Linux to achieve this or pay a sass to do it for you
but postgress will actually give you a Cron job for free if we install the PG cron extension we can then write a SQL statement that runs a KRON schedule give it a name Define the schedule and then it will run the SQL statement on that schedule like you might want to delete or aggregate a bunch of data on a daily basis that's cool but another thing you think you might need but probably don't is an in-memory cache database like reddis or mcash well in postgress you can create your own po man's redus by using an
unlog table as a cache this prevents right ahead logging where changes to the database are written to a log file before being applied to data files and that provides the D for durability in asset and that means if your server crashes unexpectedly committed transactions will not be lost however you don't really need that for a cache and disabling it provides better performance then you can update the postgress config to store it in a shared buffer which will keep the data highly available in Ram and then summon the autov vacu demon to avoid bloat and maybe
throw in PG KRON to automatically delete any entries with an expired time to live but this wouldn't be a proper fireship video if I didn't mention Ai and if you're building an AI app that uses rag stack you might consider paying for a vector database but what you could do instead is just install the PG Vector extension it provides a vector data type that allows you to store multi-dimensional data then you can do things like make a query for nearest neighbors based on L2 distance and you can even take things a step further with the
pgai extension not only does it handle Vector embeddings but it allows you to load your own data set and vectorize it all entirely in SQL code but speaking of vectors A more traditional feature you might need is a full Tech search engine where users type into a search box and get results even if they have typos the postgress actually has built-in support for this with the TS Vector type it'll take a string like this and break it up into a bunch of smaller parts then we can create a generalized inverted index for better performance which
gives us the ability to then Rank and query the most likely results from the data using double app which is the tech search operator and now you don't have to pay for expensive tools like algolia or elastic search if you're sick of writing SQL code though one thing you might do instead is use graphql the magic PG graphql extension can transform your database into a graphql API and that makes it incredibly easy for any programming language to understand in query your database just add the extension and then start writing graphql resolvers directly in your SQL
code and best of all no additional servers libraries or paid SAS middlemen are required but what if you're building a real-time application using tools like Firebase or superbase that's a difficult feature to roll out on your own but a library called electric SQL fixes this it's not a PG extension but rather a sync layer which would sit in between your database and your front end code to ensure that the end user always has fresh data and that means you don't have to mess around with websockets or write a bunch of broken code to automatically fetch
data when it changes and as an added bonus it works great with neon but another thing developers like to brag about is rolling their own off usually by writing some crappy JavaScript instead of paying a SAS to do that for them but real men roll their own off entirely in postgress and that's possible when you combine a couple of extensions like PG crypto crypto and PG Json web token like if we have a table with a username and password we can use crypto to Hash the password and also generate Assa that ensures that when our
database gets hacked the attacker won't have access to the plain text password and now to verify a user's login credentials we can write a simple query that matches the username to the hashed password with the Crypt function but the other thing we'll need is a way to validate user sessions on the server and one way to do that is with Json web tokens and PG JWT makes it really easy to cryptographically sign a token on the server and now that we have that we can Implement roow level security where the end user can only read
and write data that's actually owned by their username this is a built-in feature in postgress where we create policies that Define rules about how our data can be accessed and in this case we'll verify the current user's token before executing any queries and that means selecting everything from the horse's table will only give us the hes owned by that user at this point we have a pretty complex application and we should start collecting some analytics data instead of Google analytics or another paid SAS an option you'll want to check out is PG moon cake basically
it turns postgress into a Time series database and it's extremely fast and efficient because it adds support for column store tables with duck DB execution and all this data can be dropped into a cloud storage bucket and or visualized with other open source tools like grafana that's cool and all but we'll never actually get any analytics data if we don't make our data available on the internet to achieve that you might think you need to build a restful API with some programming language from scratch but another option is post rest which automatically turns your database
into a uccessful API after you get it set up you'll be able to go into the browser then navigate to Local Host followed by a table name and it will automatically query your postgress data and return it in Json format on top of that you can do all kinds of other stuff like filtering pagination authentication and so on and now the final step to build a true full stack application is to store some HTML CSS and JavaScript in the database in fact there are even some crazies out there getting react server components to wrun in
post grass and you really can't get your data any closer to the UI than that congratulations you're now officially a certified postgress Rison big shout out to Neon for reviewing and sponsoring this video and although they told me they don't approve of all the crazy stuff I showed you in this video I'm just going to go ahead and upload it anyway thanks for watching and I will see you in the next one
