Leaena

Setting Up a Database Node Module on a Node/Express Server with Sequelize

Dec
29

If you just need to get a node server up and running with very few lines of code then you hopefully already know about Node/Express (if you don’t, the Express website has a pretty good intro tutorial and has good documentation to get started serving up static assets). If you need all of that and to be able to route queries to a database quickly and easily then you might not know about the awesome power of Sequelize.

If you haven’t messed around with a ORM (Object Relational Mapper, a program that maps your code to a database) before, Sequelize is a really straight forward one to start with.

var voteTable = sequelize.define('vote', {
  video_id: Sequelize.STRING,
  timestamp: Sequelize.INTEGER,
  vote: Sequelize.INTEGER
});

voteTable.sync();

Once I had my server set up (and installed the sequelize dependencies). This was the line of code I needed to use to create a new table for my current group project. Sequelize automatically includes extra columns like unique id and created at/updated at timestamps (there are ways to tell it not to too).

The rest of the initial set up is easy too, but it’s well documented over in the Sequelize docs. The fun part comes when you can start to make your database more modular. The first thing I did was to take out the actual username/password information from my database connection. I stored them as an object in a separate JavaScript file (so I could add it to .gitignore and not share my passwords with the world).

//module.exports allows us to use this code in other places as a node module, 
//we'll see it again when I make the database calls modular
module.exports = { 
  database: 'database_name', 
  username: 'username', 
  password: 'secret_password',
  host: 'host_url',
  port: 5432,
  dialect: 'postgres', //obviously you don't have to use PostgreSQL
  native: true //required for Heroku Postgres (I'll cover that in another post)
};

I saved that snipped to a file called db_config.js at the root directory and then created my main database module in the subfolder /controller/ called database.js. So to have access to the private config object, all I need to do is set up my dependencies, import the db_config file, and I can start using my config variables to connect to my database:

var Sequelize = require('sequelize');
var pg = require('pg').native; //again this line is specific to using a Postgres database
var config = require('../db_config');

var sequelize = new Sequelize(config.database, config.username, config.password, {
  host: config.host,
  port: config.port,
  dialect: config.dialect,
  native: config.native //Heroku Postgress again
});

Now between that code and my creating a table I have full access to a database, but now I need to get this all into my main app.js simple server I created with Node/Express. This is a super easy leap. First I create my functions to send and retrieve data in /controller/database.js:


module.exports.createVote = function(req, res){
  //code to bundle up the created object and save it do the database
};

module.exports.getVotes = function(req, res){
  //code to find votes based on specific requests from the user
};

Now to have access to these functions (which are a part of the database.js node module, thanks to the module.exports object which is a feature of Node) I only need to require database.js in my main server app and call the functions where I need them:

var database = require('./controllers/database');

//many lines later
app.post('/votes', database.createVote);
app.get('/votes/:vidID', database.getVotes);

The /votes/:vidID is a handy trick of Express to pass information to the server. The value gets attached to the req.param.vidID property so I can use it when I request specific information from the server. For example if I wanted to query for results from a video ID of 123 I would send a post request to /votes/123 and then my req.param.vidID === 123.

One last trick, in Sequelize when you query the database you get back quite a few more rows than you might expect. When I query my voteTable (the one I only explicitly created three columns for?) I get back something that looks like this monster:

 { dataValues: 
     { video_id: '7QBgK0_RbkE', timestamp: 2, vote: 1, id: 192,
       createdAt: Sun Dec 22 2013 22:19:33 GMT-0800 (PST),
       updatedAt: Sun Dec 22 2013 22:19:33 GMT-0800 (PST) },
    __options: 
     { timestamps: true, createdAt: 'createdAt', updatedAt: 'updatedAt',
       deletedAt: 'deletedAt', touchedAt: 'touchedAt', instanceMethods: {}, classMethods: {}, 
       validate: {}, freezeTableName: false, underscored: false, syncOnAssociation: true,
       paranoid: false, whereCollection: [Object], schema: null, schemaDelimiter: '',
       language: 'en', defaultScope: null, scopes: null, hooks: [Object], omitNull: false, 
       hasPrimaryKeys: false },
    hasPrimaryKeys: false,
    selectedValues: 
     { video_id: '7QBgK0_RbkE', timestamp: 2, vote: 1, id: 192,
       createdAt: Sun Dec 22 2013 22:19:33 GMT-0800 (PST),
       updatedAt: Sun Dec 22 2013 22:19:33 GMT-0800 (PST) },
    __eagerlyLoadedAssociations: [],
    isDirty: false,
    isNewRecord: false,
    daoFactoryName: 'votes',
    daoFactory: 
     { options: [Object], name: 'votes', tableName: 'votes', rawAttributes: [Object],
       daoFactoryManager: [Object], associations: {}, scopeObj: {}, primaryKeys: {},
       primaryKeyCount: 0, hasPrimaryKeys: false, autoIncrementField: 'id', DAO: [Object] } 
 } 

And that’s just ONE entry in the database! To fix that add an option to your sequelize query: {raw: true} so the query would look like:

voteTable.findAll(query, {raw: true})

And one entry of output would be:

{ video_id: 'T-D1KVIuvjA',
    timestamp: 2,
    vote: 1,
    id: 1,
    createdAt: Sat Dec 21 2013 14:55:42 GMT-0800 (PST),
    updatedAt: Sat Dec 21 2013 14:55:42 GMT-0800 (PST) }

That is enough database tricks for today. If you want to just stare at my code for a while to create & retrieve votes from our database you can find my gist here (with sanitized login info for the db_config). The real (still being modded by the team) code is forked on my GitHub. I have a few more of these in the works from my adventures slinging code and I hope to post a few more before Hack Reactor starts back up and I lose all free time again.

Week 5: Serving Up a New Outlook

Dec
12

If I didn’t know me better I’d say I was a little manic this week compared to last. This week I was cold, tired and hungry most of the time. I had my ups, down and whiny moments. My credit card was blocked because apparently the soda machine in the mall is shady to my bank. It was a quick fix, until my Clipper Card (the way I pay for my train – which I take every day, twice a day) ended up being blocked because it had tried to autoload cash during the 3 hour window of downtime on my credit card. It takes up to a week to reprocess so I’m forced to buy crappy little paper tickets and not use the $30 I already had stored on my train card. I ended up buying a coat and neon pink gloves just to make me happy (and warmer). Allow me to be a woman for a moment and do a bit of style blogging:

Style blogging grey jacket

I obviously didn’t spend all my time buying cheap, warm clothing though. I spent most of my time in class having moments of excitement followed by moments of wanting to crawl under a desk and take a nap. I’m not sure what I’d do without the upcoming Christmas break, but it wouldn’t be pretty. What I will say instead is that I may have found my niche in programming.

This week was all about ‘backend’ which means pretty much what it sounds like. It’s a lot of the behind the scenes decisions that have to happen to decide what page you’re looking for and what data to put there. We did more Node.js servering and powered through different types of databases and how to hook them in and beat them into a format we can use well enough with our front-end JavaScripting (the answer is JSON, for the viewers at home). Finally, when I thought I couldn’t take any more (and I was half right), we started on Angular, another MV* framework like Backbone.js, but with some magic and some sleight of hand that allows you to do most of the work directly on the HTML.

I lurve the backend. I really find page routing and JSON calls and database building/retrieving from to be super interesting. It almost feels like a tangible thing where the app over the top is just a pretty cover for the awesomeness that makes it all happen.

We start our lives as Software Engineers next week – a short two-day solo project sprint followed by the start of our first group project. I’m super excited to have a bit more freedom. I’m also terrified of working on my own and having it crash and burn. I really hope excitement wins out.

After the solo project I hope I can write-up a bit about my experiences and show off my (crappy?) solo app.

HackReactor , , , , Comments Off on Week 5: Serving Up a New Outlook

Week 4: Losing Power

Dec
07

Another roller coaster week. Worst points: Someone took my laptop charger while I was in lecture, my week 3 assessment was not my finest point, and I miss my cats something desperately. Best points: I worked with my two favorite pair-partners again because I couldn’t handle this week otherwise, I had an awesome girl’s lunch today with 4/5ths of the junior class women (there are 5 of us total), and I made a node.js server!

So I definitely felt my first crazy/not enough sleep/irrational emotions. When my power cord was jacked a couple of days ago I was devastated. In retrospect, I think I’m a little tired and cranky and, like I said previously, I am in desperate need of some kitty cuddle time. I’ve  been in California now for over a month and I still love it, but it’s definitely getting more difficult. There are times I wish I could just zen out and I only really get that on the trains or at midnight in the dark when I should be sleeping so I can wake up and do it all again the next morning at 6am to catch my train.

I have never been more excited/motivated to get up in the morning in my life and I can’t get to sleep at night because code and other things are running through my head and along the way something had to give. I’ve discovered that when something has to give it’s my emotional stability. My actual sprints were amazeballs, especially since I had awesome pairs (Sara and Andy, respectively) for my Backbone and Node.js sprints. But in my quiet moments or while I was working solo the doubt and sadness came back.

So I did what I always do when I’m sad – I talked to my parents, a lot. I called them while waiting for the train, I called them sitting on the couch in the Hack Reactor lobby during lunch, and I called them while I drove home at 9pm every night. My dad especially is awesome at making me feel better. He’s always so proud of me and he’s always  interested in what I’m doing. It’s hard sometimes here, but I have an amazing support system both in Oregon and here. My platonic life partner Ava and her husband John have been my rocks in more ways than one. My new awesome friends at Hack Reactor, especially the ladies of the Nov ’13 cohort have been awesome to get to know. And I know all my family, friend, and former coworkers back in Oregon love me and miss me as much or more than I love and miss them.

Thinking about it and even writing about it has helped as well. Getting it off my chest makes it so I can breathe again. So I guess I just want to say, I love you all. Thank you for dealing with me! I really am happy even if I didn’t sound it sometimes this week. If I don’t just sleep all of tomorrow/spend my day in San Jose with other awesome people I love to pieces, I’m going to try to post a non-emotional/tired/whiny post, but honestly, I want to keep this blog real. For me as well as for you and this what has dominated my brain this week.