Databases trong AdonisJS

AdonisJs hỗ trợ tương tác mạnh mẽ với database thông qua:

Query Builder, Lucid ORM, Migrations, Factories, and Seeds.

## Install thư viện hỗ trợ

Database NPM Driver

MariaDB npm i mysql or npm i mysql2

MSSQL npm i mssql

MySQL npm i mysql or npm i mysql2

Oracle npm i oracledb

PostgreSQL npm i pg

SQLite3 npm i sqlite3

## Setup Database Provider (Lucid)

> adonis install @adonisjs/lucid

`Thêm vào File start/app.js`

const providers = [

'@adonisjs/lucid/providers/LucidProvider'

]

const aceProviders = [

'@adonisjs/lucid/providers/MigrationsProvider'

]

`Chỉnh sửa file .env`

Mặc định thì Database Provider sử dụng sqlite.

Cần chỉnh sửa lại file .env để adonis có thể kết nối mysql cho bạn.

DB_CONNECTION=mysql

DB_HOST=localhost

DB_PORT=3306

DB_USER=root

DB_PASSWORD=root

DB_DATABASE=adonis

## Kết nối với nhiều database khác nhau

<!-->

const users = await Database

.connection('mysql')

.table('users')

-->

- Để đóng 1 connection:

> `Database.close(['mysql'])`

## Thêm prefix vào các table

<!-->

File config/database.js

module.exports = {

connection: 'sqlite',

sqlite: {

client: 'sqlite3',

prefix: 'my_'

}

}

-->

Ví dụ: await Database.table('users').select('*')

==> Output: select * from `my_users`

- Cũng có thể bỏ qua việc thêm prefix bằng cách gọi `withOutPrefix`

> await Database.withOutPrefix().table('users')

## Debugging

1. Chỉnh ở config/database.js

<!-->

module.exports = {

connection: 'sqlite',

sqlite: {

client: 'sqlite3',

connection: {},

debug: true

}

}

<-->

2. Hoặc có thể tinh chỉnh ở start/hooks.js

<!-->

const { hooks } = require('@adonisjs/ignitor')

hooks.after.providersBooted(() => {

const Database = use('Database')

Database.on('query', console.log)

})

-->

3. Hoặc cũng có thể xuất log trên từng query

> await Database.table('users').select('*').on('query', console.log)

## Query Builder có các API giúp bạn dễ dàng tạo SQL queries

const Database = use('Database')

Route.get('/', async () => {

return await Database.table('users').select('*')

})

1. `Selects`

> await Database.select('id', 'username').from('users')

> await Database.select('username as uname')

> await Database.select('*').from('users')

2. `Where Clause`

> Database.table('users').where('username', username)

> await Database.table('users').where('username', 'john').first()

> Database.table('users').where('age', '>', 18)

> const users = await Database.from('users').where({ id: 1 })

3. `orWhere`

> Database .table('users').where('age', '>', 18).orWhere('vip', true)

4. `whereNot`

> await Database.from('users').whereNot('age', '>', 15)

> await Database.from('users').whereNot({username: 'foo'})

5. `whereIn`

> await Database.from('users').whereIn('id', [1,2,3])

6. `whereNotIn`

> await Database.from('users').whereNotIn('id', [1,2,3])

7. `whereNull`

> await Database.from('users').whereNotIn('id', [1,2,3])

8. `whereNotNull`

> await Database.from('users').whereNotNull('created_at')

9. `whereExists`

<!-->

await Database.from('users').whereExists(function () {

this.from('accounts').where('users.id', 'accounts.user_id')

})

-->

10. `whereNotExists`

<!-->

await Database.from('users').whereNotExists(function () {

this.from('accounts').where('users.id', 'accounts.user_id')

})

-->

11. `whereBetween`

> await Database.table('users').whereBetween('age', [18, 32])

12. `whereNotBetween`

> await Database.table('users').whereNotBetween('age', [45, 60])

13. `whereRaw`

> await Database.from('users').whereRaw('id = ?', [20])

## Joins

1. `innerJoin`

> await Database.table('users').innerJoin('accounts', 'users.id', 'accounts.user_id')

- use callback:

<!-->

await Database

.table('users')

.innerJoin('accounts', function () {

this

.on('users.id', 'accounts.user_id')

.orOn('users.id', 'accounts.owner_id')

})

-->

2. `leftJoin`

> Database.select('*').from('users').leftJoin('accounts', 'users.id', 'accounts.user_id')

3. `leftOuterJoin`

> await Database

.select('*')

.from('users')

.leftOuterJoin('accounts', 'users.id', 'accounts.user_id')

4. `rightJoin`

> await Database

.select('*')

.from('users')

.rightJoin('accounts', 'users.id', 'accounts.user_id')

5. `rightOuterJoin`

> await Database

.select('*')

.from('users')

.rightOuterJoin('accounts', 'users.id', 'accounts.user_id')

6. `outerJoin`

> await Database

.select('*')

.from('users')

.outerJoin('accounts', 'users.id', 'accounts.user_id')

Lấy ra vùng bên ngoài của vùng giao nhau.

7. `fullOuterJoin`

> await Database

.select('*')

.from('users')

.fullOuterJoin('accounts', 'users.id', 'accounts.user_id')

8. `crossJoin`

> await Database

.select('*')

.from('users')

.crossJoin('accounts', 'users.id', 'accounts.user_id')

Nếu table1 có n bản ghi, table2 có m bản ghi ==> Cross Join của table1

và table2 sẽ tạo ra n*m bản ghi

9. `joinRaw`

> await Database

.select('*')

.from('accounts')

.joinRaw('natural full join table1').where('id', 1)

## Ordering and Limits

1. `distinct`

> await Database.table('users').distinct('age')

2. `groupBy`

> await Database.table('users').groupBy('age')

3. `groupByRaw`

> await Database.table('users').groupByRaw('age, status')

4. `orderBy(column, [direction=asc])`

> await Database.table('users').orderBy('id', 'desc')

5. `orderByRaw(column, [direction=asc])`

> await Database.table('users').orderByRaw('col NULLS LAST DESC')

6. `having(column, operator, value)`

groupBy() must be called before having().

> await Database.table('users').groupBy('age').having('age', '>', 18)

7. `offset/limit(value)`

> await Database.table('users').offset(11).limit(10)

## Inserts

1. `insert(values)`

- Insert one row:

<!-->

const userId = await Database

.table('users')

.insert({username: 'foo', ...})

-->

- Insert multiple row:

<!-->

// BULK INSERT | firstUserId sẽ nhận được là id của dòng được ghi đầu tiên

const firstUserId = await Database

.from('users')

.insert([{username: 'foo'}, {username: 'bar'}])

-->

2. `into(tableName)`

> const userId = await Database.insert({username: 'foo', ...}).into('users')

3. `For PostgreSQL`

> const userId = await Database.insert({ username: 'virk' }).into('users').returning('id')

## Updates

> const affectedRows = await Database.table('users')

    .where('username', 'tutlage').update('lastname', 'Virk')

- update multiple columns:

<!-->

const affectedRows = await Database

.table('users')

.where('username', 'tutlage')

.update({ lastname: 'Virk', firstname: 'Aman' })

-->

## Deletes

> const affectedRows = await Database.table('users').where('username', 'tutlage').delete()

Cũng có thể thay thế `.delete()` bằng `.del()`

- `Truncate`

> await Database.truncate('users')

## Phân trang (Pagination)

1. `forPage(page, [limit=20])`

> const users = await Database.from('users').forPage(1, 10)

2. `paginate(page, [limit=20])`

> const results = await Database.from('users').paginate(2, 10)

Output của 2 phương thức này có chút khác biệt

## Database Transactions

1. `beginTransaction`

beginTransaction method trả về transaction object

<!-->

const trx = await Database.beginTransaction()

// process data

await trx.insert({username: 'virk'}).into('users')

await trx.update...

await trx.commit() // insert query will take place on commit

await trx.rollback() // will not insert anything

<-->

2. `transaction`

<!-->

await Database.transaction(async (trx) => {

// process data

await trx.insert({username: 'virk'}).into('users')

await trx.update...

})

<-->

Nếu không có bất kì lỗi gì thì nó sẽ tự động commit, ngược lại thì rollback lại.

## Một số hàm khác

1. `count()`

> const count = await Database.from('users').count()

> const total = count[0]['count(*)']

> const count = await Database.from('users').count('id')

> const total = count[0]['count("id")']

> const count = await Database.from('users').count('* as total')

> const total = count[0].total

2. `countDistinct`

> const count = await Database.from('users').countDistinct('id')

> const total = count[0]['count(distinct "id")']

3. `min`

> await Database.from('users').min('age')

> await Database.from('users').min('age as a')

4. `max`

> await Database.from('users').max('age')

> await Database.from('users').max('age as a')

5. `sum`

> await Database.from('cart').sum('total')

> await Database.from('cart').sum('total as t')

6. `sumDistinct`

> await Database.from('cart').sumDistinct('total')

> await Database.from('cart').sumDistinct('total as t')

7. `avg`

> await Database.from('users').avg('age')

> await Database.from('users').avg('age as age')

8. `avgDistinct`

> await Database.from('users').avgDistinct('age')

> await Database.from('users').avgDistinct('age as age')

9. `increment`

> await Database.table('credits').where('id', 1).increment('balance', 10)

10. `decrement`

> await Database.table('credits').where('id', 1).decrement('balance', 10)

## Helpers Method

1. `getCount(columnName = '*')`

> const total = await Database.from('users').getCount() // returns number

2. `getCountDistinct(columnName)`

> const total = await Database.from('users').getCountDistinct('id') // returns number

3. `getMin(columnName)`

> await Database.from('users').getMin('age') // returns a number

4. `getMax(columnName)`

> await Database.from('users').getMax('age') // returns number

5. `getSum(columnName)`

> await Database.from('cart').getSum('total') // returns number

6. `getSumDistinct(columnName)`

> await Database.from('cart').getSumDistinct('total') // returns number

7. `getAvg(columnName)`

> await Database.from('users').getAvg('age') // returns number

8. `getAvgDistinct(columnName)`

> await Database.from('users').getAvgDistinct('age') // returns number

9. Trả về dữ liệu của một cột

> const usersIds = await Database.from('users').pluck('id')

10. Lấy ra dòng dữ liệu đầu tiên

> await Database.from('users').first()

11. Clone một query

> const query = Database.from('users').where('username', 'virk').clone()

12. Lấy thông tin của 1 column

> const username = await Database.table('users').columnInfo('username')

## Subqueries

<!-->

const subquery = Database

.from('accounts')

.where('account_name', 'somename')

.select('account_name')

const users = await Database

.from('users')

.whereIn('id', subquery)

<-->

## Raw Queries

> await Database.raw('select * from users where username = ?', [username])

## Closing Connections

> Database.close() // all

> Database.close(['sqlite', 'mysql'])