Use with GitHub Actions and NodeJS

[dschinkel]

I want to use the command-line for my GitHub Actions pipelines for a NodeJS service. My NodeJS service is going to be tested against a postgres DB which locally I plan on running the DB in a docker container.

Is there a way to install flyaway so that I can run commands in GitHub Actions, Travis, and other build tools with a NodeJS app? How would I actually install flyaway in this situation for GitHub Actions for example so that npm scripts could run flyaway cli? I don't want to install flyaway in my container. I want to apply migrations from my NodeJS app itself during CI/CD from scripts run from my build.yaml

[DoodleBobBuffPants]

In your GitHub Actions workflow, you'd first want a step that downloads Flyway. You can achieve that with the following script:

FLYWAY_VERSION=7.3.1

curl -L https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/${FLYWAY_VERSION}/flyway-commandline-${FLYWAY_VERSION}.tar.gz -o flyway-commandline-${FLYWAY_VERSION}.tar.gz \
  && tar -xzf flyway-commandline-${FLYWAY_VERSION}.tar.gz --strip-components=1 \
  && rm flyway-commandline-${FLYWAY_VERSION}.tar.gz

Then you can add Flyway to the PATH with:

PATH=/flyway:$PATH

You should then be able to call Flyway inside your GitHub Actions workflow as normal i.e. ./flyway migrate

This isn't the only solution of course, you could also:

• Keep a copy of the Flyway CLI inside your repo if you felt inclined
• Pull the Flyway docker image

Does this work for you?

[dschinkel]

thanks much! I don't see how the docker image can help me if I don't want to run the flyaway stuff inside an image but thanks for the other tip.

[DoodleBobBuffPants]

I notice that you've deleted the comments now, so presumably you have it working?

The issues I noticed:

• The value of each argument needs to be wrapped in quotes in bash
• You were trying to run Flyway without issuing a command such as info or migrate

A correct command would look like ./flyway -url="<your_url>" -locations="<your_locations>" info

[dschinkel]

Almost there, you were on the right track. The problem is I wonder if I need to escape something:

- run: ./flyway -q -url="jdbc:postgresql://00.00.00.00:5432/postgres?user=postgres&password=343434*&ssl=false" -locations="filesystem:src/graphql/DB/pgMem/migrations" info

I replaced some of the real stuff above, but this gets across what I'm running. The problem I have now is I don't know if I need to escape something because it seems to cut off some of the command when github actions runs it:

[DoodleBobBuffPants]

You could try passing the user and password in separately with -user and -password, and this will let you figure out which bit is causing the URL to cut off

[dschinkel]

Thanks yea so it's the password I think. It outputs-password=*** when I tried instead of showing the hard coded password. The password has a * in it or ^ in it (depending on which password is right, but those are the two weird chars that could be in it), wonder if that's the problem.

I also wondered if it had anything to do with the &, do I need to escape that or something?

[DoodleBobBuffPants]

Does it successfully connect when you pass the username and password in as separate arguments? You shouldn't have to escape anything, and if the password is incorrect the driver should throw an authentication error

Since your database is running in a container, it's also possible that the container isn't ready yet

[dschinkel]

My database isn't running in a container. It's trying to connect to my live Google Cloud DB instance which I thought this could do? The database is running fine in google cloud, I'm able to connect to it with the same params/connection values with pgAdmin as well.

So I tried again with separating -user and -password. This time it took user, I actually finally see it printed properly as postgres. But for some reason it's making password -*** which is odd.

- run: ./flyway -q -url="jdbc:postgresql://000.000.000.000:5432/postgres" -user="postgres" -password="xyz01^" -locations="filesystem:src/graphql/DB/pgMem/migrations" info

(again I replaced the real IP and pwd here for privacy, but this illustrates an example password that has a ^ in it for example causing that weird -*** as a result )

[dschinkel]

It's so odd, I don't know why I see -***, even tried without the ^ and just characters and numbers for the password.

[DoodleBobBuffPants]

It appears as asterisks due to masking, it won't be using *** as the password internally. Could you share the output of running the above command?

You could also try downloading the Flyway CLI and running the above command. If it runs then we know there's something specific to running it as a GitHub Action

[dschinkel]

Sure, yea it might be something with the -password flag, because no matter how I format that, Actions is replacing it with -***.

How do I download the cli? This is a NodeJS project.

[DoodleBobBuffPants]

You can download it for your platform from the following link: https://flywaydb.org/download/community

[DoodleBobBuffPants]

The error indicates that the URL is incorrect - there's no database there to connect to. Since you're trying to connect to a Google Cloud database instance, the URL is formatted differently - see their documentation on how to set up your JDBC URL.

[dschinkel]

The problem is the statement is malformed, the same problem I've had from the beginning but yes, thanks for the find on the google version of the jdbc.

I still have the same problems even when running this

- run: ./flyway -q -url="jdbc:postgresql:///${{secrets.DB_NAME}}?cloudSqlInstance=${{secrets.CLOUD_SQL_CONNECTION_NAME}}&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=${{secrets.DB_USER}}&password=${{secrets.DB_PASS}}" -locations="filesystem:src/graphql/DB/pgMem/migrations" info

Besides the malformed string problems I'm having, the gcloud jdbc says that you have to install https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory which is Java based. I'm running node.

[DoodleBobBuffPants]

Flyway however is Java based and uses JDBC to connect to the database. You need to download the socket factory JAR in your Actions workflow and make it available on the classpath so that it can be used

One way would be by running a similar curl command on https://repo1.maven.org/maven2/com/google/cloud/sql/postgres-socket-factory/1.2.0/postgres-socket-factory-1.2.0.jar and then copying the downloaded JAR into /flyway/drivers

Closing for now as this is no longer a Flyway issue

[dschinkel]

This issue is about trying to get flyway working in node and github actions. It's fine to close it but this thread is all about getting flyway working via command-line which is proving to be painful.

tried your suggestion on downloading the google cloud jar, but got this:

[DoodleBobBuffPants]

You also need to download dependencies yourself too. You can find what you need in the Compile Dependencies section on Maven for this artifact here

[dschinkel]

I assume those should all be downloaded to the drivers folder

[DoodleBobBuffPants]

They all need to be available on the classpath, and Flyway ensures everything in the drivers folder is added to the classpath so it is the safest option, and recommended for dependencies related to your database connectivity

[dschinkel]

As I ventured down this path to resolving the compiled dependencies, am finding I'm chasing a rabbit hole now of dependencies which is tough because I'm manually having to find, download, and hope for the best since I'm not in an actual Java Project. And who knows once I do chase all these down if they're even the right version.

- run: chmod 744 drivers
- run: wget -P drivers https://repo1.maven.org/maven2/com/google/cloud/sql/postgres-socket-factory/1.2.0/postgres-socket-factory-1.2.0.jar
- run: wget -P drivers https://repo1.maven.org/maven2/com/google/cloud/sql/jdbc-socket-factory-core/1.2.0/jdbc-socket-factory-core-1.2.0.jar
- run: wget -P drivers https://repo1.maven.org/maven2/com/google/http-client/google-http-client/1.38.0/google-http-client-1.38.0.jar
- run: wget -P drivers https://repo1.maven.org/maven2/com/google/http-client/google-http-client-jackson2/1.38.0/google-http-client-jackson2-1.38.0.jar
- run: wget -P drivers https://repo1.maven.org/maven2/com/github/jnr/jnr-unixsocket/0.38.5/jnr-unixsocket-0.38.5.jar
- run: wget -P drivers https://repo1.maven.org/maven2/com/google/guava/guava/30.1-jre/guava-30.1-jre.jar
- run: wget -P drivers https://repo1.maven.org/maven2/com/google/auth/google-auth-library-credentials/0.22.2/google-auth-library-credentials-0.22.2.jar
- run: wget -P drivers https://repo1.maven.org/maven2/com/google/auth/google-auth-library-oauth2-http/0.22.2/google-auth-library-oauth2-http-0.22.2.jar
- run: wget -P drivers https://repo1.maven.org/maven2/com/fasterxml/jackson/core/jackson-core/2.12.0/jackson-core-2.12.0.jar
- run: wget -P drivers https://repo1.maven.org/maven2/com/google/api-client/google-api-client/1.31.1/google-api-client-1.31.1.jar
- run: wget -P drivers https://repo1.maven.org/maven2/com/google/apis/google-api-services-sqladmin/v1beta4-rev76-1.25.0/google-api-services-sqladmin-v1beta4-rev76-1.25.0.jar
- run: wget -P drivers https://repo1.maven.org/maven2/com/google/guava/failureaccess/1.0.1/failureaccess-1.0.1.jar
- run: wget -P drivers https://repo1.maven.org/maven2/io/opencensus/opencensus-api/0.28.2/opencensus-api-0.28.2.jar
- run: wget -P drivers https://repo1.maven.org/maven2/io/opencensus/opencensus-contrib-http-util/0.11.0/opencensus-contrib-http-util-0.11.0.jar

- run: ./flyway -q -url="jdbc:postgresql:///${{secrets.DB_NAME}}?cloudSqlInstance=${{secrets.CLOUD_SQL_CONNECTION_NAME}}&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=${{secrets.DB_USER}}&password=${{secrets.DB_PASS}}" -locations="filesystem:src/graphql/DB/pgMem/migrations" info /
 migrate

Result:

Run ./flyway -q -url="jdbc:***ql:///***?cloudSqlInstance=***&socketFactory=com.google.cloud.sql.***.SocketFactory&user=***&***" -locations="filesystem:src/graphql/DB/pgMem/migrations" info
Flyway Community Edition *** by Redgate
Dec 31, 2020 5:22:31 AM com.google.cloud.sql.core.CoreSocketFactory connect
INFO: Connecting to Cloud SQL instance [***:us-central1:wedotdd] via SSL socket.
Dec 31, 2020 5:22:32 AM com.google.cloud.sql.core.CoreSocketFactory getInstance
INFO: First Cloud SQL connection, generating RSA key pair.
Database: jdbc:***ql:///*** (PostgreSQL 12.4)
Dec 31, 2020 5:22:35 AM com.google.cloud.sql.core.CoreSocketFactory connect
INFO: Connecting to Cloud SQL instance [***:us-central1:wedotdd] via SSL socket.
Schema version: << Empty Schema >>

Dec 31, 2020 5:22:36 AM com.google.cloud.sql.core.CoreSocketFactory connect
INFO: Connecting to Cloud SQL instance [***:us-central1:wedotdd] via SSL socket.
+----------+---------+-------------+------+--------------+-------+
| Category | Version | Description | Type | Installed On | State |
+----------+---------+-------------+------+--------------+-------+
| No migrations found                                            |
+----------+---------+-------------+------+--------------+-------+

Flyway Community Edition *** by Redgate
Dec 31, 2020 5:43:40 AM com.google.cloud.sql.core.CoreSocketFactory connect
INFO: Connecting to Cloud SQL instance [***:us-central1:wedotdd] via SSL socket.
Successfully validated 0 migrations (execution time 00:00.149s)
WARNING: No migrations found. Are your locations set up correctly?
ERROR: Found non-empty schema(s) "public" but no schema history table. Use baseline() or set baselineOnMigrate to true to initialize the schema history table.

so I think I'm there, I'll try adding a migration now and see how that goes.

the only thing that's strange is I see Schema version: << Empty Schema >> and I know this DB has a schema, I'm running my site off it.

[dschinkel]

We have lift-off. Thanks much @DoodleBobBuffPants

[dschinkel]

Blog post about it https://t.co/34zFN3kopx

[DoodleBobBuffPants]

Glad to hear it works:)

I appreciate that manually downloading dependencies is a pain. Would it be feasible to create a Java project with Maven for the sole purpose of grabbing dependencies automatically? You could then follow this post to grab the dependencies