SQLpipe is a free, open-source tool that can transfer the result of a query, or an entire table, from PostgreSQL to MySQL.
If SQLpipe helps you, please consider starring our GitHub repository! It really helps us.
The easiest way to run SQLpipe is with Docker:
docker run --publish 9000:9000 --name sqlpipe sqlpipe/sqlpipe
There are multiple ways to acquire the program, however. You can view all of those ways on the SQLpipe downloads page.
Gather the following information for PostgreSQL:
Gather the following information for MySQL:
Transferring data is accomplished by sending a POST request to SQLpipe's /transfer/create route. You can send this kind of command any way you want - from an Airflow DAG, a Cron command, or just Curl on your command line. Here is an outline of the kind of payload you must submit:
{
"source-name": "<any name you like>",
"source-type": "postgresql",
"source-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>",
"target-name": "<any name you like>",
"target-type": "mysql",
"target-connection-string": "<username>:<password>@tcp(<hostname>:<port>)/<db name>",
"source-schema": "<source schema name>",
"source-table": "<source table name>",
"target-table": "<target table name>",
"drop-target-table-if-exists": true,
"create-target-table-if-not-exists": true
}
To move the result of a query, you can use the following command as a starting command:
curl -d '{"source-name": "<any name you like>", "source-type": "postgresql", "source-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>", "target-name": "<any name you like>", "target-type": "mysql", "target-connection-string": "<username>:<password>@tcp(<hostname>:<port>)/<db name>", "query": "<query>", "target-table": "<target table name>", "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true}' localhost:9000/transfers/create
To move an entire table (and gain slightly better automatic type inference), you can use the following command as a starting template:
curl -d '{"source-name": "<any name you like>", "source-type": "postgresql", "source-connection-string": "postgresql://<username>:<password>@<hostname>:<port>/<db name>", "target-name": "<any name you like>", "target-type": "mysql", "target-connection-string": "<username>:<password>@tcp(<hostname>:<port>)/<db name>", "source-schema": "<source schema name>", "source-table":"<source table name>", "target-table": "<target table name>", "drop-target-table-if-exists": true, "create-target-table-if-not-exists": true}' localhost:9000/transfers/create
If the command is successful, the program will tell you that a transfer was queued:
{
"query-transfer": {
"id": "6806a7b3-95e0-41cd-8a54-304b04b4afe5",
"created-at": "2023-11-20T13:51:25.684624457Z",
"status": "queued",
"keep-files": false,
"tmp-dir": "/tmp/sqlpipe/6806a7b3-95e0-41cd-8a54-304b04b4afe5",
"pipe-file-dir": "/tmp/sqlpipe/6806a7b3-95e0-41cd-8a54-304b04b4afe5/pipe-files",
"final-csv-dir": "/tmp/sqlpipe/6806a7b3-95e0-41cd-8a54-304b04b4afe5/final-csv",
"source-connection-info": {
"instance-name": "my-source",
"type": "postgresql",
"hostname": "",
"database": "",
"username": ""
},
"target-connection-info": {
"instance-name": "my-target",
"type": "mysql",
"hostname": "",
"database": "",
"username": ""
},
"drop-target-table-if-exists": true,
"create-target-schema-if-not-exists": false,
"create-target-table-if-not-exists": true,
"source-schema": "public",
"source-table": "my_table",
"target-name": "postgresql_my_table",
"delimiter": "{dlm}",
"newline": "{nwln}",
"null": "NULL"
}
}
If you make a mistake submitting the data, SQLpipe will do its best to tell you what's wrong. For example, if you forgot to specify a target table, it would return:
{
"error": {
"target-table": "must be provided"
}
}
Yes, it really is that easy!
If you’d like more in-depth documentation, or want to let us know about a bug, we please make an issue on SQLpipe’s Github page.
