Mysql connections are automaticly created when a mysql configuration section is added into the servers config file.
If it's your first connection to an external server you will need to create the file 'project/config/servers.json'.
In the mysql configuration you will need to add a server, user, password and database field.
Then your configuration file will looks like this:
  {
        "mysql": {
            "server":   "localhost:3306",
            "user":     "root",
            "password": "root",
            "database": "test-project"
        }
    }
Now if you restart the core-server into your project you will see into the logs that it's connected to your mysql server.

Percona support
Core-Server has an integrated percona support which makes it able to automaticly find the nearest node.
When a node of the percona clusters fails, the Core-Server will switch to another node in order to avoid any downtime on your app.
MySQL Queries
query
One of the primary use cases of ab api is when an interaction with the database is needed.
Because the users can not access directly the database for security reasons, we are going to make the required queries from our api's.

This queries can be executed using the 'this.query( SQL )' function.
Optionaly we can put a second argument to this function which will contain any data that will be used into the SQL syntax, by default the post data will be used.

query_object
Query by default returns an array of items when you execute a select statement. But in some cases we need only one record, this is why query_object will retuns only the first received record as an object and need as an array anymore. query_prepare
Sometimes you want to add some variables like a username or password into your SQL syntax, but you want to avoid SQL injections.
This is why we created the query_prepare function, it takes 2 arguments. The first argument is your SQL syntax and the second one is the data that will be used into your SQL syntax. By default the used data is the received post.
The query function already achieves this operational automaticly, the query_prepare function is only destinated to debug your SQL syntax.

Sql Dynamic Data
To replace values in your prepared statements, you may use the format {@property}

Example
  SELECT * FROM users WHERE id = '{@id}'
And passing the following to prepare the statment:
  // This can be given to the query_prepare function.
    // But if this would be the parameters you send to the API through POST, you could just call query( SQL ) immediatly.
    { id: 10 }
Will result in the following prepared statement:
  SELECT * FROM users WHERE id = '10'

Now you can try to execute this query into an api with the following sample.
  //First of all check if any id has been added to the post for our test case.
//This is opitional but it's always nice to make some assertions at the begining of the api
//We will not check if its numeric for the moment because we want to test if our sql is secured and SQL injections will not be possible.
this.check_variables('id'); 

//In our first test case we are going to test an SQL statement with the id variable of our post, so we are not going to specify any input data to our query function
const firstResult = await this.query(`SELECT * FROM users WHERE id='{@id}'`);
                       
//For our second test case we want to create some custom data that will be used in our SQL statement, and we are going to test the same query with the new provided data.
const sqlData = {
    id: 10
};
                       
const secondResult = await this.query(`SELECT * FROM users WHERE id='{@id}'`, sqlData);

//Now we will return both results in order to see if everything has been done successfully.
return {
    firstResult,
    secondResult
}
In the previous example we showed how to use queries in a JavaScript API. It's also possible to create an SQL api that works mostly on the same way as a JavaScript API.
The difference between both is that you will use the ".sql" extension instead of a ".js" extension.
If you want to create a users list api you can put a list.sql file into the users folder and start writing your SQL code into this file directly.
When you will call the users/list api you will receive an array containing the received results.

You can use variables of the post on the same way we showed before.
In the next example we will create an SQL api providing a list of every users matching the "search" variable that has been provided in the post.
  SELECT * FROM users WHERE username LIKE '%{@search}%'
There is also support for MongDB!
An example configuration for MongoDB would be:
  {
    "mongodb": {
        "server":   "localhost:27018",
        "user":     "admin",
        "password": "mongo",
        "project":  "someDatabaseName"
    }
}
Don't forget to restart your core-server again before using MongoDB in your API after configuring it!
MongoDB queries
The usage of MongoDB is integrated by the official MongoDB driver.
To create a client, select (or create) a collection and insert a document, use the following code:
    const mongodbClient = this.getConnections().mongodb; // Mongo client instance
  
  // List all collections (optional)
  const listCollections = mongodb.collections();
  if (listCollections.length === 0) listCollections[0] = 'awesomeCollection';

  // Select a collection, if the collection does not exist the driver will create it for us.
  const collection = mongodb.collection(listCollections[0]);  // Selected collection
  
  collection.insert({
    data: 'Yeah! A new document inserted!',
  });

  return 'MongoDB document inserted!';

MongoDB nativeCall
The async nativeCall function, is created to be able to interact with the native (official MongoDB driver) functionalities for both the client and a collection.
You'll need to pass it another function with two arguments as seen below.
This way we can use all functionalities from the driver.

An example of a native call using the mongodb client would be:
  ...
    const list = await mongodbClient.nativeCall(function(client, cb) {
        client.listCollections().toArray(cb);
    });
...

An example of a native call using a collection would be the insertMany function:
  ...
    const result = await collection.nativeCall(function(collection, cb) {
        collection.insertMany(data, cb);
    });
...

To facilitate developing we already wrapped some functionalities directly to make it easier.
The following functions may be called directly on a mongo client instance:
  collections()

If you are not familiar with those functions you may want to read the MongoDB Commands.

The following functions may be called directly on a selected collection:
  insert(data)
insertMany(data)
bulkWrite(data)
update(data, update)
delete(data)
deleteMany(data)
find(data)
findOneAndDelete(data)
findAndDelete(data)
findOneAndReplace(data, data2)
aggregation(data, cursor)

If you are not familiar with those functions you may want to read the MongoDB Collection Methods.
Redis can be used in order to share sessions between different nodes.
It can also be used to broadcast some messages to other nodes or even to save and load some data in a central store.

In order to create a connection to a Redis server you will need to add a redis object into the servers config file.
  {
    "redis": {
        "host": "localhost",
        "port": 6379
    },
                                         
    "mongodb": {
        "server":   "localhost:27018",
        "user":     "admin",
        "password": "mongo",
        "project":  "someDatabaseName"
    }
}
When you start your server, the connection will automaticly be created.

In the SiteManager the following functions are defined:
  • broadcastToRedis( event, data )
  • onRedisBroadcast( event, callback )
  • save( name, data )
  • load( name, data )
  • getExpirationTime( name )
It's possible to call a SiteManager method directly from your api using the siteManager variable of your environment.
In the next example we will save and load some data from the Redis server.
  const data = {
    username: "test1",
    id:       3
};

this.siteManager.broadcastToRedis("test-user", data);
                                               
const redis = this.siteManager.connections.redis;

redis.save("test-user", data);
                                               
return await redis.load("test-user");
Next steps
We will show you a little bit more about plugins and how to import them into your project.