Repositoryを通じてEntityの検索、挿入、更新、削除をすることができます。ここではRepositoryで提供されいてる主なメソッド(findOne, find, save, remove…)で実行されるSQLを確認します。

環境

typeormのバージョンは以下の通りです。DBはMySQLを利用してます。

$ npm list --depth=0 | grep typeorm@
├── typeorm@0.2.20

取得

findOneとfind

findOne は条件に一致する 1Entity を取得します。find は全て取得します。

import { createConnection, getRepository, Raw } from 'typeorm'
import { User } from '../entity/User'

(async () => {
  const connection = await createConnection()

  const userRepository = getRepository(User)

  const userA = await userRepository.findOne(1)
  // query: SELECT `User`.`id` AS `User_id`, `User`.`name` AS `User_name`, `User`.`created_date` AS `User_created_date`, `User`.`updated_date` AS `User_updated_date`
  //        FROM `users` `User`
  //        WHERE `User`.`id` IN (?)
  //        LIMIT 1
  //        -- PARAMETERS: [1]


  const userB = await userRepository.findOne({name: 'xxx'})
  // query: SELECT `User`.`id` AS `User_id`, `User`.`name` AS `User_name`, `User`.`created_date` AS `User_created_date`, `User`.`updated_date` AS `User_updated_date`
  //        FROM `users` `User`
  //        WHERE `User`.`name` = ?
  //        LIMIT 1
  //        -- PARAMETERS: ["xxx"]


  const users = await userRepository.find({name: 'xxx'})
  // query: SELECT `User`.`id` AS `User_id`, `User`.`name` AS `User_name`, `User`.`created_date` AS `User_created_date`, `User`.`updated_date` AS `User_updated_date`
  //        FROM `users` `User`
  //        WHERE `User`.`name` = ?
  //        -- PARAMETERS: ["xxx"]

  await connection.close()
})()

findのオプション

オプションを取得条件を指定できます。

await userRepository.find({
  select: ['name'],
  where: {
    createdDate: Raw(alias => `${alias} > NOW()`)
  },
  order: {
    name: 'ASC',
    id: 'DESC'
  },
  skip: 5,
  take: 10
})
// query: SELECT `User`.`name` AS `User_name`, `User`.`id` AS `User_id`
//        FROM `users` `User`
//        WHERE `User`.`created_date` > NOW()
//        ORDER BY `User`.`name` ASC,
//                 `User`.`id` DESC
//        LIMIT 10
//        OFFSET 5

リレーションを取得するには、relationsプロパティ で指定します。

await userRepository.find({
  select: ['name'],
  relations: ['roles', 'posts']
})
// query: SELECT `User`.`name` AS `User_name`, `User`.`id` AS `User_id`,
//               `User__roles`.`id` AS `User__roles_id`, `User__roles`.`name` AS `User__roles_name`,
//               `User__posts`.`id` AS `User__posts_id`, `User__posts`.`body` AS `User__posts_body`, `User__posts`.`user_id` AS `User__posts_user_id`
//        FROM `users` `User`
//        LEFT JOIN `roles_users` `User__roles_User`
//        ON `User__roles_User`.`user_id`=`User`.`id`
//        LEFT JOIN `roles` `User__roles`
//        ON `User__roles`.`id`=`User__roles_User`.`role_id`
//        LEFT JOIN `posts` `User__posts`
//        ON `User__posts`.`user_id`=`User`.`id`

save｜entityを指定

挿入として機能

まだ存在しな場合、INSERT が実行されました。

import { createConnection, getRepository } from 'typeorm'
import { User } from '../entity/User'

(async () => {
  const connection = await createConnection()
  const userRepository = getRepository(User)

  const user = userRepository.create({name: 'xxx'})
  console.log(userRepository.hasId(user)) // false

  await userRepository.save(user)
  // query: START TRANSACTION
  // query: INSERT INTO `users`(`id`, `name`) VALUES (DEFAULT, ?) -- PARAMETERS: ["xxx"]
  // query: COMMIT

  console.log(userRepository.hasId(user)) // true
  await connection.close()
})()

更新として機能

すでに存在するEntityを指定した場合、UPDATE が実行されました。

import { createConnection, getRepository } from 'typeorm'
import { User } from '../entity/User'

(async () => {
  const connection = await createConnection()
  const userRepository = getRepository(User)

  const user = await userRepository.findOne({name: 'xxx'})
  // query: SELECT `User`.`id` AS `User_id`, `User`.`name` AS `User_name`
  //        FROM `users` `User` WHERE `User`.`name` = ? LIMIT 1
  //        -- PARAMETERS: ["xxx"]

  user.name = 'yyy'
  console.log(userRepository.hasId(user)) // true

  await userRepository.save(user)
  // query: SELECT `User`.`id` AS `User_id`, `User`.`name` AS `User_name`
  //        FROM `users` `User` WHERE `User`.`id` IN (?)
  //        -- PARAMETERS: [4]
  // query: START TRANSACTION
  // query: UPDATE `users` SET `name` = ? WHERE `id` IN (?)
  //        -- PARAMETERS: ["yyy",4]
  // query: COMMIT

  console.log(userRepository.hasId(user)) // true
  await connection.close()
})()

remove｜entityを指定

import { createConnection, getRepository } from 'typeorm'
import { User } from '../entity/User'

(async () => {
  const connection = await createConnection()
  const userRepository = getRepository(User)

  const user = await userRepository.findOne({name: 'xxx'})

  await userRepository.remove(user)
  // query: SELECT `User`.`id` AS `User_id`, `User`.`name` AS `User_name`, `User`.`created_date` AS `User_created_date`, `User`.`updated_date` AS `User_updated_date` FROM `users` `User` WHERE `User`.`id` IN (?) -- PARAMETERS: [5]
  // query: SELECT `User_roles_rid`.`role_id` AS `role_id`, `User_roles_rid`.`user_id` AS `user_id` FROM `roles` `roles` INNER JOIN `roles_users` `User_roles_rid` ON (`User_roles_rid`.`user_id` = ? AND `User_roles_rid`.`role_id` = `roles`.`id`) ORDER BY `User_roles_rid`.`role_id` ASC, `User_roles_rid`.`user_id` ASC -- PARAMETERS: [5]
  // query: START TRANSACTION
  // query: DELETE FROM `users` WHERE `id` = ? -- PARAMETERS: [5]
  // query: COMMIT

  await connection.close()
})()

insert, update, delete

insert

const insertResult = await userRepository.insert({name: 'xxx'})
// query: INSERT INTO `users`(`id`, `name`, `created_date`, `updated_date`) VALUES (DEFAULT, ?, DEFAULT, DEFAULT) -- PARAMETERS: ["xxx"]
// query: SELECT `User`.`id` AS `User_id`, `User`.`created_date` AS `User_created_date`, `User`.`updated_date` AS `User_updated_date` FROM `users` `User` WHERE `User`.`id` = ? -- PARAMETERS: [5]

console.log(insertResult)
// InsertResult {
//   identifiers: [ { id: 5 } ],
//   generatedMaps:
//    [ { id: 5,
//        createdDate: 2019-11-27T16:07:02.006Z,
//        updatedDate: 2019-11-27T16:07:02.006Z } ],
//   raw:
//    OkPacket {
//      fieldCount: 0,
//      affectedRows: 1,
//      insertId: 5,
//      serverStatus: 2,
//      warningCount: 0,
//      message: '',
//      protocol41: true,
//      changedRows: 0 } }

update

const updateResult = await userRepository.update({name: 'yyy'}, {name: 'xxx'})
// query: UPDATE `users` SET `name` = ?, `updated_date` = CURRENT_TIMESTAMP WHERE `name` = ? -- PARAMETERS: ["xxx","yyy"]

console.log(updateResult)
// UpdateResult {
//   generatedMaps: [],
//   raw:
//    OkPacket {
//      fieldCount: 0,
//      affectedRows: 0,
//      insertId: 0,
//      serverStatus: 34,
//      warningCount: 0,
//      message: '(Rows matched: 0  Changed: 0  Warnings: 0',
//      protocol41: true,
//      changedRows: 0 } }

delete

const deleteResult = await userRepository.delete({name: 'yyy'})
// query: DELETE FROM `users` WHERE `name` = ? -- PARAMETERS: ["yyy"]

console.log(deleteResult)
// DeleteResult {
//   raw:
//    OkPacket {
//      fieldCount: 0,
//      affectedRows: 0,
//      insertId: 0,
//      serverStatus: 34,
//      warningCount: 0,
//      message: '',
//      protocol41: true,
//      changedRows: 0 },
//   affected: 0 }

参考

• https://typeorm.io/#/working-with-repository
• https://typeorm.io/#/find-options
• https://typeorm.io/#/repository-api