Connecting to PostgreSQL Server
In this document, we are going to see how to connect and use a PostgreSQL Server in Selfmade Ninja Labs.
What is PostgreSQL?
PostgreSQL is a powerful, open source object-relational database system.
PostgreSQL offers advanced features like JSON support, custom data types, advanced indexing, and powerful query optimization, making it ideal for complex applications.
Getting Started
Step 1: Access Services
Find the Services tab on the left window pane and then click on Services.
Next, find the PostgreSQL Server service and then click on Manage.
User and Database Setup
Create PostgreSQL User
In order for us to use PostgreSQL service, we need to create a User and a Database. Once you click on Manage, you will see the PostgreSQL service. Under Dashboard, click the "Create PostgreSQL User" and enter relevant details and click on Add User
Create Database
Once you add the user, now it is time to create a Database. Click on Database, next to the Dashboard button and then enter the Database name.
The database name follows the syntax 'username_databasename'.
Lab Environment Setup
Deploy Essential Lab
After creating the User and the Database, go to Machine Labs and then look for Essentials Lab and click on Dashboard.
Once you open the dashboard, click on Deploy and then click on Confirm Deploy.
Launch Development Environment
Once the service is deployed, you will have to click on the Code button.
Here, you will be able to copy the Code Server Password for the current instance, copy that and then click on Launch IDE, which will open VS Code in another tab, where you can use the VS Code's terminal to connect to the PostgreSQL.
VS Code provides a complete development environment with PostgreSQL connectivity tools and database management capabilities.
Connecting to PostgreSQL
Get Connection Command
Once the VS Code editor opens in the new tab, go back to Labs, click on the Services button and then go to the Adminer service and then copy the VS Code Command (under Port Forwarding).
Execute Connection
Paste the command in the terminal of the VS Code editor and then hit enter. A popup will appear asking you to Open in Browser, click on the button.
Access Database Interface
This will open the application in another tab. Here, under System, select the PostgreSQL Service, and enter the relevant server name, username, password and the Database that you created, then hit Login.
Once you hit login, this will open the PostgreSQL Server Service where you can perform different database commands.
Working with PostgreSQL
Once you hit login, this will open the PostgreSQL Server Service where you can perform different database commands.
What You Can Do
| Feature | Description |
|---|---|
| Advanced Queries | Complex SQL queries with CTEs, window functions |
| JSON Operations | Native JSON and JSONB data type support |
| Custom Functions | Create stored procedures and custom functions |
| Advanced Indexing | GIN, GiST, and other specialized index types |
| Extensions | Add functionality with PostgreSQL extensions |
- ACID Compliance: Full transaction support with strong consistency
- Extensibility: Add custom data types, operators, and functions
- Performance: Advanced query planner and optimization
- Standards Compliance: Follows SQL standards more closely than other databases
Best Practices
- Query Optimization: Use EXPLAIN ANALYZE to optimize query performance
- Indexing Strategy: Implement appropriate indexes for your query patterns
- Connection Pooling: Use connection pooling for production applications
- Regular Maintenance: Perform VACUUM and ANALYZE operations regularly
PostgreSQL supports advanced features like full-text search, geospatial data (PostGIS), and sophisticated data types that make it suitable for complex applications.