Managing ServBay Databases with Sequel Pro on macOS

Sequel Pro is a popular, open-source database management tool designed for macOS, and is especially useful for MySQL and MariaDB databases. For developers who use ServBay as their local development environment, Sequel Pro offers an intuitive and efficient interface to easily connect and manage the database instances provided by ServBay (typically MariaDB or MySQL).

This guide details how to connect Sequel Pro to your ServBay database and perform basic management operations.

What is Sequel Pro?

Sequel Pro provides a clean and straightforward user interface, making database management faster and simpler. Its key advantages include:

1. Focused on MySQL/MariaDB: Sequel Pro is purpose-built for MySQL and MariaDB management, offering features and performance optimizations tailored to these databases.
2. User-Friendly Interface: The intuitive graphical interface (GUI) makes it easy for users to perform a wide range of database operations without memorizing complex command-line instructions.
3. High Performance: Sequel Pro is optimized for speed, capable of handling large datasets and complex queries efficiently.
4. Robust Features: Supports query editing, data import/export, table structure management, user privileges, SQL editing, and more, covering most daily developer needs.

With Sequel Pro, developers can concentrate on building business logic instead of spending excessive time on database administration.

Prerequisites

Before you get started, ensure you have the following:

1. ServBay is installed and running on your macOS system.
2. Sequel Pro is installed on your macOS system. If not, download the latest version from the Sequel Pro official website.

Connecting to the ServBay Database

ServBay typically provides a MariaDB or MySQL database by default. To connect using Sequel Pro, follow these steps:

1. Download and Install Sequel Pro: If not installed, download the macOS client from the Sequel Pro official website and follow the installation prompts.
2. Create a New Connection:
   • Open the Sequel Pro application.
   • Click the “Connect” button in the lower-left corner, or select File -> New Connection from the menu to set up a new database connection.
3. Set Up Connection Details:
   • In the connection window, you can choose between two common methods: Socket or Standard (TCP/IP).
   • For your local ServBay environment, Socket connections are recommended as they are typically faster and more secure, communicating directly through the file system.
   • Choosing “Socket” Connection Type:
     • Username: ServBay’s default database root username is root.
     • Password: The password for root can be found and copied in ServBay's main interface under the “Database” tab.
     • Socket: Enter the default MariaDB/MySQL socket file path for ServBay: /Applications/ServBay/tmp/mysql.sock.
     • Database: Leave blank—you can select or create a database after connecting.
   • Choosing “Standard (TCP/IP)” Connection Type (Alternative):
     • Host: Enter 127.0.0.1 or localhost.
     • Username: root.
     • Password: Same as for socket connection—retrieve from ServBay’s “Database” tab.
     • Port: Enter 3306 (ServBay’s default MariaDB/MySQL port).
     • Database: Leave blank.
   • Note: You only need to choose one connection method—either Socket or Standard (TCP/IP).
4. Test and Save Connection:
   • After entering the connection details, click the “Test Connection” button in the lower-left corner to ensure the information is correct and that you can connect successfully.
   • If the test succeeds, click “Add to Favorites” to save the connection for quick access later.
   • Finally, click “Connect” to establish the database connection.

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

Basic Database Operations

After connecting to your ServBay database, Sequel Pro enables you to perform a variety of management tasks. Here are some common operations:

Create a Database

1. Once connected, the left panel in Sequel Pro will show a list of databases you have access to.
2. Click the “+” button at the top of the left panel and select “Add Database”.
3. In the popup dialog, enter the name for your new database (e.g., servbay_demo_db).
4. Click “Add Database” to create the database.

Create a Table

1. In the left panel, select the database you just created or wish to modify.
2. Click the “Structure” tab at the top of the main area.
3. Click the “+” button under the “Structure” tab and select “Add Table”.
4. In the table structure editor:
   • Enter the table name (e.g., users).
   • Click the “+ Column” button below to add fields (columns).
   • Configure each field: name, data type, length, NULL allowance, default value, indexes (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), created_at (DATETIME, Default: CURRENT_TIMESTAMP).
5. When configuration is complete, click “Save” at the bottom to create the table.

Add Data (Insert Rows)

1. In the left panel, select the database containing the table you want to work with.
2. From the list of tables, select the table you want to add data to (e.g., users).
3. Click the “Content” tab at the top of the main area.
4. In the data view, click the “+” button at the top-left to insert a new row.
5. Double-click each cell in the new row and enter values for each field.
6. After entering data, click “Save” at the bottom to save the new row.

Query Data

1. In the left panel, select the database containing the desired table.
2. From the table list, choose the table you want to query.
3. Click the “Content” tab at the top to browse all data in the table (subject to row limits).
4. For more advanced queries, click the “Query” tab at the top.
5. Enter your SQL statement in the editor (for example: SELECT * FROM users WHERE username = 'servbay-demo';).
6. Click “Run Current Query” below the editor to execute. The results will display in the area below.

Update Data

1. In the left panel, select the database containing the table you want to update.
2. From the table list, select the relevant table.
3. Click the “Content” tab at the top.
4. Locate the row you wish to edit in the data view.
5. Double-click the cell you want to modify and edit the value directly.
6. When finished, click “Save” at the bottom to apply your changes.

Delete Data (Delete Rows)

1. In the left panel, select the database containing the table with rows to delete.
2. From the table list, select the table you wish to edit.
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).
5. Click the “-” button at the top-left to delete the selected rows.
6. Click “Save” at the bottom to confirm.

Delete a Table

1. In the left panel, select the database containing the table to be deleted.
2. In the table list, right-click the name of the table you wish to remove.
3. In the context menu, select “Delete Table”.
4. Read the confirmation prompt carefully and click “Delete” if you’re sure.

Delete a Database

1. In the left panel, right-click the name of the database you wish to delete.
2. In the context menu, select “Delete Database”.
3. In the confirmation dialog, read the warning and click “Delete” if you are certain. Note: Deleting a database permanently removes ALL its tables and data. Proceed with caution.

Conclusion

Following the above steps, you can easily connect to and efficiently manage MariaDB or MySQL databases provided by ServBay using Sequel Pro. Sequel Pro’s intuitive interface and powerful features, paired with ServBay’s stable local environment, will greatly enhance your web development workflow. Whether designing database schemas, manipulating data, or running complex SQL queries, Sequel Pro is an essential tool for ServBay users managing databases on macOS.