This course has been updated! We now recommend you take the AWS for Front-End Engineers (ft. S3, Cloudfront & Route 53) course.

Check out a free preview of the full Zero to Production Node.js on Amazon Web Services course:
The "The Database Code Demo" Lesson is part of the full, Zero to Production Node.js on Amazon Web Services course featured in this preview video. Here's what you'd learn in this lesson:

Kevin looks at how the database is currently configured in the TodoMVC++ application. The .sequelizerc file feeds the default configuration and migration paths to Sequelize. The database.js file contains the specific development, testing, and production server information. Kevin also spends a few minutes explaining migrations and looks at one of the generated files.

Get Unlimited Access Now

Transcript from the "The Database Code Demo" Lesson

[00:00:00]
>> [MUSIC]

[00:00:03]
>> Kevin Whinnery: What I'd like to do now is take you through some of the database code in the sample application and talk about how some of these things are laid out. I know we've kind of dug into this a little bit but I want to take a moment to sort of step you through the key bits that make this go Todo MVC application.

[00:00:25] So, the first thing I'm gonna point out is this file the SequelizeRc.file at the at the top level of the directory. So, when you're using the sequelize CLI to execute commands in your dev environment, this file configures some of the key bits about that experience, like where your database configuration is going to live, when the CLI tool generates migrations, where it's going to put those migrations, where it's gonna look for models that have been defined, or that will be defined using the CLI.

[00:01:05] So, this file is there for when you do execute those sequelize CLI commands at the top of the directory. It's gonna take these settings into effect. The other places where you'll find database related logic in the application is here in the DB directory. Sequelize requires database configuration to be specified in kind of a specific way, which is kind of incongruent with how we're doing configuration elsewhere in the app.

[00:01:38] So we have this database.js file which spoon-feeds sequelize configuration in the format in which it is more comfortable. So we actually read in the real configuration values from our config setup as we've been doing already so far, but then we populate these things like the database URL and the dialect that we use for sequelize here.

[00:02:03] The other bit you'll notice is the migrations folders. So this is where, when you generate a new model or you generate a new migration which mutates the state of the database from one one state to another, those files are gonna be generated here. Much like other migration systems that you might have used in other environments, a migration file name is built of a couple different bits.

[00:02:29] The first is a time stamp, which is the actual you identifier of record for this migration, and this is how the sequelize or the ORM engine knows which migrations have been applied and which migrations have not been applied. So this is important and is generated by the by sequelize when you create a new migration.

[00:02:53] And then the second part is simply descriptive. So create to do could be any string, fluffy bunny one two three. It doesn't really matter. It should be descriptive to say what the migration is about. So, second part of the migration is for humans. And every migration exports a JavaScript object that has two functions on it.

[00:03:17] One is called up and the other is called down. So when we are migrating the database to the next version the up function is called, and that function is passed a sequelize query interface which has methods that let you create tables or add columns to existing tables. So as you're mutating the data model over time, you'll be able to specify these functions to go up and down.

[00:03:46] So here right now this application only has one migration, but all of you are going to change that in just a little while by updating the data model a little bit. So we're creating a new table called Todos for our to do items. And then we're also specifying the down command so when we're rolling back this migration, we're just going to drop the table that we created.

[00:04:11] So when you generate migrations to update the data model you'll be using these migration files to do it. Now the other place where you'll run into, [COUGH] database code in the application is in the models folder. And there's a couple of things that happen here, which are kind of a twist on how you'll see things structured in the sequelize documentation.

[00:04:37] So, this file, db.js, creates a single instance of a sequelize object, and that sequelize object takes in the constructor, a postgres database URL, that will have the username and password embedded inside of it. So this is where we're authenticating against the database and we're also passing in the options for that connection to the database which were loading in from our configuration.

[00:05:10] And in our case, the configuration that we're passing in is pretty much the defaults that sequelize provides. The development postgres URL is configured here, and these database options are arguments to the sequelize constructor saying we wanna use the postgres dialect. By default, we don't wanna log every SQL statement out to the console, although in the development environment you can see that we actually override that and say yes we do want to log SQL statements out to the console.

[00:05:46] And then we also configure sequalize's connection pool, which specifies how long a connection is held that's idle before it's deleted from the pool. And then the maximum number and the minimum number of concurrent connections to the database that are allowed. And those are the default values for sequelize, which you can tune to your requirements if you feel that that is necessary.

[00:06:19] So, this database object is used to define all the other models in our application, and right now that there is only one. We have a to do model, where again we're going to be requiring the sequelize module cuz we're gonna need some some class-level variables off of that as we define the model.

[00:06:41] And here we're going to create a new model. And right now the only attribute we're adding is title. Although the models by default also have an ID,which is an auto-incrementing a primary key in the database, and it also has a created at an updated at time stamp, which is added automatically by sequelize.

[00:07:04] So, we export that model object and then that model object is what we use elsewhere in our application to actually do queries and update data. So, here in the Todo's controller, we require that model and when we fetch all to dos we use the query interface attached to the model to find all the to dos with a certain limit, and then we can find by ID, create and destroy, and do all those other operations within our back-end API here.

[00:07:38] So, this is kind of the interface that we have put together. We're gonna be expanding on this data model a little bit with a migration. Some of the to be aware of here, so rather than something like Active Record where the sort of canonical source of truth about the current state of the model and the properties that it supports lives in the database.

[00:08:10] In the model itself, you don't actually configure what all the properties are, like you might configure validations and other logic about the model, but you don't sort of enumerate here all the properties that are a part of the model. In sequelize, you actually do have to do that.

[00:08:24] So, as you're creating migrations that mutate the state of the underlying tables that back your models, you'll have to keep that in sync with what is actually defined here in the model. So if you create a migration that adds a property to a model, you'll have to make sure to add that same property here in your model declaration.

[00:08:47] So one thing that is potentially somewhat onerous, it's that way in other ORM systems, but again, if you're coming from Active Record that might be a little different than what you're used to. Now, when we had you set up your development environment, the two command line utilities that you set up were the sequelize CLI and the CLI.

[00:09:14] We installed those globally. So the sequelize command line utility, if you just run sequelize
>> Kevin Whinnery: --help, you'll see all the different generators and other options that the command line interface gives you. And the help text is actually pretty good. That's where I go to a lot to see what is gonna be possible with the command line utility.

[00:09:46] Most of the commands have some decent help text that show you how to use these different commands, actually give you some examples of how to generate models of various kinds. So as you're kind of exploring this, I would definitely recommend that you take advantage of these help operations.

[00:10:03] So, for example, for model create, which I suspect is something that you're gonna be interested in,
>> Kevin Whinnery: Sequelize, and then we can just basically append help to the beginning of that. That's gonna actually show you the commands that you pass in to generate say a user model with certain attributes and it'll show you the actual JavaScript that'll get spat out by that command as well.

[00:10:38] So, definitely make use of those help texts as you're going through and doing stuff with the ORM. The other bit that you'll want to take advantage of while you're hacking on it is the sequelize documentation posted on read the docs. Specifically, I'll point you to the migrations documentation because we will be looking at expanding on our model a little bit.

[00:11:12] So you're gonna be generating migration, updating the model, running the migration, and enhancing what the application is, or what the model is capable of. So, definitely will be a resource worth consulting. So are there any questions right away about sequelize and kind of how it fits into this application or other general questions?

[00:11:43]
>> Kevin Whinnery: Obviously there's a lot to learn about the sequelize API, which is why we're gonna start off with a task that will get you familiar with that by actually wrestling with a real task. Rather than putting a bunch of method names in slides that none of you will remember, I am gonna be giving you a task that will require acquiring an operational knowledge of how this ORM works.