Implement Drizzle ORM for Development and Deploy to a Fly.io Volume

Instructor: [0:00] To implement our database, we're going to be using Drizzle ORM. Drizzle is a library that lets us connect with any kind of database, define our tables, interact with it, and generate and apply database migrations. We'll be working in an SQLite database, and we'll use the BetterSqlite3 library with Drizzle.

[0:18] Drizzle is a super lightweight and simple serverless database, and it's a good choice for our use case. Let's start setting it up. First, let's install the Drizzle packages. We're going to run npm install drizzle-orm and better-sqlite3. There we go.

[0:34] We also need to npm install, but install them as a dev dependency, drizzle-kit and also the addtypes/better-sqlite3 so we can have typings. Now, since we have all the packages installed, we're going to open the source directory and create a new folder called db. Then we're going to create an index.ts file inside of it.

[0:57] First, we're going to import Database from the better-sqlite3 package. Then, we're going to import drizzle and the type better-sqlite3 database from the drizzle-orm/better-sqlite3 package.

[1:18] First, let's create our SQLite client. We're going to create a new constant called sqlite, and we're going to assign it to a new database. The database class needs a file name as the first argument. Let's first pass an environment variable called database_url, which we can access from the process.env.database_url.

[1:41] This environment variable might not exist, so we need to provide a fallback by using the OR operator or || and then pass a string with a value of ./local.db. Putting the environment variable first will enable us to define a different file name for our production deployment.

[2:00] Now, we're ready to create our Drizzle instance. We're going to export it first, and we'll create a new constant with the name of DB. We're going to set its type to better-sqlite3 database, and we're going to assign it to the Drizzle method. The Drizzle method accepts the database as the first argument.

[2:19] We'll pass sqlite, and then we're going to pass an optional second argument with the value logger set to true. This logger flag is going to make Drizzle show us all of the internal logs that gets outputted as we're executing our SQL queries. If we don't want to debug it, we can either set it to false or not provide the second argument at all.

[2:41] Now we have our DB driver. We're going to be using this DB constant to read, write, update, and delete from our database.

[2:51] In our next lesson, we're going to implement the post-like API, which is going to use this constant to write and retrieve the number of likes, which reminds us that we need to create our likes table now. Next to the index.ts, we're going to create a new file called schema.ts. We could define the schema in the same file, but I think it's a good idea to separate these two. It's much cleaner.

[3:15] To start defining our schema, first we need to import a few things from the drizzle-orm/sqlite-core library. Those things are going to be the SQLite table method, the text, and integer. Then, we can export a constant called likes, and this is going to be our tables. We're going to assign it to the SQLite table method.

[3:40] First, we need to give it a name. Into a string, we're going to pass likes. Then we need to define the columns of our table. We're going to open a new object and define the column name as a key and the column type as the value.

[3:53] For the first column, we're going to define the slug column. We're going to set it to a text field, which is going to be named slug as well. This is also going to be our primary key. This will make it unique so that we cannot save two separate entries for the same blog post. Then we have the number of likes, so we'll just pass it as...

[4:14] We'll just name it likes. We're going to use the integer type and pass the name likes. Additionally, we can set the default to be zero because all new articles start with zero likes. This is enough to keep track of every blog post number of likes.

[4:28] Now, let's go back to the index file and add export likes from ./schema. This will make it simpler for us down the road so that when we import the DB, we can also import the likes table from the same import statement.

[4:46] Now, since we have our DB client, the driver, and the schema, let's create the Drizzle config file. At the root of our project, we're going to create a new file and name it drizzle.config.ts. We're going to import the type config from the drizzle-kit package. Then we're going to export a default object that satisfies the config type so that we have typings.

[5:14] If we hit Ctrl+Space, we're going to see all of the available configuration options. Let's start with the schema. The schema is going to be a string that points to the schema file that we just created. That's going to be ./src, since we are at the root of the project, db/index.ts.

[5:33] Then we're going to define the out directory, which is going to be the location where Drizzle is going to generate our database migration files. We'll give it a value of ./src/db/migrations.

[5:48] These are very important. Every time we make changes to the schema of our database, Drizzle is going to generate SQL queries that, when applied, are going to change the shape of our production database.

[6:02] Because we're not going to be working on our production database while we're developing, we need to find a way to apply the changes that we make during development to our production database. That's why migration files are really useful.

[6:15] Then we're going to set up our driver, which is going to be better-sqlite3. We can set the verbose field to true, and lastly, the dbCredentials field, which is going to be an object.

[6:28] We're going to pass the url and set it to process.env.DATABASE_URL or the ./local.db, the same value that we set in our index.ts file. Of course, I have a typo. It's not better-sqlite3. It's just better-sqlite.

[6:47] We talked briefly about database migration files. We learned that Drizzle can generate migrations for us, but it's not going to apply them automatically. We need to make sure that our server applies the migrations.

[7:00] Let's go back to the index file. At the end of the imports, we're going to bring in the migrate function from the drizzle-orm/better-sqlite3/migrator package. We're going to invoke this method after we create our DB instance.

[7:18] Migrate will pass the DB as the first argument, and then it needs a configurations object with the value of migrationsFolder. Now we need to point to the migrations folder that we defined into the Drizzle config, which was src/db/migrations. Bear in mind that we do not add ./ in this case, like we did here.

[7:41] With this line, Drizzle will try to apply all of the migrations we have in our migrationsFolder every time we start our Astro Server and access the DB constant. When we push a new feature to the production, we won't need to SSH into the Docker container and manually apply the migrations. Cool.

[8:01] Before we run the project, we need to define the npm script that will generate the migrations for us. Let's open our package.json file and add a new script below the astro one. We're going to name it db:generate, and we'll give it a value of drizzle-kit generate:sqlite.

[8:22] This command will read our schema and pass migrations and it will generate new migrations only for the latest changes. Let's run it and inspect the output. We'll do npm run db:generate.

[8:37] First we can see the query, "Create table if not exists" DB migrations. This table keeps track of all of the applied or new migrations, and it tells us that the new migration file is saved right here.

[8:50] If we open the Explorer, we're going to see the migrations folder that also has a meta folder. The journal.json file keeps track of what migrations has been applied. The 0000_snapshot.json holds a snapshot of how the database schema should look like for the first migration.

[9:11] These files are really important to Drizzle. We don't need to fully understand them. We just need to make sure that we track them in our Git repository. Aside from the migrations, we can also see a local.db file at the root of our project, and that's our database.

[9:25] Drizzle also gives us a Studio app that we can use to browse the data in our database. To run it, we need an npm script. Let's go back to the package.json file, and below the db:generate create a new script called db:studio. This one will invoke drizzle-kit studio. There we go. If we run this now, npm run db:studio, it's going to run a server on the port 4983. Let's check it out, localhost:4983.

[9:56] Initially, we can see a list of all of the tables in our database. If we click on the likes, we're going to see the slug column and the Likes column that we defined previously, but it's empty for now. We are going to start writing likes in the next lesson.

[10:12] Now we have a local database and we can browse it. Great, but we still don't have it configured for production. Let's do that. Since SQLite doesn't need a server and works out of a single local.db file, to have that file persisted in our production server, we need to keep it in a volume on Fly.io.

[10:31] First, we need to create that volume. Then we define that volume in our Fly.io app. Lastly, we need to define the database environment variable to point to the volume for our production build. Let's start.

[10:44] To create a volume in Fly.io, all we need to do is run fly volumes create DB. We're going to agree to the first message, and we're going to pick the same location as our Fly app, which was whichever is closer to us.

[10:58] Now Fly has created a persistent volume called DB, and it's going to attach it to our app at the /mnt/db location. Let's define our new volume.

[11:09] We're going to open the fly.toml file, and below the http_service we're going to create a new entry called mounts. We're going to set the source to DB, and we're going to set the destination to /mnt/db, as we mentioned previously.

[11:28] What this does is it tells Fly.io how our infrastructure is going to look like. Previously, we had the http_service, which defines that we want to spin up an HTTP service or an ordinary server and expose the internal port, which is going to be 4321. That was our default Astro port.

[11:49] Aside from the http_service, we now tell Fly.io that we also are going to have a persistent volume where the name is DB. That's how we created it, and the destination is /mnt/db by default. Now our Astro Server is going to have access to that persistent volume.

[12:09] All that's left to do now is to define the DATABASE_URL environment variable inside Fly.io.

[12:16] To do that, we're going to open the terminal again and we're going to run fly secrets set DATABASE_URL=sqlite3, for the protocol, ://, to start typing the actual destination, so /mnt/db. That is our persistent volume, and then /production.db, or you can name it whatever you like, db.db, if you'd like.

[12:46] We can hit Enter now. The Fly command is going to attach to our existing machine, and it's going to define the environment variable.

[12:54] You might have guessed that now our DATABASE_URL on our server points to something else. When we run the server, process.env.DATABASE_URL is going to be defined, and it will point to that persistent volume that we just created.

[13:09] On production is going to use the production database, but for development, since we don't have the DATABASE_URL defined, it will fall back to local.db. That's how we can separate the production and the development databases.

[13:23] To make sure that you added the secret, you can open your Fly Dashboard, click on your app, and click on the Secrets. You'll be able to see all of the secret that you have here and change them or delete them.

[13:36] Bear in mind that you won't be able to see their actual values, so in future, when you want to add more secrets, make sure you have them saved somewhere. We should be good to deploy the app now.

[13:47] If we push the code to main, it will trigger the action that builds our blog and deploys it to Fly.io. That's how you implement Drizzle and deploy it to production.

[13:58] Let's do a recap now. Drizzle ORM is a library that allows us to connect, define, and interact with any kind of databases. To set it up, we needed to install its packages and instantiate it into its own file in src/db/index.ts.

[14:15] We defined the shape of our database through the Likes schema, which we defined in the src/db/schema.ts file. We just needed a single table that kept track of the article slug and the number of likes.

[14:29] Then we defined an NPM script in package.json that generated the migration files for us, which we applied when we first used the DB instance after deployment.

[14:40] We also created one that starts the Drizzle Studio app, which allowed us to browse our local database. Drizzle also has its own config file at the root of the project, in which we defined the schema, the migrations location, the driver, etc.

[14:55] To prepare it for production, we needed to create a volume in Fly.io and mount it to our app. We defined the mount point in the fly.tomal file. Then we defined the DATABASE_URL secret in Fly.io by running fly secrets set DATABASE_URL.

[15:13] Now when our blog gets deployed, the process.env.DATABASE_URL points to the production database, not the local.db one. After pushing everything to our branch, the GitHub Action build our blog created the Dockerfile and deployed our blog to Fly.io.