SQLManager Pro — User Manual

SQLManager Pro manages SQLite databases and MySQL or MariaDB servers in one app. This manual covers everything the app can do, in the order you will usually need it.

1 — Getting started

After launch, SQLManager Pro shows the welcome window. From here you have three options:

• Open Database — open an existing SQLite file, see chapter 2;
• Create Database — create a new, empty SQLite file;
• Connect to MySQL Server — open a database on a server, see chapter 3.

Below, Recently Opened lists the databases you worked with last. One click opens them again. A cylinder marks a file, a network symbol a server. Clear empties this list; the databases themselves are not touched.

2 — Opening and creating SQLite databases

Opening

Open Database (⌘O) shows the file picker. Choose the database file. The file extension does not matter.

If the file sits in a folder SQLManager Pro may not yet access, a second picker appears right away with the text Allow Permanent Folder Access. Confirm the folder that contains the database. This is needed once per folder: while working, SQLite creates companion files next to the database and needs write access to the folder for that. If you pick a different folder by mistake, the app tells you and lets you choose again.

Creating a new one

New Database (⌘N) asks for the name and location of the new file and then opens it. It is empty; create tables as described in chapter 4, or import them.

Changes from outside

If another app changes the open file, SQLManager Pro reloads it by itself. So you always see the current state.

Closing

Close Database (⌘W) takes you back to the welcome window. The companion files are cleaned up at the same time.

3 — Connecting to a MySQL server

You find Connect to MySQL Server in the welcome window and in the File menu. The dialog asks for:

Test Connection checks the details without opening the database. Connect opens it. From then on you work just as with a SQLite file: data, structure, indices, SQL, import and export.

Changing credentials

Right-clicking a server connection in Recently Opened offers Edit. The dialog opens with the previous details; only the password has to be entered again. You manage all saved connections under Settings › Connections.

If a server does not respond, SQLManager Pro gives up after twenty seconds with a message.

4 — The main window

The sidebar

At the top are the name and location of the database, for a file also its size. A badge next to it shows whether it is SQLite or MySQL. Below follow all tables with their number of rows.

The System Tables switch shows the database's internal tables. They are marked with a gear and cannot be renamed or deleted.

Creating, renaming and deleting tables

The buttons below the list:

• Create — creates a new table. Enter a name and at least one column. For each column choose the type (TEXT, INTEGER, REAL, BLOB, NUMERIC) and set PRIMARY KEY, NOT NULL, UNIQUE and a default value as needed. An INTEGER column with PRIMARY KEY can get AUTOINCREMENT. At the bottom, the SQL Preview shows the command that will be run.
• Rename — gives the selected table a new name.
• Delete (⌘⌫) — deletes the selected table after a confirmation. This cannot be undone.

Rename and Delete are also available by right-clicking a table.

The four tabs

When a table is selected, the right-hand area shows four tabs at the top: Data, Structure, Indices and SQL. When no table is selected, the SQL editor appears there directly.

The toolbar at the top holds Export, Import, the File menu and Settings.

5 — Viewing and editing data

The Data tab shows the contents of the table. Top left you see how many rows are loaded and how many there are in total.

Paging

Rows are loaded 500 at a time, more as you scroll. If a table has more than 500 rows, the arrows at the top right jump forward and back page by page.

Searching and sorting

The Search contents field shows only the rows in which the text appears in any column. Upper and lower case do not matter. A click on a column header sorts by that column, a second click reverses the order. Numbers sort as numbers, empty fields go to the end.

Search and sorting apply to the loaded rows. With large tables, scroll on first, or search with a SELECT … WHERE in the SQL editor.

Changing a field

Double-clicking a field opens it for editing. The same happens with a single click on a field in a row that is already selected. Return, Tab or a click elsewhere saves; Escape discards the change.

Not directly editable are the primary key, AUTOINCREMENT columns, BLOB columns and the columns created_at and updated_at.

Adding, editing and deleting rows

The buttons for this sit at the top right above the table:

• Add Row opens a form with all columns.
• Edit Row appears when exactly one row is selected and shows all fields of the row in a form. Fields that cannot be changed are marked (not editable). Required fields (NOT NULL) must not stay empty.
• Delete removes the selected rows after a confirmation. Select several with ⌘-click or ⇧-click.

Right-clicking a field offers the same commands as a menu: Edit Row and Delete, plus Edit Field. That opens a separate window with the current and the new value, handy for long texts.

6 — The structure of a table

The Structure tab lists the table's columns with name, type and default value. The columns PK, NOT NULL, AI and INDEX show whether a column is the primary key, must be filled, counts up automatically or has an index.

Adding, changing, deleting columns

• New Column — name, data type and, if you like, a default value.
• Edit — changes name, data type, NOT NULL and default value. This is also where you set a primary key (Set as Primary Key) or an index (Mark as Index). If there already is a primary key, the app asks whether to replace it. If the column contains duplicate values, it cannot become the primary key.
• Delete — removes a column after a confirmation. If it is the primary key, the confirmation points out that the table will no longer have one.

Edit and Delete are also available by right-clicking a column.

The CREATE command

Below the columns is the table's complete CREATE TABLE command. Copy puts it on the clipboard.

7 — Indices

An index speeds up queries that search or sort by a column. The Indices tab shows all indices of the table with their columns, sort order, whether they are UNIQUE and any WHERE filter.

Creating an index

Create Index asks for a name, for example idx_customers_city, and for one or more columns. For each column you set the direction: ASC ascending, DESC descending. A UNIQUE Index prevents duplicate values; if the column already contains duplicates, it cannot be created. The SQL Preview shows the command.

Deleting indices

Select one or more indices and delete them. Indices of the primary key cannot be deleted.

8 — The SQL editor

In the SQL editor you write any SQL commands. Execute (⌘↩) runs them. Several commands, each separated by a semicolon, run one after the other.

The result

A query shows its rows below as a table, a changing command the number of affected rows. If several queries return results, each gets its own tab. CSV Export saves the result currently shown as a CSV file.

If a command changes tables or columns, the sidebar and the structure view refresh by themselves.

Completion

Pause briefly while typing and the editor suggests matching SQL keywords, functions, table and column names. Choose with the arrow keys; Tab or a double-click accepts the suggestion, Escape closes the list.

History

History shows the last ten executed commands. A click brings one back into the editor. Clear history empties the list.

Loading SQL files

Load reads an SQL file into the editor, such as an export or a script. With very large files the editor shows only the beginning; the whole file is still executed. How much is shown is set under Settings › Advanced.

9 — Importing data

Import in the toolbar brings data from a file into a table. Supported are:

• CSV — also .tsv and .txt;
• JSON — an array of objects;
• XML.

Choose the format and then the file with Select File. SQLManager Pro reads it and suggests a table layout.

Checking the table layout

In the Schema Configuration step you see:

• the Table Name, which you can change;
• suggested Indices, which you select or deselect one by one;
• all columns with the detected type, two sample values each and the checkboxes PK and NOT NULL. A yellow warning sign means the type was not detected with certainty. Point at it to see how certain the detection was.

Below, a Preview shows the first rows.

If the table already exists

If the table has a name that already exists, SQLManager Pro compares both layouts:

Duplicate IDs

If rows in the file may carry IDs that already exist in the table, the app asks:

• Update existing data (recommended) — existing rows are overwritten with the values from the file;
• Generate new IDs — all rows are added as new;
• Skip conflicts — rows with an existing ID are left out.

The import runs in its own window with a progress display; you can keep working meanwhile. If it fails, nothing is taken over.

10 — Exporting data

Export in the toolbar writes tables to files. Five formats are available:

The two SQL formats let you move a database from SQLite to MySQL or the other way round: export, open the other database, then load and execute the file in the SQL editor. BLOB contents are included.

Select the tables, all at once with Select All. For one table the app asks for a file name, for several for a folder; each table then goes into its own file. After the export, Show in Finder shows the result.

The folder offered first when exporting is set under Settings › Export. Without a default, the app remembers the place you used last.

11 — Images and binary data (BLOB)

BLOB columns contain binary data, often images or documents. In the data view the button Show BLOB then appears for a selected row; right-clicking offers View BLOB and Export BLOB.

The BLOB Preview shows images directly. For other data it gives the size. Export saves the content as a file.

When adding and editing a row, you fill a BLOB field with Select file. An existing BLOB can be replaced, removed with Clear field and restored with Back to original as long as you have not saved yet. Show preview displays the content in the form.

12 — Settings

Language

SQLManager Pro speaks English, German, Danish, French, Italian, Spanish and Chinese. The change takes effect immediately, without a restart.

Appearance

Light, dark or following the system, plus one of twelve accent colours for buttons and selection.

Connections

All saved server connections. For each connection there is:

• Edit — change name and credentials. An empty password field removes the saved password.
• Duplicate — a copy with the same credentials. Handy when you want to open a different database on the same server: only change the database name.
• Remove from List — the connection disappears from this list. It stays among the recently opened databases, and the password stays saved.

Export

The Default Export Path, see chapter 10. Reset Path removes it again.

Advanced

SQL Editor Display Limit: how many megabytes of a loaded SQL file the editor shows, between 5 and 50, 10 by default. The whole file is always executed.

Log

Write log file records what the app does; it is off by default. This helps when something goes wrong.

• Detail — ERROR writes the least, DEBUG the most. Everything from the chosen level upwards is written.
• Record executed SQL commands — very useful for troubleshooting. Note that the commands contain values from your data.
• Show, Show in Finder and Clear for the file. In the viewer, Follow keeps the end of the file in view.

About

Version, an overview of the features, the What's New of this version and the libraries used.

13 — Keyboard shortcuts

14 — Frequently asked questions

I am asked for a folder when opening. Why?

SQLite needs write access to the database's folder, not just to the file. Confirm the folder that contains the file. This is needed only once per folder.

The connection to the server fails.

SQLManager Pro tells you in plain language what is wrong: wrong password or no access from this computer, unknown database, server unreachable or too old. The technical message is below and can be selected and copied. Check host and port, and whether the user may connect to the server from outside.

A field cannot be edited in the table.

Primary keys, AUTOINCREMENT and BLOB columns as well as created_at and updated_at are excluded from direct editing. Change BLOBs via Edit Row.

The search does not find a row that exists.

The search applies to the loaded rows. Scroll on, or search in the SQL editor with SELECT … WHERE.

“The database file is currently in use by another program.”

Another app is writing to the same file. Wait a moment, or close the database in the other app.

How do I get the “What's New” window back?

Under Settings › About.