Open Source Databases

Friday, November 21, 2025

Learning Structured Query Language (SQL) with open-source software : SQLite and DBeaver

Structured Query Language, or SQL is one of the top-rated computer languages, year after year. But learning its quirky syntax and how to control its power can be a challenge.

Why?

First, you need a database. In most cases, that means setting up a relational database. You need to install the software, get it running, create accounts, and somehow load data before you can use it. This is like wanting to learn how to cook and having to build a stove before trying to make scrambled eggs.

Every database has a command-line interface, or CLI, that dates back to the 1970s and 1980s. Everyone is used to graphic interfaces, and CLIs feel clunky to many.

You may need to follow specific networking and firewall rules. If all you want is a simple way to work with SQL, this is an extremely time and labor-intensive process to surmount.

SQLite

SQLite is a self-contained, lightweight, file-based relational database management system that does not require a separate server to run. It is popular for embedded systems, mobile applications, and small to medium-sized projects because it stores an entire database in a single file, is easy to use, and requires minimal configuration.

DBeaver Community Edition

The DBeaver Community is a free, open-source, cross-platform database tool for working with data. It supports a wide variety of popular SQL databases, such as MySQL, PostgreSQL, and SQLite, and includes features for data manipulation, querying, administration, and basic visualization. It's designed for developers, database administrators, and data analysts and is built on the Java Database Connectivity (JDBC) API.

And it comes with a sample SQLite database. This database can be used to learn SQL!

The DBeaver Community Edition, with the SQLite database, can be downloaded here. Be sure to load the sample database when asked during installation.

Videos

DBeaver has a series of videos on learning SQL. There are excellent introductions to the facets of SQL.

Episodes:

SELECT
INSERT
CREATE TABLE
DELETE
ORDER BY
UPDATE
NULL
How To Get Started With JOINs
Right Joins & Left Joins
Aliases
Self Join
Full Join
Select Distinct
GROUP BY & COUNT
MAX & MIN Aggregate Functions
AVG
SUM

What You Get

When you start DBeaver and click on the Sample Database, you will see a fully functioning SQLite database with tables related to music tracks.

The DBeaver Sample SQLite Database

This collection of data is perfect for learning how to query data. it is easy to join tables together, create reports, and practice queries to build your skills.

And if something terrible happens, it is easy to delete this sample database and reload the entire database (Look under the Help menu).

You now have a platform for learning SQL. You did not have to set up a separate MySQL, PostgreSQL, MariaDB, or SQL Server instance with all the required prerequisite steps. All you have to do is install DBeaver CE on your Windows, Mac, or Linux system.

You can make the queries as straightforward or as complex as needed.

You are not causing contention on a production database server. Everything is self-contained. And you do not need a network connection after the software is installed. Perfect for learning on that long flight you have coming up, so you can make those hours productive.

First Query

Open an SQL Editor and enter your first query.

Hit Ctrl-Enter or the top orange triangle and see the results.

Conclusion

You can easily learn SQL with easy-to-install and use open-source products. Please share your results!

Monday, January 6, 2025

Tracking MySQL Query Plans Part I and Rebooting This Blog

Did you ever need to determine the performance of a query over time? One of the problems with database query optimizations is that the underlying data is always churning. Add in an increase in the number of users, expanding server demand use, and other items that impact your query. What was optimized is no longer performing as expected.

MySQL added a JSON format option to the output of the EXPLAIN command quite a while ago. More recently, Oracle added the feature of saving the output into a variable (see https://dev.mysql.com/doc/refman/9.0/en/mysql-nutshell.html and https://dev.mysql.com/blog-archive/explain-into-and-explain-for-schema-in-mysql-81-and-82). This provides a way to access the data in that variable and plop it into a database. The saved result can be compared to current or other past responses for analysis.

mysql> explain format=json into @var
SELECT * from city where District='Texas';
Query OK, 0 rows affected (0.00 sec)
mysql> select json_pretty(@var)\G
*************************** 1. row ***************************
json_pretty(@var): {
"query_block": {
"table": {
"filtered": "10.00",
"cost_info": {
"eval_cost": "40.46",
"read_cost": "370.39",
"prefix_cost": "410.85",
"data_read_per_join": "97K"
},
"table_name": "city",
"access_type": "ALL",
"used_columns": [
"ID",
"Name",
"CountryCode",
"District",
"Population"
],
"attached_condition": "(`world`.`city`.`District` = 'Texas')",
"rows_examined_per_scan": 4046,
"rows_produced_per_join": 404
},
"cost_info": {
"query_cost": "410.85"
},
"select_id": 1
}
}
1 row in set (0.00 sec)
MySQL>

The detailed items are easy to access.

mysql> SELECT JSON_EXTRACT(@var, "$.query_block.cost_info.query_cost") AS cost;

+----------+

| cost |

+----------+

| "410.85" |

+----------+

1 row in set (0.00 sec)

In Part II, let's gather the various items we want to track to study query performance over time.

Blog Reboot

I am rebooting the blog after a long break. If you have ideas for a post, please let me know.

Thursday, September 1, 2022

PostgreSQL Tutorial - Watching - PostgreSQL for MySQL DBAs - Ep. 10

Thursday, June 30, 2022

The MySQL Track at the Southern California Linux Expo 2022

Yes, SCaLE, or the Southern California Linux Expo is back. Friday is the MySQL track with eight presentations. A big thanks to all the presenters and we hope to see you at the LAX Hilton July 29th!

MySQL 8.0 New Features

Dave Stokes
Audience:Everyone
Topic:MySQL
MySQL now features quarterly releases that come with new features. So if you do not know about hash joins, replica set, JSON document validation, or other new features then you need to be in this presentation.!
Room:
Carmel
Time:
Friday, July 29, 2022 - 10:00 to 11:00

State of MySQL Security in 2022

Alexander Rubin
Audience:Developer
Topic:MySQL
In this talk we will look into the state of MySQL Security in 2022. We will cover the new security focused features available in MySQL (and MySQL Compatible variants) as well as share best practices to consider for secure MySQL deployment.
Room:
Carmel
Time:
Friday, July 29, 2022 - 11:00 to 12:00

Forward to the Past: Point in time recovery for MySQL

Pep Pla
Audience:Intermediate
Topic:MySQL
Disasters happen. Somebody runs a process that renders the data in your database unusable, inconsistent, or just deleted. Wouldn't it be great to be able to recover your database to the state just before the disaster happened?
In this talk we will learn the basics of Point in time recovery for MySQL:
- Disasters happen.
- You need three things.

- I know what you did last transaction: the binary logs.
- I need some backup.
- The quest for the third thing: find the last transaction before the disaster.
- Putting all together the traditional way.
- Make it faster using replication.
- What you can't recover, or at least you can't easily recover: the transactions executed after the disaster.

Room:
Carmel
Time:
Friday, July 29, 2022 - 13:00 to 14:00

Query Optimization 101 in MySQL

Matthew Boehm
Queries are essential to retrieving data from your database. How do you know if your queries are performing well? What is an index, and how does it help access the data? We will answer these questions and many more as we take our first steps into the realm of query optimization.
Room:
Carmel
Time:
Friday, July 29, 2022 - 14:00 to 15:00

I need some (MySQL) backup!

Pep Pla
Audience:Intermediate
Topic:MySQL
"If you have a hammer, all you see is nails." also known as the Law of the Instrument describes the cognitive bias that involves over-reliance on a familiar tool. To protect MySQL data, a DBA must have a toolbox full of the proper utensils. To know only one approach is not enough.
In this talk, we will cover the following topics:
- Why your backup strategy is probably wrong?
- Backups: Cold, hot... and warm?
- The right answer is not always the logical one.
- Replication is not the solution.
- Cold backups: rsync, snapshots...
- Warm backup using rsync.
- The logical song:
- Mysqldump.
- Mydumper.
- Mysqlpump.
- MySQL shell.
- Getting physical:
- Percona XtraBackup: my swiss army knife.
- MySQL Enterprise Backup.
- Point-in-time recovery.
- Don't try this at prod: Test your backups.
Room:
Carmel
Time:
Friday, July 29, 2022 - 15:00 to 16:00

The Great Migration: Galera Cluster to InnoDB Cluster

Michael Marx
Topic:MySQL
Since the introduction of replication in MySQL, users have been trying to automate the promotion of a replica to a primary as well as automating the failover of TCP connections from one database server to another in the event of a database failure: planned or unplanned. For over a decade, users and organizations have designed various types of solutions to achieve this. Though, many of these solutions were problematic. Eventually, a solution was introduced to the community that clustered the MySQL Server. This product is known as Percona XtraDB Cluster and uses a technology called Galera to achieve high availability.
To compete with this product, MySQL eventual introduced cluster for MySQL, known as InnoDB Cluster. This is the official high availability solution for and from MySQL. It utilizes some of the same principles as Percona XtraDB Cluster, but it includes a mysql shell with an API that makes setting up, configuring, and monitor the InnoDB Cluster much easier than Galera. In addition, the InnoDB Cluster includes a proxy called router that allows for automatic failover of TCP connections if a node in the cluster becomes unavailable.
Through this presentation the InnoDB Cluster will be explored in detail as well as simple instructions on how to move from Percona XtraDB Cluster to MySQL InnoDB Cluster.
Room:
Carmel
Time:
Friday, July 29, 2022 - 16:00 to 17:00

The Great Migration: Galera Cluster to InnoDB Cluster

Michael Marx
Topic:MySQL
Since the introduction of replication in MySQL, users have been trying to automate the promotion of a replica to a primary as well as automating the failover of TCP connections from one database server to another in the event of a database failure: planned or unplanned. For over a decade, users and organizations have designed various types of solutions to achieve this. Though, many of these solutions were problematic. Eventually, a solution was introduced to the community that clustered the MySQL Server. This product is known as Percona XtraDB Cluster and uses a technology called Galera to achieve high availability.
To compete with this product, MySQL eventual introduced cluster for MySQL, known as InnoDB Cluster. This is the official high availability solution for and from MySQL. It utilizes some of the same principles as Percona XtraDB Cluster, but it includes a mysql shell with an API that makes setting up, configuring, and monitor the InnoDB Cluster much easier than Galera. In addition, the InnoDB Cluster includes a proxy called router that allows for automatic failover of TCP connections if a node in the cluster becomes unavailable.
Through this presentation the InnoDB Cluster will be explored in detail as well as simple instructions on how to move from Percona XtraDB Cluster to MySQL InnoDB Cluster.
Room:
Carmel
Time:
Friday, July 29, 2022 - 16:00 to 17:00

MySQL Indexes

Dave Stokes
Audience:Everyone
Topic:MySQL
Nobody complains where the database is fast. But adding one index speeds things up. But the second index makes things worse? There is a lot of mythology about indexes in the MySQL area and this talk will show that getting performance out of an index is simple engineering and not magic. You will walk out of this session know how indexes are built, how to use them to provide better information to the query optimizer, and how to make sure that what you are doing is a positive change and not hurting your instance performance.
Room:
Carmel
Time:
Friday, July 29, 2022 - 17:00 to 18:00

Wednesday, May 4, 2022

PostgreSQL \d commands versus MySQL's SHOW commands

One type of databases is pretty much the same as any other type of database base, right?

Well, at one high abstract level that may be true. But at the level where you have to use the database then things can be radically different.

For the past several years I have mainly bee focusing on MySQL but my new job is letting me explore PostgreSQL. I have used PostgreSQL in the past but it has been over a decade since I have done any production work. And as such I am re-learning some basic commands.

When using the MySQL cli or shell I am used to typing SHOW SCHEMAS; or SHOW DATABASES; when I want to know which schemas/databases are on a given server. PostgreSQL's psql uses \l.

There are an entire series of \d commands. A \d by itself shows the tables, sequences, and views in a schema. You use \dt to see the tables, \ds to see the sequences, and \dv to see the views. And \du displays the users.

MySQL uses \g at the end of the command to mark the end of the SQL while in PostgreSQL \g by itself executes the last command.

And you can have that output in an aligned format or unaligned by toggling the \a command or use \H to see the output in HTML.

Friday, April 22, 2022

Percona Live 2022 Tutorials

Looking to get smarter? Well, Percona Live is next month in Austin and there is a full day of tutorials just waiting for you in Austin. And I hope to see you there!