Supabase Studio can now be self-hosted and accessed over an API. Although there is a web UI but if you plan to use it for your apps or web apps then you can use it as a backend to query the PostgreSQL database that it runs under the hood. The web UI has a database explorer and SQL editor. You can create and manage tables in easy way either visually or by running simple and complex queries. The output of a SQL query can be saved and you can also manage database security settings.
Supabase is one of the best free managed PostgreSQL database that you can use. They even have a hosted version with more features that you can try at their main website. And in order to contribute to the FOSS community, they have open sourced their PostgreSQL Studio that you can self-host. This is in heavy development and so only core functionality is available. But in the coming updates there will be more features.
This PostgreSQL Studio is an exceptionally powerful tool that you can use for your applications. The JSON API that it comes with is very fast and robust. They even have their own API client in popular programming languages such a Python, Dart, JavaScript, etc. Apart from PostgreSQL database. it even offers you a storage that you can use as an object storage to save files.
Main Features of Supabase PostgreSQL Studio:
- Managed PostgreSQL Database: The PostgreSQL database that it runs under behind the scene is fast and secure You don’t have to worry about the internal settings or the database credentials. It gives you an API key that you can use to communicate with the database. All the database operations are supported.
- API with Official Clients: It exposes a very powerful API that you can connect to and make request to update data or retrieve data. The API client for Supbase is written in almost all the prominent programming languages such as Python, Go, Dart, JavaScript. No matter if you are building a web app or mobile app, you will be able to easily integrate the API in the apps. All you need is URL of the instance where this hosted along with the secret key.
- Detailed API Docs: As mentioned above about the API, you can use the Docs that it comes with. The API docs are developer friend and you can find them in the Supabase Studio. There are some example code and projects as well that you can use to learn how to implement this.
- Tables Editor and Explorer: Supabase comes with an amazing web UI and interface where you can manage everything visually. You can explore all the tables in the database and also create new ones. You can opt for RLS (row level security) while creating tables and insert data visually as well.
- SQL Editor: Supabase PostgreSQL Studio comes with a powerful SQL editor that you can use to query the entre database. it supports highlighting and generates suggestions as you type your query. You can run simple and complex queries to retrieve data and then save the output. You can export results of a SQL as CSV or copy the result to clipboard as markdown.
- Extensions Support: Supabase is extensible software and thus its functionality can be extended with the help of extensions. For now, it already comes with a lot of extensions that you can enable and use. Extensions such as PGCRYPTO, PLPGSQL, FUZZYSTRMATCH are already available in it. You can enable them for cryptographic functions, PL/pgSQL procedural language, and fuzzy search respectively.
- Collaboration: The Supabase studio supports multi user architecture. Here you can invite different users and then collaborate on database tasks. You can create invites in the Users section and then invite people via email.
Installing PostgreSQL Studio by Supabase:
The installation method is very simple for Supabase Studio. It supports Docker installation and you can set it up on any server by running a couple of commands. There is a sample Docker configuration it comes with and you can use that for testing it locally. But for production environment, you can change those values to anything else.
Make sure that Docker, docker-compose, and Git are installed. Then proceed by running the following commands to cline the GitHub repository.
Now, you just start the server by running this docker-compose command.
it will take a while to download and setup Docker images. When you start using the application log after downloading and extraction process, just open browser and type localhost:3000 to reach the main dashboard.
At this point, the installation is successful for Supabase Studio.
Using PostgreSQL Studio to Manage Data and Tables:
After you are on the main interface, you can see the that there is a default database available. You can now start tinkering with it and add tables. Initially it is blank, so you have to add different tables to start filling it. So, create tables either visually or by running SQL. Go to Tables section and from there, use the “Create a new table” button to add a new table. Define your table there and then simply add it to the database.
After the table has been created, just click on it to see the data. It is blank at first, so just click on the fields to add some rows. You can manually insert data or you can use SQL editor.
Top open SQL editor, just click on the Terminal icon from the sidebar. In the SQL editor, you can literally type any SQL query and it will run against the specified table. It shows the SQL output in the bottom pane and from there, you can either save it as CSV or simply copy it to the clipboard as markdown.
Next, you can explore the users management section. Here you can enter emails of the user you’d like to collaborate with you on the database. Just specify the email and it will send the invites.
There is a database settings and status that you can explore. Just click on the database icon and there you can see the database definition at glace. It displays information about tables and user roles. Here, you can also configure database replication option if you want. From the same section, you can also configure some extensions to add a new functionality.
In this way, you can use this powerful PostgreSQL studio. Just run your own instance either locally or on VPS and then manage the database. You can play with the API and then simply do whatever you want.
Final thoughts:
If you are looking for a self-hosted managed PostgreSQL database then this one by Supabase is one of the best options you can try. Just run your instance and use the API to manage data. You can implement the API in your applications and perform data retrieval and updating tasks efficiently and securely. I liked the way it works and that they have made the installation process is super simple. So, give it a try if you usually use tools like this and let me know what you think about it.
