Hello, friends. There are database management tools created with web technology such as PHPMyAdmin but there are also others that emerge as alternatives. So, in this post, you will learn how to deploy the OmniDB server on Ubuntu 20.04.
OmniDB is a client for managing MySQL, MariaDB, PostgreSQL, and SQLite databases. It has two versions, a desktop version which we told you about some time ago and a self-hosted version. We will talk about the latter.
This client allows us to install it on our server and access it from anywhere. Or even use it in an internal network for the same purposes. With OmniDB we will be able to do what it is expected to do, query, modify and manage databases; besides this, it includes SQL Editors with autocomplete and others.
On the other hand, work unification is the order of the day thanks to Tab System with multiple contexts.
So, let’s get started.
Install OmniDB Server on Ubuntu 20.04
The process is quite simple thanks to the DEB package that the developers leave us. So, connect to your server and update it.
sudo apt update sudo apt upgrade
Now, download OmniDB DEB package
cd /tmp/ wget https://github.com/OmniDB/OmniDB/releases/download/3.0.3b/omnidb-server_3.0.3b_linux_x86_64.deb --2021-08-04 15:08:35-- https://github.com/OmniDB/OmniDB/releases/download/3.0.3b/omnidb-server_3.0.3b_linux_x86_64.deb Resolving github.com (github.com)... 140.82.121.4 Connecting to github.com (github.com)|140.82.121.4|:443... connected. HTTP request sent, awaiting response... 302 Found Location: https://github-releases.githubusercontent.com/56064194/13663400-3ff3-11eb-8d0c-7de42ff344c5?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20210804%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20210804T130835Z&X-Amz-Expires=300&X-Amz-Signature=b329de2c87863f6cbe238e861488be0337ed0570899b2395511ceac88222ab8f&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=56064194&response-content-disposition=attachment%3B%20filename%3Domnidb-server_3.0.3b_linux_x86_64.deb&response-content-type=application%2Foctet-stream [following] --2021-08-04 15:08:35-- https://github-releases.githubusercontent.com/56064194/13663400-3ff3-11eb-8d0c-7de42ff344c5?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20210804%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20210804T130835Z&X-Amz-Expires=300&X-Amz-Signature=b329de2c87863f6cbe238e861488be0337ed0570899b2395511ceac88222ab8f&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=56064194&response-content-disposition=attachment%3B%20filename%3Domnidb-server_3.0.3b_linux_x86_64.deb&response-content-type=application%2Foctet-stream Resolving github-releases.githubusercontent.com (github-releases.githubusercontent.com)... 185.199.110.154, 185.199.111.154, 185.199.108.154, ... Connecting to github-releases.githubusercontent.com (github-releases.githubusercontent.com)|185.199.110.154|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 51477258 (49M) [application/octet-stream] Saving to: ‘omnidb-server_3.0.3b_linux_x86_64.deb’ omnidb-server_3.0.3b_linux_x86_64.deb 100%[=====================================================================================>] 49.09M 52.5MB/s in 0.9s 2021-08-04 15:08:36 (52.5 MB/s) - ‘omnidb-server_3.0.3b_linux_x86_64.deb’ saved [51477258/51477258]
At the time of writing this post, the latest stable version is 3.0.3b so you should check the project website before doing anything.
Then, install it like any other package
sudo apt install ./omnidb-server_3.0.3b_linux_x86_64.deb Reading package lists... Done Building dependency tree Reading state information... Done Note, selecting 'omnidb-server' instead of './omnidb-server_3.0.3b_linux_x86_64.deb' The following NEW packages will be installed: omnidb-server 0 upgraded, 1 newly installed, 0 to remove and 24 not upgraded. Need to get 0 B/51.5 MB of archives. After this operation, 53.0 MB of additional disk space will be used. Get:1 /tmp/omnidb-server_3.0.3b_linux_x86_64.deb omnidb-server amd64 3.0.3 [51.5 MB] Selecting previously unselected package omnidb-server. (Reading database ... 28542 files and directories currently installed.) Preparing to unpack .../omnidb-server_3.0.3b_linux_x86_64.deb ... Unpacking omnidb-server (3.0.3) ... Setting up omnidb-server (3.0.3) ... Created symlink /etc/systemd/system/multi-user.target.wants/omnidb.service → /etc/systemd/system/omnidb.service
When the process is finished, OmniDB will be ready to use.
Configuring OmniDB Server
The OmniDB installation includes a Systemd service to manage the program.
The first thing to do is to check the status of this service.
sudo systemctl status omnidb.service
● omnidb.service - OmniDB server daemon
Loaded: loaded (/etc/systemd/system/omnidb.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2021-08-04 15:09:06 CEST; 1min 31s ago
Process: 1466 ExecStart=/bin/bash -c /opt/omnidb-server/omnidb-server & (code=exited, status=0/SUCCESS)
Main PID: 1467 (omnidb-server)
Tasks: 14 (limit: 4566)
Memory: 266.9M
CGroup: /system.slice/omnidb.service
├─1467 /opt/omnidb-server/omnidb-server
└─1498 /opt/omnidb-server/omnidb-server
Aug 04 15:09:10 osradar bash[1498]: Applying social_django.0008_partial_timestamp... OK
Aug 04 15:09:10 osradar bash[1498]: Applying social_django.0009_auto_20191118_0520... OK
Aug 04 15:09:10 osradar bash[1498]: Applying social_django.0010_uid_db_index... OK
Aug 04 15:09:10 osradar bash[1498]: Attempting to migrate users, connections and monitoring units and snippets from OmniDB 2 to 3...
Aug 04 15:09:10 osradar bash[1498]: Source database file does not contain the required tables, skipping...
Aug 04 15:09:10 osradar bash[1498]: Starting OmniDB server...
Aug 04 15:09:10 osradar bash[1498]: Checking port availability...
Aug 04 15:09:10 osradar bash[1498]: Starting server OmniDB 3.0.3b at 127.0.0.1:8000.
Aug 04 15:09:10 osradar bash[1498]: Open OmniDB in your favorite browser
Aug 04 15:09:10 osradar bash[1498]: Press Ctrl+C to exit
As you can see, it is running on http://127.0.0.1:8000 but since we are installing it on a server, this is not useful.
So, stop the service.
sudo systemctl stop omnidb.service
Now, initialize but with the -p options to define a port and -H to define the host.
omnidb-server -p 8080 -H 0.0.0.0.0 Attempting to migrate users, connections and monitoring units and snippets from OmniDB 2 to 3... Source database file does not contain the required tables, skipping... Starting OmniDB server... Checking port availability... Starting server OmniDB 3.0.3b at 0.0.0.0:8080. Open OmniDB in your favorite browser Press Ctrl+C to exit
Now yes. When you open the web browser and visit http://ipserver:8080 you can see the login screen.
Press CTRL + C to finish running OmniDB because we need to create a new user.
This can be done as follows:
omnidb-server -u angelo 123456
Replace angelo with your username and 123456 with a more appropriate password.
Now run it again and you will be able to log in. You will see the login screen and then the welcome screen.
And you will be able to edit a connection.
Optional: Using Nginx as a reverse proxy
The default is that we have to use a reverse proxy like Nginx and from there manage the connection to OmniDB. This is very well detailed in the official documentation.
So, if OmniDB is running, stop it. Either using the service or manually.
Install Nginx
sudo apt install nginx
And create a new configuration file for Nginx
sudo nano /etc/nginx/sites-available/omnidb
Now add the following
server {
listen 443 ssl;
listen [::]:443 ssl;
include snippets/ssl-domain.conf;
include snippets/ssl-params.conf;
server_name domain.org;
client_max_body_size 75M;
location / {
proxy_pass http://127.0.0.1:8000;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Ssl https;
proxy_set_header X-Forwarded-Proto https;
proxy_set_header X-Forwarded-Port 443;
proxy_set_header Host $host;
proxy_http_version 1.1;
proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection "upgrade";
}
}
Remember to replace server_name with your domain.
Save the changes and close the editor.
Check the Nginx syntax for an error.
sudo nginx -t nginx: the configuration file /etc/nginx/nginx.conf syntax is ok nginx: configuration file /etc/nginx/nginx.conf test is successful
For all this to work, it is necessary to edit the default OmniDB configuration file.
sudo nano ~/.omnidb/omnidb-server/config.py
And make sure to leave these values as follows
LISTENING_ADDRESS = '127.0.0.1' LISTENING_PORT = 8000IS_SSL = False SESSION_COOKIE_SECURE = True CSRF_COOKIE_SECURE = True
Save the changes and close the editor.
The next step is to create an SSL certificate with Let’s Encrypt. So, install certbot and the Nginx plugin.
sudo apt install certbot python3-certbot-nginx
And generate the certificate
sudo certbot --nginx -d [domain]
Put your domain and accept the license terms and you are done.
Finish the process by starting the OmniDB service and restarting Nginx.
sudo systemctl start omnidb sudo systemctl restart nginx
Now login from your browser in a secure way.
Conclusion
Tools like OmniDB are very welcome because they allow us to have flexibility in accessing our databases. With this tool, you can set up a web interface that you can access from anywhere to manage a database anywhere. So, enjoy it.
