Upsun User Documentation
Edit page

Add a database

Back to home

On this page

Upsun Beta access

Test and provide feedback for our newest offering - Upsun!

You can register for the Beta by clicking here and completing the form.

Sign up for Beta access

Once Your Express application has been deployed on Upsun, you might want to add a service to your application.

Upsun projects already include a variety of services, so you don’t have to subscribe to an external cache or search-engine services. And as these services are included in your project, you can manage them through Git and they’re backed up along with the rest of your project. Your project source code defines the services configuration in the main .upsun/config.yaml file, and this is where you can add new services.

As an example on how to do so, to add a MariaDB database engine into your Express project, complete the following 6 steps:

1. Create a new branch for testing Anchor to this heading

Create a new branch using the following command:

Terminal
upsun environment:branch add-mysql-database

2. Add a MariaDB service Anchor to this heading

Configure the MariaDB service by adding this Yaml definition at the end of your .upsun/config.yaml file:

.upsun/config.yaml
services:
  mariadb:
    type: mariadb:10.6

Connect the service to your application app by adding a relationships setting into your app definition:

.upsun/config.yaml
applications:
  app:
    type: nodejs:18
    relationships:
      mariadb: "mariadb:mysql"

Add into your .environment file the following lines:

.environment
export RELATIONSHIPS_JSON="$(echo $PLATFORM_RELATIONSHIPS | base64 --decode)"

# Set database environment variables
export DB_HOST="$(echo $RELATIONSHIPS_JSON | jq -r '.mariadb[0].host')"
export DB_PORT="$(echo $RELATIONSHIPS_JSON | jq -r '.mariadb[0].port')"
export DB_DATABASE="$(echo $RELATIONSHIPS_JSON | jq -r '.mariadb[0].path')"
export DB_USERNAME="$(echo $RELATIONSHIPS_JSON | jq -r '.mariadb[0].username')"
export DB_PASSWORD="$(echo $RELATIONSHIPS_JSON | jq -r '.mariadb[0].password')"

Commit your change:

Terminal
git add  .environment .upsun/config.yaml && git commit -m "adding MariaDb database service"
upsun push

Upsun will now read your configuration files and deploy your project using default container resources.
If you don’t want to use those default resources, define your own resource initialization strategy, or amend those default container resources after your project is deployed.

3. Configure your Express application to use this new database Anchor to this heading

First, you need a NodeJs module named mysql2. Install it by running the following command:

Terminal
npm install mysql2

Then modify your index.js with the following :

index.js
const express = require('express')
const app = express()
const mysql = require("mysql2/promise");
const port = (process.env.PORT || '3000');

function openConnection() {
  return mysql.createConnection({
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    user: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE
  });
}

function createTable(connection) {
  return connection.execute(
    `CREATE TABLE IF NOT EXISTS upsuninfo (
      uid INT(10) NOT NULL AUTO_INCREMENT,
      username VARCHAR(64) NULL DEFAULT NULL,
      departname VARCHAR(128) NULL DEFAULT NULL,
      created DATE NULL DEFAULT NULL,
      PRIMARY KEY (uid)
    ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;`
  );
}

function insertData(connection) {
  return connection.execute(
    "INSERT INTO upsuninfo (username, departname, created) VALUES ('upsun', 'Deploy Friday', '2023-09-29')"
  );
}

function readData(connection) {
  return connection.query("SELECT * FROM upsuninfo");
}

function dropTable(connection) {
  return connection.execute("DROP TABLE upsuninfo");
}

// Define the main route.
app.get('/', async function(req, res){

  // Connect to MariaDB.
  const connection = await openConnection();

  await createTable(connection);
  await insertData(connection);

  const [rows] = await readData(connection);

  const droppedResult = await dropTable(connection);

  // Make the output.
  const outputString = `Hello, World! - A simple Express web framework template for Upsun

MariaDB Tests:

* Connect and add row:
  - Row ID (1): ${rows[0].uid}
  - Username (upsun): ${rows[0].username}
  - Department (Deploy Friday): ${rows[0].departname}
  - Created (2023-09-29): ${rows[0].created}
* Delete row:
  - Status (0): ${droppedResult[0].warningStatus}`;

  res.set('Content-Type', 'text/plain');
  res.send(outputString);
});

// Get PORT and start the server
app.listen(port, function() {
  console.log(`Listening on port ${port}`)
});

Commit your changes and deploy your changes:

Terminal
git add package.json package-lock.json index.js && git commit -m "adding MariaDb database service"
upsun push
upsun environment:url --primary

4. Merge to production Anchor to this heading

When satisfied with your changes, merge them to the main branch, and remove the feature branch:

Terminal
upsun merge
 Are you sure you want to merge add-mysql-database into its parent, main? [Y/n] y

5. Remove the feature branch Anchor to this heading

Then, you need to remove the feature branch

Terminal
upsun checkout main
git pull upsun main
upsun environment:delete add-mysql-database
git fetch --prune

Tips & Tricks Anchor to this heading

You can get your project relationships information using the following command:

Terminal
upsun relationships
  ...
  mariadb:
    -
      username: user
      scheme: mysql
      service: mariadb
      fragment: null
      ip: 198.12.123.45
      hostname: abcdefghijklm1234567890123.mariadb.service._.<region>.upsunapp.com
      public: false
      cluster: abcdefgh1234567-add-mysql-database-abcd123
      host: mariadb.internal
      rel: mysql
      query:
        is_master: true
      path: main
      password: ''
      type: 'mariadb:10.6'
      port: 3306
      host_mapped: false
      url: 'mysql://user:@mariadb.internal:3306/main'

Is this page helpful?