Skip to main content
version 1.0.0

Database: Getting Started

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​

Athenna provides a very simple command inside @athenna/cli package to easily install and configure the database package in your project. In your project root path run:

athenna install:database

This command will do the following operations in your project:

  • Install pg package
  • Install @athenna/database package
  • Create config/database.js file
  • Add the database provider to config/app.js file in providers property
  • Add the database commands and templates to app/Commands/Kernel.js
  • Add environment variables to your .env file
  • Create docker-compose.yaml file
  • Lint your project
tip

PostgreSQL is the default database of install:database command. If you want to configure for MySQL database use the --db=mysql argument.

Configuration​

All the configuration options for your application's database behavior is housed in the config/database.js 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 config/database.js configuration file, so be sure to review this file to become familiar with its contents:

NameWebsiteBuilt with
mysqlhttps://www.mysql.com/TypeORM
postgresqlhttps://www.postgresql.org/TypeORM

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_DATABASE=database
DB_USERNAME=root
DB_PASSWORD=root
DB_SYNCHRONIZE=(true)
DB_AUTO_CONNECT=(true)

Let's focus in DB_CONNECTION, DB_SYNCHRONIZE and DB_AUTO_CONNECT 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_SYNCHRONIZE​

This variable are useful when running your application locally, If DB_SYNCHRONIZE is true, than TypeORM will read the schema of all your models and auto create the tables for your in your database. This variable should be used only for development purpose. Do NOT use synchronize in production environment (might have data losses).

DB_AUTO_CONNECT​

This variable specify for Athenna if the default connection should be auto connected inside DatabaseProvider. We set the value of this variable as true when bootstrapping the application by bootstrap/main.js file, but when running the application from artisan we set this variable to false. If this variable is set to false you will need to call the connect method from Database facade, we will check how to use it further in the documentation.

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 a database​

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 a table​

import { Database } from '@athenna/database'

const columns = [{ name: 'id', type: 'int', isGenerated: true, isPrimary: true }]

await Database.createTable('products', { columns })
await Database.dropTable('products')

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.buildTable('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))
}

Running queries​

You may use the buildTable method provided by the Database facade to begin a query. The buildTable 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 buildTable 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.buildTable('users') // First instance of QueryBuilder
const orderQuery = Database.buildTable('orders') // Second instance of QueryBuilder
const productsQuery = Database.buildTable('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.buildTable('users')

const { id, name } = await query
.buildSelect('id', 'name')
.buildWhere({ 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.buildTable('users')

const users = await query
.buildSelect('id', 'name')
.buildWhereNull('deletedAt')
.buildWhereLike('name', '%Lenon%')
.buildOrderBy('name', 'DESC')
.findMany()

Running a create query​

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

const query = Database.buildTable('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.buildTable('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.buildTable('users')

const users = await query
.buildSelect('id', 'name')
.buildWhereIn('id', [1, 2])
.buildOrderBy('name', 'ASC')
.update({ name: 'Danrley Morais' })

Running a delete query​

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

const query = Database.buildTable('users')

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

Using multiple database connections​

If your application defines multiple connections in your config/database.js 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 config/database.js configuration file. You also need to explicit call the connect method when working with other connection that is not the default:

const database = await Database.connection('mysql').connect()
const query = database.buildTable('users')

const users = await query
.buildSelect('id', 'name')
.buildWhereNotIn('id', [1, 2])
.buildOrderBy('name', 'ASC')
.findMany()

The connection created by the connect method will be stored inside the DriverFactory class. This means that if you call the connection method of Database facade again, you don't need to call the connect method again:

await Datatabase.connection('mysql')
.buildTable('users')
.buildSelect('id', 'name')
.buildWhereNotIn('id', [1, 2])
.buildOrderBy('name', 'ASC')
.findMany()

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

const force = false // Force the connection to be established
const saveOnDriver = false // Set if connection should be saved in Database driver or not

const database = await Datatabase.connection('mysql').connect(force, saveOnDriver)

await database
.buildTable('users')
.buildSelect('id', 'name')
.buildWhereNotIn('id', [1, 2])
.buildOrderBy('name', 'ASC')
.findMany()
caution

If you specify to connect 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 method of Database facade:

await Database.close()
await Database.connection('mysql').close()

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 force = false
const saveOnDriver = false

const database = await Datatabase.connection('mysql').connect(force, saveOnDriver)

await database
.buildTable('users')
.buildSelect('id', 'name')
.buildWhereNotIn('id', [1, 2])
.buildOrderBy('name', 'ASC')
.findMany()

await database.close()

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