Free SQL Software Tools
Our databases are the ones that keep the Internet alive. There has even been talk for some years about the development of operating systems with which users do not have direct contact with the files, but their personal files are stored in a local DB. In fact, we already see how there are online services that do this for us, such as Google Drive or Nextcloud, the latter written under free license Affero.
We have already talked about solutions for BD, both in proprietary and free software, noSQL or SQL type, and the latter will be dealt with in this article.
A practical example, which we have already brought up, is MySQL Workbench, with which we exposed most of the tasks of monitoring and development teams. But today, more and more users deal with cases in which they must extract information from a DB.
Although we will, for now, leave the non-SQL DB aside, generally we will be able to create a user group with read-only rights so that certain employees can remunerate raw material for their work. This is inside the BD itself, scattered in many files in the BD engine.
Thus anyone can use the Structured Query Language (SQL) via the command line and get a huge amount of information. This is possible because every BD engine, almost always, brings its own program for it.
We will not describe such software in this article because, although we are fanatics of pure and simple text, graphics and colors have a particular attraction for many human beings. In the rules set by Edgar Frank Codd (Codd Rules) for relational DBs, the number four (description of the DB) is suitable to be represented graphically, especially the entity and relationship models.
Caption: Example of relationship entity
Free SQL Software
Thus, our list of applications made in free software meets or contains most of the tools necessary to create, visualize or modify both the DB structures and their reports, queries, triggers, and so on. It doesn’t pretend to be an exhaustive list, nor will we worry about how up-to-date or maintained they are: free software is eternal, because we will always be able to learn from the basics, direct from the experience of our predecessors through its original source code. So we can even adapt it to our needs and/or possibilities, and even bifurcate and create our own tools!
In chronological order of creation:
Inspired by MySQL-Webadmin, in 1998 this tool appears for MySQL (and then for its bifurcation,MariaDB). But it’s really in 2001 when it takes off on SourceForge to be taken over by Olivier Müller, Marc Delisle and Loïc Chapeaux. Its creator, Tobias Ratschiller, lacked the time to take care of the large community that grew around his idea, which grew steadily and in 2015 had its second upturn by creating its own content distribution network (CDN) and hosting its source code in GitHub (with 17 different repositories; it’s a whole universe in itself).
phpMyAdmin even generated phpPgAdmin, which is used for PostgreSQL but now have independent developments. Worldwide name confusion is frequent; despite being very simple, Google receives many wrong searches every day. This, by the way, leads us to our next tool on the list.
Yes, pgAdmin is another software for BD PostgreSQL, and also presented in 1998… as proprietary software. Since change is the only constant, pgAdmin has gone through four major stages, each very different, and in this version 4 is written in Python with Flask and JQuery with Bootstrap and the Qt graphics library; it also works under Microsoft Windows®. It deserves special mention for showing geometric type data and producing panoramas like the following, directly from a BD:
Caption: pgAdmin, free SQL software, representing geometric type data
The next three on our list are from 2001. SQLyog is poorly publicized and, like pgAdmin, was originally in proprietary software and since 2013 maintains a repository in GitHub, as the Google Code service announced its closure.
It has user-friendly features: auto-completion of SQL statements, spreadsheet-style environment, session restore preventing job loss, favorite SQL scripts, etc.. Written in C++ language and only for Windows® but in self-contained mode (it does not use operating system libraries).
Squirrel SQL Client
Written in Java language, with its graphical library Swing runs in any environment with Java Virtual Machine; we have included it for its portability. Although its graphical interface is frugal, it can connect to 27 different BDs, including Microsoft Access®, MySQL, PostgreSQL (actually all those included in JDBC 2.0).
Caption: Squirrel SQL Client
Privative at birth in 2001, it is free software since 2005. It’s not very well known, but… if we name Oracle®, does it ring a bell? TOra actually uses a package of tools from that company to connect precisely the commercial databases. This library, made by Sergei Kuchin, is under BSD license since 1996 and as Squirrel SQL Client is able to use ODBC to connect to several DBs. The latest update of May 12, 2019 is optimized for compiling with GNU C++ 9.1.
We reach the year 2006 and Java plus JDBC continue to be protagonists. Therefore (as well as Squirrell SQL Client) DBEdit connects with many DBs but with a simplicity that is foolproof. Look:
No, it doesn’t come from Switzerland. Its previous name was “MySQL-Front” and we told you about the confusion of names, so in order not to have legal problems of any kind between both projects was relaunched as HeidiSQL for BD MySQL. Going through multiple stumbling blocks, its creator, the German Ansgar Becker, in 2011 added support for Microsoft SQL Server® (Heidi also runs on Windows®) and in 2014 for PostgreSQL. It has full command-line support. However, look how pretty it is graphically, here by creating a table:
It becomes a giant when handling large BD, Oracle®, MS SQL®, PostgreSQL, MongoDB, Firebird, SQLite, and even Elasticsearch (a tool using another tool), all remotely. Don’t expect more beauty, even your GitHub repository is quite sparse (several users have composed “themes” independently). Come on, a command terminal with windows would be the correct description, but when we need to work hard this Czech application doesn’t beat around the bush. Made with PHP 5 (not exclusive for previous versions of PHP), it allows you to compile a single file (use and delete, if you want or need). Jakub Vrana, its author, used for more than 5 years phpMyAdmin and everything that this software lacks or is deficient we will find in Adminer, except options to export DB, where phpMyAdmin is clearly winner.
Database Deployment Manager
We reached this decade of 2010 and introduced Database Deployment Manager, oriented towards the design and creation of DB. We include it here for its didactic use as it lacks elements that cause any distraction. It is worth mentioning the export of graphics in SVG format with the structure we have created or modified. Excellent for students, a clean graphical interface, with no major pretensions. It only works with Sqlite, MySQL and CUBRID, an open source BD made in pure C language.
It is also made in Java with the corresponding DB connector; therefore they boast of connecting to any database.
Caption: DBeaver Logo
It is the most complete solution and even, Pandora FMS, has an Enterprise version to handle BD noSQL.
Caption: DBeaver in action
It is the only one that is presented as a complement in WordPress, but careful, only to manage the DB containing WP. Yes, the proposal sounds a bit risky, in fact the community officially rejected it, but free software offers full freedom and each programmer can download it on their own. We are excited that it has an exclusive module for configuring keyboard shortcuts, a very practical and useful issue for anyone.
Caption: Keyboard shortcuts in MyWebSQL
They assert that, without knowing a single SQL command and through its intuitive graphical environment, any user can maintain and edit a DB, thanks to its “what you see is what you get”.
Polish application. Again Java and its connector, but in this opportunity we observe instructions to place Orbada in a pendrive since it uses only 150 megabytes, something small when we talk about hard disks with terabytes of space. You can export XML, HTML, SQL (INSERT), DBF, EXCEL, PDF and many other formats. It has autocomplete code, wizards and surprises us by using an add-on to connect HSQLDB (also used in OpenOffice and LibreOffice):
Caption: Orbada with an open instance in HSQLDB
Online documentation, on the other hand, has very few graphic elements and is always focused from a programmer’s point of view, so we do not recommend it for people with little experience. For example, look at an SQL query with regular expression for a macro:
Caption: Orbada with an open instance in HSQLDB
For a change we go back to PHP, in this case completely written in this language and is the smallest of this report; even if you don’t believe it, it doesn’t even reach a megabyte… Do you want to be more surprised? Incorporates a lightweight web server for use, translated into 10 languages, interface inspired by phpMyAdmin, does not need further configuration: place in a folder, run and use with our preferred web browser (or the one that comes). Not everything is so good: it only connects to SQLite versions 2 and 3, it is the only thing that does and does it well.
Caption: phpLiteAdmin, demonstrative https://www.phpliteadmin.org/demo/
We are fascinated by the simplicity of this program; we consider it very suitable for people starting in the field of programming and we even recommend exporting selected data (SQL, CSV) from any BD to SQLite and place in the same folder of phpLiteAdmin. Its main repository rests on Bitbucket.
It is the last to arrive and the first to leave, as we can read on Adminer’s page, who considered it its main competitor (I would rather worry about phpMyAdmin). It was translated into 47 languages and there is more information on GitHub (MIT license) than on its own website. It was discontinued in 2011, very similar to the veteran phpMyAdmin, and we include it here for its peculiar use of Ajax technology and the guaranteed use of the keyboard without moving your mouse at all.
We invite you to see our latest list of BD, which you will be able to manage with the tools we have just presented.
Still want to know more about computer systems monitoring? Luckily, you’re in the right place to learn more. In this blog there are dozens of articles that can introduce you to this exciting world. Here you have a link to our homepage: https://blog.pandorafms.org/
Do you want to know better what Pandora FMS can offer you? Click here: https://pandorafms.com/
If you have more than 100 devices to monitor you can contact the Pandora FMS team through the following form: https://pandorafms.com/contact/
Also, remember that if your monitoring needs are more limited you have at your disposal the OpenSource version of Pandora FMS. Find more information here: https://pandorafms.org/
El equipo de redacción de Pandora FMS está formado por un conjunto de escritores y profesionales de las TI con una cosa en común: su pasión por la monitorización de sistemas informáticos.
Pandora FMS’s editorial team is made up of a group of writers and IT professionals with one thing in common: their passion for computer system monitoring.