Transcription

Modern web development sucks. We have amazing tools that solve every problem. And technically, it's never been easier to build a billion dollar failed app idea. The problem is that by the time you deploy it, you end up paying 20 different YC-backed startups to use their fancy shovels. You think you need an in-memory cache database to handle your load of 5 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 Next.js in 2016. But what if I told you, you could throw all of these shovels in the incinerator, and use nothing but PostgresQL? 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 Postgres. 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 Postgres special, and why not use something like MySQL or SQLite? Well out of the box, Postgres 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 Postgres 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 vs NoSQL, and one of the key selling points for NoSQL 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 Postgres 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 WHERE clause, and then this arrow syntax to filter and access fields inside this unstructured data. That feature's built right into Postgres, 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 Postgres 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 Postgres 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 ORMs. Developers already love Postgres, 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 Postgres database. Then take your connection details to a BS Code extension like SQL Tools and connect to it locally. Now we're ready to do some really crazy stuff with extensions. Developers only want one thing, and it's disgusting. A cron job, which is code that runs on a schedule. Now normally you would have to edit the cron tab file in Linux to achieve this, or pay a SaaS to do it for you, but Postgres will actually give you a cron job for free. If we install the pgcron extension, we can then write a SQL statement that runs a cron schedule. Give it a name, define the schedule, and then it will run this 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 Redis or Memcache. Well in Postgres, you can create your own poor man's Redis by using an unlogged table as a cache. This prevents write-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 and ACID. 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 Postgres config to store it in a shared buffer, which will keep the data highly available on RAM, and then summon the auto-vacu-demon to avoid bloat. And maybe throw in pgcron 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 Ragstack, you might consider paying for a vector database. But what you could do instead is just install the pgvector extension. It provides a vector data type that allows you to store multidimensional 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 dataset and vectorize it all entirely in SQL code. But speaking of vectors, a more traditional feature you might need is a full-text search engine, where users type into a search box and get results even if they have typos. Postgres actually has built-in support for this with the tsvector 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 at, which is the text search operator. And now you don't have to pay for expensive tools like Algolia or Elasticsearch. If you're sick of writing SQL code though, one thing you might do instead is use GraphQL. The magic pgGraphQL extension can transform your database into a GraphQL API. And that makes it incredibly easy for any programming language to understand and query your database. All you have to do is add the extension and then start writing GraphQL resolvers directly in your SQL code. And best of all, no additional servers, libraries, or paid SaaS middlemen are required. But what if you're building a real-time application using tools like Firebase or Supabase? That's a difficult feature to roll out on your own, but a library called ElectricSQL fixes this. It's not a pg extension, but rather a sync layer, which would sit in between your database and your frontend 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 auth, usually by writing some crappy JavaScript instead of paying a SaaS to do that for them. But real men roll their own auth entirely in Postgres. And that's possible when you combine a couple of extensions like pgCrypto and pgJSONWebToken. Like if we have a table with a username and password, we can use crypto to hash the password and also generate a salt. This 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 JSONWebTokens. And pgJWT makes it really easy to cryptographically sign a token on the server. And now that we have that, we can implement row-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 Postgres 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 horses table will only give us the horses 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 SaaS, an option you'll want to check out is pgMoonCake. Basically, it turns Postgres into a time series database, and it's extremely fast and efficient because it adds support for column store tables with DuckDB 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 Postgres, which automatically turns your database into a RESTful API. After you get it set up, you'll be able to go into the browser, then navigate to localhost followed by a table name, and it will automatically query your Postgres 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 run in Postgres. And you really can't get your data any closer to the UI than that. Congratulations, you're now officially a certified Postgres RISCON. 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 gonna go ahead and upload it anyway. Thanks for watching, and I will see you in the next one.