Liitostaulut ja -kyselyt

Sovelluksen strukturointi

Olemme toistaiseksi kirjoittaneet kaiken koodin samaan tiedostoon. Strukturoidaan nyt sovellus hieman paremmin. Luodaan seuraava hakemistorakenne ja tiedostot:

index.js
util
  config.js
  db.js
models
  index.js
  note.js
controllers
  notes.js

Tiedostojen sisältö on seuraava. Tiedosto util/config.js huolehtii ympäristömuuttujien käsittelystä:

require('dotenv').config()

module.exports = {
  DATABASE_URL: process.env.DATABASE_URL,
  PORT: process.env.PORT || 3001,
}

Tiedoston index.js rooliksi jää sovelluksen konfigurointi ja käynnistäminen:

const express = require('express')
const app = express()

const { PORT } = require('./util/config')
const { connectToDatabase } = require('./util/db')

const notesRouter = require('./controllers/notes')

app.use(express.json())

app.use('/api/notes', notesRouter)

const start = async () => {
  await connectToDatabase()
  app.listen(PORT, () => {
    console.log(`Server running on port ${PORT}`)
  })
}

start()

Sovelluksen käynnistys poikkeaa hieman aiemmin näkemästä, sillä haluamme varmistaa ennen varsinaista käynnistämistä että tietokantayhteys on muodostettu.

Tiedosto util/db.js sisältää tietokannan alustukseen liittyvän koodin:

const Sequelize = require('sequelize')
const { DATABASE_URL } = require('./config')

const sequelize = new Sequelize(DATABASE_URL)

const connectToDatabase = async () => {
  try {
    await sequelize.authenticate()
    console.log('database connected')
  } catch (err) {
    console.log('connecting database failed')
    return process.exit(1)
  }

  return null
}

module.exports = { connectToDatabase, sequelize }

Muistiinpanot tallettavaa taulua vastaava model on talletettu tiedostoon models/note.js:

const { Model, DataTypes } = require('sequelize')

const { sequelize } = require('../util/db')

class Note extends Model {}

Note.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  content: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  important: {
    type: DataTypes.BOOLEAN
  },
  date: {
    type: DataTypes.DATE
  }
}, {
  sequelize,
  underscored: true,
  timestamps: false,
  modelName: 'note'
})

module.exports = Note

Tiedosto models/index.js on tässä vaiheessa lähes turha, sillä sovelluksessa on vasta yksi model. Kun lisäämme sovellukseen muitakin modeleja tulee tiedostolle enemmän käyttöä, sillä tiedoston ansiosta muualla ohjelmassa ei tarvitse importata erikseen yksittäisen modelin määritteleviä tiedostoja.

const Note = require('./note')

Note.sync()

module.exports = {
  Note
}

Muistiinpanoihin liittyvät routejen käsittelijät löytyvät tiedostosta controllers/notes.js:

const router = require('express').Router()

const { Note } = require('../models')

router.get('/', async (req, res) => {
  const notes = await Note.findAll()
  res.json(notes)
})

router.post('/', async (req, res) => {
  try {
    const note = await Note.create(req.body)
    res.json(note)
  } catch(error) {
    return res.status(400).json({ error })
  }
})

router.get('/:id', async (req, res) => {
  const note = await Note.findByPk(req.params.id)
  if (note) {
    res.json(note)
  } else {
    res.status(404).end()
  }
})

router.delete('/:id', async (req, res) => {
  const note = await Note.findByPk(req.params.id)
  if (note) {
    await note.destroy()
  }
  res.status(204).end()
})

router.put('/:id', async (req, res) => {
  const note = await Note.findByPk(req.params.id)
  if (note) {
    note.important = req.body.important
    await note.save()
    res.json(note)
  } else {
    res.status(404).end()
  }
})

module.exports = router

Sovelluksen rakenne on nyt hyvä. Huomaamme kuitenkin että yksittäistä muistiinpanoa käsittelevät reitinkäsittelijät sisältävät aavistuksen verran toisteista koodia, sillä kaikki niistä alkavat käsiteltävän muistiinpanon etsivällä rivillä:

const note = await Note.findByPk(req.params.id)

Refaktoroidaan tämä omaan middlewareen ja otetaan se käyttöön reittienkäsittelijöissä:

const noteFinder = async (req, res, next) => {  req.note = await Note.findByPk(req.params.id)  next()}
router.get('/:id', noteFinder, async (req, res) => {  if (req.note) {
    res.json(req.note)
  } else {
    res.status(404).end()
  }
})

router.delete('/:id', noteFinder, async (req, res) => {  if (req.note) {
    await req.note.destroy()
  }
  res.status(204).end()
})

router.put('/:id', noteFinder, async (req, res) => {  if (req.note) {
    req.note.important = req.body.important
    await req.note.save()
    res.json(req.note)
  } else {
    res.status(404).end()
  }
})

Reitinkäsittelijät saavat nyt kolme parametria, näistä ensimmäinen on reitin määrittelevä merkkijono ja toisena on määrittelemämme middleware noteFinder, joka hakee muistiinpanon tietokannasta ja sijoittaa sen req olion kenttään note. Pieni määrä copypastea poistuu ja olemme tyytyväisiä!

Sovelluksen tämänhetkinen koodi on kokonaisuudessaan GitHubissa, branchissa part13-2.

Tehtävät 13.5.-13.7.

Tehtävä 13.5.

Muuta sovelluksesi rakenne edellä olevan esimerkin mukaiseksi, tai noudattamaan jotain muuta vastaavaa selkeää konventiota.

Tehtävä 13.6.

Toteuta sovellukseen myös tuki blogien like-määrän muuttamiselle, eli operaatio

PUT /api/blogs/:id (blogin like-määrän muokkaus)

Likejen päivitetty määrä välitetään pyynnön mukana:

{
  likes: 3
}

Tehtävä 13.7.

Keskitä sovelluksen virheidenkäsittely middlewareen osan 3 tapaan. Voit ottaa käyttöösi myös middlewaren express-async-errors kuten osassa 4 tehtiin.

Virheilmoituksen yhteydessä palautettavalla datalla ei ole suurta merkitystä.

Tässä vaiheessa sovelluksen virhekäsittelyä vaativat tilanteet ovat uuden blogin luominen sekä blogin tykkäysmäärän muuttaminen. Varmista, että virheidenkäsittelijä hoitaa molemmat asiaankuuluvalla tavalla.

Käyttäjänhallinta

Lisätään seuraavaksi sovellukseen tietokantataulu users, johon tallennetaan sovelluksen käyttäjät. Toteutetaan lisäksi mahdollisuus käyttäjien luomiseen sekä token-perustainen kirjautuminen osan 4 tapaan. Yksinkertaisuuden vuoksi teemme toteutuksen nyt niin, että kaikilla käyttäjillä on sama salasana salainen.

Käyttäjän määrittelevä model tiedostossa models/user.js on melko suoraviivainen

const { Model, DataTypes } = require('sequelize')

const { sequelize } = require('../util/db')

class User extends Model {}

User.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  username: {
    type: DataTypes.STRING,
    unique: true,
    allowNull: false
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
}, {
  sequelize,
  underscored: true,
  timestamps: false,
  modelName: 'user'
})

module.exports = User

Käyttäjätunnukseen on asetettu ehdoksi että se on uniikki. Käyttäjätunnusta olisi periaatteessa voitu käyttää taulun pääavaimena. Päätimme kuitenkin luoda pääavaimen erillisenä kokonaislukuarvoisena kenttänä id.

Tiedosto models/index.js laajenee hieman:

const Note = require('./note')
const User = require('./user')
Note.sync()
User.sync()
module.exports = {
  Note, User}

Tiedostoon controllers/users.js sijoitettavat uuden käyttäjä luomisesta sekä kaikkien käyttäjien näyttämisestä huolehtivat reitinkäsittelijät eivät sisällä mitään dramaattista

const router = require('express').Router()

const { User } = require('../models')

router.get('/', async (req, res) => {
  const users = await User.findAll()
  res.json(users)
})

router.post('/', async (req, res) => {
  try {
    const user = await User.create(req.body)
    res.json(user)
  } catch(error) {
    return res.status(400).json({ error })
  }
})

router.get('/:id', async (req, res) => {
  const user = await User.findByPk(req.params.id)
  if (user) {
    res.json(user)
  } else {
    res.status(404).end()
  }
})

module.exports = router

Kirjautumisen hoitava reitinkäsittelijä (tiedosto controllers/login.js) on seuraavassa:

const jwt = require('jsonwebtoken')
const router = require('express').Router()

const { SECRET } = require('../util/config')
const User = require('../models/user')

router.post('/', async (request, response) => {
  const body = request.body

  const user = await User.findOne({
    where: {
      username: body.username
    }
  })

  const passwordCorrect = body.password === 'salainen'

  if (!(user && passwordCorrect)) {
    return response.status(401).json({
      error: 'invalid username or password'
    })
  }

  const userForToken = {
    username: user.username,
    id: user.id,
  }

  const token = jwt.sign(userForToken, SECRET)

  response
    .status(200)
    .send({ token, username: user.username, name: user.name })
})

module.exports = router

Post-pyynnön mukana vastaanotetaan käyttäjätunnus (username) sekä salasana (password). Ensin käyttäjää vastaava olio haetaan tietokannasta modelin User metodilla findOne:

const user = await User.findOne({
  where: {
    username: body.username
  }
})

Konsolista näemme metodikutsua vastaavan SQL-lauseen

SELECT "id", "username", "name"
FROM "users" AS "User"
WHERE "User"."username" = 'mluukkai';

Jos käyttäjä löytyy ja salasana on oikein (eli kaikkien käyttäjien tapauksessa salainen), palautetaan kutsujalle jsonwebtoken, joka sisältää käyttäjän tietot. Tätä varten asennamme riippuvuuden

npm install jsonwebtoken

Tiedosto index.js laajenee hiukan

const notesRouter = require('./controllers/notes')
const usersRouter = require('./controllers/users')const loginRouter = require('./controllers/login')
app.use(express.json())

app.use('/api/notes', notesRouter)
app.use('/api/users', usersRouter)app.use('/api/login', loginRouter)

Sovelluksen tämänhetkinen koodi on kokonaisuudessaan GitHubissa, branchissa part13-3.

Taulujen välinen liitos

Sovellukseen voi nyt lisätä käyttäjiä ja käyttäjät voivat kirjautua, mutta itsessään tämä ei ole vielä kovin hyödyllinen ominaisuus. Ideana on se, että ainoastaan kirjautunut käyttäjä voi lisätä muistiinpanoja, ja että jokaiseen muistiinpanoon liitetään sen luonut käyttäjä. Tarvitsemme tätä varten viiteavaimen muistiinpanot tallettavaan tauluun notes.

Sequelizeä käytettäessä viiteavaimen määrittely onnistuu muuttamalla tiedostoa models/index.js seuraavasti

const Note = require('./note')
const User = require('./user')

User.hasMany(Note)Note.belongsTo(User)
Note.sync({ alter: true })User.sync({ alter: true })
module.exports = {
  Note, User
}

Näin siis määritellään että users ja notes rivien välillä on yhden suhde moneen ‑yhteys. Muutimme myös sync-kutsuja siten että ne muuttavat taulut, jos taulujen määrittelyyn on tullut muutoksia. Kun nyt katsotaan tietokannan skeemaa konsolista, se näyttää seuraavalta:

postgres=# \d users
                                     Table "public.users"
  Column  |          Type          | Collation | Nullable |              Default
----------+------------------------+-----------+----------+-----------------------------------
 id       | integer                |           | not null | nextval('users_id_seq'::regclass)
 username | character varying(255) |           | not null |
 name     | character varying(255) |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "notes" CONSTRAINT "notes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL

postgres=# \d notes
                                      Table "public.notes"
  Column   |           Type           | Collation | Nullable |              Default
-----------+--------------------------+-----------+----------+-----------------------------------
 id        | integer                  |           | not null | nextval('notes_id_seq'::regclass)
 content   | text                     |           | not null |
 important | boolean                  |           |          |
 date      | timestamp with time zone |           |          |
 user_id   | integer                  |           |          |
Indexes:
    "notes_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "notes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL

Eli tauluun notes on luotu viiteavain user_id, joka viittaa taulun users-riviin.

Tehdään nyt uuden muistiinpanon lisäämiseen sellainen muutos, että muistiinpano liitetään käyttäjään. Ennen kuin teemme kunnollisen toteutuksen (missä liitos tapahtuu tokenin avulla kirjautumisen osoittavaan käyttäjään), liitetään muistiinpano ensimmäiseen tietokannasta löytyvään käyttäjään:

router.post('/', async (req, res) => {
  try {
    const user = await User.findOne()    const note = await Note.create({ ...req.body, userId: user.id })    res.json(note)
  } catch(error) {
    return res.status(400).json({ error })
  }
})

Huomionarvoista koodissa on se, että vaikka tietokannan tasolla muistiinpanoilla on sarake user_id, tietokantariviä vastaavassa oliossa siihen viitataan Sequelizen nimentäkonvention takia camel case muodossa userId.

Yksinkertaisen liitoskyselyn tekeminen on erittäin helppoa. Muutetaan kaikki käyttäjät näyttävää routea siten, että se näyttää myös jokaisen käyttäjän muistiinpanot:

router.get('/', async (req, res) => {
  const users = await User.findAll({    include: {      model: Note    }  })  res.json(users)
})

Liitoskysely siis tehdään kyselyn parametrina olevaan olioon include-määreen avulla.

Kyselystä muodostuva sql-lause nähdään konsolissa:

SELECT "User"."id", "User"."username", "User"."name", "Notes"."id" AS "Notes.id", "Notes"."content" AS "Notes.content", "Notes"."important" AS "Notes.important", "Notes"."date" AS "Notes.date", "Notes"."user_id" AS "Notes.UserId"
FROM "users" AS "User" LEFT OUTER JOIN "notes" AS "Notes" ON "User"."id" = "Notes"."user_id";

Lopputulos on myös sen kaltainen kuin odottaa saattaa

Muistiinpanojen kunnollinen lisääminen

Muutetaan muistiinpanojen lisäys toimimaan samoin kuin osassa 4, eli muistiinpanon luominen onnistuu ainoastaan jos luomista vastaavan pyynnön mukana on validi, kirjautumisen yhteydessä saatava token. Muistiinpano talletetaan tokenin identifioiman käyttäjän tekemien muistiinpanojen listaan:

const jwt = require('jsonwebtoken')const { SECRET } = require('../util/config')
const tokenExtractor = (req, res, next) => {  const authorization = req.get('authorization')  if (authorization && authorization.toLowerCase().startsWith('bearer ')) {    try {      console.log(authorization.substring(7))      req.decodedToken = jwt.verify(authorization.substring(7), SECRET)    } catch (error){      console.log(error)      return res.status(401).json({ error: 'token invalid' })    }  } else {    return res.status(401).json({ error: 'token missing' })  }  next()}
router.post('/', tokenExtractor, async (req, res) => {  try {
    const user = await User.findByPk(req.decodedToken.id)    const note = await Note.create({...req.body, userId: user.id, date: new Date()})    res.json(note)
  } catch(error) {
    return res.status(400).json({ error })
  }
})

Token otetaan ja dekoodataan pyyntöön headereista ja sijoitetaan req-olioon middlewaren tokenExtractor toimesta. Muistiinpanoa luotaessa myös sen luontihetken kertovalle kentälle date annetaan arvo.

Hienosäätöä

Backendimme toimii tällä hetkellä virheidenkäsittelyä lukuun ottamatta lähes samalla tavalla kuin osan 4 versio samasta sovelluksesta. Ennen kun teemme backendiin muutamia laajennuksia, muutetaan kaikkien muistiinpanojen sekä kaikkien käyttäjien routeja hieman.

Lisätään muistiinpanojen yhteyteen tieto sen lisänneestä käyttäjästä:

router.get('/', async (req, res) => {
  const notes = await Note.findAll({    attributes: { exclude: ['userId'] },    include: {      model: User,      attributes: ['name']    }  })  res.json(notes)
})

Olemme myös rajoittaneet minkä kenttien arvot haluamme. Muistiinpanoista otetaan kaikki muut kentät paitsi userId ja muistiinpanoon liittyvästä käyttäjästä ainoastaan name eli nimi.

Tehdään samantapainen muutos kaikkien käyttäjien reittiin, poistetaan käyttäjään liittyvistä muistiinpanoista turha kenttä userId:

router.get('/', async (req, res) => {
  const users = await User.findAll({
    include: {
      model: Note,
      attributes: { exclude: ['userId'] }    }
  })
  res.json(users)
})

Sovelluksen tämänhetkinen koodi on kokonaisuudessaan GitHubissa, branchissa part13-4.

Huomio modelien määrittelystä

Tarkkasilmäisimmät huomasivat, että sarakkeen user_id lisäämisestä huolimatta emme tehneet muutosta muistiinpanot määrittelevään modeliin, mutta voimme lisätä muistinpano-olioille käyttäjän:

const user = await User.findByPk(req.decodedToken.id)
const note = await Note.create({ ...req.body, userId: user.id, date: new Date() })

Syynä tälle on se, että kun määrittelimme tiedostossa models/index.js, että käyttäjien ja muistiinpanojen välillä on yhdestä moneen ‑yhteys:

const Note = require('./note')
const User = require('./user')

User.hasMany(Note)
Note.belongsTo(User)

// ...

luo Sequelize automaattisesti modeliin Note attribuutin userId, johon viittaamalla päästään käsiksi tietokannan sarakkeeseen user_id.

Huomaa, että voisimme luoda muistiinpanon myös seuraavasti metodin build avulla:

const user = await User.findByPk(req.decodedToken.id)

// luodaan muistiinpano tallettamatta sitä vielä
const note = Note.build({ ...req.body, date: new Date() })
 // sijoitetaan käyttäjän id mustiinpanolle
note.userId = user.id
// talletetaan muistiinpano-olio tietokantaan
await note.save()

Näin näemme eksplisiittisesti sen, että userId on muistiinpano-olion attribuutti.

Voisimme määritellä saman myös modeliin:

Note.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  content: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  important: {
    type: DataTypes.BOOLEAN
  },
  date: {
    type: DataTypes.DATE
  },
  userId: {    type: DataTypes.INTEGER,    allowNull: false,    references: { model: 'users', key: 'id' },  }}, {
  sequelize,
  underscored: true,
  timestamps: false,
  modelName: 'note'
})

module.exports = Note

tämä ei kuitenkaan ole välttämätöntä. Model-luokkien tasolla tapahtuva määrittely

User.hasMany(Note)
Note.belongsTo(User)

sen sijaan on välttämätön, muuten Sequelize ei osaa koodin tasolla liittää tauluja toisiinsa.

Tehtävät 13.8.-13.12.

Tehtävä 13.8.

Lisää sovellukseen tuki käyttäjille. Käyttäjillä on tunnisteen lisäksi seuraavat kentät:

name (merkkijono, ei saa olla tyhjä)
username (merkkijono, ei saa olla tyhjä)

Toisin kuin materiaalissa älä nyt estä Sequelizea luomasta käyttäjille aikaleimoja created_at ja updated_at

Kaikilla käyttäjillä voi olla sama salasana materiaalin tapaan. Voit myös halutessasi toteuttaa salasanan kunnolla osan 4 tapaan.

Toteuta seuraavat routet

POST api/users (uuden käyttäjän lisäys)
GET api/users (kaikkien käyttäjien listaus)
PUT api/users/:username (käyttäjän nimen muutos, huomaa että parametrina ei ole id vaan käyttäjätunnus)

Varmista, että Sequelizen automaattisesti asettamat aikaleimat created_at ja updated_at toimivat oikein kun luot käyttäjän ja muutat käyttäjän nimeä.

Tehtävä 13.9.

Sequelize tarjoaa joukon valmiiksi määriteltyjä validointeja modelien kentille, jotka se suorittaa ennen olioiden tallentamista tietokantaan.

Päätetään muuttaa käyttäjätunnuksen luontiperiaatetta siten, että käyttäjätunnukseksi kelpaa ainoastaan validi emailosoite. Tee tunnuksen luomisen yhteyteen validointi, joka tarkastaa asian.

Muuta virheidenkäsittelymiddlewarea siten, että se antaa tilanteessa kuvaavamman virheilmoituksen (esim. hyödyntäen Sequelizen virheeseen liittyvää viestiä):

{
    "error": [
        "Validation isEmail on username failed"
    ]
}

Tehtävä 13.10.

Laajenna sovellusta siten, että blogi liitetään tokenin perusteella identifioitavalle kirjautuneelle käyttäjälle. Joudut siis toteuttamaan myös tokenin palauttavan kirjautumisesta huolehtivan endpointin POST /api/login

Tehtävä 13.11.

Tee blogin poisto mahdolliseksi ainoastaan blogin lisänneelle käyttäjälle.

Tehtävä 13.12.

Muokkaa blogien ja käyttäjien routea siten, että blogien yhteydessä näytetään tieto blogin lisänneestä käyttäjästä, ja käyttäjän yhteydessä tiedot käyttäjien blogeista.

Lisää kyselyitä

Toistaiseksi sovelluksemme on ollut kyselyiden suhteen hyvin yksinkertainen, kyselyt ovat hakeneet joko yksittäisen rivin pääavaimeen perustuen metodia findByPk käyttäen tai ne ovat hakeneet metodilla findAll taulun kaikki rivit. Nämä riittävät sovellukselle osassa 5 tehdylle frontendille, mutta laajennetaan backendia siten, että pääsemme myös harjoittelemaan hieman monimutkaisempien kyselyjen tekemistä.

Toteutetaan ensin mahdollisuus hakea ainoastaan tärkeät tai ei-tärkeät muistiinpanot. Toteutetaan nämä query-parametrin important avulla:

router.get('/', async (req, res) => {
  const notes = await Note.findAll({
    attributes: { exclude: ['userId'] },
    include: {
      model: User,
      attributes: ['name']
    },
    where: {      important: req.query.important === "true"    }  })
  res.json(notes)
})

Nyt backendilta voidaan hakea tärkeät muistiinpanot reitiltä /api/notes?important=true ja ei-tärkeät reitiltä /api/notes?important=false

Sequelizen generoima SQL-kysely sisältää luonnollisesti palautettavia rivejä rajaavan where-määreen:

SELECT "note"."id", "note"."content", "note"."important", "note"."date", "user"."id" AS "user.id", "user"."name" AS "user.name"
FROM "notes" AS "note" LEFT OUTER JOIN "users" AS "user" ON "note"."user_id" = "user"."id"
WHERE "note"."important" = true;

Ikävä kyllä tämä toteutus ei toimi jos haettaessa ei olla kiinnostuneita onko muistiinpano tärkeä vai ei, eli jos pyyntö tehdään osoitteeseen http://localhost:3001/api/notes. Korjaus voidaan tehdä monella tapaa. Eräs, mutta ei kenties paras tapa tehdä korjaus olisi seuraavassa:

const { Op } = require('sequelize')
router.get('/', async (req, res) => {
  let important = {    [Op.in]: [true, false]  }  if ( req.query.important ) {    important = req.query.important === "true"  }
  const notes = await Note.findAll({
    attributes: { exclude: ['userId'] },
    include: {
      model: User,
      attributes: ['name']
    },
    where: {
      important    }
  })
  res.json(notes)
})

Olio important tallettaa nyt kyselyn ehdon. Se on oletusarvoisesti

where: {
  important: {
    [Op.in]: [true, false]
  }
}

eli sarake important voi olla arvoltaan true tai false. Käytössä on yksi monista Sequelizen operaatioista Op.in. Jos query-parametri req.query.important on määritelty, muuttuu kysely jompaankumpaan muotoon

where: {
  important: true
}

tai

where: {
  important: false
}

riippuen query-parametrin arvosta.

Tietokantaan on saattanut päästä note-rivejä joiden kentällä important ei ole arvoa. Näitä eivät ylläolevien muutosten jälkeen enää pysty kannasta hakemaan. Annetaan tietokantakonsolista puuttuville tärkeyksille jotkut arvot, ja muutetaan skeemaa siten, että tärkeys tulee pakolliseksi:

Note.init(
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    content: {
      type: DataTypes.TEXT,
      allowNull: false,
    },
    important: {
      type: DataTypes.BOOLEAN,
      allowNull: false,    },
    date: {
      type: DataTypes.DATE,
    },
  },
  // ...
)

Laajennetaan toiminnallisuutta vielä siten, että muistiinpanoja haettaessa voidaan määritellä vaadittu hakusana, eli esim. tekemällä pyyntö http://localhost:3001/api/notes?search=database saadaan kaikki muistiinpanot, joissa mainitaan database tai pyynnöllä http://localhost:3001/api/notes?search=javascript&important=true saadaan kaikki tärkeäksi merkityt muistiinpanot, joissa mainitaan javascript. Toteutus on seuraavassa

router.get('/', async (req, res) => {
  let important = {
    [Op.in]: [true, false]
  }

  if ( req.query.important ) {
    important = req.query.important === "true"
  }

  const notes = await Note.findAll({
    attributes: { exclude: ['userId'] },
    include: {
      model: User,
      attributes: ['name']
    },
    where: {
      important,
      content: {        [Op.substring]: req.query.search ? req.query.search : ''      }    }
  })

  res.json(notes)
})

Sequelizen Op.substring muodostaa haluamamme kyselyn SQL:n like-avainsanaa käyttäen. Jos esim. teemme pyynnön http://localhost:3001/api/notes?search=database&important=true näemme että sen aikaansaama SQL-kysely on juuri olettamamme kaltainen.

SELECT "note"."id", "note"."content", "note"."important", "note"."date", "user"."id" AS "user.id", "user"."name" AS "user.name"
FROM "notes" AS "note" LEFT OUTER JOIN "users" AS "user" ON "note"."user_id" = "user"."id"
WHERE "note"."important" = true AND "note"."content" LIKE '%database%';

Sovelluksessamme on vielä sellainen kauneusvirhe, että jos teemme pyynnön http://localhost:3001/api/notes eli haluamme kaikki muistiinpanot, toteutuksemme aiheuttaa kyselyyn turhan wheren, joka saattaa (riippuen tietokantamoottorin toteutuksesta) vaikuttaa tarpeettomasti kyselyn tehokkuuteen:

SELECT "note"."id", "note"."content", "note"."important", "note"."date", "user"."id" AS "user.id", "user"."name" AS "user.name"
FROM "notes" AS "note" LEFT OUTER JOIN "users" AS "user" ON "note"."user_id" = "user"."id"
WHERE "note"."important" IN (true, false) AND "note"."content" LIKE '%%';

Optimoidaan koodia vielä siten, että where-ehtoja käytetään ainoastaan tarvittaessa:

router.get('/', async (req, res) => {
  const where = {}

  if (req.query.important) {
    where.important = req.query.important === "true"
  }

  if (req.query.search) {
    where.content = {
      [Op.substring]: req.query.search
    }
  }

  const notes = await Note.findAll({
    attributes: { exclude: ['userId'] },
    include: {
      model: User,
      attributes: ['name']
    },
    where
  })

  res.json(notes)
})

Jos pyynnössä on hakuehtoja esim. http://localhost:3001/api/notes?search=database&important=true muodostuu wheren sisältävä kysely

SELECT "note"."id", "note"."content", "note"."important", "note"."date", "user"."id" AS "user.id", "user"."name" AS "user.name"
FROM "notes" AS "note" LEFT OUTER JOIN "users" AS "user" ON "note"."user_id" = "user"."id"
WHERE "note"."important" = true AND "note"."content" LIKE '%database%';

Jos pyyntö on hakuehdoton http://localhost:3001/api/notes ei kyselyssä ole turhaa whereä

SELECT "note"."id", "note"."content", "note"."important", "note"."date", "user"."id" AS "user.id", "user"."name" AS "user.name"
FROM "notes" AS "note" LEFT OUTER JOIN "users" AS "user" ON "note"."user_id" = "user"."id";

Sovelluksen tämänhetkinen koodi on kokonaisuudessaan GitHubissa, branchissa part13-5.

Tehtävät 13.13.-13.16

Tehtävä 13.13.

Toteuta sovellukseen kaikki blogit palauttavaan reittiin filtteröinti hakusanan perusteella. Filtteröinti toimii seuraavasti

GET /api/blogs?search=react palauttaa ne blogit joiden kentässä title esiintyy hakusana react, hakusana on epäcasesensitiivinen
GET /api/blogs palauttaa kaikki blogit

Tämä lienee hyödyksi tässä ja seuraavassa tehtävässä.

Tehtävä 13.14.

Laajenna filtteriä siten, että se etsii hakusanaa kentistä title ja author, eli

GET /api/blogs?search=jami palauttaa ne blogit joiden kentässä title tai kentässä author esiintyy hakusana jami

Tehtävä 13.15.

Muokkaa blogien reittiä siten, että se palauttaa blogit tykkäysten perusteella laskevassa järjestyksessä. Etsi dokumentaatiosta ohjeet järjestämiselle.

Tehtävä 13.16.

Tee sovellukselle reitti /api/authors, joka palauttaa kustakin authorista blogien lukumäärän sekä tykkäysten yhteenlasketun määrän. Toteuta operaatio suoraan tietokannan tasolla. Tarvitset suurella todennäköisyydellä group by-toiminnallisuutta, sekä sequelize.fn-aggregaattorifunktiota.

Reitin palauttama JSON voi näyttää esim. seuraavalta:

[
  {
    author: "Martin Fowler",
    blogs: "2",
    likes: "10"
  },
  {
    author: "Robert C. Martin",
    blogs: "1",
    likes: "0"
  },
  {
    author: "Cam Jackson",
    blogs: "1",
    likes: "2"
  },
  {
    author: "Dan Abramov",
    blogs: "3",
    likes: "7"
  }
]

Bonustehtävä: järjestä palautettava data tykkäysten perusteella, tee järjestäminen tietokantakyselyssä.

Ehdota muutosta materiaalin sisältöön

Osa 13a

Edellinen osa

Osa 13c

Seuraava osa