Using DBeaver with a Hosted Postgres

DBeaver is a versatile database management tool that allows you to interact with a wide range of databases, including PostgreSQL. This guide will walk you through the steps to set up and use DBeaver with a hosted Postgres database, enabling you to perform various database operations efficiently.

Table of Contents

• Setting Up DBeaver
• Connecting to Your Hosted Postgres Database
• Basic Operations in DBeaver

Setting Up DBeaver

1. Download and Install DBeaver: If you haven't already, download DBeaver from the official website. Choose the version suitable for your operating system and follow the installation instructions.

2. Launch DBeaver: Open DBeaver from your applications menu and ensure it is running.

Provisioning a Serverless Postgres

1. To get started, go to the the Neon Console or pg.new and create a new project by entering a project name of your choice.

2. Retrieve connection details for your Neon Postgres database:

   • Navigate to the Dashboard of your Neon project.
   • Click on the Connect button which opens a modal.
   • Select your database and branch.
   • Select Parameters only to view the connection details.

   You will be provided with the following details:

   • PGHOST: The hostname of your Neon Postgres database.
   • PGDATABASE: The name of your database
   • PGUSER: Your database username.
   • PGPASSWORD: Your database password.

Save the connection details as you will need them in the next steps.

Connecting to Your Hosted Postgres Database

1. Open DBeaver: Ensure DBeaver is running. You will see the main dashboard.

2. Create a New Database Connection:

   • Click on the "New Database Connection" button (usually a plug icon or from the "Database" menu).
   • In the "Connect to Database" wizard, select "PostgreSQL" from the list of database types and click "Next".

3. Enter Connection Details:

   

   • Fill in the required fields based on your Neon connection string:
     • Host: The endpoint of your hosted Postgres database. Enter the value of PGHOST you saved earlier from Neon.
     • Port: The port number. Enter 5432 (default for Postgres).
     • Database: The database name. Enter the value of PGDATABASE.
     • Username: Your database username. Enter the value of PGUSER.
     • Password: Your database password. Enter the value of PGPASSWORD.
   • Enable "Show all databases" to ensure all databases in your Neon project are listed.

   

   • Click "Edit Driver Settings" if needed to ensure SSL is enabled. Under the "Driver Properties" tab, set sslmode to require.

4. Test the Connection:

   • Click the "Test Connection" button to verify the connection details.
   • If successful, click "Finish" to save the connection. Your new database connection will appear in the left sidebar.

Basic Operations in DBeaver

1. Running SQL Queries

• Right-click on your database connection in the left sidebar and select "SQL Editor" > "New SQL Script".
• Enter your SQL queries in the editor and click the "Execute" button (play icon) to run them.
• View the results in the results pane below the editor.

2. Managing Tables

• Expand your database connection in the left sidebar, then navigate to "Databases" > "neondb" > "Schemas" > "public" > "Tables".
• Right-click on "Tables" to create a new table or manage existing ones (e.g., view, edit, or drop tables).

3. Importing and Exporting Data

• To import data:
  • Right-click on a table and select "Import Data".
  • Choose the source file (e.g., CSV) and follow the prompts to map the columns.
• To export data:
  • Right-click on a table and select "Export Data".
  • Choose the format (e.g., CSV, JSON) and follow the prompts to save the file.

Conclusion

DBeaver is a powerful tool for managing your hosted Postgres database. With its intuitive interface and robust features, you can easily perform tasks such as creating tables, running queries, and visualizing data. By following this guide, you should be well-equipped to utilize DBeaver effectively for your database management needs.