How to Manage Databases in ServBay Using TablePlus

ServBay is a local web development environment designed specifically for macOS. It integrates multiple programming languages, web servers, and database systems, providing developers with a comprehensive development platform. To efficiently manage the integrated databases in ServBay, such as MySQL/MariaDB, PostgreSQL, SQLite, Redis, MongoDB, and more, using a powerful third-party database management tool is highly recommended. TablePlus is a modern and intuitive client that supports multiple databases and is an ideal companion for ServBay.

This guide will walk you through how to connect to and manage various databases provided by ServBay using TablePlus on macOS.

What is TablePlus?

TablePlus is a robust and user-friendly GUI tool for database management. It supports a broad range of database types, including but not limited to:

• Relational Databases: MySQL, MariaDB, PostgreSQL, SQLite, SQL Server
• NoSQL Databases: Redis, MongoDB
• Others: Cassandra, Oracle, and more

TablePlus offers a suite of features aimed at simplifying database development, management, and maintenance:

• Intuitive Interface: Clean and easy-to-navigate user interface.
• Fast and Responsive: Optimized for performance, remaining smooth even with large data sets.
• Powerful Query Editor: Supports syntax highlighting, auto-completion, and multi-tab queries.
• Data Editing & Viewing: Easily browse, insert, update, and delete data.
• Structure Management: Visual management of table structures, indexes, views, and more.
• Secure Connections: Supports SSL/TLS encrypted connections.

For developers working with ServBay on local projects, TablePlus significantly boosts database operation efficiency.

Connecting ServBay Databases to TablePlus

ServBay, by default, launches and runs various database services locally. You can monitor installed and running database services in the Packages tab within the ServBay application. To connect these databases to TablePlus, you'll need the correct connection parameters, which can be found under the Databases tab in ServBay.

Below are step-by-step instructions and parameters for connecting different types of databases in ServBay.

Connecting to MariaDB or MySQL

ServBay comes with MariaDB installed and running by default (fully compatible with MySQL).

1. Download and Install TablePlus: If you haven’t installed it yet, download and install the TablePlus client for macOS from the official TablePlus website.
2. Open TablePlus and Create a New Connection:
   • Launch TablePlus.
   • Click the "+" icon in the top-left corner or go to File -> New -> Connection....
   • In the popup window, select the database type: MariaDB or MySQL.
3. Configure the Connection Info:
   • Name: Give your connection a recognizable name, such as ServBay MariaDB.
   • Host: 127.0.0.1 (local connection)
   • Port: 3306 (default for MariaDB/MySQL)
   • User: Find the MariaDB/MySQL root username in the Databases tab of ServBay (usually root).
   • Password: Find the MariaDB/MySQL root password in the same place.
   • Database: You can leave this blank (and choose a database after connecting), or specify a particular database to connect to.
   • Alternative: You may also choose to connect via Socket, which is often faster and more direct than TCP/IP.
     • Check Use socket file.
     • Socket Path: /Applications/ServBay/tmp/mysql.sock (default ServBay MariaDB/MySQL socket file path).
     • When using the socket, Host and Port are ignored.
4. Test & Save the Connection:
   • Click the Test button in the bottom right to verify the connection info. A successful test means your parameters are correct.
   • Click Connect to establish the connection, or Save to store the connection details for future use.

Connecting to PostgreSQL

ServBay also supports running PostgreSQL.

1. Open TablePlus and Create a New Connection:
   • Launch TablePlus.
   • Click the "+" icon or go to File -> New -> Connection....
   • Select PostgreSQL as the database type.
2. Configure the Connection Info:
   • Name: For example, ServBay PostgreSQL.
   • Host: 127.0.0.1
   • Port: 5432 (PostgreSQL default)
   • User: Find the default PostgreSQL username in the Databases tab of ServBay (usually servbay).
   • Password: Available alongside the username.
   • Database: PostgreSQL usually requires specifying an initial database – either use the default postgres database, or one you created in ServBay.
   • Alternative: You may also use Socket connection.
     • Check Use socket file.
     • Socket Path: /Applications/ServBay/tmp/.s.PGSQL.5432 (default ServBay PostgreSQL socket file path).
     • Host and Port are ignored for socket connections.
3. Test & Save the Connection:
   • Click Test to verify connectivity.
   • Click Connect or Save.

Connecting to SQLite 3

SQLite is a serverless, self-contained database engine, with all data stored in a single file. ServBay does not “run” a SQLite server, but your applications may access SQLite files using PHP PDO SQLite, Python’s sqlite3 module, etc. TablePlus can connect directly to these database files.

1. Open TablePlus and Create a New Connection:
   • Launch TablePlus.
   • Click the "+" icon or go to File -> New -> Connection....
   • Choose SQLite as the database type.
2. Configure the Connection Info:
   • Name: For example, ServBay SQLite Project.
   • Database File: Click the folder icon, browse, and select your SQLite file (.sqlite, .db, .sqlite3, etc.). For better management, store your project’s database files in the project directory, or under /Applications/ServBay/db/sqlite as recommended by ServBay.
3. Test & Save the Connection:
   • Click Test (if the file exists and is readable).
   • Click Connect or Save.

Connecting to Redis

Redis is a high-performance key-value store. ServBay can install and run a Redis service.

1. Open TablePlus and Create a New Connection:
   • Launch TablePlus.
   • Click the "+" icon or go to File -> New -> Connection....
   • Choose Redis as the database type.
2. Configure the Connection Info:
   • Name: For example, ServBay Redis.
   • Host: 127.0.0.1
   • Port: 6379 (default Redis port)
   • Password: If you set a password for Redis in ServBay, enter it here. The default ServBay Redis installation typically has no password set, so you can leave this blank.
3. Test & Save the Connection:
   • Click the Test button.
   • Click Connect or Save.

Connecting to MongoDB

ServBay supports installing and running MongoDB.

1. Open TablePlus and Create a New Connection:
   • Launch TablePlus.
   • Click the "+" icon or go to File -> New -> Connection....
   • Choose MongoDB as the type.
2. Configure the Connection Info:
   • Name: For example, ServBay MongoDB.
   • Host: 127.0.0.1
   • Port: 27017 (default MongoDB port)
   • User: If you have enabled authentication for MongoDB in ServBay, enter the username. By default, authentication is usually not enabled, so you can leave user and password blank.
   • Password: If authentication is enabled, enter the password.
   • Authentication Database: If needed, specify the authentication database, such as admin.
   • Database: Can be left blank to select a database after connecting, or specify a specific database.
3. Test & Save the Connection:
   • Click the Test button.
   • Click Connect or Save.

Managing Relational Databases with TablePlus (MariaDB/MySQL, PostgreSQL, SQLite)

Once connected, TablePlus provides a similar interface and workflow for managing relational databases like MariaDB/MySQL, PostgreSQL, and SQLite.

1. Creating a Database

• After connecting to a database server (or directly to a file for SQLite), you'll see a sidebar on the left.
• For MariaDB/MySQL and PostgreSQL, click the Databases tab in the sidebar.
• Click the "+" (new) button in the top left, or right-click in a blank area and choose New Database....
• Enter the new database name, and select charset and collation if needed.
• Click Save.

2. Creating a Table

• In the sidebar, select the database where you want to create a table.
• Click the Tables tab in the sidebar.
• Click the "+" (new) button at the top left, or right-click a blank area and select New Table....
• Enter the table name.
• Define the table fields in the lower area:
  • Click "+" to add a column.
  • Enter the column name.
  • Select the data type.
  • Set length/values if the type requires it.
  • Tick PK for primary key, NN for NOT NULL, UQ for unique index, AI for auto-increment, etc.
  • Set a default value if needed.
  • Optionally add a comment.
• After defining all fields, click Save at the top left (or press Cmd + S) to save the table structure.

3. Adding Data

• In the sidebar, select the database containing your table.
• Click the Tables tab, select the table you want to add data to.
• In the table data view, click the + Row button at the bottom.
• A new blank row will appear—double-click each cell to enter the data value.
• Once finished, click Save at the top left (or use Cmd + S) to apply your changes.

4. Querying Data

• In the sidebar, select the database and table.
• Scroll through the data directly in the table data view.
• Use the filter box at the top to quickly search data.
• For more advanced queries, use the SQL Query Editor in TablePlus:
  • Click the SQL Query button in the toolbar or use Cmd + N.
  • In the new tab, enter your SQL statement (e.g., SELECT * FROM your_table WHERE condition;).
  • Click Run Current (lightning icon) or use Cmd + Enter to execute the query. Results appear below.

5. Updating Data

• In the table data view, double-click the cell you want to modify.
• Enter the new value.
• After making changes, click Save at the top left (or use Cmd + S) to commit.

6. Deleting Data

• In the table data view, select the row(s) you want to delete.
• Right-click the selected row(s) and choose Delete Row(s).
• Confirm in the pop-up dialog by clicking Delete.
• Afterward, click Save at the top left (or Cmd + S) to apply the changes.

7. Deleting a Table

• In the sidebar, select the database containing the table.
• Click the Tables tab.
• Select the table(s) you want to delete.
• Right-click the selected table(s) and choose Delete Table(s).
• In the confirmation dialog, enter the confirmation text (usually the table name), then click Delete.

8. Deleting a Database

• In the sidebar, for MariaDB/MySQL or PostgreSQL, click the Databases tab.
• Select the database you want to delete.
• Right-click it and choose Delete Database.
• Enter the confirmation text (usually the database name) in the dialog and click Delete.
• For SQLite: Deleting a database means deleting the corresponding .sqlite file. You need to manually remove the file from your filesystem.

Managing NoSQL Databases with TablePlus (Redis, MongoDB)

TablePlus provides dedicated management interfaces for Redis and MongoDB, with workflows differing from those of relational databases.

Managing Redis Data

After connecting to the Redis instance:

• The left sidebar displays Redis databases (DB0, DB1, etc.), with DB0 used by default.
• Select a database, and all keys within that database appear on the right.
• View Keys: Browse key names directly in the list. Use the filter box at the top to search for specific keys. Click a key to view its type and value. TablePlus presents data appropriately depending on whether the key is a string, hash, list, set, or sorted set.
• Add Key: Click the + Row button at the bottom. Choose the key type, then enter the key name and value. Click Save at the top left.
• Edit Key: Double-click the key name or value field to edit. Click Save at the top left to save changes.
• Delete Key: Select one or more keys, right-click and choose Delete Row(s), then confirm by clicking Delete in the dialog.

Managing MongoDB Data

After connecting to the MongoDB instance:

• The left sidebar lists all MongoDB databases.
• Select a database, and the collections within it appear on the right.
• View Collection Data: Double-click a collection name; the documents in that collection will appear on the right, typically in JSON or a similar format.
• Add Document: In the collection’s document view, click the + Row button at the bottom. TablePlus creates a new blank document—double-click fields or values to edit, then click Save at the top left.
• Edit Document: In the document view, double-click any field name or value to edit. TablePlus also supports direct editing in raw JSON/BSON format. Click Save when you’re done.
• Delete Document: Select one or more documents, right-click and choose Delete Row(s), then confirm deletion.
• Create Collection: In the left sidebar, select the database, click the Collections tab, then click the "+" icon or right-click and choose New Collection.... Enter a collection name and click Save.
• Delete Collection: In the left sidebar, select the database, click the Collections tab, select the collection(s) to delete, right-click and choose Delete Collection(s), and confirm.
• Delete Database: In the left sidebar, select the database you want to remove, right-click and select Delete Database, then confirm.

Important Notes & FAQ

• ServBay Status: Before attempting to connect databases from TablePlus, make sure ServBay is running, and that the database service (e.g., MariaDB, PostgreSQL, Redis, MongoDB) you wish to use is shown as “Running” in ServBay’s Packages tab.
• Firewall: Ensure that your macOS firewall or any third-party security software does not block TablePlus communication on the local database ports (3306, 5432, 6379, 27017).
• Obtaining Credentials: Remember, default database usernames and passwords can be found within the Databases tab of the ServBay app. Keep these safe.
• Changing Default Passwords: For security, it is recommended to change the default root or user passwords for your databases. ServBay’s Databases tab usually provides a password reset function. After resetting, be sure to update your saved credentials in TablePlus accordingly.
• Socket vs Port Connection: Socket connections use the file system pathway for inter-process communication, ideal for connections on the same device—slightly faster and unaffected by network conditions. Port (TCP/IP) connections use the 127.0.0.1 address and work both locally and across networks. In ServBay’s local environment, either approach typically works.
• Can I Use Other Tools? Absolutely. Besides TablePlus, there are many other popular database management tools, such as DBeaver, DataGrip, Sequel Ace (MySQL/MariaDB), pgAdmin (PostgreSQL), MongoDB Compass, and more. The choice depends on your personal preference and needs. The connection procedure will be similar—just copy the correct connection details from ServBay’s Databases tab.

Conclusion

With this guide, you should now be able to connect to and efficiently manage various databases in your ServBay local development environment on macOS using TablePlus. Leveraging TablePlus’s powerful features and user-friendly interface alongside ServBay’s integrated local environment can greatly enhance your productivity. Remember, obtaining accurate connection credentials from the ServBay app is key to a successful connection. Happy coding!