Switch CI to GitHub Actions

[simonw]

MySQL failed with:

E       MySQLdb._exceptions.OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")

/opt/hostedtoolcache/Python/3.6.13/x64/lib/python3.6/site-packages/MySQLdb/connections.py:185: OperationalError

[simonw]

PostgreSQL failed with:

>       conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
E       psycopg2.OperationalError: could not connect to server: No such file or directory
E           Is the server running locally and accepting
E           connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

[simonw]

From https://docs.github.com/en/actions/guides/creating-postgresql-service-containers looks like I need to get the tests to run against these:

    env:
      # The hostname used to communicate with the PostgreSQL service container
      POSTGRES_HOST: postgres
      # The default PostgreSQL port
      POSTGRES_PORT: 5432

[simonw]

Here's where the current tests connect to the DB in order to create the tables:

https://github.com/simonw/db-to-sqlite/blob/452ae39ede794edef8c06bac92c43f48e0e35dda/tests/conftest.py#L139-L144

https://github.com/simonw/db-to-sqlite/blob/452ae39ede794edef8c06bac92c43f48e0e35dda/tests/conftest.py#L117-L121

And here's how the tests specify the database to run the CLI tool against:

https://github.com/simonw/db-to-sqlite/blob/452ae39ede794edef8c06bac92c43f48e0e35dda/tests/shared.py#L15-L30

These are designed to run tests on my laptop and on Travis CI at the moment - here's the Travis CI configuration that makes that work: https://github.com/simonw/db-to-sqlite/blob/6570467ba776ce89b2255eb2643b339dd0f1d497/.travis.yml#L9-L11

Looks like Travis defaults to running these on the same host: https://docs.travis-ci.com/user/database-setup/#postgresql

[simonw]

Tried this but it didn't work - keeps giving the psycopg2 error DROP DATABASE cannot run inside a transaction block:

diff --git a/tests/conftest.py b/tests/conftest.py
index 1f601c3..8839b5b 100644
--- a/tests/conftest.py
+++ b/tests/conftest.py
@@ -1,6 +1,9 @@
+from os import pathsep
 import pytest
 from click.testing import CliRunner
 from db_to_sqlite import cli
+from sqlalchemy import create_engine
+from .shared import MYSQL_TEST_DB_CONNECTION, POSTGRESQL_TEST_DB_CONNECTION

 try:
     import MySQLdb
@@ -118,21 +121,17 @@ def setup_mysql():
     if MySQLdb is None:
         yield
         return
-    db = MySQLdb.connect(user="root", passwd="")
-    cursor = db.cursor()
-    cursor.execute("CREATE DATABASE IF NOT EXISTS test_db_to_sqlite;")
-    cursor.execute("USE test_db_to_sqlite;")
-    cursor.execute(MYSQL_SQL)
-    cursor.close()
-    db.commit()
+    db = create_engine(MYSQL_TEST_DB_CONNECTION.rsplit("/", 1)[0]).raw_connection()
+    with db.cursor() as cursor:
+        cursor.execute("CREATE DATABASE IF NOT EXISTS test_db_to_sqlite;")
+        cursor.execute("USE test_db_to_sqlite;")
+        cursor.execute(MYSQL_SQL)
     db.close()
     yield
     # teardown_stuff
-    db = MySQLdb.connect(user="root", passwd="")
-    cursor = db.cursor()
-    cursor.execute("DROP DATABASE test_db_to_sqlite;")
-    cursor.close()
-    db.commit()
+    db = create_engine(MYSQL_TEST_DB_CONNECTION.rsplit("/", 1)[0]).raw_connection()
+    with db.cursor() as cursor:
+        cursor.execute("DROP DATABASE test_db_to_sqlite;")
     db.close()

@@ -141,22 +140,21 @@ def setup_postgresql():
     if psycopg2 is None:
         yield
         return
-    db = psycopg2.connect(user="postgres", password="postgres")
-    db.autocommit = True
-    cursor = db.cursor()
-    cursor.execute("SELECT datname FROM pg_database;")
-    databases = [r[0] for r in cursor.fetchall()]
+    db = create_engine(POSTGRESQL_TEST_DB_CONNECTION).raw_connection()
+    db.autocommit = False
+    with db.cursor() as cursor:
+        cursor.execute("SELECT datname FROM pg_database")
+        databases = [r[0] for r in cursor.fetchall()]
     if "test_db_to_sqlite" in databases:
-        cursor.execute("DROP DATABASE test_db_to_sqlite;")
-    cursor.execute("CREATE DATABASE test_db_to_sqlite;")
-    cursor.close()
-    db.commit()
+        with db.cursor() as cursor:
+            cursor.execute("DROP DATABASE test_db_to_sqlite")
+        with db.cursor() as cursor:
+            cursor.execute("CREATE DATABASE test_db_to_sqlite")
     db.close()
-    db = psycopg2.connect(user="postgres", dbname="test_db_to_sqlite")
+    db = create_engine(POSTGRESQL_TEST_DB_CONNECTION).raw_connection()
     db.autocommit = True
-    cursor = db.cursor()
-    cursor.execute(POSTGRESQL_SQL)
-    db.commit()
+    with db.cursor() as cursor:
+        cursor.execute(POSTGRESQL_SQL)
     db.close()
     yield

diff --git a/tests/shared.py b/tests/shared.py
index f0a92f8..f482f50 100644
--- a/tests/shared.py
+++ b/tests/shared.py
@@ -1,4 +1,5 @@
 import pytest
+import os

 try:
     import MySQLdb
@@ -9,6 +10,13 @@ try:
 except ImportError:
     psycopg2 = None

+MYSQL_TEST_DB_CONNECTION = os.environ.get(
+    "MYSQL_TEST_DB_CONNECTION", "mysql://root@localhost/test_db_to_sqlite"
+)
+POSTGRESQL_TEST_DB_CONNECTION = os.environ.get(
+    "POSTGRESQL_TEST_DB_CONNECTION", "postgresql://localhost/test_db_to_sqlite"
+)
+

 def all_databases(fn):
     "Decorator which parameterizes test function for mysql and postgresql"
@@ -16,13 +24,13 @@ def all_databases(fn):
         "connection",
         [
             pytest.param(
-                "mysql://root@localhost/test_db_to_sqlite",
+                MYSQL_TEST_DB_CONNECTION,
                 marks=pytest.mark.skipif(
                     MySQLdb is None, reason="pip install mysqlclient"
                 ),
             ),
             pytest.param(
-                "postgresql://localhost/test_db_to_sqlite",
+                POSTGRESQL_TEST_DB_CONNECTION,
                 marks=pytest.mark.skipif(
                     psycopg2 is None, reason="pip install psycopg2"
                 ),

[simonw]

I'll try using from sqlalchemy.engine.url import make_url instead of creating an engine.

[simonw]

Still haven't figured this out:

E       psycopg2.OperationalError: could not connect to server: Connection refused
E           Is the server running on host "localhost" (::1) and accepting
E           TCP/IP connections on port 5432?
E       could not connect to server: Connection refused
E           Is the server running on host "localhost" (127.0.0.1) and accepting
E           TCP/IP connections on port 5432?

[simonw]

In another repo I've used this:

      env:
        DATABASE_URL: postgres://postgres:postgres@127.0.0.1:${{ job.services.postgres.ports['5432'] }}/mydb

[simonw]

New error:

E MySQLdb._exceptions.OperationalError: (1045, "Access denied for user 'runner'@'172.18.0.1' (using password: NO)")