Hosted PostgreSQL with Neon

[00:00:00]
>> Speaker 1: The next thing we want to do is set up the database We want to create a database, set it up, connect to it, and do all that stuff So let me make sure I'm on the right branch I'm on the right branch here, so you can stay on your branch You don't need to check out to whatever branch I am

[00:00:00]
If you want to be where I am, you can check out to, I believe, Lesson 3 I just made a new branch for 4 where I'm going to start writing this code, but you can start there if you want So, following along with me here What we want to do is create a database As I said before, it's just Postgres

[00:00:00]
So if you have Postgres on your computer, you can use that if you're familiar with it I believe it's probably Postgres 16 or so, yeah, 16 If you already have that and that's what you're familiar with, feel free For everyone else who doesn't want to do that or doesn't have it, I've already figured it out for you

[00:00:00]
The first thing you're going to do is click here and go to neon.new This is going to bring you to this website Neon is a company that basically provides Postgres databases hosted for you, completely managed They're just one of many, but they happen to be one of my favorites They have this cool product called Launchpad

[00:00:00]
If you get to this website, all you need to do is click the button that says "Try in the Browser" Click that button It'll verify that you're a human I am indeed a human And it's going to set up a database for you You can see right now it says, "This is an unclaimed database It will expire in 72 hours unless you make an account." That's more than enough time for what we need, so you can just copy that or keep this page open

[00:00:00]
That's going to be your database URL So there you go, a free database, no account, no nothing, ready to go Let's take that database URL and put it in our code The first thing we want to do is make sure we have our .env file and set up that database URL I already have it, but I'm going to set up a new one from scratch so we can all do this together

[00:00:00]
I'll say DATABASE_URL and set it to the database URL I copied That's it In our .env file, not to be confused with the .env.example or .env.test, just the .env The next thing is we want to create the connection to the database Before we do that, I want to talk a little bit about pooling

[00:00:00]
It's a simple concept, but basically, when we connect to the database because we're making a long-lived traditional server, we don't want to have to connect to the database every time a new request comes into our server We want to reuse different connections because there's a limit on how many connections the database can have

[00:00:00]
Creating connections is quite expensive computationally, so we want to reuse as many connections as we can across requests, so we'll create a pool Pooling is a simple concept It's essentially just reusing the connection we've already established for our database Some problems with pooling exist, but one of the biggest issues we'll have is when using Node watch for development, which reloads our server whenever we change a file

[00:00:00]
What ends up happening is we'll get memory leaks for those pooling connections because the server will restart and we'll lose the connection, although the previous pool is still open Eventually, you'll run into an error locally saying you've reached the limit of how many connections you can have on a pool

[00:00:00]
We'll solve for this by creating our connection We want to go to sourcedb - we don't have that, we need to make it Oh, I'm sorry, we already made it because we did the schema In that same folder, we'll make a new file called connection.ts We'll import Drizzle from drizzle-orm/node-postgres

[00:00:00]
We'll import pool from pg, which stands for Postgres We'll import our entire schema using import * as schema We'll import our ENV object from our .env file, a helper called isProd to let us know if we're in production or not, and another thing called Remember Once we have all those, let's create our pool

[00:00:00]
I'll make a function called createPool that returns a new pool with a connection string set to ENV.DATABASE_URL Then we'll create a client If we're in production, we'll simply create a new pool Otherwise, in development, we'll use Remember to create a singleton - essentially a cached version of the value to ensure we never recreate it

[00:00:00]
We'll then create the database client using Drizzle, passing in the client and our schema We'll export it and set a default export This is more than good enough to get started, primarily addressing development and watching concerns In production or with a service database like Neon, which supports HTTP-based databases, you wouldn't need this complex connection management at all.