Skip to content

Basic Queries

insert

Single record

const users = await db.table('users')
  .insert({ name: 'John Doe', email: 'john.doe@example.com' })

Multiple records

const users = await db.table('users')
  .insert([
    { name: 'John Doe', email: 'john.doe@example.com' },
    { name: 'Jane Doe', email: 'jane.doe@example.com' }
  ])

update

const user = await db.table('users')
  .where('id', 1)
  .update({ name: 'John Doe', email: 'john.doe@example.com' })

delete

const user = await db.table('users')
  .where('id', 1)
  .delete()

upsert

Note: The table must have a unique constraint on the conflict column(s) for upsert to work properly.

const user = await db.table('users')
  .onConflict('id')
  .upsert({ id: 1, name: 'John Doe', email: 'john.doe@example.com' })

const user = await db.table('users')
  .onConflict(['email', 'name'])
  .upsert({ name: 'John Doe', email: 'john.doe@example.com' })

all

const users = await db.table('users')
  .get()

const users = await db.table('users')
  .all()

// output 
[
  {
    name: 'John Doe',
    email: 'john.doe@example.com'
    age: 25
  }
]

select

The select method allows selecting columns from the database table. You can either pass an array of columns or pass them as multiple arguments.

const users = await db.table('users')
  .select(['name', 'email'])
  .get()

const users = await db.table('users')
  .select('name', 'email')
  .get()

You can define aliases for the columns using the as expression or passing an object of key-value pair.

const users = await db.table('users')
  .select('name as user_name', 'email as user_email')
  .get()

const users = await db.table('users')
  .select({ 
    // Key is alias name
    user_name: 'name', 
    user_email: 'email' 
  })
  .get()

// output
[
  {
    user_name: 'John Doe',
    user_email: 'john.doe@example.com'
  }
]

first

The select queries always return an array of objects, even when the query is intended to fetch a single row. However, using the first method will give you the first row or null (when there are no rows).

const user = await db.table('users')
  .where('id', 1)
  .first()

// output
{
  name: 'John Doe',
  email: 'john.doe@example.com'
  age: 25
}

where

The where method is used to define the where clause in your SQL queries. The query builder accepts a wide range of arguments types to let you leverage the complete power of SQL.

const users = await db.table('users')
  .where('active', true)
  .where('age', '>', 25)
  .where('name', 'like', '%John%')
  .get()

You can create where groups by passing a callback to the where method. For example:

const query = db
  .table('users')
  .where((query) => {
    query
      .where('username', 'James Bond')
      .whereNull('deleted_at')
  })
  .orWhere((query) => {
    query
      .where('email', 'james@example.com')
      .whereNull('deleted_at')
  })

whereIn

The whereIn method is used to define the wherein SQL clause. The method accepts the column name as the first argument and an array of values as the second argument.

const users = await db.table('users')
  .whereIn('id', [1, 2, 3])
  .get()

whereNull

The whereNull method adds a where null clause to the query.

const users = await db.table('users')
  .whereNull('age')
  .get()

The whereNotNull method adds a where not null clause to the query.

whereNotNull

const users = await db.table('users')
  .whereNotNull('age')
  .get()

whereNotIn

The whereNotIn method adds a where not in clause to the query.

const users = await db.table('users')
  .whereNotIn('id', [1, 2, 3])
  .get()

orWhere

The where method is used to define the where clause in your SQL queries. The query builder accepts a wide range of arguments types to let you leverage the complete power of SQL.

const users = await db.table('users')
  .where('active', '=', true)
  .orWhere('age', '>', 25)
  .get()

orWhereIn

The whereIn method is used to define the wherein SQL clause. The method accepts the column name as the first argument and an array of values as the second argument.

const users = await db.table('users')
  .where('active', '=', true)
  .OrWhereIn('id', [1, 2, 3])
  .get()

orWhereNull

The whereNull method adds a where null clause to the query.

const users = await db.table('users')
  .where('active', '=', true)
  .orWhereNull('age')
  .get()

The whereNotNull method adds a where not null clause to the query.

orWhereNotNull

const users = await db.table('users')
  .where('active', '=', true)
  .orWhereNotNull('age')
  .get()

orWhereNotIn

The whereNotIn method adds a where not in clause to the query.

const users = await db.table('users')
  .where('active', '=', true)
  .orWhereNotIn('id', [1, 2, 3])
  .get()

whereBetween

The whereBetween method adds a where between clause to the query.

const users = await db.table('users')
  .where('active', '=', true)
  .whereBetween('age', [25, 30])
  .get()

whereBetween method variants

Method Description
whereBetween Adds a where between clause to the query.
orWhereBetween Adds a where between clause to the query.
whereNotBetween Adds a where not between clause to the query.
orWhereNotBetween Adds a where not between clause to the query.

whereLike

The whereLike method adds a where like clause to the query.

const users = await db.table('users')
  .whereLike('name', '%John%')
  .get()

whereLike method variants

Method Description
whereLike Adds a where like clause to the query.
orWhereLike Adds a or where like clause to the query.
whereNotLike Adds a where not like clause to the query.
orWhereNotLike Adds a or where not like clause to the query.

whereILike

The whereILike method adds a where ilike clause to the query.

const users = await db.table('users')
  .whereILike('name', '%johnson')
  .get()

whereILike method variants

Method Description
whereILike Adds a where ilike clause to the query.
orWhereILike Adds a or where ilike clause to the query.
whereNotILike Adds a where not ilike clause to the query.
orWhereNotILike Adds a or where not ilike clause to the query.

whereRaw

You can use the whereRaw method to express conditions not covered by the existing query builder methods. Always make sure to use bind parameters to define query values.

const users = await db.table('users')
  .whereRaw('age > ?', [25])
  .orWhereRaw('name = ?', ['John Doe'])
  .get()

orderBy

The orderBy method is used to sort the results of the query.

const users = await db.table('users')
  .orderBy('name', 'desc') // or 'asc'
  .get()

groupBy

The groupBy method is used to group the results of the query.

const users = await db.table('users')
  .groupBy('name')
  .get()

having

The having method is used to filter the results of the query after the groupBy method is applied.

const users = await db.table('users')
  .groupBy('users.id')
  .having('age', '>=', 25)

const users = await db
  .table('users')
  .select('age', 'count(*) as count')
  .groupBy('users.age')
  .havingRaw('count(*) >= ?', [2])
  .get()

limit

The limit method is used to limit the number of results returned by the query.

const users = await db.table('users')
  .limit(10)
  .get()

offset

The offset method is used to skip a number of results in the query.

const users = await db.table('users')
  .offset(5)
  .get()

Transactions

Bun ORM supports both automatic and manual transaction management.

Callback (Automatic) Transactions

The transaction method provides an easy way to run database transactions. It accepts a callback function, and if the callback throws an exception, the transaction is automatically rolled back. Otherwise, the transaction is committed if the callback runs successfully.

// Successful transaction
const result = await db.transaction(async (trx) => {
  // Insert data
  await trx
    .table('users')
    .insert({ name: 'John Doe', email: 'john@example.com' })

  // Update data
  await trx
    .table('users')
    .where('name', '=', 'John Doe')
    .update({ age: 30 })

  // Return final result
  return await trx
    .table('users')
    .where('name', '=', 'John Doe')
    .first()
})

If an error is thrown inside the transaction callback, the transaction will be automatically rolled back.

// Failed transaction (automatic rollback)
try {
  await db.transaction(async (trx) => {
    await trx
      .table('users')
      .insert({ name: 'Jane Smith', email: 'jane@example.com' })

    // This will fail and cause rollback
    await trx.rawQuery('INSERT INTO users (name) VALUES (NULL)')
  })
} catch (error) {
  console.log('Transaction rolled back:', error.message)
}

Manual Transactions

For more control over the transaction lifecycle, you can use manual transactions. The beginTransaction method starts a new transaction, and you can use the commit and rollback methods to control the outcome.

// Manual transaction with commit
const trx = await db.beginTransaction()
try {
  await trx
    .table('users')
    .where('name', 'John')
    .update({ age: 30 })

  await trx.commit()
} catch (error) {
  await trx.rollback()
  console.log('Transaction rolled back:', error.message)
}

// Manual transaction with rollback
const trx = await db.beginTransaction()
try {
  await trx
    .table('users')
    .where('name', 'John')
    .update({ age: 25 })

  // Some operation that might fail
  throw new Error('Something went wrong')

  await trx.commit()
} catch (error) {
  await trx.rollback()
  console.log('Transaction rolled back:', error.message)
}