Query BuilderでSQLを構築(取得, 挿入, 更新, 削除)

Query Builderを利用してSQLクエリを構築することができます。ここでは、基本的なSQLクエリ(SELECT, INSERT, UPDATE, DELETE)の構築方法を確認していきます。

QueryBuilderについて

使用方法

Connection Repository から QueryBuilder のインスタンスを取得できます。

また、QueryBuilderクラス を継承しているクラス( SelectQueryBuilder InsertQueryBuilder UpdateQueryBuilder DeleteQueryBuilder RelationQueryBuilder)が存在しており、それぞれ目的別に必要な機能が提供されています。

下記コードで確認します。

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

(async () => {
  const connection = await createConnection()
  console.log(connection.options.type)

  console.log(`Connectionを使用: ${connection.createQueryBuilder().constructor.name}`)
  console.log(`Repositoryを使用: ${getRepository(User).createQueryBuilder().constructor.name}`)

  console.log()
  console.log('QueryBuilderの5つのタイプ')
  console.log(`select(): ${connection.createQueryBuilder().select().constructor.name}`)
  console.log(`insert(): ${connection.createQueryBuilder().insert().constructor.name}`)
  console.log(`update(): ${connection.createQueryBuilder().update().constructor.name}`)
  console.log(`delete(): ${connection.createQueryBuilder().delete().constructor.name}`)
  console.log(`relation(): ${connection.createQueryBuilder().relation(User, 'roles').constructor.name}`)

  await connection.close()
})()
mysql
Connectionを使用: SelectQueryBuilder
Repositoryを使用: SelectQueryBuilder

QueryBuilderの5つのタイプ
select(): SelectQueryBuilder
insert(): InsertQueryBuilder
update(): UpdateQueryBuilder
delete(): DeleteQueryBuilder
relation(): RelationQueryBuilder

構築クエリの確認方法
( getSql )

getSqlメソッドで構築されるクエリを確認できます。

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

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

  console.log(createQueryBuilder().from(User, 'users').getSql())

  await connection.close()
})()
SELECT * FROM `users` `users`

SelectQueryBuilder

select, addSelect, from

console.log(createQueryBuilder(User, 'users').getSql())
// SELECT `users`.`id` AS `users_id`, `users`.`name` AS `users_name` FROM `users` `users`

console.log(createQueryBuilder(User, 'xxx_users').getSql())
// SELECT `xxx_users`.`id` AS `xxx_users_id`, `xxx_users`.`name` AS `xxx_users_name` FROM `users` `xxx_users`

console.log(createQueryBuilder().from(User, 'users').getSql())
// SELECT * FROM `users` `users`

console.log(createQueryBuilder().from(User, 'xxx_users').getSql())
// SELECT * FROM `users` `xxx_users`

console.log(createQueryBuilder().select('xxx_users.id').from(User, 'xxx_users').getSql())
// SELECT `xxx_users`.`id` AS `xxx_users_id` FROM `users` `xxx_users`

console.log(createQueryBuilder().select('id, name').from(User, 'users').getSql())
// SELECT id, name FROM `users` `users`

console.log(createQueryBuilder().select('COUNT(id)', 'count').addSelect('name').from(User, 'xxx_users').getSql())
// SELECT COUNT(id) AS `count`, name FROM `users` `xxx_users`

where, andWhere, orWhere

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

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

  console.log(
    createQueryBuilder()
      .from(User, 'users')
      .where('name = :name', {name: 'wakuwaku'})
      .getSql()
  )
  // SELECT * FROM `users` `users` WHERE name = ?

  console.log(
    createQueryBuilder()
      .from(User, 'users')
      .where('users.name = :name', {name: 'wakuwaku'})
      .getSql()
  )
  // SELECT * FROM `users` `users` WHERE `users`.`name` = ?

  console.log(
    createQueryBuilder()
      .from(User, 'users')
      .where('id > :id', {id: 1})
      .andWhere('name = :name', {name: 'wakuwaku'})
      .getSql()
  )
  // SELECT * FROM `users` `users` WHERE id > ? AND name = ?

  console.log(
    createQueryBuilder()
      .from(User, 'users')
      .where('id > :id', {id: 1})
      .orWhere('name = :name', {name: 'wakuwaku'})
      .getSql()
  )
  // SELECT * FROM `users` `users` WHERE id > ? OR name = ?
  
  console.log(
    createQueryBuilder()
      .from(User, 'users')
      .where('id > :id', {id: 1})
      .andWhere(new Brackets(qb => {
        qb.where('name = :name', {name: 'xxx'})
          .orWhere('name = :name', {name: 'yyy'})
      }))
      .getSql()
  )
  // SELECT * FROM `users` `users` WHERE id > ? AND (name = ? OR name = ?)

  await connection.close()
})()

orderBy, addOrderBy, skip, take

console.log(
  createQueryBuilder()
    .from(User, 'users')
    .orderBy('name')
    .addOrderBy('id', 'DESC')
    .skip(5)
    .take(10)
    .getSql()
)
// SELECT * FROM `users` `users` ORDER BY name ASC, id DESC LIMIT 10 OFFSET 5

groupBy, having

console.log(
  createQueryBuilder()
    .select('COUNT(id)', 'count')
    .from(User, 'users')
    .groupBy('name')
    .having('name = :name', {name: 'wakuwaku'})
    .getSql()
)
// SELECT COUNT(id) AS `count` FROM `users` `users` GROUP BY name HAVING name = ?

subQuery

console.log(
  createQueryBuilder()
    .from(User, 'users')
    .where('name in ' + createQueryBuilder()
      .subQuery()
      .select('tmp_users.name')
      .from(User, 'tmp_users')
      .getQuery())
    .getSql()
)
// SELECT * FROM `users` `users` WHERE name in (SELECT `tmp_users`.`name` AS `tmp_users_name` FROM `users` `tmp_users`)

leftJoin, leftJoinAndSelect

console.log(
  createQueryBuilder()
    .from(User, 'users')
    .leftJoin('users.posts', 'posts')
    .getSql()
)
// SELECT *
// FROM `users` `users`
// LEFT JOIN `posts` `posts`
// ON `posts`.`user_id`=`users`.`id`

console.log(
  createQueryBuilder()
    .from(User, 'users')
    .leftJoinAndSelect('users.posts', 'posts')
    .getSql()
)
// SELECT `posts`.`id` AS `posts_id`, `posts`.`body` AS `posts_body`, `posts`.`user_id` AS `posts_user_id`
// FROM `users` `users`
// LEFT JOIN `posts` `posts`
// ON `posts`.`user_id`=`users`.`id`

getXxx

console.log(await createQueryBuilder(User, 'users').getRawOne());
// RowDataPacket { users_id: 1, users_name: 'aaa' }

console.log(await createQueryBuilder(User, 'users').getOne());
// User { name: 'aaa', roles: undefined, id: 1 }

console.log(await createQueryBuilder(User, 'users').getRawMany());
// [ RowDataPacket { users_id: 1, users_name: 'aaa' }, RowDataPacket { users_id: 2, users_name: 'bbb' } ]

console.log(await createQueryBuilder(User, 'users').getMany());
// [ User { name: 'aaa', roles: undefined, id: 1 }, User { name: 'bbb', roles: undefined, id: 2 } ]

console.log(await createQueryBuilder(User, 'users').getManyAndCount());
// [ [ User { name: 'aaa', roles: undefined, id: 1 }, User { name: 'bbb', roles: undefined, id: 2 } ], 2 ]

InsertQueryBuilder

insert, into, values

executeメソッド で実行できます。

console.log(
  createQueryBuilder()
    .insert()
    .into(User)
    .values([
      {name: 'xxx'},
      {name: 'yyy'}
    ])
    .getSql()
)
// INSERT INTO `users`(`id`, `name`) VALUES (DEFAULT, ?), (DEFAULT, ?)

UpdateQueryBuilder

update, set, where

executeメソッド で実行できます。

console.log(
  createQueryBuilder()
    .update(User)
    .set({name: 'xxx'})
    .where('users.id = :id', {id: 1})
    .getSql()
)
// UPDATE `users` SET `name` = ? WHERE users.id = ?

DeleteQueryBuilder

delete, from, where

executeメソッド で実行できます。

console.log(
  createQueryBuilder()
    .delete()
    .from(User)
    .where('users.id = :id', {id: 1})
    .getSql()
)
// DELETE FROM `users` WHERE users.id = ?

RelationQueryBuilder

of, add, remove

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

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

  const user = await createQueryBuilder(User, 'users').getOne()
  console.log(user)
  // User { name: 'aaa', roles: undefined, id: 1 }

  const post = await createQueryBuilder(Post, 'posts').getOne()
  console.log(post)
  // Post { body: '111', user: undefined, id: 10 }

  await createQueryBuilder(User, 'users').relation(User, 'posts')
    .of(user)
    .add(post)
  // query: UPDATE `posts` SET `user_id` = ? WHERE `id` IN (?) -- PARAMETERS: [1,10]

  await createQueryBuilder(User, 'users').relation(User, 'posts')
    .of(user)
    .remove(post)
  // query: UPDATE `posts` SET `user_id` = ? WHERE (`user_id` = ? AND `id` = ?) -- PARAMETERS: [null,1,10]

  await connection.close()
})()

参考