Database: Getting Started

See how to create database connections in Athenna Framework.

Introduction

Almost every modern application interacts with a database. Athenna makes interacting with databases extremely simple across a variety of supported databases using a fluent query builder and the ORM.

Installation

First of all you need to install @athenna/database package and configure it. Artisan provides a very simple command to install and configure the database library in your project. Simply run the following:

node artisan install @athenna/database

The database configurer will do the following operations in your project:

Ask you for the default database you want to use. (mongo, mysql, sqlite or postgres).
Create the database.ts configuration file.
Add all database providers in your .athennarc.json file.
Add all database commands in your .athennarc.json file.
Add all database template files in your .athennarc.json file.
Add database environment variables to .env, .env.test and .env.example.
Configure the docker-compose.yml file according to the database selected.
Install libraries like knex and pg. It will always depends on the default database you selected.

Configuration

All the configuration options for your application's database behavior is housed in the

Path.config('database.ts')

configuration file. This file allows you to configure your application's database connections, so be sure to review each of the available connections and their options. We'll review a few common options below.

Available connection drivers

Each database connection is powered by a "driver". The driver determines how and where the data is actually transported. The following database connection drivers are available in every Athenna application. An entry for most of these drivers is already present in your application's

Path.config('database.ts')

configuration file, so be sure to review this file to become familiar with its contents:

Driver name	Website	Built with
`mongo`	https://www.mongodb.com/	mongoose
`mysql`	https://www.mysql.com/	knex
`sqlite`	https://www.sqlite.org/	knex
`postgres`	https://www.postgresql.org/	knex

note

Athenna has another driver called fake that is very helpful when running tests. The fake driver got the same signature of all other drivers, but it returns empty objects and arrays in executors methods like find(), which is perfect to use within the Mock class. For more information about the FakeDriver, take a look at the mocking database documentation section.

Overview of some environment variables

After installing the database component using the Athenna CLI you can check your .env file in your project root path, the Athenna CLI have added some environment variables there to help you connect to your database. These variables are:

DB_CONNECTION=postgres
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DEBUG=false
DB_USERNAME=root
DB_PASSWORD=root
DB_DATABASE=database

Let's focus on DB_CONNECTION and DB_DEBUG variables:

DB_CONNECTION

This variable specify for Athenna what is the default connection name that should be used by Database facade when running database operations.

DB_DEBUG

This variable is useful when running your application locally, If DB_DEBUG is true, then you will be able to see all the queries being executed in your database.

tip

Before going through the documentation, remember to run docker-compose up -d to start up the database in your machine.

Database operations

Once you have configured your database connection, you may communicate with it using the Database facade. The Database facade provides a lot of methods to perform database operations such as creating, dropping and listing databases/tables, running and reverting migrations, transactions, queries, connecting to new databases and also closing these connections.

Creating and dropping databases

import { Database } from '@athenna/database'

await Database.createDatabase('hello')
await Database.dropDatabase('hello')

You can also get all databases names as string and check if some database name exists:

const databases = await Database.getDatabases() // ['postgres']
const current = await Database.getCurrentDatabase() // 'postgres'

await Database.hasDatabase('postgres') // true
await Database.hasDatabase('not-found') // false

Creating and dropping tables

import { Database } from '@athenna/database'

await Database.createTable('products', table => {
  table.increments('id').primary()
})

await Database.dropTable('products')

Need to change a table that already exists? Use the alterTable() method. It receives the same closure of createTable(), so you can add, rename or drop columns and indexes:

await Database.alterTable('products', table => {
  table.string('description').nullable()
  table.dropColumn('legacyCode')
})

You can also get all tables names as string and check if some table name exists:

const tables = await Database.getTables() // ['users']

await Database.hasTable('users') // true
await Database.hasTable('not-found') // false

Running and reverting migrations

If you don't know what is a migration you can check clicking here

await Database.runMigrations()
await Database.revertMigrations()

Transactions

If you don't know what is a transaction you can check clicking here

import { Log } from '@athenna/logger'

const trx = await Database.startTransaction()
const query = trx.table('users')

const users = [] // Imagine a lot of users to be inserted here...

try {
    await query.createMany(users)

    await trx.commitTransaction()
} catch (error) {
    // If some user in the "users" array has been created,
    // it will be removed if one fails to insert.
    await trx.rollbackTransaction()

    Log.error('Failed to create one of the users. Original error: %s', JSON.stringify(error))
}

tip

Working with models? You can run any model query inside the same transaction using the setTransaction() method. Take a look at the ORM transactions documentation section.

Named locks

Some flows are only safe if one process runs them at a time. A classic example is "check if the slot is free, then book it": if two requests arrive at the same time, both may see the slot as free and both will book it. The lock() method solves this by running your closure while holding an exclusive lock for a given key:

import { Database } from '@athenna/database'

await Database.lock(`booking:${professionalId}`, async () => {
  const isAvailable = await isSlotAvailable(data)

  if (!isAvailable) {
    throw new SlotTakenException()
  }

  await createBooking(data)
})

While the closure is running, any other call to lock() with the same key will wait until it finishes. Calls with different keys run in parallel as usual. The lock is always released when your closure finishes, even if it throws an error. Whatever your closure returns is returned by lock():

const booking = await Database.lock('booking:1', () => createBooking(data))

By default, lock() waits forever for the lock to be released. If you prefer to give up after some time, set the timeout option in milliseconds. When the time is over, a LockTimeoutException will be thrown:

import { LockTimeoutException } from '@athenna/database'

try {
  await Database.lock('reports:monthly', () => generateReport(), {
    timeout: 5000
  })
} catch (error) {
  if (error instanceof LockTimeoutException) {
    // Someone else is already generating the report.
  }
}

note

Each driver uses the best tool available to implement the lock:

postgres: transaction-level advisory locks (pg_advisory_xact_lock).
mysql: GET_LOCK() and RELEASE_LOCK().
sqlite: an in-process lock, since SQLite is an embedded database that lives in a single file.
mongo: not supported yet. Calling lock() will throw a NotImplementedMethodException.

If you need to lock specific rows instead of a key, take a look at the pessimistic locking documentation section.

Running queries

You may use the table() method provided by the Database facade to begin a query. The table() method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally retrieve the results of the query using one of the executors methods. These are the most relevant methods:

find()
findMany()
create()
createMany()
update()
delete()

Everytime that you use the Database facade you are using a different instance of DatabaseImpl class. This means that you would need to call table() method everytime for different queries. To get around this, you can save the instance in a local variable:

import { Database } from '@athenna/database'

const userQuery = Database.table('users') // First instance of QueryBuilder
const orderQuery = Database.table('orders') // Second instance of QueryBuilder
const productsQuery = Database.table('products') // Third instance of QueryBuilder

Running a find query

The find() method is useful to retrieve only one record that match the query statements from database:

const query = Database.table('users')

const { id, name } = await query
    .select('id', 'name')
    .where({ id: 10 })
    .find()

Running a findMany query

The findMany() method is useful to retrieve more than one record that match the query statements from database:

const query = Database.table('users')

const users = await query
    .select('id', 'name')
    .whereNull('deletedAt')
    .whereLike('name', '%Lenon%')
    .orderBy('name', 'DESC')
    .findMany()

Running a create query

The create() method is useful to create one record in database:

const query = Database.table('users')

const user = await query.create({ name: 'Victor Tesoura' })

Running a createMany query

The createMany() method is useful to create many records in database:

const query = Database.table('users')

const users = await query.createMany([
    { name: 'Victor Tesoura' },
    { name: 'João Lenon' }
])

Running an update query

The update() method is useful to update one or more records based in statements in database:

const query = Database.table('users')

const users = await query
    .select('id', 'name')
    .whereIn('id', [1, 2])
    .orderBy('name', 'ASC')
    .update({ name: 'Danrley Morais' })

Running delete query

The delete() method is useful to delete one or more records based in statements in database:

const query = Database.table('users')

await query.whereBetween('id', [1, 10]).delete()

Using multiple database connections

If your application defines multiple connections in your

Path.config('database.ts')

configuration file, you may access each connection via the connection() method provided by the Database facade. The connection name passed to the connection() method should correspond to one of the connections listed in your

Path.config('database.ts')

configuration file. You also need to explicit call the connect() method when working with other connection that is not the default:

const query = Database.connection('mysql').table('users')

const users = await query
    .select('id', 'name')
    .whereNotIn('id', [1, 2])
    .orderBy('name', 'ASC')
    .findMany()

The connection created by the connection() method will be stored inside the DriverFactory class. This means that if you call the connection() method again, it will use the same connection created that was saved in DriverFactory:

await Database.connection('mysql')
    .table('users')
    .select('id', 'name')
    .whereNotIn('id', [1, 2])
    .orderBy('name', 'ASC')
    .findMany()

You can force connection() method to not save the connection instance in DriverFactory passing some properties to it:

const database = Database.connection('mysql', {
    // Force the connection to be established
    force: false,
    // Set if connection will be saved in DriverFactory
    // to be reused in all Database instances
    saveOnDriver: false
})

await database
    .table('users')
    .select('id', 'name')
    .whereNotIn('id', [1, 2])
    .orderBy('name', 'ASC')
    .findMany()

caution

If you specify to connection() method that you don't want to save the connection in driver, you will need to close the connection using your database instance. We will see next how to close a database connection.

Closing database connections

You can simply close a connection using the close() or closeAll() methods of Database facade:

await Database.close() // Close the default connection
await Database.connection('mysql').close() // Close the mysql connection

await Database.closeAll() // Close all the connections saved in DriverFactory

Remember that when creating a connection that will not be saved in driver, you will need to close the connection using your database instance:

const database = Database.connection('mysql', {
    force: false,
    saveOnDriver: false
})

await database
    .table('users')
    .select('id', 'name')
    .whereNotIn('id', [1, 2])
    .orderBy('name', 'ASC')
    .findMany()

// The code below will not close the connection
// created above since DriverFactory doesn't
// know what is your connection.
await Database.connection('mysql').close() // DOES NOT WORK

await database.close() // WORKS

Handling database errors

When a write breaks one of your database constraints, Athenna translates the original driver error into a friendly exception. This means you don't need to remember that Postgres uses the 23505 code for unique violations while MySQL uses ER_DUP_ENTRY. You just need to catch the right exception:

import { UniqueViolationException } from '@athenna/database'

try {
  await Database.table('users').create({ email: 'lenon@athenna.io' })
} catch (error) {
  if (error instanceof UniqueViolationException) {
    console.log(error.columns) // ['email']
    console.log(error.constraint) // 'users_email_unique'
  }
}

These are the available exceptions:

Exception	Status	Code	When it happens
`UniqueViolationException`	`409`	`E_UNIQUE_VIOLATION`	A unique constraint or index was violated.
`ForeignKeyViolationException`	`409`	`E_FOREIGN_KEY_VIOLATION`	The referenced record doesn't exist or is still being referenced.
`NotNullViolationException`	`422`	`E_NOT_NULL_VIOLATION`	A `null` value was sent to a column that doesn't accept it.
`CheckViolationException`	`422`	`E_CHECK_VIOLATION`	The value doesn't respect a check constraint.

All of them extend the ConstraintViolationException class, so you can catch every constraint error at once if you prefer. They also expose some useful properties when the driver provides them, like table, column/columns, constraint, driver and raw (the original error thrown by the database client):

import { ConstraintViolationException } from '@athenna/database'

try {
  await Database.table('orders').create(order)
} catch (error) {
  if (error instanceof ConstraintViolationException) {
    Log.warn('Constraint violated on table %s', error.table)
  }

  throw error
}

tip

Since these exceptions already have a status code, if you don't catch them inside an HTTP route, Athenna will answer the request with a 409 or 422 status automatically.

note

The mongo driver only translates duplicated key errors, which become a UniqueViolationException. Any other error that is not a constraint violation is rethrown exactly as it was received.

Running queries from the terminal

Sometimes you just want to take a quick look in your data without opening a database client. The db:query command runs a raw query against your database and prints the result in the terminal:

node artisan db:query "SELECT id, name FROM users LIMIT 5"

You can use the --connection option to run the query in a connection that is not your default one:

node artisan db:query "SELECT count(*) FROM orders" --connection=mysql

Introduction​

Installation​

Configuration​

Available connection drivers​

Overview of some environment variables​

DB_CONNECTION​

DB_DEBUG​

Database operations​

Creating and dropping databases​

Creating and dropping tables​

Running and reverting migrations​

Transactions​

Named locks​

Running queries​

Running a find query​

Running a findMany query​

Running a create query​

Running a createMany query​

Running an update query​

Running delete query​

Using multiple database connections​

Closing database connections​

Handling database errors​

Running queries from the terminal​

Introduction

Installation

Configuration

Available connection drivers

Overview of some environment variables

DB_CONNECTION

DB_DEBUG

Database operations

Creating and dropping databases

Creating and dropping tables

Running and reverting migrations

Transactions

Named locks

Running queries

Running a find query

Running a findMany query

Running a create query

Running a createMany query

Running an update query

Running delete query

Using multiple database connections

Closing database connections

Handling database errors

Running queries from the terminal