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 }