Remote › Databases
PremiumThe Remote databases page works with the databases on your servers, MySQL, MariaDB or PostgreSQL. You browse tables and edit rows, read the structure, run SQL, export, back up and restore. No tab on SFTP / SSH needs to be open for this.
Nothing is opened to the internet. Every query runs over the server's saved SSH connection: with the database client on the server (mysql, mariadb or psql) or through an SSH tunnel with the client on your Mac. The password never appears on a command line.
What you do here
- Add a database: enter the access, choose the path and test it
- Browse and edit data
- read a table's structure
- Run SQL
- Export for other programs
- Back up a database
- Restore a database
- set the character set for old databases
Page layout
- Header: Connections leads to SFTP / SSH, Add database creates an entry. Below it the notice Caution: working directly on the server.
- On the left the Databases list: Search databases…, the projects of your connection list, and in them the servers with their database entries. A plus per server (Add database for …) creates an entry there. Servers without an entry are listed under Servers without a database. The pencil and Remove edit or remove an entry. The list folds away like on the other Remote pages.
- On the right the chosen entry as a card: in the header the kind, Character set, Refresh, Backup, Restore and Database settings. Below it on the left the choice of database (with its size) and the tables (with an approximate row count, Filter tables…), on the right the views Data, Structure and SQL.
WebDonkey remembers the chosen entry across a restart. If you last opened a server on SFTP / SSH, the list jumps to it.
The same card appears in every tab on SFTP / SSH below the editor when the server has an entry, see The server's database.
Database backups land in WebDonkey's backup folder on your Mac unless you choose otherwise, and they are listed under Remote › Backups.
Free and Premium
Remote databases are part of Premium. Without Premium the page is covered by Remote is a Premium feature with the way to unlock it.
Add a database Premium
A database entry is the saved access to a database on one of your servers. It always belongs to an SSH connection, through which WebDonkey reaches the database. A server can have several entries, for example a MySQL and a PostgreSQL database.
Where to find it
Sidebar Remote › Databases, then Add database at the top right or the plus next to a server in the list on the left.
How to do it
- Click Add database. If there is no SSH connection yet, first add a server on SFTP / SSH.
- Choose the Server. The database is reached through this SSH connection.
- Under Database, choose the kind: MySQL, MariaDB or PostgreSQL. The port switches to
3306or5432. - Enter Database host (as seen from the server), usually
127.0.0.1, plus Port, Database user and Database password. - Optional: Database (optional) for a particular database, Display name (optional) for the list.
- Click Test access. On success it says Access works with the version of the database server.
- Click Save. The entry shows on the left under its server and opens on the right.
Fields and options
| Field | Meaning |
|---|---|
| Server | the SSH connection through which the database is reached |
| Database | MySQL, MariaDB or PostgreSQL. Default MySQL. |
| Database host (as seen from the server) | Address of the database as the server sees it. Empty means 127.0.0.1. |
| Port | Default 3306 (MySQL, MariaDB) or 5432 (PostgreSQL) |
| Database user | The database user, not the SSH user |
| Database (optional) | a database that is preselected. Empty shows all the user may see. |
| Database password | stored encrypted. Leave it blank when editing to keep the stored one. |
| Display name (optional) | Name in the list. Empty uses the database name or user@host. |
| Path to the database | Automatic, Through the server (its database client) or Through an SSH tunnel (client on this Mac) |
Path to the database
- Automatic uses the database client on the server. If it is missing or cannot sign in, WebDonkey switches to an SSH tunnel with the client on your Mac. Nothing has to be installed on the server.
- Through the server (its database client) only uses the client on the server.
- Through an SSH tunnel (client on this Mac) only uses the client on your Mac. You get it by installing MySQL, MariaDB or PostgreSQL under Services.
The tunnel listens on your Mac only, and only while it is needed. When an entry runs through the tunnel, a sentence above the card says so.
Edit and remove
The pencil in the list or the gear Database settings in the card opens Edit database.
- If you choose another Server there, the entry moves when you save. Password and settings stay, and the path to the database is worked out again for the new server.
- Remove asks Remove database and takes the entry out of WebDonkey. Nothing is deleted on the server.
Troubleshooting
| What you see | Why | What you do |
|---|---|---|
| No database client … on the server | mysql or psql is missing on the server |
Set Path to the database to Automatic |
A note about a missing plugin such as caching_sha2_password |
The client on the server does not match the login method, typically a MariaDB client against MySQL 8 | Choose Automatic or install the MySQL client on the server |
| This Mac has no client for this database | The tunnel needs a client on your Mac | Install the matching database with Open Services |
| The server does not allow SSH forwarding | AllowTcpForwarding no on the server |
Have forwarding allowed or use the client on the server |
| The server cannot reach the database at … | Wrong host or port | Check host and port, they are meant as seen from the server |
| Removing is refused | A remote backup still backs up this database | Take the database out of the backup set under Remote › Backups first |
See also
Browse and edit data Premium
The Data view shows a table's rows, page by page, sortable and filterable. You insert, change and delete rows, copy values and build filters with a right-click. Every change takes effect on the server right away.
Where to find it
Sidebar Remote › Databases, choose an entry on the left, then a table in the card and the Data tab. The Database card in a tab on SFTP / SSH has the same view.
How to do it
- At the top left of the card, choose the database if the entry shows several. Each name is followed by its size.
- Choose a table on the left. Filter tables… searches the list, and the number after a table is its approximate row count.
- The rows appear on the right. Without a table it says Pick a table on the left to browse its rows.
Page, sort, filter
- Rows come in pages. Rows per page sets 50, 100 (default), 250 or 500, Previous page and Next page turn the pages. Next to them you see which rows are shown and how many the table has.
- A click on a column header sorts by that column, a second click reverses the order.
- In the WHERE line you type a condition, such as
`name` LIKE '%shirt%'(in PostgreSQL"name" ILIKE '%shirt%'), and confirm with Enter. Clear filter resets it. A condition must not contain;. - The info line above the rows lists type, engine, collation, rows, the size of data and indexes, total, the next id, created, updated, the row format and the table's comment, as far as the database knows them.
Insert, change, delete rows
- Row opens New row. For each column you choose Value, Default (leaves the column to the server) or SQL expression, such as
NOW(). Computed columns are marked computed and read-only. Insert saves. - Right-clicking a row offers Edit row…, Duplicate row… and Delete row…, for a selection Delete n rows…. While editing, Changed columns keeps count, and Undo change resets a cell.
- Deleting asks Delete n row(s)? and names the table and the server. Changes and deletions cannot be undone.
After saving, the card briefly reports Saved, Inserted or Deleted with the number of rows.
Copy and filter with a right-click
A cell's context menu also offers Copy value, Copy row(s) as CSV, Copy row(s) as JSON and Copy row(s) as INSERT. Filter: = this value and Filter: ≠ this value add a condition to the filter.
What happens in the background
WebDonkey finds a row by its primary key or by a unique key over columns without NULL. If the key matches more than one row, it changes nothing. You write binary values as hex (0x…).
Troubleshooting
| What you see | Why | What you do |
|---|---|---|
| The table carries a note that it has no key and is read-only | It has no primary key and no suitable unique key | Change rows with SQL |
| The table carries a note that it is a view and read-only | It is a view | Edit the underlying table |
| The row no longer exists | Someone deleted or changed it in the meantime | Reload the view with Refresh |
| The filter must be a single condition | The filter contains ; |
Enter a single condition without ; |
| Umlauts look broken | Wrong character set | See Character set |
See also
Structure Premium
The Structure view shows how the chosen table is built: columns with their types and defaults, indexes and foreign keys. It only reads and changes nothing.
Where to find it
Sidebar Remote › Databases, choose an entry and a table, then the Structure tab. Without a chosen table the tab is disabled.
What the view shows
| Section | Content |
|---|---|
| Columns (n) | per column Name, Type, Default, Key, Extra, Collation and Comment. Keys are marked as primary key or unique key. |
| Indexes (n) | the index name, its Columns and the Kind |
| Foreign keys (n) | the Columns and References with the target table and columns |
If a section has nothing, it says None.
How to do it
- Choose the table on the left.
- Click Structure.
- To change the structure, switch to SQL and write the statement, such as
ALTER TABLE.
See also
Run SQL Premium
The SQL tab runs your own statements in the chosen database: queries, changes to data or to the structure. The result appears as a table below the field.
Where to find it
Sidebar Remote › Databases, choose an entry, then the SQL tab. Below the field it says which database the statement runs in.
How to do it
- Choose the database at the top left.
- Write the statement into the field.
- Click Run or press ⌘↵.
- The result appears below the field.
The result
- The result shows the duration in ms, and for changes Rows changed: n.
- If the statement returns several result sets, the card shows the last one. With Result 1, Result 2 … you switch to the others.
- Long results are cut, marked first n rows. For all rows use Export with All rows.
- Without rows it says Done, and that no rows were returned.
Statements that change data
If SQL may change data, WebDonkey first asks Change data on the server? and shows the SQL. After Run it runs right away and cannot be undone. Back up the database first, see Back up a database.
SQL only counts as reading if every statement in it starts with SELECT, SHOW, DESCRIBE, DESC, EXPLAIN, WITH, VALUES, TABLE, HELP or USE. Everything else, such as INSERT, UPDATE, DELETE or ALTER, asks first. Reading SQL runs in a session that may only read.
History
History lists the last 30 statements of this connection. A click puts one of them back into the field.
Troubleshooting
- Database error with the server's message: the statement is wrong or the user is not allowed to do this. Read the message, it comes from the database server.
- The tab shows nothing: check that a database is chosen at the top left.
See also
Export Premium
Export writes table data out for other programs, such as a spreadsheet, a ticket or documentation. You choose whether only the shown rows or all rows of the table go out, the format, and whether to the clipboard or to a file.
Where to find it
Sidebar Remote › Databases, choose an entry and a table, Data tab. The Export button sits above the rows. A result in the SQL tab has it too.
How to do it
- Open the table, filter and sort it the way you need it.
- Click Export.
- Choose the scope:
- Shown rows (n): exactly what the grid shows.
- All rows: streamed from the server without the row limit, with the current filter and order.
- At the format you want, click the copy icon (Copy to clipboard) or the save icon (Save as file…).
Afterwards it says how many rows there were and how large the output is. For a file, Show in Finder opens the folder.
Formats
| Format | Extension | Note |
|---|---|---|
| CSV | .csv |
comma as separator |
| CSV (Excel) | .csv |
semicolon as separator, with BOM, so Excel reads umlauts correctly |
| TSV | .tsv |
tab as separator |
| JSON | .json |
a list of the rows |
| SQL INSERT | .sql |
INSERT statements for the table |
| Markdown | .md |
a table for documentation |
| HTML | .html |
an HTML table |
| XML | .xml |
an XML document |
Only Shown rows go to the clipboard. You save All rows as a file.
What happens in the background
With All rows, WebDonkey reads the table straight from the server and writes the file row by row, without holding everything in memory. An export from the SQL tab needs exactly one reading statement for this.
An export is not a backup and cannot be restored. For backups see Back up a database.
Troubleshooting
- All rows is disabled for an SQL result: it only works for exactly one reading statement with one result set. Reduce the SQL to a single
SELECT.
See also
Back up a database Premium
Backup saves a whole database of the server into a file on your Mac. An SQL backup can later go back with Restore, CSV and JSON are exports for other programs.
Where to find it
Sidebar Remote › Databases, choose an entry, then Backup in the card's header. The same button is in the Database card on SFTP / SSH.
How to do it
- At the top left of the card, choose the database you want to back up.
- Click Backup. The Back up database dialog opens.
- Choose the Format (see below). The default is SQL (gzip).
- Check File name and Save to.
- Click Create backup. The progress shows the phase and the amount, Cancel stops it.
- At the end the dialog reports Backup created with the size. Show in Finder opens the folder.
Fields and options
Format
| Format | Content | Restorable |
|---|---|---|
| SQL (gzip) | complete dump, compressed | yes |
| SQL | complete dump as plain text, readable in any editor | yes |
| PostgreSQL archive | pg_dump's own format (.dump), compressed. PostgreSQL only. |
yes, here or with pg_restore |
| CSV | one CSV file per table, packed as ZIP | no |
| JSON | all tables in one JSON document | no |
File name
The name is a pattern. The buttons at the field insert placeholders at the cursor: the name of the Database, the Server, Date and Time. Default name restores WebDonkey's pattern. WebDonkey adds the extension itself. Under File: you see the name the file would get now.
Save to
The default is WebDonkey's backup folder. Choose folder… picks another folder on your Mac, Back to the default folder resets it.
Nothing is overwritten: a name that is taken gets -2, -3 … WebDonkey remembers format, name and folder for this database.
What happens in the background
- WebDonkey creates the dump over the SSH connection with a backup program that fits the database server (
mysqldump,mariadb-dumporpg_dumpin at least the server's version), on the server or through the SSH tunnel with the programs on your Mac. It does not use a program of another kind or an older version, because its dumps cannot be restored reliably. - If a dump ends before its last line, WebDonkey discards it and keeps no half backup.
- The backup appears with your other backups under Remote › Backups. CSV and JSON are listed there as exports, because they cannot be restored.
Troubleshooting
| What you see | What you do |
|---|---|
| No backup program on the server fits … | Set the entry's Path to the database to Automatic or the tunnel. WebDonkey then uses its own programs on your Mac. |
| No fitting program for … on this Mac | Install the matching database under Services, its programs come with it. |
| The dump is incomplete | Back up again. If it stops again, check the connection and the space on the server. |
| Choose a folder that exists on this Mac. | Pick an existing folder or connect the drive. |
See also
Restore a database Premium
Restore plays an SQL dump into a database on the server. This overwrites data: tables from the backup replace the current ones in the target database. That is why WebDonkey always backs up the target database first and asks you twice.
Where to find it
Sidebar Remote › Databases, choose an entry, then Restore in the card's header. The same button is in the Database card on SFTP / SSH.
How to do it
- Click Restore. The Restore database dialog opens with the warning A restore overwrites data.
- Choose the Target database.
- Choose the Source:
- A backup made by WebDonkey: pick it under Choose a backup…. Each line names the label, date, size, format and whether it lies on this Mac or on the server. If there is none, it says No SQL backups yet.
- A file (.sql, .sql.gz, .dump): pick it with Choose….
- If the backup is an archive from Remote › Backups, pick the matching dump in it under Dump in the archive (Choose a dump…).
- Click Restore….
- The confirmation Overwrite …? names the server, the source, the date and the database the backup comes from. Type the name of the target database into the field Type the name of the target database to confirm.
- Click Overwrite. The dialog behind it shows the progress.
No backup is preselected, not even the one of the target database. Which dump goes back is your decision. If the backup comes from another database than the target, a note in yellow says so.
The safety backup
Below the source it says The target database is backed up first. This is not a choice, it always happens. The safety backup is an SQL backup of the current state, listed like any other and marked with ⟲. It lets you undo the restore. Keep it until you have checked the result.
Questions during the run
- Restore into another version? The dump comes from a server with another version. Across versions a restore can fail or turn out differently (data types, defaults, functions). Restore anyway plays it in.
- Rewrite DEFINER? Views, routines, triggers or events in the backup run under an account the target cannot create them for. Rewrite DEFINER to … makes them run under the account that restores. The file itself stays unchanged. Cancel restores nothing.
Progress and cancelling
The progress shows the phases Checking file, Fetching the dump from the archive (for archives), Safety backup and Uploading.
- Cancel stops the upload. What has already reached the server stays applied. Then play in the safety backup to get the previous state back.
- If the restore fails, WebDonkey puts the database back to the state before from the safety backup (Putting the database back) and names the reason.
At the end it says Restore finished.
What happens in the background
- WebDonkey reads from the start of the file which server and version it comes from. A backup only goes back into the same kind of database server: MySQL into MySQL, MariaDB into MariaDB, PostgreSQL into PostgreSQL. For another version series the question above appears.
- A file that switches the database itself, creates one or drops one (such as a
mysqldump --databases) is not played in, because it could change another database than the target. - Backups with folders and databases are restored through Remote › Backups.
Troubleshooting
| What you see | What you do |
|---|---|
| Only SQL backups (.sql, .sql.gz, .dump) can be restored. | Choose an SQL backup, CSV and JSON are exports |
| A message that the file switches the database itself | Use a dump of only this database (mysqldump without --databases, pg_dump without -C) |
| A message that the file is a backup of another kind of database | Choose a dump of the same kind |
| The safety backup failed | Nothing was restored. Check space and access, then try again. |
| This backup belongs to another connection. | Restore it as a file (A file) |
| This archive holds no dump that fits this database. | Choose another archive or a file |
See also
Character set Premium
Old shops and websites often do not store text as UTF-8. Umlauts then show up broken, such as "ä" instead of "ä". The Character set choice in the card's header sets how WebDonkey reads and writes the data of this database.
Where to find it
Sidebar Remote › Databases, choose an entry. The Character set choice sits in the card's header, next to the kind of database.
How to do it
- Open a table with text.
- If umlauts look double-encoded, the card shows the note Umlauts look double-encoded. Click Use Latin-1 there.
- If
�appears, characters could not be decoded. Choose the character set the data was stored in under Character set. - Check the rows. If they look right, the choice stays saved for this entry.
Fields and options
| Choice | For |
|---|---|
| Auto (UTF-8) | default, for all newer databases |
| Latin-1 / Windows-1252 | Western European text of old shops |
| Latin-2 (ISO-8859-2) | Central and Eastern European text |
| Windows-1250 | Central and Eastern European text (Windows) |
| Windows-1251 | Cyrillic (Windows) |
| Greek (ISO-8859-7) | Greek |
| UTF-8 (old servers, utf8mb3) | older MySQL servers with the old UTF-8 |
| Shift-JIS | Japanese |
| GBK | Simplified Chinese |
| Big5 | Traditional Chinese |
| EUC-KR | Korean |
What happens in the background
The character set applies to the connection WebDonkey reads and writes with. It does not change the data stored in the database. WebDonkey remembers the choice per database entry.