a Relaatiotietokannan käyttö Sequelize-kirjastollab Liitostaulut ja -kyselyt

c

Migraatiot, monen suhde moneen -yhteydet

Migraatiot

Jatketaan backendin laajentamista. Haluamme toteuttaa tuen sille, että admin-statuksen omaavat käyttäjät voivat asettaa haluamiaan käyttäjiä epäaktiiviseen tilaan, estäen heiltä kirjautumisen ja uusien muistiinpanojen luomisen. Toteuttaaksemme nämä, meidän tulee lisätä käyttäjien tietokantatauluun boolean-arvoinen tieto siitä, onko käyttäjä admin sekä siitä onko käyttäjätunnus epäaktiivinen.

Voisimme edetä kuten aiemmin, eli muuttaa taulun määrittelevää modelia ja luottaa, että Sequelize synkronoi muutokset tietokantaan. Tämänhän saavat aikaan tiedostossa models/index.js olevat rivit

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

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

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

Tämä toimintatapa ei kuitenkaan ole pitkässä juoksussa järkevä. Poistetaan synkronoinnin tekevät rivit ja siirrytään käyttämään paljon robustimpaa tapaa, Sequelizen (ja monien muiden kirjastojen) tarjoamia migraatioita.

Käytännössä migraatio on yksittäinen JavaScript-tiedosto, joka kuvaa jonkin tietokantaan tehtävän muutoksen. Jokaista yksittäistä tai useampaa kerralla tapahtuvaa muutosta varten tehdään oma migraatio-tiedosto. Sequelize pitää kirjaa siitä, mitkä migraatioista on suoritettu, eli minkä migraatioiden aiheuttama muutos on synkronoitu tietokannan skeemaan. Uusien migraatioiden luomisen myötä Sequelize pysyy ajan tasalla siitä, mitkä muutokset kannan skeemaan on vielä tekemättä. Näin muutokset tehdään hallitusti, versionhallintaan talletetulla ohjelmakoodilla.

Luodaan aluksi migraatio, joka vie tietokannan sen nykyiseen tilaansa. Migraation koodi on seuraavassa:

const { DataTypes } = require('sequelize')

module.exports = {
  up: async ({ context: queryInterface }) => {
    await queryInterface.createTable('notes', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      content: {
        type: DataTypes.TEXT,
        allowNull: false
      },
      important: {
        type: DataTypes.BOOLEAN,
        allowNull: false
      },
      date: {
        type: DataTypes.DATE
      },
    })
    await queryInterface.createTable('users', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      username: {
        type: DataTypes.STRING,
        unique: true,
        allowNull: false
      },
      name: {
        type: DataTypes.STRING,
        allowNull: false
      },
    })
    await queryInterface.addColumn('notes', 'user_id', {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: { model: 'users', key: 'id' },
    })
  },
  down: async ({ context: queryInterface }) => {
    await queryInterface.dropTable('notes')
    await queryInterface.dropTable('users')
  },
}

Migraatiotiedostossa on määriteltynä funktiot up ja down joista ensimmäinen määrittelee miten tietokantaa tulee muuttaa migraatiota suorittaessa. Funktio down kertoo taas sen miten migraatio perutaan jos näin on tarvetta tehdä.

Migraatiomme sisältää kolme operaatiota, ensimmäinen luo taulun notes, toinen taulun users ja kolmas lisää tauluun notes viiteavaimen muistiinpanon luojaan. Skeeman muutokset määritellään queryInterface-olion metodeja kutsumalla.

Migraatioiden määrittelyssä on oleellista muistaa, että toisin kuin modeleissa, sarakkeiden ja taulujen nimet kirjoitetaan snake case ‑muodossa:

await queryInterface.addColumn('notes', 'user_id', {  type: DataTypes.INTEGER,
  allowNull: false,
  references: { model: 'users', key: 'id' },
})

Migraatioissa siis taulujen sekä sarakkeiden nimet kirjoitetaan juuri niin kuin ne tietokantaan tulevat, kun taas modeleissa on käytössä Sequelizen oletusarvoinen camelCase-nimentä.

Talletetaan migraation koodi tiedostoon migrations/20211209_00_initialize_notes_and_users.js. Migraatiotiedostojen nimien tulee olla aakkosjärjestyksessä siten, että aiempi muutos on aina aakkosissa uudempaa muutosta edellä. Eräs hyvä tapa saada tämä järjestys aikaan on aloittaa migraatiotiedoston nimi päivämäärällä sekä järjestysnumerolla.

Voisimme suorittaa migraatiot komentoriviltä käsin Sequelizen komentorivityökalun avulla. Päätämme kuitenkin suorittaa migraatiot ohjelmakoodista käsin Umzug-kirjastoa käyttäen. Asennetaan kirjasto

npm install umzug

Muutetaan tietokantayhteyden muodostavaa tiedostoa utils/db.js seuraavasti:

const Sequelize = require('sequelize')
const { DATABASE_URL } = require('./config')
const { Umzug, SequelizeStorage } = require('umzug')
const sequelize = new Sequelize(DATABASE_URL)

const runMigrations = async () => {  const migrator = new Umzug({     migrations: {      glob: 'migrations/*.js',    },    storage: new SequelizeStorage({ sequelize, tableName: 'migrations' }),    context: sequelize.getQueryInterface(),    logger: console,  })  const migrations = await migrator.up()  console.log('Migrations up to date', {    files: migrations.map((mig) => mig.name),  })}
const connectToDatabase = async () => {
  try {
    await sequelize.authenticate()
    await runMigrations()    console.log('database connected')
  } catch (err) {
    console.log('connecting database failed')
    console.log(err)
    return process.exit(1)
  }

  return null
}

module.exports = { connectToDatabase, sequelize }

Migraatiot suorittava funktio runMigrations suoritetaan nyt joka kerta kun sovellus käynnistyessään avaa tietokantayhteyden. Sequelize pitää kirjaa siitä mitkä migraatiot on jo suoritettu, eli jos uusia migratioita ei ole, ei funktion runMigrations suorittaminen tee mitään.

Aloitetaan nyt puhtaalta pöydältä ja poistetaan sovelluksesta kaikki olemassaolevat tietokantataulut:

username => drop table notes;
username => drop table users;
username => \d
Did not find any relations.

Käynnistetään sovellus. Lokiin tulostuu migraatioiden statuksesta kertova viesti

INSERT INTO "migrations" ("name") VALUES ($1) RETURNING "name";
Migrations up to date { files: [ '20211209_00_initialize_notes_and_users.js' ] }
database connected

Jos käynnistämme sovelluksen uudelleen, lokistakin on pääteltävissä että migraatiota ei suoriteta.

Sovelluksen tietokantaskeema näyttää nyt seuraavalta

postgres=# \d
                 List of relations
 Schema |     Name     |   Type   |     Owner
--------+--------------+----------+----------------
 public | migrations   | table    | username
 public | notes        | table    | username
 public | notes_id_seq | sequence | username
 public | users        | table    | username
 public | users_id_seq | sequence | username

Sequelize on siis luonut taulun migrations, jonka avulla se pitää kirjaa suoritetuista migraatiosta. Taulun sisältö näyttää seuraavalta:

postgres=# select * from migrations;
                   name
-------------------------------------------
 20211209_00_initialize_notes_and_users.js

Luodaan tietokantaan muutama käyttäjä sekä joukko muistiinpanoja, ja sen jälkeen olemme valmiina laajentamaan sovellusta.

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

Admin-käyttäjä ja käyttäjien disablointi

Haluamme siis lisätä tauluun users kaksi boolean-arvoista kenttää

  • admin kertoo onko käyttäjä admin
  • disabled taas kertoo sen onko käyttäjätunnus asetettu käyttökieltoon

Luodaan tietokantaskeeman tekevä migraatio tiedostoon migrations/20211209_01_admin_and_disabled_to_users.js:

const { DataTypes } = require('sequelize')

module.exports = {
  up: async ({ context: queryInterface }) => {
    await queryInterface.addColumn('users', 'admin', {
      type: DataTypes.BOOLEAN,
      default: false
    })
    await queryInterface.addColumn('users', 'disabled', {
      type: DataTypes.BOOLEAN,
      default: false
    })
  },
  down: async ({ context: queryInterface }) => {
    await queryInterface.removeColumn('users', 'admin')
    await queryInterface.removeColumn('users', 'disabled')
  },
}

Tehdään vastaavat muutokset taulua users vastaavaan modeliin:

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

Kun uusi migraatio suoritetaan koodin uudelleenkäynnistymisen yhteydessä, muuttuu skeema halutulla tavalla:

username-> \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 |
 admin    | boolean                |           |          |
 disabled | boolean                |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)
Referenced by:
    TABLE "notes" CONSTRAINT "notes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

Laajennetaan nyt kontrollereita seuraavasti. Estetään kirjautuminen jos käyttäjän kentän disabled arvona on true:

loginRouter.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'
    })
  }

  if (user.disabled) {    return response.status(401).json({      error: 'account disabled, please contact admin'    })  }
  const userForToken = {
    username: user.username,
    id: user.id,
  }

  const token = jwt.sign(userForToken, process.env.SECRET)

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

Disabloidaan käyttäjän jakousa tunnus:

username => update users set disabled=true where id=3;
UPDATE 1
username => update users set admin=true where id=1;
UPDATE 1
username => select * from users;
 id | username |       name       | admin | disabled
----+----------+------------------+-------+----------
  2 | lynx     | Kalle Ilves      |       |
  3 | jakousa  | Jami Kousa       | f     | t
  1 | mluukkai | Matti Luukkainen | t     |

Ja varmistetaan että kirjautuminen ei enää onnistu:

fullstack content

Tehdään vielä route, jonka avulla admin pystyy muuttamaan käyttäjän tunnuksen statusta:

const isAdmin = async (req, res, next) => {
  const user = await User.findByPk(req.decodedToken.id)
  if (!user.admin) {
    return res.status(401).json({ error: 'operation not permitted' })
  }
  next()
}

router.put('/:username', tokenExtractor, isAdmin, async (req, res) => {
  const user = await User.findOne({ 
    where: {
      username: req.params.username
    }
  })

  if (user) {
    user.disabled = req.body.disabled
    await user.save()
    res.json(user)
  } else {
    res.status(404).end()
  }
})

Käytössä on kaksi middlewarea, ensin kutsuttu tokenExtractor on sama mitä myös muistiinpanoja luova route käyttää, eli se asettaa dekoodatun tokenin request-olion kenttään decodedToken. Toisena suoritettava middleware isAdmin tarkastaa onko käyttäjä admin, ja jos ei, pyynnön statukseksi asetetaan 401 ja annetaan asiaan kuuluva virheilmoitus.

Huomaa, miten reitinkäsittelijään on siis ketjutettu kaksi middlewarea jotka molemmat suoritetaan ennen varsinaista reitinkäsittelijää. Middlewareja on mahdollista ketjuttaa pyyntöjen yhteyteen mielivaltainen määrä.

Middleware tokenExtractor on nyt siirretty tiedostoon util/middleware.js koska sitä käytetään useasta paikasta.

const jwt = require('jsonwebtoken')
const { SECRET } = require('./config.js')

const tokenExtractor = (req, res, next) => {
  const authorization = req.get('authorization')
  if (authorization && authorization.toLowerCase().startsWith('bearer ')) {
    try {
      req.decodedToken = jwt.verify(authorization.substring(7), SECRET)
    } catch{
      return res.status(401).json({ error: 'token invalid' })
    }
  } else {
    return res.status(401).json({ error: 'token missing' })
  }
  next()
}

module.exports = { tokenExtractor }

Admin voi nyt enabloida jakousan tunnuksen tekemällä routeen /api/users/jakousa PUT-pyynnön, missä pyynnön mukana on seuraava data:

{
    "disabled": false
}

Kuten osan 4 loppupuolella todetaan, tässä toteuttamamme tapa käyttäjätunnusten disablointiin on ongelmallinen. Se onko tunnus disabloitu tarkastetaan ainoastaan kirjautumisen yhteydessä. Jos käyttäjällä on token hallussaan siinä vaiheessa kun tunnus disabloidaan, voi käyttäjä jatkaa saman tokenin käyttöä, sillä tokenille ei ole asetettu elinikää eikä sitä seikkaa, että käyttäjän tunnus on disabloitu tarkasteta muistiinpanojen luomisen yhteydessä.

Ennen kuin jatkamme eteenpäin, tehdään sovellukselle npm-skripti, jonka avulla edellinen migraatio on mahdollista perua. Kaikki ei nimittäin mene aina ensimmäisellä kerralla oikein migraatioita kehitettäessä.

Muutetaan tiedostoa util/db.js seuraavasti:

const Sequelize = require('sequelize')
const { DATABASE_URL } = require('./config')
const { Umzug, SequelizeStorage } = require('umzug')

const sequelize = new Sequelize(DATABASE_URL, {
  dialectOptions: {
    ssl: {
      require: true,
      rejectUnauthorized: false
    }
  },
});

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

  return null
}

const migrationConf = {  migrations: {    glob: 'migrations/*.js',  },  storage: new SequelizeStorage({ sequelize, tableName: 'migrations' }),  context: sequelize.getQueryInterface(),  logger: console,}const runMigrations = async () => {  const migrator = new Umzug(migrationConf)  const migrations = await migrator.up()  console.log('Migrations up to date', {    files: migrations.map((mig) => mig.name),  })}const rollbackMigration = async () => {  await sequelize.authenticate()  const migrator = new Umzug(migrationConf)  await migrator.down()}
module.exports = { connectToDatabase, sequelize, rollbackMigration }

Tehdään tiedosto util/rollback.js, jonka kautta npm-skripti pääsee suorittamaan määritellyn migraation peruvan funktion:

const { rollbackMigration } = require('./db')

rollbackMigration()

ja itse skripti:

{
  "scripts": {
    "dev": "nodemon index.js",
    "migration:down": "node util/rollback.js"  },
}

Voimme nyt siis perua edellisen migraation suorittamalla komentoriviltä npm run migration:down.

Migraatiot suoritetaan automaattisesti kun ohjelma käynnistetään. Ohjelman kehitysvaiheessa saattaisi välillä olla tarkoituksenmukaisempaa poistaa migraatioiden automaattinen suoritus ja tehdä migraatiot komentoriviltä käsin.

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

Monen suhde moneen ‑yhteydet

Jatketaan sovelluksen laajentamista siten, että jokainen käyttäjä voidaan lisätä yhteen tai useampaan tiimiin.

Koska yhteen tiimiin voi liittyä mielivaltainen määrä käyttäjiä, ja yksi käyttäjä voi liittyä mielivaltaiseen määrään tiimejä, on kysessä many-to-many eli monen-suhde-moneen tyyppinen yhteys, joka perinteisesti toteutetaan relaatiotietokannoissa liitostaulun avulla.

Luodaan nyt tiimin sekä liitostaulun tarvitsema koodi. Tiedostoon 2021120902addteamsand_memberships.js talletettava migraatio on seuraavassa:

const { DataTypes } = require('sequelize')

module.exports = {
  up: async ({ context: queryInterface }) => {
    await queryInterface.createTable('teams', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      name: {
        type: DataTypes.TEXT,
        allowNull: false,
        unique: true
      },
    })
    await queryInterface.createTable('memberships', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      user_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'users', key: 'id' },
      },
      team_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'teams', key: 'id' },
      },
    })
  },
  down: async ({ context: queryInterface }) => {
    await queryInterface.dropTable('teams')
    await queryInterface.dropTable('memberships')
  },
}

Modelit sisältävät lähes saman koodin kuin migraatio. Tiimin modeli models/team.js:

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

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

class Team extends Model {}

Team.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.TEXT,
    allowNull: false,
    unique: true
  },
}, {
  sequelize,
  underscored: true,
  timestamps: false,
  modelName: 'team'
})

module.exports = Team

Liitostaulun modeli models/membership.js:

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

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

class Membership extends Model {}

Membership.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  userId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: { model: 'users', key: 'id' },
  },
  teamId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: { model: 'teams', key: 'id' },
  },
}, {
  sequelize,
  underscored: true,
  timestamps: false,
  modelName: 'membership'
})

module.exports = Membership

Olemme siis antaneet liitostaululle kuvaavan nimen, membership. Liitostauluille ei aina löydy yhtä osuvaa nimeä, tällöin liitostaulun nimi voidaan muodostaa yhdistelmänä liitettävien taulujen nimistä esim. user_teams voisi sopia tilanteeseemme.

Tiedostoon models/index.js tulee pieni lisäys, joka liittää metodin belongsToMany avulla tiimit ja käyttäjät toisiinsa myös koodin tasolla.

const Note = require('./note')
const User = require('./user')
const Team = require('./team')const Membership = require('./membership')
Note.belongsTo(User)
User.hasMany(Note)

User.belongsToMany(Team, { through: Membership })Team.belongsToMany(User, { through: Membership })
module.exports = {
  Note, User, Team, Membership}

Huomaa eroavaisuus liitostaulun migraation ja modelin välillä viiteavainkenttien määrittelyssä. Migraatiossa kentät määritellään snake case ‑muodossa:

await queryInterface.createTable('memberships', {
  // ...
  user_id: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: { model: 'users', key: 'id' },
  },
  team_id: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: { model: 'teams', key: 'id' },
  }
})

modelissa taas samat määritellään camel casena:

Membership.init({
  // ...
  userId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: { model: 'users', key: 'id' },
  },
  teamId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: { model: 'teams', key: 'id' },
  },
  // ...
})

Luodaan nyt pqql-konsolista pari tiimiä sekä muutama jäsenyys:

insert into teams (name) values ('toska');
insert into teams (name) values ('mosa climbers');
insert into memberships (user_id, team_id) values (1, 1);
insert into memberships (user_id, team_id) values (1, 2);
insert into memberships (user_id, team_id) values (2, 1);
insert into memberships (user_id, team_id) values (3, 2);

Lisätään sitten kaikkien käyttäjien reittiin tieto käyttäjän joukkueista

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

Tarkkasilmäisimmät huomaavat, että konsoliin tulostuva kysely yhdistää nyt kolme taulua.

Ratkaisu on aika hyvä, mutta siinä on eräs kauneusvirhe. Tuloksen mukana ovat myös liitostaulun rivin attribuutit vaikka emme niitä halua:

fullstack content

Dokumentaatiota tarkkaan lukemalla löytyy ratkaisu:

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

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

Huomio Sequelizen model-olioiden ominaisuuksista

Modeliemme määrittely sisälsi mm. seuraavat rivit:

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

User.belongsToMany(Team, { through: Membership })
Team.belongsToMany(User, { through: Membership })

Näiden ansiosta Sequelize osaa tehdä kyselyt, jotka hakevat esim. käyttäjien kaikki muistiinpanot, tai joukkueen kaikki jäsenet.

Määrittelyjen ansiosta pääsemme myös koodissa suoraan käsiksi esim. käyttäjän muistiinpanoihin. Seuraavassa haetaan käyttäjä, jonka id on 1 ja tulostetaan käyttäjään liittyvät muistiinpanot:

const user = await User.findByPk(1, {
  include: {
    model: Note
  }
})

user.notes.forEach(note => {
  console.log(note.content)
})

Määrittely User.hasMany(Note) siis liittää user-olioille attribuutin notes, jonka kautta päästään käsiksi käyttäjän tekemiin muistiinpanoihin. Määrittely User.belongsToMany(Team, { through: Membership })) liittää vastaavasti käyttäjille attribuutin teams jota on myös mahdollisuus hyödyntää koodissa:

const user = await User.findByPk(1, {
  include: {
    model: Team
  }
})

user.teams.forEach(team => {
  console.log(team.name)
})

Oletetaan että haluaisimme palauttaa yksittäisen käyttäjän reitiltä jsonin, joka sisältää käyttäjän nimen, käyttäjätunnuksen sekä luotujen muistiinpanojen määrän. Voisimme yrittää seuravaa:

router.get('/:id', async (req, res) => {
  const user = await User.findByPk(req.params.id, {
    include: {
        model: Note
      }
    }
  )

  if (user) {
    user.noteCount = user.notes.length    delete user.notes    res.json(user)

  } else {
    res.status(404).end()
  }
})

Eli yritimme liittää Sequelizen palauttamaan olioon kentän noteCount sekä poistaa siitä muistiinpanot sisältävän kentän notes. Tämä lähestymistapa ei kuitenkaan toimi, sillä Sequelizen palauttamat oliot eivät ole normaaleja olioita, joihin uusien kenttien lisääminen toimii siten kuin haluamme.

Parempi ratkaisu onkin luoda tietokannasta haetun datan perusteella kokonaan uusi olio:

router.get('/:id', async (req, res) => {
  const user = await User.findByPk(req.params.id, {
    include: {
        model: Note
      }
    }
  )

  if (user) {
    res.json({
      username: user.username,      name: user.name,      noteCount: user.notes.length    })

  } else {
    res.status(404).end()
  }
})

Monen suhde moneen uudelleen

Tehdään sovellukseen vielä toinen monesta moneen ‑yhteys. Jokaiseen muistiinpanoon liittyy sen luonut käyttäjä viiteavaimen kautta. Päätetään, että sovellus tukee myös sitä, että muistiinpanoon voidaan liittää muitakin käyttäjiä, ja että käyttäjään voi liittyä mielivaltainen määrä jonkun muun käyttäjän tekemiä muistiinpanoja. Ajatellaan että nämä muistiinpanot ovat sellaisia, jotka käyttäjä on merkinnyt itselleen.

Tehdään tilannetta varten liitostaulu user_notes. Migraatio, joka tallennetaan tiedostoon 20211209_03_add_user_notes.js on suoraviivainen:

const { DataTypes } = require('sequelize')

module.exports = {
  up: async ({ context: queryInterface }) => {
    await queryInterface.createTable('user_notes', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      user_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'users', key: 'id' },
      },
      note_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: { model: 'notes', key: 'id' },
      },
    })
  },
  down: async ({ context: queryInterface }) => {
    await queryInterface.dropTable('user_notes')
  },
}

Myöskään modelissa ei ole mitään erikoista:

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

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

class UserNotes extends Model {}

UserNotes.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  userId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: { model: 'users', key: 'id' },
  },
  noteId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: { model: 'notes', key: 'id' },
  },
}, {
  sequelize,
  underscored: true,
  timestamps: false,
  modelName: 'userNotes'
})

module.exports = UserNotes

Tiedostoon models/index.js sen sijaan tulee hienoinen muutos aiemmin näkemäämme:

const Note = require('./note')
const User = require('./user')
const Team = require('./team')
const Membership = require('./membership')
const UserNotes = require('./user_notes')
Note.belongsTo(User)
User.hasMany(Note)

User.belongsToMany(Team, { through: Membership })
Team.belongsToMany(User, { through: Membership })

User.belongsToMany(Note, { through: UserNotes, as: 'markedNotes' })Note.belongsToMany(User, { through: UserNotes, as: 'usersMarked' })
module.exports = {
  Note, User, Team, Membership, UserNotes
}

Käytössä on taas belongsToMany joka liittää käyttäjän muistiinpanoihin liitostaulua vastaavan modelin UserNotes kautta. Annamme kuitenkin tällä kertaa avainsanaa as käyttäen muodostuvalle attribuutille aliasnimen, oletusarvoinen nimi (käyttäjillä notes) menisi päällekkäin sen aiemman merkityksen, eli käyttäjän luomien muistiinpanojen kanssa.

Laajennetaan yksittäisen käyttäjän routea siten, että se palauttaa käyttäjän joukkueet, omat muistiinpanot sekä käyttäjään liitetyt muut muistiinpanot:

router.get('/:id', async (req, res) => {
  const user = await User.findByPk(req.params.id, { 
    attributes: { exclude: [''] } ,
    include:[{
        model: Note,
        attributes: { exclude: ['userId'] }
      },
      {        model: Note,        as: 'markedNotes',        attributes: { exclude: ['userId']},        through: {          attributes: []        },      },      {
        model: Team,
        attributes: ['name', 'id'],
        through: {
          attributes: []
        }
      },
    ]
  })

  if (user) {
    res.json(user)
  } else {
    res.status(404).end()
  }
})

Includen yhteydessä on nyt mainittava as-määrettä käyttäen äsken määrittelemämme aliasnimi markedNotes.

Jotta ominaisuutta päästään testaamaan, luodaan tietokantaan hieman testidataa:

insert into user_notes (user_id, note_id) values (2, 1);
insert into user_notes (user_id, note_id) values (2, 2);

Lopputulos on toimiva:

fullstack content

Entä jos haluaisimme, että käyttäjän merkitsemissä muistiinpanoissa olisi myös tieto muistiinpanon tekijästä? Tämä onnistuu lisäämällä liitetyille muistiinpanoille oma include:

router.get('/:id', async (req, res) => {
  const user = await User.findByPk(req.params.id, { 
    attributes: { exclude: [''] } ,
    include:[{
        model: Note,
        attributes: { exclude: ['userId'] }
      },
      {
        model: Note,
        as: 'marked_notes',
        attributes: { exclude: ['userId']},
        through: {
          attributes: []
        },
        include: {          model: User,          attributes: ['name']        }      },
      {
        model: Team,
        attributes: ['name', 'id'],
        through: {
          attributes: []
        }
      },
    ]
  })

  if (user) {
    res.json(user)
  } else {
    res.status(404).end()
  }
})

Lopputulos on halutun kaltainen:

fullstack content

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

Loppuhuomioita

Sovelluksemme alkaa olla vähintään kelvollisessa kunnossa. Ennen osan loppua tarkastellaan kuitenkin vielä muutamaa seikkaa.

Eager vs lazy fetch

Kun teemme kyselyt käyttäen include-määrettä:

User.findOne({
  include: {
    model: Note
  }
})

tapahtuu niin sanottu eager fetch eli kaikki haettavaan käyttäjään liitoskyselyllä liitettävien taulujen rivit, esimerkin tapauksessa käyttäjän tekemät muistiinpanot, haetaan samalla tietokannasta. Tämä on usein se mitä haluamme, mutta on myös tilanteita joissa haluttaisiin tehdä ns. lazy fetch eli hakea vaikkapa käyttäjään liittyvät joukkueet ainoastaan jos niitä tarvitaan.

Muutetaan nyt yksittäisen käyttäjän routea siten, että se hakee kannasta käyttäjän joukkueet ainoastaan jos pyynnölle on asetettu query parametri teams:

router.get('/:id', async (req, res) => {
  const user = await User.findByPk(req.params.id, {
    attributes: { exclude: [''] } ,
    include:[{
        model: note,
        attributes: { exclude: ['userId'] }
      },
      {
        model: Note,
        as: 'marked_notes',
        attributes: { exclude: ['userId']},
        through: {
          attributes: []
        },
        include: {
          model: user,
          attributes: ['name']
        }
      },
    ]
  })

  if (!user) {
    return res.status(404).end()
  }

  if (!user) {    return res.status(404).end()  }  let teams = undefined  if (req.query.teams) {    teams = await user.getTeams({      attributes: ['name'],      joinTableAttributes: []      })  }  res.json({ ...user.toJSON(), teams })})

Nyt siis User.findByPk-kysely ei hae joukkueita, vaan ne haetaan tarvittaessa tietokantariviä vastaavan olion user metodilla getTeams, jonka Sequelize on generoinut modelin oliolle automaattisesti. Vastaava get- ja muutamia muitakin hyödyllisiä metodeja generoituu automaattisesti kun tauluille määritellään Sequelizen tasolla assosiaatioita.

Modelien ominaisuuksia

On joitain tilanteita, missä emme oletusarvoisesti halua käsitellä kaikkia tietyn taulun rivejä. Eräs tällainen tapaus voisi olla se, että emme normaalisti haluasi näyttää sovelluksessamme niitä käyttäjiä joiden tunnus on suljettu (disabled). Tälläisessä tilanteessa voisimme määritellä modelille oletusarvoisen scopen:

class User extends Model {}

User.init({
  // kenttien määrittely
}, {
  sequelize,
  underscored: true,
  timestamps: false,
  modelName: 'user',
  defaultScope: {    where: {      disabled: false    }  },})

module.exports = User

Nyt funktiokutsun User.findAll() aiheuttamassa kyselyssä on seuraava where-ehto:

WHERE "user"."disabled" = false;

Modeleille on mahdollista määritellä myös muita scopeja:

User.init({
  // kenttien määrittely
}, {
  sequelize,
  underscored: true,
  timestamps: false,
  modelName: 'user',
  defaultScope: {
    where: {
      disabled: false
    }
  },
  scopes: {    admin: {      where: {        admin: true      }    },    disabled: {      where: {        disabled: true      }    },    name(value) {      return {        where: {          name: {            [Op.iLike]: value          }        }      }    },  }})

Scopeja käytetään seuraavasti:

// kaikki adminit
const adminUsers = await User.scope('admin').findAll()

// kaikki epäaktiiviset käyttäjät
const disabledUsers = await User.scope('disabled').findAll()

// käyttäjät, joiden nimessä merkkijono jami
const jamiUsers =  User.scope({ method: ['name', '%jami%'] }).findAll()

Scopeja on myös mahdollista ketjuttaa:

// adminit, joiden nimessä merkkijono jami
const jamiUsers =  User.scope('admin', { method: ['name', '%jami%'] }).findAll()

Koska Sequelizen modelit ovat normaaleja JavaScript-luokkia, on niihin mahdollista lisätä uusia metodeja.

Seuraavassa kaksi esimerkkiä:

const { Model, DataTypes, Op } = require('sequelize')
const Note = require('./note')
const { sequelize } = require('../util/db')

class User extends Model {
  async numberOfNotes() {    return (await this.getNotes()).length  }
  static async withNotes(limit){    return await User.findAll({      attributes: {        include: [[ sequelize.fn("COUNT", sequelize.col("notes.id")), "note_count" ]]      },      include: [        {          model: Note,          attributes: []        },      ],      group: ['user.id'],      having: sequelize.literal(`COUNT(notes.id) > ${limit}`)    })  }}

User.init({
  // ...
})

module.exports = User

Ensimmäinen metodeista numberOfNotes on instanssimetodi, eli sitä voidaan kutsua modelin instansseille:

const jami = await User.findOne({ name: 'Jami Kousa'})
const cnt = await jami.numberOfNotes()
console.log(`Jami has created ${cnt} notes`)

Instanssimetodin sisällä avainsanalla this siis viitataan instanssiin itseensä:

async numberOfNotes() {
  return (await this.getNotes()).length
}

Metodeista toinen, joka palauttaa ne käyttäjät, joilla on vähintään parametrin verran muistiinpanoja, on taas luokkametodi eli sitä kutsutaan suoraan modelille:

const users = await User.withNotes(2)
console.log(JSON.stringify(users, null, 2))
users.forEach(u => {
  console.log(u.name)
})

Modelien ja migraatioiden toisteisuus

Olemme huomanneet, että modelien ja migraatioiden koodi on hyvin toisteista. Esimerkiksi joukkueiden model

class Team extends Model {}

Team.init({
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.TEXT,
    allowNull: false,
    unique: true
  },
}, {
  sequelize,
  underscored: true,
  timestamps: false,
  modelName: 'team'
})

module.exports = Team

ja migraatio sisältävät paljon samaa

const { DataTypes } = require('sequelize')

module.exports = {
  up: async ({ context: queryInterface }) => {
    await queryInterface.createTable('teams', {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      name: {
        type: DataTypes.TEXT,
        allowNull: false,
        unique: true
      },
    })
  },
  down: async ({ context: queryInterface }) => {
    await queryInterface.dropTable('teams')
  },
}

Emmekö voisi optimoida koodia siten, että esim. model exporttaisi jaetut osat migraation käyttöön?

Ongelman muodostaa kuitenkin se, että modelin määritelmä voi muuttua ajan myötä, esimerkiksi kenttä name voi muuttaa nimeä tai sen datatyyppi voi vaihtua. Migraatiot tulee pystyä suorittamaan milloin tahansa onnistuneesti alusta loppuun, ja jos migraatiot luottavat että modelilla on tietty sisältö, ei asia enää välttämättä pidä paikkaansa kuukauden tai vuoden kuluttua. Siispä migraatioiden koodin on syytä olla "copy pastesta" huolimatta täysin erillään modelien koodista.

Eräs ratkaisu asiaan olisi Sequelizen komentorivityökalun käyttö, joka luo sekä modelit että migratiotiedostot komentorivillä annettujen komentojen perusteella. Esim. seuraava komento loisi modelin User, jolla on attribuutteina name, username ja admin sekä tietokantataulun luomisen hoitavan migraation:

npx sequelize-cli model:generate --name User --attributes name:string,username:string,admin:boolean

Komentoriviltä käsin voi myös suorittaa sekä rollbackata eli perua migraatioita. Komentorivin dokumentaatio on valitettavan ohkaista ja tällä kurssilla päätimmekin tehdä sekä modelit että migratiot käsin. Ratkaisu saattoi olla viisas tai sitten ei.

Ehdota muutosta materiaalin sisältöön