How to Manage ServBay Databases with Navicat

Navicat is a highly regarded database management and development tool among developers. It supports a wide range of popular database systems, including but not limited to MySQL, MariaDB, PostgreSQL, SQLite, and Redis. For developers using ServBay as their local web development environment, integrating Navicat can significantly improve the efficiency and convenience of database management.

This article will guide you through connecting to and managing ServBay’s bundled database instances using Navicat.

What is Navicat?

Navicat provides an intuitive and feature-rich graphical user interface (GUI), enabling developers to easily perform complex database tasks, such as visually designing database structures, running SQL queries, importing/exporting data, and synchronizing databases.

Main advantages of Navicat include:

1. Broad Database Support: Manage multiple database types with a single tool—no need to switch interfaces.
2. User-Friendly Interface: Easy to learn and use, allowing even database novices to get started quickly.
3. High Performance: Optimized for fast data processing and query execution.
4. Comprehensive Feature Set: Covers all phases of database design, development, management, and maintenance.

Using Navicat with ServBay brings tremendous convenience to your local development workflow, letting you efficiently manage ServBay’s database services via a visual interface.

Prerequisites

Before you begin, ensure that you have completed the following:

1. Downloaded and installed ServBay on macOS, and ServBay is running.
2. The databases you wish to connect to (such as MariaDB/MySQL, PostgreSQL, or Redis) are running in ServBay.
3. Downloaded and installed the Navicat client. You can get the appropriate version for your operating system from the official Navicat website.

Managing ServBay’s Built-in MariaDB (MySQL) Database with Navicat

ServBay includes MariaDB or MySQL database services by default. Here’s how to connect and manage these with Navicat:

Connecting to the Database

1. Open the Navicat client.
2. Click the “Connect” button or menu in the top left, and choose “MySQL” or “MariaDB” to create a new connection.
3. In the connection setup window, enter the following information:
   • Connection Name: Choose an easy-to-recognize name, such as ServBay MariaDB or ServBay MySQL.
   • Hostname/IP Address: 127.0.0.1 or localhost.
   • Port: 3306.
   • Username and Password: These credentials can be found in the Database tab of the ServBay application. Make sure to use the correct username and password displayed by ServBay.
   • Socket File: You can choose to connect via Socket instead of TCP/IP (hostname/port). For ServBay MariaDB/MySQL, the socket file path is typically /Applications/ServBay/tmp/mysql.sock.
     Note: You generally only need to enter either Port or Socket for your connection method. If using Socket, you can leave hostname and port blank. Socket connections are typically more efficient for local databases.
   • Database Name: This can be left blank for now—you can select or create a database once connected.
4. Click the “Test Connection” button to verify your settings.
5. If the test is successful, click “OK” to save and establish the connection.

Creating a Database

1. In Navicat’s left-side connection list, right-click the newly created ServBay MariaDB/MySQL connection.
2. Select “New Database.”
3. Enter the desired database name.
4. Choose the appropriate character set and collation (commonly use utf8mb4 and utf8mb4_unicode_ci).
5. Click “OK” to complete database creation.

Creating a Table

1. In Navicat’s left-side list, expand the ServBay connection and select the database you just created.
2. Right-click the database name or the “Tables” section below it, and choose “New Table.”
3. In the table design editor, input the table name and define the columns (fields), including name, data type, length, whether NULL is allowed, default values, primary key, indexes, etc.
4. Once designed, click “Save” in the toolbar, enter the table name, then click “OK.”

Adding Data (Inserting Records)

1. In Navicat’s left panel, select the target table.
2. In the right data view, click the “Insert” button in the toolbar.
3. Enter data for each field in the new blank row.
4. After entering the data, click “Save” in the toolbar.

Querying Data (Browsing Records)

1. In the left panel, select the table you want to work with.
2. Directly browse all data in the table via the data view on the right.
3. Optionally, click the “Query” button in the toolbar to open the SQL editor and write/executive SELECT statements to filter or retrieve data.

Updating Data (Modifying Records)

1. In the left panel, select the target table.
2. In the right data view, double-click a cell in the row you wish to edit.
3. Update the value as needed.
4. Once done, click “Save” in the toolbar.

Deleting Data (Removing Records)

1. In the left panel, select the target table.
2. In the right data view, highlight the rows you want to delete (hold Shift or Ctrl/Cmd to select multiple).
3. Right-click the highlighted rows and select “Delete Record,” or click the “Delete” button on the toolbar.
4. Confirm the deletion in the popup dialog by clicking “Yes.”

Deleting Tables

1. In the left panel, select the database containing the table you want to delete.
2. Right-click the table name and choose “Delete Table.”
3. Confirm the deletion by clicking “Yes” in the popup dialog.

Deleting Databases

1. In the left panel, right-click the name of the database you wish to delete.
2. Select “Delete Database.”
3. Confirm the deletion by clicking “Yes” in the popup dialog. Warning: This operation will permanently delete the database and all its data. Proceed with caution.

Managing ServBay’s Built-in PostgreSQL Database with Navicat

ServBay also supports PostgreSQL databases. Here’s how to connect and manage ServBay’s PostgreSQL instance with Navicat:

Connecting to the Database

1. Open the Navicat client.
2. Click the “Connect” button or menu in the top left, and choose “PostgreSQL” to create a new connection.
3. In the connection setup window, enter the following information:
   • Connection Name: For example, ServBay PostgreSQL.
   • Host Name/IP Address: 127.0.0.1 or localhost.
   • Port: 5432.
   • Username and Password: These credentials can be found in the Database tab of the ServBay application.
   • Socket File: The typical socket path for ServBay PostgreSQL is /Applications/ServBay/tmp/.s.PGSQL.5432.
     Note: If you enter a socket path, you usually don’t need to specify the hostname and port.
   • Database Name: For the initial connection, you can connect to the default database (like postgres) and select or create other databases after connecting.
4. Click the “Test Connection” button to verify your settings.
5. If the test is successful, click “OK” to save and establish the connection.

Creating a Database

1. In Navicat’s left-side connection list, right-click the newly created ServBay PostgreSQL connection.
2. Select “New Database.”
3. Enter the desired database name.
4. Choose the appropriate template, encoding, and collation.
5. Click “OK” to complete database creation.

Creating a Table

1. In Navicat’s left panel, expand the ServBay connection and select the database you just created.
2. Right-click the database name or the “Tables” section below it, and choose “New Table.”
3. In the table design editor, input the table name and define the columns (fields), including name, data type, length, NULL settings, defaults, primary key, indexes, etc.
4. Once designed, click “Save” in the toolbar, enter the table name, and click “OK.”

Adding Data

(These steps are the same as for MariaDB/MySQL. Please refer to the “Adding Data” section above.)

Querying Data

(These steps are the same as for MariaDB/MySQL. Please refer to the “Querying Data” section above.)

Updating Data

(These steps are the same as for MariaDB/MySQL. Please refer to the “Updating Data” section above.)

Deleting Data

(These steps are the same as for MariaDB/MySQL. Please refer to the “Deleting Data” section above.)

Deleting Tables

(These steps are the same as for MariaDB/MySQL. Please refer to the “Deleting Tables” section above.)

Deleting Databases

(These steps are the same as for MariaDB/MySQL. Please refer to the “Deleting Databases” section above.)

Managing SQLite 3 Databases with Navicat

SQLite is a lightweight embedded database engine that stores data in a single file. ServBay allows for easy integration and usage of SQLite database files.

Connecting to the Database

Unlike server connections, connecting to SQLite means connecting directly to a database file:

1. Open the Navicat client.
2. Click the “Connect” button or menu in the top left, and select “SQLite” to create a new connection.
3. In the connection setup window, enter the following information:
   • Connection Name: For example, ServBay SQLite DB.
   • Database File: Click the adjacent button, browse, and select your .sqlite or .db database file. It’s recommended to place your SQLite database files in ServBay’s data directory, such as /Applications/ServBay/db/sqlite/.
4. Click “Test Connection” (if the file exists) to verify accessibility.
5. Click “OK” to save and establish the connection.

Creating a Database

Creating a SQLite database is effectively creating a new database file:

1. In the Navicat connection window, select “SQLite.”
2. Enter a connection name.
3. Set the full path and name of the new database file in the “Database File” field (e.g., /Applications/ServBay/db/sqlite/my_new_database.db).
4. Click “OK” to save the connection. Navicat will prompt you to create the file upon first connection or operation.

Creating a Table

1. In Navicat’s left panel, select the SQLite database you just connected to or created.
2. Right-click the database name and select “New Table.”
3. In the table designer, enter the table name and define the fields.
4. Once designed, click “Save” in the toolbar, enter the table name, then click “OK.”

Adding Data

(These steps are the same as for MariaDB/MySQL. Please refer to the “Adding Data” section above.)

Querying Data

(These steps are the same as for MariaDB/MySQL. Please refer to the “Querying Data” section above.)

Updating Data

(These steps are the same as for MariaDB/MySQL. Please refer to the “Updating Data” section above.)

Deleting Data

(These steps are the same as for MariaDB/MySQL. Please refer to the “Deleting Data” section above.)

Deleting Tables

(These steps are the same as for MariaDB/MySQL. Please refer to the “Deleting Tables” section above.)

Deleting Databases

Deleting a SQLite database simply involves deleting the respective database file:

1. In Navicat’s left panel, right-click the SQLite connection you wish to remove and choose “Delete Connection.” This only removes the connection from Navicat.
2. To permanently remove the database, locate the corresponding .sqlite or .db file and delete it. On macOS Finder, browse to the file’s directory (e.g., /Applications/ServBay/db/sqlite/), then delete the file. Warning: This operation will permanently delete the database and all its contents—proceed with caution.

Managing ServBay’s Built-in Redis Database with Navicat

Redis is a high-performance key-value store, commonly used for caching, message queues, and more. ServBay includes Redis services by default.

Connecting to the Database

1. Open the Navicat client.
2. Click the “Connect” button or menu in the top left, and choose “Redis” to create a new connection.
3. In the connection setup window, enter the following information:
   • Connection Name: For example, ServBay Redis.
   • Host Name/IP Address: 127.0.0.1 or localhost.
   • Port: 6379.
   • Password: By default, Redis installed by ServBay has no password. Unless you’ve manually set a password for Redis, leave this field blank.
4. Click the “Test Connection” button to verify your settings.
5. If the test is successful, click “OK” to save and establish the connection.

Managing Data (Key-Value Operations)

Redis is key-value based, so management differs from relational databases like MySQL. Navicat provides a graphical interface for manipulating Redis keys and values.

1. In Navicat’s left panel, select the ServBay Redis connection you just established.
2. In the right-side view, you’ll see all databases within the current Redis instance (by default, DB 0-15). Double-click to enter a database (e.g., DB 0).
3. View Keys: In the database view, Navicat lists all keys. Use the top search or filter function to quickly locate specific keys.
4. Add Key: Click “New Key” in the toolbar. Choose the key type (String, List, Hash, Set, Sorted Set), enter the key name and its value, and click “Save.”
5. Edit Key: Double-click on a key in the list. You can view and edit its value, type, and expiration time (TTL). Click “Save” after making changes.
6. Delete Key: Select the keys to delete (you can select multiple), right-click and choose “Delete Key,” or click “Delete” in the toolbar. In the confirmation dialog, click “Yes.”

Frequently Asked Questions (FAQ)

• Cannot connect to the database, connection refused?
  • Make sure the ServBay app is running, and the target database service (such as MariaDB/MySQL, PostgreSQL, or Redis) is running in ServBay (check service status in the main ServBay interface).
  • Double-check your connection details in Navicat, including hostname (127.0.0.1), ports (MariaDB/MySQL: 3306, PostgreSQL: 5432, Redis: 6379).
  • If you’re using socket connections, ensure the socket file path is correct (/Applications/ServBay/tmp/mysql.sock or /Applications/ServBay/tmp/.s.PGSQL.5432).
  • Check your firewall settings to ensure Navicat is not being blocked from accessing local ports.
• Forgot database username or password?
  • The default username and password for ServBay’s databases can be found in the Database tab of the ServBay app. Check the application directly for your credentials.

Summary

After reading this article, you should know how to use Navicat, the powerful database management tool, to connect to ServBay’s local development databases: MariaDB/MySQL, PostgreSQL, SQLite, and Redis. With Navicat’s intuitive GUI, you can efficiently design database structures, handle data operations, and manage your databases—optimizing your local development workflow. ServBay provides a stable and reliable backend, while Navicat gives you an easy-to-use administration interface. Together, they make for an ideal solution for efficient local database development.