Provision a Database and Import a SQL File for initialization

This tutorial will talk about how to provision a relational database with SQL file imported, and bootstrap an application which depends on the database.

Favorite Links is an interesting project which can store all your favorite web links in one application. It is using Node.js and MySQL. It has been built into a container image oamdev/nodejs-mysql-links:v0.0.1. Let’s bootstrap the application and see how to provision a database, and import a SQL file (which means to create tables, and insert data into them if needed).

Currently, it only works on Alibaba Cloud ComponentDefinition alibaba-rds-preview is the feature preview for Alibaba Cloud RDS, and will be merged into alibaba-rds later.

How it works

Feel free to skip this section if you are not interested.

Alibaba Cloud RDS Preview supports importing SQL file when create an RDS instance with these properties:

NameDescriptionTypeRequiredDefault
sql_fileThe name of SQL file in the bucket, like db.sqlstringfalse
sql_bucket_nameThe bucket name of the SQL file. like oss://examplestringfalse
sql_bucket_endpointThe endpoint of the bucket. like oss-cn-hangzhou.aliyuncs.comstringfalse

After an RDS database is created, the SQL file from OSS bucket will be imported into the database by the power of Terraform local-exec provisioner which is referenced by Alibaba Cloud RDS Preview ComponentDefinition.

Prerequisites

Let’s say we have an OSS bucket oss://favorite-links which contains a SQL file db.sql in it, and the bucket endpoint is oss-cn-hongkong.aliyuncs.com.

Provision cloud resources

Use the following Application to provision a database links, import the SQL file db.sql, and bootstrap the application Favorite Links.

  1. apiVersion: core.oam.dev/v1beta1
  2. kind: Application
  3. metadata:
  4.   name: favorite-links
  5. spec:
  6.   components:
  7.     - name: web
  8.       type: webservice
  9.       properties:
  10.         image: oamdev/nodejs-mysql-links:v0.0.1
  11.         port: 4000
  12.       traits:
  13.         - type: service-binding
  14.           properties:
  15.             envMappings:
  16.               DATABASE_HOST:
  17.                 secret: db-conn
  18.                 key: DB_PUBLIC_HOST
  19.               DATABASE_NAME:
  20.                 secret: db-conn
  21.                 key: DATABASE_NAME
  22.               DATABASE_USER:
  23.                 secret: db-conn
  24.                 key: DB_USER
  25.               DATABASE_PASSWORD:
  26.                 secret: db-conn
  27.                 key: DB_PASSWORD
  29.     - name: db
  30.       type: alibaba-rds
  31.       properties:
  32.         instance_name: favorite-links
  33.         database_name: links
  34.         account_name: oamtest
  35.         password: U34rfwefwefffaked
  36.         security_ips: [ "0.0.0.0/0" ]
  37.         privilege: ReadWrite
  38.         sql_file: db.sql
  39.         sql_bucket_endpoint: oss-cn-hongkong.aliyuncs.com
  40.         sql_bucket_name: oss://favorite-links
  41.         writeConnectionSecretToRef:
  42.           name: db-conn

After the application is successfully deployed, you can access the web application by the following URL:

  1. $ vela ls
  2. APP             COMPONENT   TYPE                TRAITS          PHASE   HEALTHY STATUS                                          CREATED-TIME
  3. favorite-links  web         webservice          service-binding running healthy Ready:1/1                                       2022-02-21 14:15:45 +0800 CST
  4. └─              db          alibaba-rds-preview                 running healthy Cloud resources are deployed and ready to use   2022-02-21 14:15:45 +0800 CST
  1. $ vela port-forward favorite-links 4000:4000
  2. Forwarding from 127.0.0.1:4000 -> 4000
  3. Forwarding from [::1]:4000 -> 4000
  5. Forward successfully! Opening browser ...
  6. Handling connection for 4000
  7. Handling connection for 4000

Provision a Database and Import a SQL File for initialization - 图1