MySQL

The MySQL database package is one of the optional packages that is available for the horsepower server. It allows you to connect to the database and make queries against it by selecting, updating, deleting and inserting items. The package comes with many great features that allow you to write queries using built in methods aka the query builder. The package also contains Models so you can modify your table by using a model.

Configuration

The database is configured using the config file config/db.js. Within that file you can setup your database connection information.

The export has a named item, it can be whatever you would like. So we added one called mysql, and setup the database settings for that driver within that object.

You can have multiple default items in this config but they must be for different drivers. That means that each driver type may only have one default. If there is more than one default for a driver, then an error will be thrown.

const { env } = require('@horsepower/server')

module.exports = {
  mysql: {
    default: true,
    driver: 'mysql',
    database: env('DB_DATABASE', ''),
    username: env('DB_USERNAME', ''),
    password: env('DB_PASSWORD', ''),
    hostname: env('DB_HOSTNAME', 'localhost')
  }
}

The mysql object has default: true which means that when we don't call connect() it will use this default mysql driver like this:

// This will use the default driver
DB.table('my_table')

If we want to use a driver that isn't the default we can do it like so:

DB.connect('mysql2').table('my_table')
DB.connect('mysql2', 'my_table')

Query Builder

The query builder is a tool that allows you to build queries by chaining methods together. When the queries run, they are escaped automatically as to avoid sql injection.

Select all

Here is a basic query that selects all users from the table users

let result = await DB.table('users').get()

The resulting query string will look like this:

SELECT * FROM users;

Select one

We can select one item using first() this will get the first item in the result set it will also add a limit to the query for optimization.

let result = await DB.table('users').first()

The resulting query string will look like this:

SELECT * FROM users LIMIT 1 OFFSET 0;

Filtering results

There are multiple types of where methods and they are used to add filters to a where statement. These methods can be strung together to form one where clause.

Here is a list of where items that can be used to build a mysql query:

  • where
  • whereIn
  • whereNotIn
  • whereBetween
  • whereNotBetween
  • whereNull
  • whereNotNull
  • whereMatchAgainst

In the following queries, the actual queries will be escaped for query safety. You do not need to worry about escaping these values before making the query as it is done automatically.

where

// This performs an equality search on the table
await DB.table('users').where('username', 'Billy').first()

// This performs a greater than search on the table
await DB.table('users').where('age', '>', 10).get()
-- The query for the equality search
SELECT * FROM `users` WHERE `username` = 'Billy' LIMIT 1 OFFSET 0;

-- The query for the greater than search
SELECT * FROM `users` WHERE `age` > 10;

whereIn

await DB.table('users').whereIn('username', ['Billy', 'Bob', 'Joe']).get()
SELECT * FROM `users` WHERE `username` IN('Billy', 'Bob', 'Joe');

whereNotIn

await DB.table('users').whereNotIn('username', ['Billy', 'Bob', 'Joe']).get()
SELECT * FROM `users` WHERE `username` NOT IN('Billy', 'Bob', 'Joe');

whereBetween

await DB.table('users').whereBetween('age', 10, 20).get()
SELECT * FROM `users` WHERE `age` BETWEEN 10 AND 20;

whereNotBetween

await DB.table('users').whereNotBetween('age', 10, 20).get()
SELECT * FROM `users` WHERE `age` NOT BETWEEN 10 AND 20;

whereNull

await DB.table('users').whereNull('gender').get()
SELECT * FROM `users` WHERE `gender` IS NULL;

whereNotNull

await DB.table('users').whereNotNull('gender').get()
SELECT * FROM `users` WHERE `gender` IS NOT NULL;

whereMatchAgainst

await DB.table('search').whereMatchAgainst(['title', 'document'], 'red roses').get()
await DB.table('search').whereMatchAgainst(['title', 'document'], 'red roses', 'boolean').get()
SELECT * FROM users WHERE MATCH ('title', 'document') AGAINST ('red roses');
SELECT * FROM users WHERE MATCH ('title', 'document') AGAINST ('red roses' IN BOOLEAN MODE);