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
  28. - name: db
  29. type: alibaba-rds
  30. properties:
  31. instance_name: favorite-links
  32. database_name: links
  33. account_name: oamtest
  34. password: U34rfwefwefffaked
  35. security_ips: [ "0.0.0.0/0" ]
  36. privilege: ReadWrite
  37. sql_file: db.sql
  38. sql_bucket_endpoint: oss-cn-hongkong.aliyuncs.com
  39. sql_bucket_name: oss://favorite-links
  40. writeConnectionSecretToRef:
  41. 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
  4. Forward successfully! Opening browser ...
  5. Handling connection for 4000
  6. Handling connection for 4000

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