Node: The Sequel

Databases the relational way

@zpnk

Let's build an app..

Music Catalog

Store & Find:

  • Artists

  • Albums

  • Songs

Artist

Album

Song

Artist

  • Name

  • Bio

Album

  • Title

  • Release Date

Song

  • Title

  • Length

{
  "id":   1,
  "name": "Foo Fighters",
  "bio":  "Foo Fighters are an American rock band,  \
           formed in Seattle in 1994."
}
{
  "id":        1,    
  "title":     "Echoes, Silence, Patience & Grace",
  "released":  "September 25, 2007",
  "artist_id": 1
}
{
  "id":       1,
  "title":    "The Pretender",
  "length":   269,
  "album_id": 1
}

That's our Schema!

Time to code.

{
  "id":   1,
  "name": "Foo Fighters",
  "bio":  "Foo Fighters are an American rock band,  \
           formed in Seattle in 1994."
}
knex.schema.createTable('artists', function(column) {
  column.increments('id').primary()
  column.text('name').notNullable()
  column.text('bio').notNullable()
})

Build Artist Table

var artist = {
  name: "Foo Fighters",
  bio:  "Foo Fighters are an American rock band,
         formed in Seattle in 1994."
}

knex.insert(artist).into('artists').then(console.log)
// {id: 1, name: "..", bio: ".."}

Add An Artist

knex.select().from('artists').where({id: 1}).then(console.log)
// {id: 1, name: "Foo Fighters", bio: "..."}

Find An Artist

But Wait...

What about CHanging a table?

Adding a Column

exports.up = function(knex) {
  return knex.schema.table('artists', function(column) {
    column.text('origin').notNullable()
  })
}

exports.down = function(knex) {
  return knex.schema.table('artists', function(table) {
    table.dropColumn('origin')
  })
}

Updating a row

knex('artists').update({origin: "Seattle, WA"}).where({id: 1})
// {id: 1, name: "...", bio: "...", origin: "Seattle, WA"}

Arists have Albums

knex.schema.createTable('albums', function(column) {
  column.increments('id').primary()
  column.text('title').notNullable()
  column.date('released').notNullable()
  column.integer('artist_id').references('id').inTable('artists')
})

Build Album Table

{
  "id":        1,    
  "title":     "Echoes, Silence, Patience & Grace",
  "released":  "September 25, 2007",
  "artist_id": 1
}
var album = {
  title:     "Echoes, Silence, Patience & Grace",
  released:  "September 25, 2007",
  artist_id: 1
}

knex.insert(album).into('albums').then(console.log)
// {id: 1, title: "...", released: "...", artist_id: "..."}

Add An Album

knex('albums')
  .innerJoin('artists', 'artists.id', 'albums.artist_id')
  .then(console.log)

// {id: 1, title: "..", length: "..", ...}

Get Album with Artist

Albums Have Songs

knex.schema.createTable('songs', function(column) {
  column.increments('id').primary()
  column.text('title').notNullable()
  column.integer('length').notNullable()
  column.integer('album_id').references('id').inTable('albums')
})

Build Song Table

{
  "id":       1,
  "title":    "The Pretender",
  "length":   269,
  "album_id": 1
}
var songs = [
  {title: "The Pretender", length: 269},
  {title: "Erase/Replace", length: 245}
]

knex.insert(songs).into('songs').then(console.log)
// [{id: 1, ...}, {id: 2, ...}]

Add Some Songs

let's make it better

var Artist = bookshelf.Model.extend({
    tableName: 'artists',
    
    albums: function() {
      return this.hasMany(Album)
    },

    songs: function() {
      return this.hasMany(Song).through(Album)
    }
})

Artist Model

knex.select().from('artists').where({id: 1})

Orm Vs Ad-Hoc

new Artist({id: 1}).fetch()
var artist = new Artist({id: 1})

artist.albums().fetch()
// [{id: 1, title: "Echoes...", released: "September..."}]

artist.songs().fetch()
// [{title: "The Pretender", length: 269}, {title: ...}]

Find all Artist Data

What did we Just do?

  • Built a schema

  • Ran some queries

  • Made a migration

  • Added an ORM

Resources

Knex.js

Bookshelf.js

Patio

Sequelize

OpenRecord

Ghost

knexjs.org

bookshelfjs.org

github.com/c2fo/patio

sequelizejs.com

github.com/philwaldmann/openrecord

github.com/tryghost/ghost

Questions?

Go.ApplyTo.Vegas

Node: The Sequel

By zpnk

Node: The Sequel

Relational database presentation for VegasJS #40 - 6/16/15

  • 1,140
Loading comments...

More from zpnk