Forem: Mark Drake

How To Manage a Redis Database: A Free eBook

Mark Drake — Fri, 17 Jul 2020 20:37:48 +0000

We at DigitalOcean are excited to share with you a free eBook to support you as you explore Redis. How To Manage a Redis Database is available for download in PDF or EPUB format.

About this Book

Redis is an open-source, in-memory key-value data store known for its flexibility, performance, and broad language support. A NoSQL database, Redis doesn't use structured query language (otherwise known as SQL) to store, manipulate, and retrieve data. Redis instead comes with its own set of commands for managing and accessing data.

The chapters in this book cover a broad range of Redis commands, but they generally focus on connecting to a Redis database, managing a variety of data types, and troubleshooting and debugging problems, along with a few other more specific functions. You're encouraged to jump to whichever chapter is relevant to the task you're trying to complete.

Motivation for this Book

Every available Redis command is covered and thoroughly explained in the official Redis command documentation, as well as in numerous other resources in print and online. However, many resources tend to silo each command off from one another with little tying them together.

This book aims to provide an approachable introduction to Redis concepts by outlining many of the key-value store's commands so readers can learn their patterns and syntax, thus building up readers' understanding gradually. This book covers topics ranging from connecting to a Redis data store, to managing a variety of Redis data types, troubleshooting errors, and more.

Learning Goals and Outcomes

The goal for this book is to serve as an introduction to Redis for those interested in getting started with it, or key-value stores in general. For more experienced users, this book can function as a collection of helpful cheat sheets and in-depth reference. Each chapter is self-contained and can be followed independently of the others. By reading through and following along with each chapter, you'll become acquainted with many of Redis's most widely used commands, which will help you as you begin to build applications that take advantage of its power and speed.

Download the eBook

You can download the eBook in either the EPUB or PDF format by following the links below.

To learn more about the book, check out our book landing page.

The DigitalOcean Community Monthly: Kubernetes Curriculum, Community Tools, a Contest for Student Developers, and More!

Mark Drake — Wed, 04 Dec 2019 16:21:44 +0000

Welcome to The DOCOM Monthly, your monthly digest featuring some of the best content published in and around the DigitalOcean Community over the last month.

I'm Mark Drake, a Technical Writer on DigitalOcean's Developer Education team. This month's roundup includes a new Kubernetes curriculum, some exciting new Community tools, a special contest for student developers, and more!

Here are this month's featured posts:

Kubernetes for Full-Stack Developers

The DigitalOcean Community team launched its first curriculum targeted towards developers. This curriculum contains a selection of DigitalOcean technical tutorials and conceptual articles, curated to help developers learn what Kubernetes is and how to use it. Check out the curriculum or read our announcement blog post.

Imperative vs. Declarative Kubernetes Management: A DigitalOcean Comic

Speaking of Kubernetes, we're excited to share this comic by Daisy Tsang which visualizes two different strategies for deploying infrastructure with Kubernetes:

How To Build an Inspirational Quote Application Using AdonisJs and MySQL

In this tutorial, Oluyemi Olususi outlines how to build applications with AdonisJS, a Node.js web framework written in JavaScript. Having written this guide for the Write for DOnations program, Oluyemi selected the Tech Education Fund to receive a donation.

How To Build and Install Go Programs

Part of our How To Code in Go series, this tutorial from Gopher Guides outlines how to turn Go source code into a binary executable (in other words, build it) and then put it into an executable file path so you can run it from anywhere on your system (or, install it).

New DigitalOcean Community Developer Tools

We have a large collection of tools which help developers build things on DigitalOcean. With over 200 such tools built and maintained by members of the Community, we launched the following open-source tools built internally by DigitalOcean's Community team in November:

DNS Lookup: Returns the DNS records of a given domain
SPF Explainer: Explains a given domain's SPF records
NGINX Config: Helps generate configuration files for performant, secure, and stable NGINX servers
Glob Tool: Tests globs against sets of test strings

You can check out all of our Community tools or even submit your own!

DigitalOcean on the Web

In an interview with PowerToFly, Varsha Varadarajan discusses her experience as both a summer intern and then as a part-time engineering intern at DigitalOcean. She recounts what initially drew her to working at DigitalOcean, how she balances her job with her graduate studies, and what makes DigitalOcean a great place for young engineers to work.

Daniel Zaltsman, DigitalOcean's Senior Developer Relations manager, wrote a retrospective of the sixth annual Hacktoberfest event. Here are some of the highlights:

// Detect dark theme var iframe = document.getElementById('tweet-1194272582507339777-599'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1194272582507339777&theme=dark" }

In this tutorial, DEV user Jan Zavrel explains how to set up a PHP development environment with Docker and Docker Compose, and then deploy an application using GitHub and Digitalocean:

Docker and Docker Compose for PHP Development with GitHub and Digital Ocean Deployment

Jan Zavrel ・ Nov 25 '19

#docker #php #github #digitalocean

In a blog post, Nick Janetakis shares his experiences building a podcasting platform with Jekyll and DigitalOcean.

Community IRL

Last month, DigitalOcean organized and hosted the seventh edition of our TIDE Mumbai confernece. Over 250 people came to learn more about DigitalOcean, listen to talks and panel discussions, attend hands-on technical demonstrations, and network with others.

The DigitalOcean team had a busy week at Kubecon North America. There, we met and chatted with developers, gave a few talks, and announced DigitalOcean's new Container Registry which aims to make it easier for developers to manage container images. Read about this new service, as well as updates to some other Kubernetes-related DigitalOcean products, in our announcement blog post.

Finally, DigitalOcean's Cambridge office hosted an event coordinated by the Boston chapter of Women in Machine Learning and Data Science. The event featured a talk by Francesca Dominici, co-director of the Harvard Data Science Initiative and professor of biostatistics, titled "How Data Science Can Tackle Climate Change." You can watch the full talk here:

That's all for this month. Thanks for reading, and happy coding!

How To Install WordPress with a Managed Database on Ubuntu 18.04

Mark Drake — Tue, 20 Aug 2019 15:02:09 +0000

Introduction

WordPress is the most popular CMS (content management system) on the internet. It's a great choice for getting a website up and running quickly, and after the initial setup, almost all administration can be done through the web frontend.

WordPress is designed to pull content – including posts, comments, user profiles, and other data – from a database backend. As a website grows and must satisfy more and more traffic, it can eventually outgrow its initial database. To resolve this, one can scale up their database by migrating their data to a machine with more RAM or CPU, but this is a tedious process that runs the risk of data loss or corruption. This is why some WordPress developers choose to build their websites on managed databases, which allow users to scale their database automatically with a far lower risk of data loss.

In this guide, we'll focus on setting up a WordPress instance with a managed MySQL database and an Ubuntu 18.04 server. This will require you to install PHP and Apache to serve the content over the web.

Prerequisites

In order to complete this tutorial, you will need:

Access to an Ubuntu 18.04 server: This server should have a non-root sudo-enabled user and a firewall configured. You can set this up by following our Ubuntu 18.04 initial server setup guide.
A managed MySQL database: To provision a Managed Database from DigitalOcean, see our Managed Databases product documentation. Note that this guide will refer to DigitalOcean Managed Databases in examples, but the instructions provided here should generally work for managed databases from other cloud providers.
A LAMP stack installed on your server: In addition to a database, WordPress requires a web server and PHP to function correctly. Setting up a complete LAMP stack (Linux, Apache, MySQL, and PHP) fulfills all of these requirements. Follow this guide to install and configure this software. As you follow this guide, make sure that you set up a virtual host to point to a domain name that you own. Additionally, be sure to skip Step 2, as installing mysql-server on your machine will make your managed database instance redundant.
TLS/SSL security implemented for your site: If you have a domain name, the easiest way to secure your site is with Let's Encrypt, which provides free, trusted certificates. Follow our Let's Encrypt guide for Apache to set this up. Note that this will also require you to obtain a domain name and set up DNS records on your server. Follow this introduction to DigitalOcean DNS for details on how to configure this. Altneratively, if you don't have a domain name, you use a self-signed certificate for your site.

When you are finished with the setup steps, log into your server as your non-root user and continue below.

Step 1 – Adding the MySQL Software Repository and Installing `mysql-client`

In order to configure your managed MySQL instance, you will need to install a client that will allow you to access the database from your server. This step will walk you through the process of installing the mysql-client package.

In many cases, you can just install mysql-client with the apt command, but if you're using the default Ubuntu repositories this will install version 5.7 of the program. In order to access a DigitalOcean Managed MySQL database, you will need to install version 8.0 or above. To do so, you must first add the MySQL software repository before installing the package.

Begin by navigating to the MySQL APT Repository page in your web browser. Find the Download button in the lower-right corner and click through to the next page. This page will prompt you to log in or sign up for an Oracle web account. You can skip that and instead look for the link that says No thanks, just start my download. Right-click the link and select Copy Link Address (this option may be worded differently, depending on your browser).

Now you're ready to download the file. On your server, move to a directory you can write to:

cd /tmp

Download the file using curl, remembering to paste the address you just copied in place of the highlighted portion of the following command. You also need to pass two command line flags to curl. -O instructs curl to output to a file instead of standard output. The L flag makes curl follow HTTP redirects, which is necessary in this case because the address you copied actually redirects to another location before the file downloads:

curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb

The file should now be downloaded in your current directory. List the files to make sure:

ls

You will see the filename listed in the output:

mysql-apt-config_0.8.13-1_all.deb
. . .

Now you can add the MySQL APT repository to your system's repository list. The dpkg command is used to install, remove, and inspect .deb software packages. The following command includes the -i flag, indicating that you'd like to install from the specified file:

sudo dpkg -i mysql-apt-config*

During the installation, you'll be presented with a configuration screen where you can specify which version of MySQL you'd prefer, along with an option to install repositories for other MySQL-related tools. The defaults will add the repository information for the latest stable version of MySQL and nothing else. This is what we want, so use the down arrow to navigate to the Ok menu option and hit ENTER.

Following that, the package will finish adding the repository. Refresh your apt package cache to make the new software packages available:

sudo apt update

Next, you can clean up your system a bit and delete the file you downloaded, as you won't need it in the future:

rm mysql-apt-config*

Note: If you ever need to update the configuration of these repositories, just run the following command to select your new options:

sudo dpkg-reconfigure mysql-apt-config

After selecting your new options, run the following command to refresh your package cache:

sudo apt update

Now that you've added the MySQL repositories, you're ready to install the actual MySQL client software. Do so with the following apt command:

sudo apt install mysql-client

Once that command finishes, check the software version number to ensure that you have the latest release:

mysql --version

mysql  Ver 8.0.17-cluster for Linux on x86_64 (MySQL Community Server - GPL)

You're now able to connect to your managed database and begin preparing it to function with WordPress.

Step 2 – Creating a MySQL Database and User for WordPress

WordPress uses MySQL to manage and store site and user information. Assuming you have completed all the prerequisite tutorials, you will have already provisioned a managed MySQL instance. Here, we'll take the preparatory step of creating a database and a user for WordPress to use.

Most managed database providers provide a uniform resource identifier (URI) used for connecting to the database instance. If you're using a DigitalOcean Managed Database, you can find the relevant connection information in your Cloud Control Panel.

First, click Databases in the left-hand sidebar menu and select the MySQL database you want to use for your WordPress installation. Scroll down to the Connection Details section and copy the link in the host field. Then paste this link into the following command, replacing host_uri with the information you just copied. Likewise, copy the port number in the port field – which will be 25060 on a DigitalOcean Managed Database – and replace port with that number. Additionally, if this is your first time connecting to your managed database and you've not created your own administrative MySQL user, copy the value in the username field and paste it into the command, replacing user:

mysql -u user -p -h host_uri -P port

This command includes the -p flag, which will prompt you for the password of the MySQL user you specified. For a DigitalOcean Managed Database's default doadmin user, you can find this by clicking the show link in the Connection Details section to reveal the password. Copy and paste it into your terminal when prompted.

Note: If you are not using a DigitalOcean Managed Database, your connection options may differ. If that's the case, you should consult your provider's documentation for instructions on connecting third party applications to your database.

From the MySQL prompt, create a new database that WordPress will control. You can call this whatever you would like, but we will use the name wordpress in this guide to keep it simple. Create the database for WordPress by typing:

CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Note: Every MySQL statement must end in a semi-colon (;). Check to make sure this is present if you are running into any issues.

Next, create a new MySQL user account that you will use exclusively to operate on the new database. Creating single-purpose databases and accounts is a good idea from a management and security standpoint. We will use the name wordpressuser in this guide, but feel free to change this if you'd like.

Run the following command, but replace your_server_ip with your Ubuntu server's IP address. Be aware, though, that this will limit wordpressuser to only be able to connect from your LAMP server; if you plan to manage WordPress from your local computer, you should enter that machine's IP address instead. Additionally, choose a strong password for your database user:

CREATE USER 'wordpressuser'@your_server_ip IDENTIFIED WITH mysql_native_password BY 'password';

Note: If you do not know what your server's public IP address is, there are a number of ways you can find it. Usually, this is the address you use to connect to your server through SSH.

One method is to use the curl utility to contact an outside party to tell you how it sees your server. For example, you can use curl to contact an IP-checking tool like ICanHazIP:

curl http://icanhazip.com

This command will return your server's public IP address in your output.

Then grant this user access to the database you just created. Do so by running the following command:

GRANT ALL ON wordpress.* TO 'wordpressuser'@your_server_ip;

You now have a database and user account, each made specifically for WordPress. Go ahead and exit out of MySQL by typing:

exit

That takes care of configuring your managed MySQL database to function with WordPress. In the next step, you will install a few PHP extensions in order to get more functionality out of the CMS.

Step 3 – Installing Additional PHP Extensions

Assuming you followed the prerequisite LAMP stack tutorial, you will have installed a few extensions intended to get PHP to properly communicate with MySQL. WordPress and many of its plugins leverage additional PHP extensions to add additional functionalities.

To download and install some of the more popular PHP extensions for use with WordPress, run the following command:

sudo apt install php-curl php-gd php-mbstring php-xml php-xmlrpc php-soap php-intl php-zip

Note: Each WordPress plugin has its own set of requirements. Some may require you to install additional PHP packages. Check your plugin documentation to see which extensions it requires. If they are available, they can be installed with apt as demonstrated above.

You will restart Apache to load these new extensions in the next section. If you're returning here to install additional plugins, though, you can restart Apache now by typing:

sudo systemctl restart apache2

Otherwise, continue on to Step 4.

Step 4 – Adjusting Apache's Configuration to Allow for .htaccess Overrides and Rewrites

In order for Apache to be able to properly serve your WordPress installation, you must make a few minor adjustments to your Apache configuration.

If you followed the prerequisite tutorials, you should already have a configuration file for your site in the /etc/apache2/sites-available/ directory. We'll use /etc/apache2/sites-available/your_domain.conf as an example here, but you should substitute the path to your configuration file where appropriate.

Additionally, we will use /var/www/your_domain as the root directory in this example WordPress install. You should use the web root specified in your own configuration.

Note: It's possible you are using the 000-default.conf default configuration (with /var/www/html as your web root). This is fine to use if you're only going to host one website on this server. If not, it's best to split the necessary configuration into logical chunks, one file per site.

Currently, the use of .htaccess files is disabled. WordPress and many WordPress plugins use these files extensively for in-directory tweaks to the web server's behavior.

Open the Apache configuration file for your website:

sudo nano /etc/apache2/sites-available/your_domain.conf

To allow .htaccess files, you need to set the AllowOverride directive within a Directory block pointing to your document root. Add the following block of text inside the VirtualHost block in your configuration file, being sure to use the correct web root directory:

<Directory /var/www/your_domain>
    AllowOverride All
</Directory>

When you are finished, save and close the file.

Next, enable mod_rewrite so that you can employ the WordPress permalink feature:

sudo a2enmod rewrite

Before implementing the changes you've just made, check to make sure there aren't any syntax errors in your configuration file:

sudo apache2ctl configtest

The output might have a message that looks like this:

AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1. Set the 'ServerName' directive globally to suppress this message
Syntax OK

If you wish to suppress the top line, just add a ServerName directive to your main (global) Apache configuration file at /etc/apache2/apache2.conf. The ServerName can be your server's domain or IP address. However, this is just a message; it doesn't affect the functionality of your site and as long as the output contains Syntax OK, you're all set to continue.

Restart Apache to implement the changes:

sudo systemctl restart apache2

With that, you're ready to download and set up WordPress itself.

Step 5 – Downloading WordPress

Now that your server software is configured, you can install and configure WordPress. For security reasons, it is always recommended to get the latest version of WordPress from their site.

First, navigate to into a writable directory. /tmp will work for the purposes of this step:

cd /tmp

Then download the compressed release by typing:

curl -O https://wordpress.org/latest.tar.gz

Extract the compressed file to create the WordPress directory structure:

tar xzvf latest.tar.gz

You will move these files into your document root momentarily. Before doing so, add a dummy .htaccess file so that this will be available for WordPress to use later.

Create the file by typing:

touch /tmp/wordpress/.htaccess

Also, copy over the sample configuration file to the filename that WordPress actually reads:

cp /tmp/wordpress/wp-config-sample.php /tmp/wordpress/wp-config.php

Create an upgrade directory, so that WordPress won't run into permissions issues when trying to do this on its own following an update to its software:

mkdir /tmp/wordpress/wp-content/upgrade

Then copy the entire contents of the directory into your document root. The following command uses a period at the end of the source directory to indicate that everything within the directory should be copied, including hidden files (like the .htaccess file you just created):

sudo cp -a /tmp/wordpress/. /var/www/your_domain

That takes care of downloading WordPress onto your server. At this point, though, you still won't be able to access the WordPress setup interface in your browser. To fix that, you'll need to make a few changes to your server's WordPress configuration.

Step 6 – Configuring the WordPress Directory

Before going through the web-based WordPress setup, you need to adjust some items in your WordPress directory. One important configuration change involves setting up reasonable file permissions and ownership.

Start by giving ownership of all the files to the www-data user and group. This is the user that the Apache web server runs as on Debian and Ubuntu systems, and Apache will need to be able to read and write WordPress files in order to serve the website and perform automatic updates.

Update the ownership of your web root directory with chown:

sudo chown -R www-data:www-data /var/www/your_domain

Next run the following two find commands to set the correct permissions on the WordPress directories and files:

sudo find /var/www/your_domain/ -type d -exec chmod 750 {} \;
sudo find /var/www/your_domain/ -type f -exec chmod 640 {} \;

These should be a reasonable permissions set to start with. Be aware, though, that some plugins and procedures might require additional updates.

Now, you need to make some changes to the main WordPress configuration file.

When you open the file, the first order of business will be to replace some secret keys to provide security for your installation. WordPress provides a secure generator for these values so that you do not have to try to come up with good values on your own. These are only used internally, so it won't hurt usability to have complex, secure values here.

To grab secure values from the WordPress secret key generator, run the following command:

curl -s https://api.wordpress.org/secret-key/1.1/salt/

You will get back unique values that look something like this:

Warning! It is important that you request unique values each time. Do NOT copy the values shown here!

define('AUTH_KEY',         '1jl/vqfs<XhdXoAPz9 DO NOT COPY THESE VALUES c_j{iwqD^<+c9.k<J@4H');
define('SECURE_AUTH_KEY',  'E2N-h2]Dcvp+aS/p7X DO NOT COPY THESE VALUES {Ka(f;rv?Pxf})CgLi-3');
define('LOGGED_IN_KEY',    'W(50,{W^,OPB%PB<JF DO NOT COPY THESE VALUES 2;y&,2m%3]R6DUth[;88');
define('NONCE_KEY',        'll,4UC)7ua+8<!4VM+ DO NOT COPY THESE VALUES #`DXF+[$atzM7 o^-C7g');
define('AUTH_SALT',        'koMrurzOA+|L_lG}kf DO NOT COPY THESE VALUES  07VC*Lj*lD&?3w!BT#-');
define('SECURE_AUTH_SALT', 'p32*p,]z%LZ+pAu:VY DO NOT COPY THESE VALUES C-?y+K0DK_+F|0h{!_xY');
define('LOGGED_IN_SALT',   'i^/G2W7!-1H2OQ+t$3 DO NOT COPY THESE VALUES t6**bRVFSD[Hi])-qS`|');
define('NONCE_SALT',       'Q6]U:K?j4L%Z]}h^q7 DO NOT COPY THESE VALUES 1% ^qUswWgn+6&xqHN&%');

These are configuration lines that you can paste directly into your configuration file to set secure keys. Copy the output you received now.

Then, open the WordPress configuration file:

sudo nano /var/www/your_domain/wp-config.php

Find the section that contains the dummy values for those settings. It will look something like this:

. . .

define('AUTH_KEY',         'put your unique phrase here');
define('SECURE_AUTH_KEY',  'put your unique phrase here');
define('LOGGED_IN_KEY',    'put your unique phrase here');
define('NONCE_KEY',        'put your unique phrase here');
define('AUTH_SALT',        'put your unique phrase here');
define('SECURE_AUTH_SALT', 'put your unique phrase here');
define('LOGGED_IN_SALT',   'put your unique phrase here');
define('NONCE_SALT',       'put your unique phrase here');

. . .

Delete those lines and paste in the values you copied from the command line:

. . .

define('AUTH_KEY',         'VALUES COPIED FROM THE COMMAND LINE');
define('SECURE_AUTH_KEY',  'VALUES COPIED FROM THE COMMAND LINE');
define('LOGGED_IN_KEY',    'VALUES COPIED FROM THE COMMAND LINE');
define('NONCE_KEY',        'VALUES COPIED FROM THE COMMAND LINE');
define('AUTH_SALT',        'VALUES COPIED FROM THE COMMAND LINE');
define('SECURE_AUTH_SALT', 'VALUES COPIED FROM THE COMMAND LINE');
define('LOGGED_IN_SALT',   'VALUES COPIED FROM THE COMMAND LINE');
define('NONCE_SALT',       'VALUES COPIED FROM THE COMMAND LINE');

. . .

Next you need to modify some of the database connection settings at the beginning of the file. First, update the 'DB_NAME', 'DB_USER', and 'DB_PASSWORD' fields to point to the database name, database user, and the associated password that you configured within MySQL:

. . .
/** The name of the database for WordPress */
define('DB_NAME', 'wordpress');

/** MySQL database username */
define('DB_USER', 'wordpressuser');

/** MySQL database password */
define('DB_PASSWORD', 'password');

. . .

You will also need to replace localhost in the 'DB_HOST' field with your managed database's host. Additionally, append a colon (:) and your database's port number to the host:

. . .

/** MySQL hostname */
define( 'DB_HOST', 'managed_database_host:managed_database_port' );

. . .

The last change you need to make is to set the method that WordPress will use to write to the filesystem. Since you've already given the web server permission to write where it needs to, you can explicitly set the filesystem method to direct port. Failure to set this with your current settings would result in WordPress prompting for FTP credentials when you perform certain actions.

This setting can be added below the database connection settings, or anywhere else in the file:

. . .

define('FS_METHOD', 'direct');
. . .

Save and close the file when you are finished.

After making those changes, you're all set to finish the process of installing WordPress in your web browser. However, there's one more step that we recommend you complete to add an extra layer of security to your configuration.

Step 7 – (Recommended) Configuring WordPress to Communicate with MySQL Over TLS/SSL

At this point, your WordPress installation is communicating with your managed MySQL database. However, there's no guarantee that data transfers between the two machines are secure. In this step, we will configure WordPress to communicate with your MySQL instance over a TLS/SSL connection to ensure secure communications between the two machines.

To do so, you'll need your managed database's CA certificate. For a DigitalOcean Managed Database, you can find this by once again navigating to the Databases tab in your Control Panel. Click on your database, and find the Connection Details section. There will be a button there that reads Download the CA certificate. Click this button to download the certificate to your local machine.

Then transfer this file to your WordPress server. If your local machine is running Linux or macOS, you can use a tool like scp:

scp /path/to/file/ca-certificate.crt sammy@your_server_ip:/tmp

If your local machine is running Windows, you can use an alternative tool like WinSCP.

Once the CA certificate is on your server, move it to the /user/local/share/ca-certificates/ directory, Ubuntu's trusted certificate store:

sudo mv /tmp/ca-certificate.crt /usr/local/share/ca-certificates/

Following this, run the update-ca-certificates command. This program looks for certificates within /usr/local/share/ca-certificates, adds any new ones to the /etc/ssl/certs/ directory, and generates a list of trusted SSL certificates based on its contents:

sudo update-ca-certificates

Then, reopen your wp-config.php file:

nano /var/www/your_domain/wp-config.php

Somewhere in the file, add the following line:

. . .
define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);
. . .

Save and close the file.

Following that, WordPress will securely communicate with your managed MySQL database.

Step 8 – Completing the Installation Through the Web Interface

Now that the server configuration is complete, you can complete the installation through the WordPress web interface.

In your web browser, navigate to your server's domain name or public IP address:

https://server_domain_or_IP

Assuming there aren't any errors in your WordPress or Apache configurations, you'll see the WordPress language selection splash page. Select the language you would like to use:

After selecting your language, you will see the main setup page.

Select a name for your WordPress site and choose a username (it is recommended not to choose something like "admin" for security purposes). A strong password is generated automatically. Save this password or enter an alternative strong password.

Enter your email address and select whether you want to discourage search engines from indexing your site:

When you click ahead, you will be taken to a page that prompts you to log in:

Once you log in, you will be taken to the WordPress administration dashboard:

From here, you can begin customizing your new WordPress site and start publishing content. If this is your first time using WordPress, we encourage you to explore the interface a bit to get acquainted with your new CMS.

Warning: Depending on your managed database provider, you may need to adjust which SQL modes the database is using. SQL modes define how MySQL validates data and the syntax it supports, and certain SQL modes can negatively impact WordPress's performance. For instance, the default SQL modes on DigitalOcean Managed MySQL Databases enable MySQL's strict mode which can cause errors in a WordPress database.

There are a number of SQL modes that can affect WordPress's performance, as well as a number of ways to disable strict mode, so you should review the official MySQL documentation on the subject to determine which settings are best suited to your needs. If you're using a DigitalOcean Managed Database, see our Managed MySQL product documentation for details on how to change the default SQL mode settings.

Conclusion

By completing this guide, you will have WordPress installed and ready to use on your server. Additionally, your WordPress installation is dynamically pulling posts, pages, and other content from your managed MySQL database.

Some common next steps are to choose the permalinks setting for your posts. This setting can be found under Settings > Permalinks. You could also select a new theme in Appearance > Themes. Once you start loading some content into your site, you could also configure a CDN to speed up your site's asset delivery.

The DigitalOcean Community Weekly: Debian 10, Galera, Hacktoberfest, and more!

Mark Drake — Fri, 12 Jul 2019 14:15:19 +0000

Welcome to The DOCOM Weekly, your weekly roundup of some of the best content published in and around the DigitalOcean Community this past week.

Hello there, I'm Mark, a Technical Writer on DigitalOcean's Developer Education team. Today's digest includes our rundown of new features in the latest Debian release, a tutorial on how to build a highly-available database cluster, an exciting talk on Hacktoberfest, and more!

Here are this week's featured posts from the DigitalOcean Community:

What's New in Debian 10 Buster

Debian 10 (also known by its codename, "Buster") was released this past weekend. In this post, Brian Boucheron goes over some of the changes that come with this new version.

We're continuing to publish tutorials for this release, but you can find all of our latest Debian 10 content here.

How to Set Up a Prometheus, Grafana and Alertmanager Monitoring Stack on DigitalOcean Kubernetes

This latest tutorial from Hanif Jetha shows how to deploy a monitoring stack consisting of Prometheus, Grafana, and Alertmanager on Kubernetes.

How To Configure a Galera Cluster with MariaDB on CentOS 7 Servers

This tutorial was written by bsder, one of our wonderful Community authors, as part of our Write for DOnations program. It describes how to set up a database clustering solution with Galera and MariaDB, giving you a highly-available database with synchronous replication.

If you'd like to contribute high-quality technical content to the DigitalOcean Community, get paid for your efforts, and designate a tech-related charity to receive a matching donation, apply to write for us here.

Featured Q&A

Here are some of our favorite questions and answers that have been posted to our Community in the past week:

You, too, can get involved in the Community Q&A. Ask questions about challenges you encounter and share your expertise with other members!

Community IRL

This week, DigitalOcean hosted a Pride celebration event titled Looking Back, Loving Forward. A panel of LGBTQ+ technologists visited us at our headquarters in NYC and led an insightful discussion on issues facing the LGBTQ+ tech community.

The DigitalOcean Startup team was at RISEConf in Hong Kong last week. There, they met with hundreds of developers and entrepreneurs to talk about the cloud, passed out some cool Sammy swag, and also led a few talks. Stay tuned for more news about what they were up to in Hong Kong.

// Detect dark theme var iframe = document.getElementById('tweet-1148751194225106945-155'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1148751194225106945&theme=dark" }

Daniel Zaltsman — Senior Developer Relations Manager at DigitalOcean — was recently in San Francisco attending DevRelCon. There, he gave a talk on What You Can Learn From Hacktoberfest, an annual program that opens up opportunities for both aspiring and experienced developers to contribute to open-source projects. Check out the video here:

Lastly, DigitalOcean's Director of Developer Relations, Tim Falls, was interviewed for InfoQ's Engineering Culture podcast. He and host Shane Hastie discussed developer relations, the importance of open-source software, the need for ethics in education, and more. Listen here:

We'll be back with another roundup next week. Until then, thanks for reading and Happy Hacking!

Understanding Managed Databases

Mark Drake — Mon, 29 Apr 2019 13:53:29 +0000

Introduction

Secure, reliable data storage is a must for nearly every modern application. However, the infrastructure needed for a self-managed, on-premises database can be prohibitively expensive for many teams. Similarly, employees who have the skills and experience needed to maintain a production database effectively can be difficult to come by.

The spread of cloud computing services has lowered the barriers to entry associated with provisioning a database, but many developers still lack the time or expertise needed to manage and tune a database to suit their needs. For this reason, many businesses are turning to managed database services to help them build and scale their databases in line with their growth.

In this conceptual article, we will go over what managed databases are and how they can be beneficial to many organizations. We will also cover some practical considerations one should make before building their next application on top of a managed database solution.

Managed Databases in a Nutshell

A managed database is a cloud computing service in which the end user pays a cloud service provider for access to a database. Unlike a typical database, users don't have to set up or maintain a managed database on their own; rather, it's the provider's responsibility to oversee the database's infrastructure. This allows the user to focus on building their application instead of spending time configuring their database and keeping it up to date.

The process of provisioning a managed database varies by provider, but in general it's similar to that of any other cloud-based service. After registering an account and logging in to the dashboard, the user reviews the available database options — such as the database engine and cluster size — and then chooses the setup that's right for them. After you provision the managed database, you can connect to it through a GUI or client and can then begin loading data and and integrating the database with your application.

Managed data solutions simplify the process of provisioning and maintaining a database. Instead of running commands from a terminal to install and set one up, you can deploy a production-ready database with just a few clicks in your browser. By simplifying and automating database management, cloud providers make it easier for anyone, even novice database users, to build data-driven applications and websites. This was the result of a decades-long trend towards simplifying, automating, and abstracting various database management tasks, which was itself a response to pain points long felt by database administrators.

Pain Points of On-Premises and Self-Managed Databases

Prior to the rise of the cloud computing model, any organization in need of a data center had to supply all the time, space, and resources that went into setting one up. Once their database was up and running, they also had to maintain the hardware, keep its software updated, hire a team to manage the database, and train their employees on how to use it.

As cloud computing services grew in popularity in the 2000s, it became easier and more affordable to provision server infrastructure, since the hardware and the space required for it no longer had to be owned or managed by those using it. Likewise, setting up a database entirely within the cloud became far less difficult; a business or developer would just have to requisition a server, install and configure their chosen database management system, and begin storing data.

While cloud computing did make the process of setting up a traditional database easier, it didn't address all of its problems. For instance, in the cloud it can still be difficult to pinpoint the ideal size of a database's infrastructure footprint before it begins collecting data. This is important because cloud consumers are charged based on the resources they consume, and they risk paying for more than what they require if the server they provision is larger than necessary. Additionally, as with traditional on-premises databases, managing one's database in the cloud can be a costly endeavor. Depending on your needs, you may still need to hire an experienced database administrator or spend a significant amount of time and money training your existing staff to manage your database effectively.

Many of these issues are compounded for smaller organizations and independent developers. While a large business can usually afford to hire employees with a deep knowledge of databases, smaller teams usually have fewer resources available, leaving them with only their existing institutional knowledge. This makes tasks like replication, migrations, and backups all the more difficult and time consuming, as they can require a great deal of on-the-job learning as well as trial and error.

Managed databases help to resolve these pain points with a host of benefits to businesses and developers. Let's walk through some of these benefits and how they can impact development teams.

Benefits of Managed Databases

Managed database services can help to reduce many of the headaches associated with provisioning and managing a database. For one thing, developers build applications on top of managed database services to drastically speed up the process of provisioning a database server. With a self-managed solution, you must obtain a server (either on-premises or in the cloud), connect to it from a client or terminal, configure and secure it, and then install and set up the database management software before you can begin storing data. With a managed database, you only have to decide on the initial size of the database server, configure any additional provider-specific options, and you'll have a new database ready to integrate with your app or website. This can usually be done in just a few minutes through the provider's user interface.

Another appeal of managed databases is automation. Self-managed databases can consume a large amount of an organization's resources because its employees have to perform every administrative task — from scaling to performing updates, running migrations, and creating backups — manually. With a managed database, however, these and other tasks are done either automatically or on-demand, which markedly reduces the risk of human error.

This relates to the fact that managed database services help to streamline the process of database scaling. Scaling a self-managed database can be very time- and resource-intensive. Whether you choose sharding, replication, load balancing, or something else as your scaling strategy, if you manage the infrastructure yourself then you're responsible for ensuring that no data is lost in the process and that the application will continue to work properly. If you integrate your application with a managed database service, however, you can scale the database cluster on demand. Rather than having to work out the optimal server size or CPU usage beforehand, you can quickly provision more resources on-the-fly. This helps you avoid using unnecessary resources, meaning you also won't pay for what you don't need.

Managed solutions tend to have built-in high-availability. In the context of cloud computing, a service is said to be highly available if it is stable and likely to run without failure for long periods of time. Most reputable cloud providers' products come with a service level agreement (SLA), a commitment between the provider and its customers that guarantees the availability and reliability of their services. A typical SLA will specify how much downtime the customer should expect, and many also define the compensation for customers if these service levels are not met. This provides assurance for the customer that their database won't crash and, if it does, they can at least expect some kind of reparation from the provider.

In general, managed databases simplify the tasks associated with provisioning and maintaining a database. Depending on the provider, you or your team will still likely need some level of experience working with databases in order to provision a database and interact with it as you build and scale your application. Ultimately, though, the database-specific experience needed to administer a managed database will be much less than with self-managed solution.

Of course, managed databases aren't able to solve every problem, and may prove to be a less-than-ideal choice for some. Next, we'll go over a few of the potential drawbacks one should consider before provisioning a managed database.

Practical Considerations

A managed database service can ease the stress of deploying and maintaining a database, but there are still a few things to keep in mind before committing to one. Recall that a principal draw of managed databases is that they abstract away most of the more tedious aspects of database administration. To this end, a managed database provider aims to deliver a rudimentary database that will satisfy the most common use cases. Accordingly, their database offerings won't feature tons of customization options or the unique features included in more specialized database software. Because of this, you won't have as much freedom to tailor your database and you'll be limited to what the cloud provider has to offer.

A managed database is almost always more expensive than a self-managed one. This makes sense, since you're paying for the cloud provider to support you in managing the database, but it can be a cause for concern for teams with limited resources. Moreover, pricing for managed databases is usually based on how much storage and RAM the database uses, how many reads it handles, and how many backups of the database the user creates. Likewise, any application using a managed database service that handle large amounts of data or traffic will be more expensive than if it were to use a self-managed cloud database.

One should also reflect on the impact switching to a managed database will have on their internal workflows and whether or not they'll be able to adjust to those changes. Every provider differs, and depending on their SLA they may shoulder responsibility for only some administration tasks, which would be problematic for developers looking for a full-service solution. On the other hand, some providers could have a prohibitively restrictive SLA or make the customer entirely dependent on the provider in question, a situation known as vendor lock-in.

Lastly, and perhaps most importantly, one should carefully consider whether or not any managed database service they're considering using will meet their security needs. All databases, including on-premises databases, are prone to certain security threats, like SQL injection attacks or data leaks. However, the security dynamic is far different for databases hosted in the cloud. Managed database users can't control the physical location of their data or who has access to it, nor can they ensure compliance with specific security standards. This can be especially problematic if your client has heightened security needs.

To illustrate, imagine that you're hired by a bank to build an application where its clients can access financial records and make payments. The bank may stipulate that the app must have data at rest encryption and appropriately scoped user permissions, and that it must be compliant with certain regulatory standards like PCI DSS. Not all managed database providers adhere to the same regulatory standards or maintain the same security practices, and they're unlikely to adopt new standards or practices for just one of their customers. For this reason, it's critical that you ensure any managed database provider you rely on for such an application is able to meet your security needs as well as the needs of your clients.

Conclusion

Managed databases have many features that appeal to a wide variety of businesses and developers, but a managed database may not solve every problem or suit everyone's needs. Some may find that a managed database's limited feature set and configuration options, increased cost, and reduced flexibility outweigh any of its potential advantages. However, compelling benefits like ease of use, scalability, automated backups and upgrades, and high availability have led to increased adoption of managed database solutions in a variety of industries.

If you're interested in learning more about DigitalOcean Managed Databases, we encourage you to check out our Managed Databases product documentation.

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License

An Introduction to Queries in PostgreSQL

Mark Drake — Thu, 21 Feb 2019 23:47:47 +0000

Introduction

Databases are a key component of many websites and applications, and are at the core of how data is stored and exchanged across the internet. One of the most important aspects of database management is the practice of retrieving data from a database, whether it's on an ad hoc basis or part of a process that's been coded into an application. There are several ways to retrieve information from a database, but one of the most commonly-used methods is performed through submitting queries through the command line.

In relational database management systems, a query is any command used to retrieve data from a table. In Structured Query Language (SQL), queries are almost always made using the SELECT statement.

In this guide, we will discuss the basic syntax of SQL queries as well as some of the more commonly-employed functions and operators. We will also practice making SQL queries using some sample data in a PostgreSQL database.

PostgreSQL, often shortened to "Postgres," is a relational database management system with an object-oriented approach, meaning that information can be represented as objects or classes in PostgreSQL schemas. PostgreSQL aligns closely with standard SQL, although it also includes some features not found in other relational database systems.

Prerequisites

In general, the commands and concepts presented in this guide can be used on any Linux-based operating system running any SQL database software. However, it was written specifically with an Ubuntu 18.04 server running PostgreSQL in mind. To set this up, you will need the following:

An Ubuntu 18.04 machine with a non-root user with sudo privileges. This can be set up using our Initial Server Setup guide for Ubuntu 18.04.
PostgreSQL installed on the machine. For help with setting this up, follow the "Installing PostgreSQL" section of our guide on How To Install and Use PostgreSQL on Ubuntu 18.04.

With this setup in place, we can begin the tutorial.

Creating a Sample Database

Before we can begin making queries in SQL, we will first create a database and a couple tables, then populate these tables with some sample data. This will allow you to gain some hands-on experience when you begin making queries later on.

For the sample database we'll use throughout this guide, imagine the following scenario:

You and several of your friends all celebrate your birthdays with one another. On each occasion, the members of the group head to the local bowling alley, participate in a friendly tournament, and then everyone heads to your place where you prepare the birthday-person's favorite meal.

Now that this tradition has been going on for a while, you've decided to begin tracking the records from these tournaments. Also, to make planning dinners easier, you decide to create a record of your friends' birthdays and their favorite entrees, sides, and desserts. Rather than keep this information in a physical ledger, you decide to exercise your database skills by recording it in a PostgreSQL database.

To begin, open up a PostgreSQL prompt as your postgres superuser:

sudo -u postgres psql

Note: If you followed all the steps of the prerequisite tutorial on Installing PostgreSQL on Ubuntu 18.04, you may have configured a new role for your PostgreSQL installation. In this case, you can connect to the Postgres prompt with the following command, substituting sammy with your own username:

sudo -u sammy psql

Next, create the database by running:

CREATE DATABASE birthdays;

Then select this database by typing:

\c birthdays

Next, create two tables within this database. We'll use the first table to track your friends' records at the bowling alley. The following command will create a table called tourneys with columns for the name of each of your friends, the number of tournaments they've won (wins), their all-time best score, and what size bowling shoe they wear (size):

CREATE TABLE tourneys ( 
name varchar(30), 
wins real, 
best real, 
size real 
);

Once you run the CREATE TABLE command and populate it with column headings, you’ll receive the following output:

Output
CREATE TABLE

Populate the tourneys table with some sample data:

INSERT INTO tourneys (name, wins, best, size) 
VALUES ('Dolly', '7', '245', '8.5'), 
('Etta', '4', '283', '9'), 
('Irma', '9', '266', '7'), 
('Barbara', '2', '197', '7.5'), 
('Gladys', '13', '273', '8');

You’ll receive the following output:

Output
INSERT 0 5

Following this, create another table within the same database which we'll use to store information about your friends' favorite birthday meals. The following command creates a table named dinners with columns for the name of each of your friends, their birthdate, their favorite entree, their preferred side dish, and their favorite dessert:

CREATE TABLE dinners ( 
name varchar(30), 
birthdate date, 
entree varchar(30), 
side varchar(30), 
dessert varchar(30) 
);

Similarly for this table, you’ll receive feedback verifying that the table was created:

Output
CREATE TABLE

Populate this table with some sample data as well:

INSERT INTO dinners (name, birthdate, entree, side, dessert) 
VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'), 
('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'), 
('Irma', '1941-02-18', 'tofu', 'fries', 'cake'), 
('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'), 
('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream');

Output
INSERT 0 5

Once that command completes successfully, you're done setting up your database. Next, we'll go over the basic command structure of SELECT queries.

Understanding SELECT Statements

As mentioned in the introduction, SQL queries almost always begin with the SELECT statement. SELECT is used in queries to specify which columns from a table should be returned in the result set. Queries also almost always include FROM, which is used to specify which table the statement will query.

Generally, SQL queries follow this syntax:

SELECT column_to_select FROM table_to_select WHERE certain_conditions_apply;

By way of example, the following statement will return the entire name column from the dinners table:

SELECT name FROM dinners;

Output
  name   
---------
 Dolly
 Etta
 Irma
 Barbara
 Gladys
(5 rows)

You can select multiple columns from the same table by separating their names with a comma, like this:

SELECT name, birthdate FROM dinners;

Output
  name   | birthdate  
---------+------------
 Dolly   | 1946-01-19
 Etta    | 1938-01-25
 Irma    | 1941-02-18
 Barbara | 1948-12-25
 Gladys  | 1944-05-28
(5 rows)

Instead of naming a specific column or set of columns, you can follow the SELECT operator with an asterisk (*) which serves as a placeholder representing all the columns in a table. The following command returns every column from the tourneys table:

SELECT * FROM tourneys;

Output
  name   | wins | best | size 
---------+------+------+------
 Dolly   |    7 |  245 |  8.5
 Etta    |    4 |  283 |    9
 Irma    |    9 |  266 |    7
 Barbara |    2 |  197 |  7.5
 Gladys  |   13 |  273 |    8
(5 rows)

WHERE is used in queries to filter records that meet a specified condition, and any rows that do not meet that condition are eliminated from the result. A WHERE clause typically follows this syntax:

. . . WHERE column_name comparison_operator value

The comparison operator in a WHERE clause defines how the specified column should be compared against the value. Here are some common SQL comparison operators:

Operator	What it does
`=`	tests for equality
`!=`	tests for inequality
`<`	tests for less-than
`>`	tests for greater-than
`<=`	tests for less-than or equal-to
`>=`	tests for greater-than or equal-to
`BETWEEN`	tests whether a value lies within a given range
`IN`	tests whether a row's value is contained in a set of specified values
`EXISTS`	tests whether rows exist, given the specified conditions
`LIKE`	tests whether a value matches a specified string
`IS NULL`	tests for `NULL` values
`IS NOT NULL`	tests for all values other than `NULL`

For example, if you wanted to find Irma's shoe size, you could use the following query:

SELECT size FROM tourneys WHERE name = 'Irma';

Output
 size 
------
    7
(1 row)

SQL allows the use of wildcard characters, and these are especially handy when used in WHERE clauses. Percentage signs (%) represent zero or more unknown characters, and underscores (_) represent a single unknown character. These are useful if you're trying to find a specific entry in a table, but aren't sure of what that entry is exactly. To illustrate, let's say that you've forgotten the favorite entree of a few of your friends, but you're certain this particular entree starts with a "t." You could find its name by running the following query:

SELECT entree FROM dinners WHERE entree LIKE 't%';

Output
 entree  
-------
 tofu
 tofu
(2 rows)

Based on the output above, we see that the entree we have forgotten is tofu.

There may be times when you're working with databases that have columns or tables with relatively long or difficult-to-read names. In these cases, you can make these names more readable by creating an alias with the AS keyword. Aliases created with AS are temporary, and only exist for the duration of the query for which they're created:

SELECT name AS n, birthdate AS b, dessert AS d FROM dinners;

Output
    n    |     b      |     d     
---------+------------+-----------
 Dolly   | 1946-01-19 | cake
 Etta    | 1938-01-25 | ice cream
 Irma    | 1941-02-18 | cake
 Barbara | 1948-12-25 | ice cream
 Gladys  | 1944-05-28 | ice cream
(5 rows)

Here, we have told SQL to display the name column as n, the birthdate column as b, and the dessert column as d.

The examples we've gone through up to this point include some of the more frequently-used keywords and clauses in SQL queries. These are useful for basic queries, but they aren't helpful if you're trying to perform a calculation or derive a scalar value (a single value, as opposed to a set of multiple different values) based on your data. This is where aggregate functions come into play.

Aggregate Functions

Oftentimes, when working with data, you don't necessarily want to see the data itself. Rather, you want information about the data. The SQL syntax includes a number of functions that allow you to interpret or run calculations on your data just by issuing a SELECT query. These are known as aggregate functions.

The COUNT function counts and returns the number of rows that match a certain criteria. For example, if you'd like to know how many of your friends prefer tofu for their birthday entree, you could issue this query:

SELECT COUNT(entree) FROM dinners WHERE entree = 'tofu';

Output
 count 
-------
     2
(1 row)

The AVG function returns the average (mean) value of a column. Using our example table, you could find the average best score amongst your friends with this query:

SELECT AVG(best) FROM tourneys;

Output  
 avg  
-------
 252.8
(1 row)

SUM is used to find the total sum of a given column. For instance, if you'd like to see how many games you and your friends have bowled over the years, you could run this query:

SELECT SUM(wins) FROM tourneys;

Output 
 sum 
-----
  35
(1 row)

Note that the AVG and SUM functions will only work correctly when used with numeric data. If you try to use them on non-numerical data, it will result in either an error or just 0, depending on which RDBMS you're using:

SELECT SUM(entree) FROM dinners;

Output
ERROR:  function sum(character varying) does not exist
LINE 1: select sum(entree) from dinners;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

MIN is used to find the smallest value within a specified column. You could use this query to see what the worst overall bowling record is so far (in terms of number of wins):

SELECT MIN(wins) FROM tourneys;

Output 
 min 
-----
   2
(1 row)

Similarly, MAX is used to find the largest numeric value in a given column. The following query will show the best overall bowling record:

SELECT MAX(wins) FROM tourneys;

Output
 max 
-----
  13
(1 row)

Unlike SUM and AVG, the MIN and MAX functions can be used for both numeric and alphabetic data types. When run on a column containing string values, the MIN function will show the first value alphabetically:

SELECT MIN(name) FROM dinners;

Output
   min   
---------
 Barbara
(1 row)

Likewise, when run on a column containing string values, the MAX function will show the last value alphabetically:

SELECT MAX(name) FROM dinners;

Output
 max  
------
 Irma
(1 row)

Aggregate functions have many uses beyond what was described in this section. They're particularly useful when used with the GROUP BY clause, which is covered in the next section along with several other query clauses that affect how result sets are sorted.

Manipulating Query Outputs

In addition to the FROM and WHERE clauses, there are several other clauses which are used to manipulate the results of a SELECT query. In this section, we will explain and provide examples for some of the more commonly-used query clauses.

One of the most frequently-used query clauses, aside from FROM and WHERE, is the GROUP BY clause. It's typically used when you're performing an aggregate function on one column, but in relation to matching values in another.

For example, let's say you wanted to know how many of your friends prefer each of the three entrees you make. You could find this info with the following query:

SELECT COUNT(name), entree FROM dinners GROUP BY entree;

Output
 count | entree  
-------+---------
     1 | chicken
     2 | steak
     2 | tofu
(3 rows)

The ORDER BY clause is used to sort query results. By default, numeric values are sorted in ascending order, and text values are sorted in alphabetical order. To illustrate, the following query lists the name and birthdate columns, but sorts the results by birthdate:

SELECT name, birthdate FROM dinners ORDER BY birthdate;

Output
  name   | birthdate  
---------+------------
 Etta    | 1938-01-25
 Irma    | 1941-02-18
 Gladys  | 1944-05-28
 Dolly   | 1946-01-19
 Barbara | 1948-12-25
(5 rows)

Notice that the default behavior of ORDER BY is to sort the result set in ascending order. To reverse this and have the result set sorted in descending order, close the query with DESC:

SELECT name, birthdate FROM dinners ORDER BY birthdate DESC;

Output
  name   | birthdate  
---------+------------
 Barbara | 1948-12-25
 Dolly   | 1946-01-19
 Gladys  | 1944-05-28
 Irma    | 1941-02-18
 Etta    | 1938-01-25
(5 rows)

As mentioned previously, the WHERE clause is used to filter results based on specific conditions. However, if you use the WHERE clause with an aggregate function, it will return an error, as is the case with the following attempt to find which sides are the favorite of at least three of your friends:

SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3;

Output
ERROR:  aggregate functions are not allowed in WHERE
LINE 1: SELECT COUNT(name), side FROM dinners WHERE COUNT(name) >= 3...

The HAVING clause was added to SQL to provide functionality similar to that of the WHERE clause while also being compatible with aggregate functions. It's helpful to think of the difference between these two clauses as being that WHERE applies to individual records, while HAVING applies to group records. To this end, any time you issue a HAVING clause, the GROUP BY clause must also be present.

The following example is another attempt to find which side dishes are the favorite of at least three of your friends, although this one will return a result without error:

SELECT COUNT(name), side FROM dinners GROUP BY side HAVING COUNT(name) >= 3;

Output
 count | side  
-------+-------
     3 | fries
(1 row)

Aggregate functions are useful for summarizing the results of a particular column in a given table. However, there are many cases where it's necessary to query the contents of more than one table. We'll go over a few ways you can do this in the next section.

Querying Multiple Tables

More often than not, a database contains multiple tables, each holding different sets of data. SQL provides a few different ways to run a single query on multiple tables.

The JOIN clause can be used to combine rows from two or more tables in a query result. It does this by finding a related column between the tables and sorts the results appropriately in the output.

SELECT statements that include a JOIN clause generally follow this syntax:

SELECT table1.column1, table2.column2
FROM table1
JOIN table2 ON table1.related_column=table2.related_column;

Note that because JOIN clauses compare the contents of more than one table, the previous example specifies which table to select each column from by preceding the name of the column with the name of the table and a period. You can specify which table a column should be selected from like this for any query, although it's not necessary when selecting from a single table, as we've done in the previous sections. Let's walk through an example using our sample data.

Imagine that you wanted to buy each of your friends a pair of bowling shoes as a birthday gift. Because the information about your friends' birthdates and shoe sizes are held in separate tables, you could query both tables separately then compare the results from each. With a JOIN clause, though, you can find all the information you want with a single query:

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
JOIN dinners ON tourneys.name=dinners.name;

Output
  name   | size | birthdate  
---------+------+------------
 Dolly   |  8.5 | 1946-01-19
 Etta    |    9 | 1938-01-25
 Irma    |    7 | 1941-02-18
 Barbara |  7.5 | 1948-12-25
 Gladys  |    8 | 1944-05-28
(5 rows)

The JOIN clause used in this example, without any other arguments, is an inner JOIN clause. This means that it selects all the records that have matching values in both tables and prints them to the results set, while any records that aren't matched are excluded. To illustrate this idea, let's add a new row to each table that doesn't have a corresponding entry in the other:

INSERT INTO tourneys (name, wins, best, size) 
VALUES ('Bettye', '0', '193', '9');

INSERT INTO dinners (name, birthdate, entree, side, dessert) 
VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream');

Then, re-run the previous SELECT statement with the JOIN clause:

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
JOIN dinners ON tourneys.name=dinners.name;

Output
  name   | size | birthdate  
---------+------+------------
 Dolly   |  8.5 | 1946-01-19
 Etta    |    9 | 1938-01-25
 Irma    |    7 | 1941-02-18
 Barbara |  7.5 | 1948-12-25
 Gladys  |    8 | 1944-05-28
(5 rows)

Notice that, because the tourneys table has no entry for Lesley and the dinners table has no entry for Bettye, those records are absent from this output.

It is possible, though, to return all the records from one of the tables using an outer JOIN clause. Outer JOIN clauses are written as either LEFT JOIN, RIGHT JOIN, or FULL JOIN.

A LEFT JOIN clause returns all the records from the “left” table and only the matching records from the right table. In the context of outer joins, the left table is the one referenced by the FROM clause, and the right table is any other table referenced after the JOIN statement.

Run the previous query again, but this time use a LEFT JOIN clause:

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
LEFT JOIN dinners ON tourneys.name=dinners.name;

This command will return every record from the left table (in this case, tourneys) even if it doesn't have a corresponding record in the right table. Any time there isn't a matching record from the right table, it's returned as a blank value or NULL, depending on your RDBMS:

Output
  name   | size | birthdate  
---------+------+------------
 Dolly   |  8.5 | 1946-01-19
 Etta    |    9 | 1938-01-25
 Irma    |    7 | 1941-02-18
 Barbara |  7.5 | 1948-12-25
 Gladys  |    8 | 1944-05-28
 Bettye  |    9 | 
(6 rows)

Now run the query again, this time with a RIGHT JOIN clause:

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
RIGHT JOIN dinners ON tourneys.name=dinners.name;

This will return all the records from the right table (dinners). Because Lesley's birthdate is recorded in the right table, but there is no corresponding row for her in the left table, the name and size columns will return as blank values in that row:

Output
  name   | size | birthdate  
---------+------+------------
 Dolly   |  8.5 | 1946-01-19
 Etta    |    9 | 1938-01-25
 Irma    |    7 | 1941-02-18
 Barbara |  7.5 | 1948-12-25
 Gladys  |    8 | 1944-05-28
         |      | 1946-05-02
(6 rows)

Note that left and right joins can be written as LEFT OUTER JOIN or RIGHT OUTER JOIN, although the OUTER part of the clause is implied. Likewise, specifying INNER JOIN will produce the same result as just writing JOIN.

There is a fourth join clause called FULL JOIN available for some RDBMS distributions, including PostgreSQL. A FULL JOIN will return all the records from each table, including any null values:

SELECT tourneys.name, tourneys.size, dinners.birthdate 
FROM tourneys 
FULL JOIN dinners ON tourneys.name=dinners.name;

Output
  name   | size | birthdate  
---------+------+------------
 Dolly   |  8.5 | 1946-01-19
 Etta    |    9 | 1938-01-25
 Irma    |    7 | 1941-02-18
 Barbara |  7.5 | 1948-12-25
 Gladys  |    8 | 1944-05-28
 Bettye  |    9 | 
         |      | 1946-05-02
(7 rows)

Note: As of this writing, the FULL JOIN clause is not supported by either MySQL or MariaDB.

As an alternative to using FULL JOIN to query all the records from multiple tables, you can use the UNION clause.

The UNION operator works slightly differently than a JOIN clause: instead of printing results from multiple tables as unique columns using a single SELECT statement, UNION combines the results of two SELECT statements into a single column.

To illustrate, run the following query:

SELECT name FROM tourneys UNION SELECT name FROM dinners;

This query will remove any duplicate entries, which is the default behavior of the UNION operator:

Output
  name   
---------
 Irma
 Etta
 Bettye
 Gladys
 Barbara
 Lesley
 Dolly
(7 rows)

To return all entries (including duplicates) use the UNION ALL operator:

SELECT name FROM tourneys UNION ALL SELECT name FROM dinners;

Output
  name   
---------
 Dolly
 Etta
 Irma
 Barbara
 Gladys
 Bettye
 Dolly
 Etta
 Irma
 Barbara
 Gladys
 Lesley
(12 rows)

The names and number of the columns in the results table reflect the name and number of columns queried by the first SELECT statement. Note that when using UNION to query multiple columns from more than one table, each SELECT statement must query the same number of columns, the respective columns must have similar data types, and the columns in each SELECT statement must be in the same order. The following example shows what might result if you use a UNION clause on two SELECT statements that query a different number of columns:

SELECT name FROM dinners UNION SELECT name, wins FROM tourneys;

Output
ERROR:  each UNION query must have the same number of columns
LINE 1: SELECT name FROM dinners UNION SELECT name, wins FROM tourne...

Another way to query multiple tables is through the use of subqueries. Subqueries (also known as inner or nested queries) are queries enclosed within another query. These are useful in cases where you're trying to filter the results of a query against the result of a separate aggregate function.

To illustrate this idea, say you want to know which of your friends have won more matches than Barbara. Rather than querying how many matches Barbara has won then running another query to see who has won more games than that, you can calculate both with a single query:

SELECT name, wins FROM tourneys 
WHERE wins > (
SELECT wins FROM tourneys WHERE name = 'Barbara'
);

Output
  name  | wins 
--------+------
 Dolly  |    7
 Etta   |    4
 Irma   |    9
 Gladys |   13
(4 rows)

The subquery in this statement was run only once; it only needed to find the value from the wins column in the same row as Barbara in the name column, and the data returned by the subquery and outer query are independent of one another. There are cases, though, where the outer query must first read every row in a table and compare those values against the data returned by the subquery in order to return the desired data. In this case, the subquery is referred to as a correlated subquery.

The following statement is an example of a correlated subquery. This query seeks to find which of your friends have won more games than is the average for those with the same shoe size:

SELECT name, size FROM tourneys AS t 
WHERE wins > (
SELECT AVG(wins) FROM tourneys WHERE size = t.size
);

In order for the query to complete, it must first collect the name and size columns from the outer query. Then, it compares each row from that result set against the results of the inner query, which determines the average number of wins for individuals with identical shoe sizes. Because you only have two friends that have the same shoe size, there can only be one row in the result set:

Output
 name | size 
------+------
 Etta |    9
(1 row)

As mentioned earlier, subqueries can be used to query results from multiple tables. To illustrate this with one final example, say you wanted to throw a surprise dinner for the group's all-time best bowler. You could find which of your friends has the best bowling record and return their favorite meal with the following query:

SELECT name, entree, side, dessert 
FROM dinners 
WHERE name = (SELECT name FROM tourneys 
WHERE wins = (SELECT MAX(wins) FROM tourneys));

Output
  name  | entree | side  |  dessert  
--------+--------+-------+-----------
 Gladys | steak  | fries | ice cream
(1 row)

Notice that this statement not only includes a subquery, but also contains a subquery within that subquery.

Conclusion

Issuing queries is one of the most commonly-performed tasks within the realm of database management. There are a number of database administration tools, such as phpMyAdmin or pgAdmin, that allow you to perform queries and visualize the results, but issuing SELECT statements from the command line is still a widely-practiced workflow that can also provide you with greater control.

If you're new to working with SQL, we encourage you to use our SQL Cheat Sheet as a reference and to review the official PostgreSQL documenation. Additionally, if you'd like to learn more about SQL and relational databases, the following tutorials may be of interest to you:

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License

Forem: Mark Drake

How To Manage a Redis Database: A Free eBook

About this Book

Motivation for this Book

Learning Goals and Outcomes

Download the eBook

The DigitalOcean Community Monthly: Kubernetes Curriculum, Community Tools, a Contest for Student Developers, and More!

Kubernetes for Full-Stack Developers

Imperative vs. Declarative Kubernetes Management: A DigitalOcean Comic

How To Build an Inspirational Quote Application Using AdonisJs and MySQL

How To Build and Install Go Programs

New DigitalOcean Community Developer Tools

DigitalOcean on the Web

Docker and Docker Compose for PHP Development with GitHub and Digital Ocean Deployment

Jan Zavrel ・ Nov 25 '19

Community IRL

How To Install WordPress with a Managed Database on Ubuntu 18.04

Introduction

Prerequisites

Step 1 – Adding the MySQL Software Repository and Installing mysql-client

Step 2 – Creating a MySQL Database and User for WordPress

Step 3 – Installing Additional PHP Extensions

Step 4 – Adjusting Apache's Configuration to Allow for .htaccess Overrides and Rewrites

Step 5 – Downloading WordPress

Step 6 – Configuring the WordPress Directory

Step 7 – (Recommended) Configuring WordPress to Communicate with MySQL Over TLS/SSL

Step 8 – Completing the Installation Through the Web Interface

Conclusion

The DigitalOcean Community Weekly: Debian 10, Galera, Hacktoberfest, and more!

What's New in Debian 10 Buster

How to Set Up a Prometheus, Grafana and Alertmanager Monitoring Stack on DigitalOcean Kubernetes

How To Configure a Galera Cluster with MariaDB on CentOS 7 Servers

Featured Q&A

Community IRL

Understanding Managed Databases

Introduction

Managed Databases in a Nutshell

Pain Points of On-Premises and Self-Managed Databases

Benefits of Managed Databases

Practical Considerations

Conclusion

An Introduction to Queries in PostgreSQL

Introduction

Prerequisites

Creating a Sample Database

Understanding SELECT Statements

Aggregate Functions

Manipulating Query Outputs

Querying Multiple Tables

Conclusion

Step 1 – Adding the MySQL Software Repository and Installing `mysql-client`