emeraldsquad/cf-flyway-resource Docker 镜像 - 轩辕镜像

Version control for your database so you can migrate it with ease and confidence to Cloud Foundry database services with concourse-ci and Flyway.

Features:

• Seamless connection to Cloud Foundry database services.
• Support all flyway cli commands.
• Support flyway configuration file.
• Support Community and Pro Edition
• Output database service metadata.

![emeraldsquad/cf-flyway-resource]([***]

resource_types

The image cf-flyway-resource is built from ubuntu:22.04.

yml
- name: cf-flyway-resource
  type: docker-image
  source:
    repository: emeraldsquad/cf-flyway-resource
    tag: 5.0.0

resources

yml
- name: cf-flyway
  type: cf-flyway-resource
  source:
    api: ((pcf-api))   
    username: ((product-name))-ciuser
    password: ((pcf-ciuser-passwd))
    organization: ((product-name))-org
    space: ((pcf-space))
    service: ((database-service-name))

source

• api : required the api endpoint of the Cloud Foundry Cloud Controller
• username: required username to authenticate
• password: required password to authenticate
• organization : required the name of the organization to push to
• space : required the name of the space to push to
• service : required the name of the database service instance to push to

yml
- name: cf-flyway
  type: cf-flyway-resource
  source:
    api: cf-api-endpoint
    username: cf-user
    password: cf-user-password
    organization: organization-name
    space: space-name
    service: service-instance-name

check

version

The check script always returns the current version number, or generate an initial version if none was received.

Version is expressed as datetime (utc).

in

version

The in script always returns the current version number. Therefore, the resources cannot be used as an input trigger.

metadata

Read service information from the cf api to produce metadata.

yml
cf_api : [***]
cf_org : organization-name
cf_space : space-name
dashboard_url : https://{hostname}/service-instances/53rv1c30-1n5t-aNc3-Gu1d-000000000000/
metadata_url : /v2/service_instances/53rv1c30-1n5t-aNc3-Gu1d-000000000000
service_instance : cf-db-service-instance-name
service_label : cf-db-service-name
service_plan : cf-db-service-plan

out

migration

Migrate database schemas to CloudFoundry database service.

The out script creates a service-key on the Cloud Foundry database service. It then reads the credentials of the service-key and generate a flyway.conf file containing the Cloud Foundry database service-key url, username and password.

The generated flyway.conf file will also have flyway.cleanDisabled=true set by default.

Any other flyway configuration provided by the user is also appended to the generated flyway.conf file. Note that the out script will always override the following config:

conf
flyway.url={service-key jdbc:driver//url/database}  # Overwritten by Cloud Foundry service-key credentials
flyway.user={service-key username}                  # Overwritten by Cloud Foundry service-key credentials
flyway.password={service-key password}              # Overwritten by Cloud Foundry service-key credentials
flyway.locations={params.locations}                 # Overwritten by locations parameter (required)
flyway.cleanDisabled=true                           # Overwritten by clean_disabled parameter (optional, default=true)

The out script then execute flyway commands. If a command list parameters is set, they will be executed sequentialy. If no command list parameters are set, the script defaults to the following commands.

• flyway info
• flyway migrate
• flyway info

The service-key can be automatically deleted by setting the delete_service_key flag to true.

Database supported

• Azure SQL Database (Cloud Service Broker)
• Azure SQL Database (Microsoft Azure Service Broker)
• a9s PostgreSQL
• Crunchy PostgreSQL
• MySQL for PCF

run_task (optional)

Alternatively, the out script can push an lightweight flyway application and bind it to the Cloud Foundry database service. It then reads the credentials of the service-binding and generate a flyway.conf file containing the Cloud Foundry database service-key url, username and password.

The out script then execute flyway commands trough Cloud Foundry run-task command.

The application can be automatically unbinded and deleted by setting the delete_application flag to true.

This option is especially usefull when you Cloud Foundry platform has access to a database service while your Concourse instance does not.

manifest

The pushed flyway application uses the following manifest.

yml
applications:
- name: {service-instance-name}-flyway
  buildpacks: [binary_buildpack]
  health-check-type: process
  no-route: true
  instances: 0                            # faster staging time
  disk_quota: 256M
  memory: 256M
  command: sleep 3600                     # ensure the app will stop after 1h.

ssh

To open an ssh connection to the flyway application, first scale the application to one instance before starting it. Once the application is started you will be able to open an ssh connection.

bash
cf scale {service-instance-name}-flyway -i 1

bash
cf start {service-instance-name}-flyway

bash
cf ssh {service-instance-name}-flyway

The flyway_cli inside the application container is already configured to connect to the Cloud Foundry database service.

bash
vcap@xxxxxxxx-xxxx-xxxx-xxxx-xxxx:~$ cd app

vcap@xxxxxxxx-xxxx-xxxx-xxxx-xxxx:~/app$ ./flyway info
Flyway Community Edition 9.4.0 by Redgate
Database: {database connection information}
Schema version: << Empty Schema >>

+-----------+---------+---------------------+------+--------------+---------+
| Category  | Version | Description         | Type | Installed On | State   |
+-----------+---------+---------------------+------+--------------+---------+
| Versioned | 1.0     | migration initial   | SQL  |              | Success |
+-----------+---------+---------------------+------+--------------+---------+

Remember to stop the application when finished

bash
cf stop {service-instance-name}-flyway

params

• locations required
  • One of:
    • Comma-separated list of locations to scan recursively for migrations.
    • YAML list of locations to scan recursively for migrations.
  • The location type is determined by its prefix.
  • Unprefixed locations or locations starting with classpath: point to a package on the classpath and may contain both SQL and Java-based migrations.
  • Locations starting with filesystem: point to a directory on the filesystem, may only contain SQL migrations and are only scanned recursively down non-hidden directories.
• commands optional
  • List of commands to execute. (default: [info, migrate, info])
  • Available commands are:
    • migrate : Migrates the database
    • clean : Drops all objects in the configured schemas
    • info : Prints the information about applied, current and pending migrations
    • validate : Validates the applied migrations against the ones on the classpath
    • undo : [pro] Undoes the most recently applied versioned migration
    • baseline : Baselines an existing database at the baselineVersion
    • repair : Repairs the schema history table
• clean_disabled optional
  • Whether to disabled clean. (default: true)
  • This is especially useful for production environments where running clean can be quite a career limiting move.
• delete_service_key optional
  • Whether to delete the service-key on the Cloud Foundry database service when done. (default: false)
  • This is especially useful for on-demand qa environment.
• flyway_conf optional
  • One of:
    • path to an existing flyway.conf file.
    • inline flyway configuration.
  • The following configuration will be overwritten by the out script
    • flyway.url
    • flyway.user
    • flyway.password
    • flyway.locations
    • flyway.cleanDisabled
• jdbc_builder optional
  • Use this feature to connect to unrecognized database services.
  • One of:
    • path to an executable shell script.
    • inline shell script.
  • Script receives the CloundFoundry service-key json credentials has positional parameter $1.
  • Script output CloundFoundry database jdbc url.
• run_task optional
  • true (default): The resource will :
    • Create an application in Cloud Foundry and bind it to your database service.
    • Generate a flyway.conf file.
    • Package the flyway_cli along with the generated flyway.conf and your migration files.
    • Push the application to Cloud Foundry.
    • Execute flyway commands trough Cloud Foundry run-task command.
  • false : The resource will :
    • Create a service-key on your database service.
    • Generate a flyway.conf file.
    • Execute flyway commands inside the container runned by a concourse worker.
• delete_application optional
  • Whether to unbind and delete the application pushed to Cloud Foundry when done when using run_task: true. (default: false)
  • This is especially useful for on-demand qa environment.
• memory optional
  • Specify the memory limit for the task when using run_task: true. (default: 1024M)
  • This attribute requires a unit of measurement: B, K, KB, M, MB, G, GB, T, or TB in upper case or lower case.
• disk_quota optional
  • Specify the disk limit for the task when using run_task: true. (default: 1024M)
  • This attribute requires a unit of measurement: B, K, KB, M, MB, G, GB, T, or TB in upper case or lower case.
• cf_logs_wait_time optional
  • Specify the delay in seconds before retrieving cf task logs execution when using run_task: true (default: 15)
• cf_logs_max_retry_count optional
  • Specify the maximum count of retries to get the cf task logs execution when using run_task: true (default: 3)

Exemples

1. Only use the required locations parameter

yml
jobs:
- name: deploy
  plan:
  - get: my-app-package
  - put: cf-flyway
    params:
      locations: filesystem:my-app-package/DB/Schema

2. Add a flyway configuration file

yml
jobs:
- name: deploy
  plan:
  - get: my-app-package
  - put: cf-flyway
    params:
      locations: filesystem:my-app-package/DB/Schema
      flyway_conf: my-app-package/DB/flyway.config

3. Add inline flyway configuration

yml
jobs:
- name: deploy
  plan:
  - get: my-app-package
  - put: cf-flyway
    params:
      locations: filesystem:my-app-package/DB/Schema
      flyway_conf: |
        flyway.schemas=dbo
        flyway.connectRetries=2

4. Use a command sequence

yml
jobs:
- name: deploy
  plan:
  - get: my-app-package
  - put: cf-flyway
    params:
      locations: filesystem:my-app-package/DB/Schema
      commands: [info, validate]

5. Use Flyway Pro feature

yml
jobs:
- name: deploy
  plan:
  - get: my-app-package
  - put: cf-flyway
    params:
      locations: filesystem:my-app-package/DB/Schema
      commands: [info, undo, info]
      flyway_conf: |
        flyway.licenseKey=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

6. Use jdbc_builder

yml
jobs:
- name: deploy
  plan:
  - get: my-app-package
  - put: cf-flyway
    params:
      locations: filesystem:my-app-package/DB/Schema
      jdbc_builder: |
        echo jdbc:postgresql://$(echo $1 | jq -r '.uri' | grep -Poh '(?<=@).*')

7. Use run_task and delete application when migration is complete

yml
jobs:
- name: deploy
  plan:
  - get: my-app-package
  - put: cf-flyway
    params:
      run_task: true
      delete_application: true
      locations: filesystem:my-app-package/DB/Schema

8. Use memory and disk_quota when migration is done on a PCF app

yml
jobs:
- name: deploy
  plan:
  - get: my-app-package
  - put: cf-flyway
    params:
      run_task: true
      locations: filesystem:my-app-package/DB/Schema
      memory: 256M
      disk_quota: 256M

version

The out script always returns a new version number.

metadata

Read service information from the cf api to produce metadata.

yml
cf_api : [***]
cf_org : organization-name
cf_space : space-name
dashboard_url : https://{hostname}/service-instances/53rv1c30-1n5t-aNc3-Gu1d-000000000000/
metadata_url : /v2/service_instances/53rv1c30-1n5t-aNc3-Gu1d-000000000000
service_instance : cf-db-service-instance-name
service_label : cf-db-service-name
service_plan : cf-db-service-plan