Managing ServBay Databases with Sequel Pro on macOS

Sequel Pro is a popular open-source database management tool built for macOS, designed specifically for managing MySQL and MariaDB databases. For developers using ServBay as a local development environment, Sequel Pro offers an intuitive and efficient interface, making it easy to connect to and manage the databases bundled with ServBay (typically MariaDB or MySQL).

This article provides a detailed walkthrough on how to connect to ServBay databases via Sequel Pro, along with essential guidance for basic database operations.

What is Sequel Pro?

Sequel Pro provides a clean and straightforward graphical interface that makes database management much more efficient and user-friendly. Its key advantages include:

1. Focused on MySQL/MariaDB: Tailored exclusively for MySQL and MariaDB, Sequel Pro delivers optimized features and performance for these databases.
2. User-Friendly: Its easy-to-use graphical user interface (GUI) allows users to carry out a variety of database operations without needing to memorize complex command-line instructions.
3. High Performance: Sequel Pro is engineered for optimal performance, capable of handling large datasets and complex queries quickly.
4. Comprehensive Feature Set: Supports query editing, data import/export, table structure management, user privilege management, SQL editing, and more—covering most daily database management needs for developers.

With Sequel Pro, developers can focus more on their business logic without spending excessive effort on database administration.

Prerequisites

Before you begin, ensure the following requirements are met:

1. ServBay is installed and running on your macOS system.
2. Sequel Pro is installed on your macOS system. If it’s not yet installed, you can download the latest version from the Sequel Pro official site.

Connecting to the ServBay Database

ServBay typically comes pre-configured with a MariaDB or MySQL database. Follow these steps to connect using Sequel Pro:

1. Download and Install Sequel Pro: If you haven’t already, download the macOS client from the Sequel Pro official website and install it following the prompts.
2. Create a New Connection:
   • Open the Sequel Pro application.
   • Click the “Connect” button in the bottom-left corner, or select File -> New Connection from the menu to set up a new database connection.
3. Configure Connection Details:
   • In the connection window, you can choose between two typical connection methods: Socket or Standard (TCP/IP).
   • For a local ServBay environment, it is recommended to use Socket connection, which is generally faster and more secure as it communicates directly through the file system.
   • Select the “Socket” connection type:
     • Username: ServBay’s default root username is root.
     • Password: Find and copy the root user password from the “Database” tab on the ServBay main interface.
     • Socket: Enter the default MariaDB/MySQL socket file path for ServBay: /Applications/ServBay/tmp/mysql.sock.
     • Database: This can be left blank; you can select or create a database after a successful connection.
   • Select the “Standard (TCP/IP)” connection type (Alternative):
     • Host: Enter 127.0.0.1 or localhost.
     • Username: root.
     • Password: Same as Socket connection; get it from the “Database” tab in ServBay.
     • Port: Enter 3306 (ServBay’s default MariaDB/MySQL port).
     • Database: Can be left blank.
   • Note: Choose only one connection method—Socket or Standard (TCP/IP).
4. Test and Save the Connection:
   • After entering the connection details, click the “Test Connection” button at the bottom-left of the window to ensure all information is correct and the database is reachable.
   • If successful, click “Add to Favorites” to save this connection for the future.
   • Finally, hit the “Connect” button to establish the database connection.

Once connected, you’ll enter Sequel Pro’s database management interface.

Basic Database Operations

After connecting to the ServBay database, you can perform various database management tasks using Sequel Pro. Here are some common operations:

Create a Database

1. After a successful connection, the left panel in Sequel Pro displays the list of databases accessible to the current user.
2. Click the “+” button at the top of the left panel and select “Add Database”.
3. Enter the name of the database you wish to create in the prompt (e.g. servbay_demo_db).
4. Click “Add Database” to finalize database creation.

Create a Table

1. Select the database you just created or want to work with from the left panel.
2. Click the “Structure” tab at the top of the main interface.
3. Under the “Structure” tab, click the “+” button and select “Add Table”.
4. In the table structure editor:
   • Enter the table name (e.g. users).
   • Click the “+” column button to add new fields (columns).
   • Define the properties for each field: name, data type, length, whether NULL is allowed, default value, index (e.g. PRIMARY KEY, INDEX, UNIQUE), auto increment, etc.
   • For example, you might add id (INT, PRIMARY KEY, Auto Increment), username (VARCHAR, 50, NOT NULL), email (VARCHAR, 100, UNIQUE), and created_at (DATETIME, Default: CURRENT_TIMESTAMP).
5. After setting up, click “Save” at the bottom of the window to create your table.

Insert Data (Add Rows)

1. In the left panel, select the database containing the table you'll be working with.
2. Choose the target table (e.g. users) from the list of tables under the database.
3. Click the “Content” tab at the top of the main interface.
4. In the data view, click the “+” button at the top left to insert a new row.
5. Double-click each cell of the new row to enter data values for the respective fields.
6. When finished, click “Save” at the bottom to persist the data.

Query Data

1. In the left panel, select the database containing the table you want to query.
2. Choose the specific table from the list under the database.
3. Click the “Content” tab at the top to browse all data in the table (limited by row settings).
4. For more advanced queries, select the “Query” tab at the top.
5. In the SQL editor, enter your SQL query (e.g. SELECT * FROM users WHERE username = 'servbay-demo';).
6. Click “Run Current Query” below the editor to execute it. The results will be displayed in the panel below.

Update Data

1. In the left panel, select the database containing the table whose data you want to update.
2. Select the target table from the list under the database.
3. Click the “Content” tab at the top.
4. Locate the specific data row you wish to edit.
5. Double-click the target cell and edit the value directly.
6. After editing, click “Save” at the bottom to apply your changes.

Delete Data (Delete Rows)

1. In the left panel, select the database containing the table from which you wish to delete data.
2. Select the target table under the database.
3. Click the “Content” tab at the top.
4. In the data view, select the row(s) you want to delete (hold Shift or Command to select multiple rows).
5. Click the “-” button at the top left to delete the selected rows.
6. Confirm deletion by clicking “Save” at the bottom of the window.

Delete a Table

1. In the left panel, select the database containing the table you want to remove.
2. In the table list under the database, right-click the table name you want to delete.
3. From the context menu, choose “Delete Table”.
4. In the confirmation dialog, carefully read the prompt and click “Delete” to confirm.

Delete a Database

1. In the left panel, right-click the name of the database you wish to delete.
2. From the context menu, select “Delete Database”.
3. In the confirmation dialog, read the prompt carefully and, once sure, click “Delete”. Note: Deleting a database will permanently remove the database along with all its tables and data. Use this function with caution.

Conclusion

By following these steps, you can easily use Sequel Pro to connect to and efficiently manage MariaDB or MySQL databases provided by ServBay. The intuitive interface and robust feature set of Sequel Pro, combined with the stable local development environment offered by ServBay, will significantly enhance your web development productivity. Whether designing database schemas, managing data, or running complex SQL queries, Sequel Pro is an indispensable tool for ServBay users handling databases on macOS.