One place for hosting & domains

      Manage

      How To Build a Modern Web Application to Manage Customer Information with Django and React on Ubuntu 18.04


      The author selected Open Sourcing Mental Illness Ltd to receive a donation as part of the Write for DOnations program.

      Introduction

      People use different types of devices to connect to the internet and browse the Web. Because of this, applications need to be accessible from a variety of locations. For traditional websites, having a responsive UI is usually enough, but more complex applications often require the use of other techniques and architectures. These include having separate REST back-end and front-end applications that can be implemented as client-side web applications, Progressive Web Apps (PWAs), or native mobile apps.

      Some tools that you can use when building more complex applications include:

      • React, a JavaScript framework that allows developers to build web and native frontends for their REST API backends.
      • Django, a free and open-source Python web framework that follows the model view controller (MVC) software architectural pattern.
      • Django REST framework, a powerful and flexible toolkit for building REST APIs in Django.

      In this tutorial, you will build a modern web application with a separate REST API backend and frontend using React, Django, and the Django REST Framework. By using React with Django, you’ll be able to benefit from the latest advancements in JavaScript and front-end development. Instead of building a Django application that uses a built-in template engine, you will use React as a UI library, taking advantage of its virtual Document Object Model (DOM), declarative approach, and components that quickly render changes in data.

      The web application you will build stores records about customers in a database, and you can use it as a starting point for a CRM application. When you are finished you’ll be able to create, read, update, and delete records using a React interface styled with Bootstrap 4.

      Prerequisites

      To complete this tutorial, you will need:

      Step 1 — Creating a Python Virtual Environment and Installing Dependencies

      In this step, we’ll create a virtual environment and install the required dependencies for our application, including Django, the Django REST framework, and django-cors-headers.

      Our application will use two different development servers for Django and React. They will run on different ports and will function as two separate domains. Because of this, we need to enable cross-origin resource sharing (CORS) to send HTTP requests from React to Django without being blocked by the browser.

      Navigate to your home directory and create a virtual environment using the venv Python 3 module:

      • cd ~
      • python3 -m venv ./env

      Activate the created virtual environment using source:

      Next, install the project's dependencies with pip. These will include:

      • Django: The web framework for the project.
      • Django REST framework: A third-party application that builds REST APIs with Django.
      • django-cors-headers: A package that enables CORS.

      Install the Django framework:

      • pip install django djangorestframework django-cors-headers

      With the project dependencies installed, you can create the Django project and the React frontend.

      Step 2 — Creating the Django Project

      In this step, we'll generate the Django project using the following commands and utilities:

      • django-admin startproject project-name: django-admin is a command-line utility used to accomplish tasks with Django. The startproject command creates a new Django project.

      • python manage.py startapp myapp: manage.py is a utility script, automatically added to each Django project, that performs a number of administrative tasks: creating new applications, migrating the database, and serving the Django project locally. Its startapp command creates a Django application inside the Django project. In Django, the term application describes a Python package that provides some set of features in a project.

      To begin, create the Django project with django-admin startproject. We will call our project djangoreactproject:

      • django-admin startproject djangoreactproject

      Before moving on, let's look at the directory structure of our Django project using the tree command.

      Tip: tree is a useful command for viewing file and directory structures from the command line. You can install it with the following command:

      • sudo apt-get install tree

      To use it, cd into the directory you want and type tree or provide the path to the starting point with tree /home/sammy/sammys-project.

      Navigate to the djangoreactproject folder within your project root and run the tree command:

      • cd ~/djangoreactproject
      • tree

      You will see the following output:

      Output

      ├── djangoreactproject │ ├── __init__.py │ ├── settings.py │ ├── urls.py │ └── wsgi.py └── manage.py

      The ~/djangoreactproject folder is the root of the project. Within this folder, there are several files that will be important to your work:

      • manage.py: The utility script that does a number of administrative tasks.
      • settings.py: The main configuration file for the Django project where you can modify the project's settings. These settings include variables such as INSTALLED_APPS, a list of strings designating the enabled applications for your project. The Django documentation has more information about available settings.
      • urls.py: This file contains a list of URL patterns and related views. Each pattern maps a connection between a URL and the function that should be called for that URL. For more on URLs and views, please refer to our tutorial on How To Create Django Views.

      Our first step in working with the project will be to configure the packages we installed in the previous step, including the Django REST framework and the Django CORS package, by adding them to settings.py. Open the file with nano or your favorite editor:

      • nano ~/djangoreactproject/djangoreactproject/settings.py

      Navigate to the INSTALLED_APPS setting and add the rest_framework and corsheaders applications to the bottom of the list:

      ~/djangoreactproject/djangoreactproject/settings.py

      ...
      INSTALLED_APPS = [
          'django.contrib.admin',
          'django.contrib.auth',
          'django.contrib.contenttypes',
          'django.contrib.sessions',
          'django.contrib.messages',
          'django.contrib.staticfiles',
          'rest_framework',
          'corsheaders'
      ]
      

      Next, add the corsheaders.middleware.CorsMiddleware middleware from the previously installed CORS package to the MIDDLEWARE setting. This setting is a list of middlewares, a Python class that contains code processed each time your web application handles a request or response:

      ~/djangoreactproject/djangoreactproject/settings.py

      ...
      
      MIDDLEWARE = [
      ...
      'django.contrib.messages.middleware.MessageMiddleware',
      'django.middleware.clickjacking.XFrameOptionsMiddleware',
      'corsheaders.middleware.CorsMiddleware'
      ]
      

      Next, you can enable CORS. The CORS_ORIGIN_ALLOW_ALL setting specifies whether or not you want to allow CORS for all domains, and CORS_ORIGIN_WHITELIST is a Python tuple that contains allowed URLs. In our case, because the React development server will be running at http://localhost:3000, we will add new CORS_ORIGIN_ALLOW_ALL = False and CORS_ORIGIN_WHITELIST('localhost:3000',) settings to our settings.py file. Add these settings anywhere in the file:

      ~/djangoreactproject/djangoreactproject/settings.py

      
      ...
      CORS_ORIGIN_ALLOW_ALL = False
      
      CORS_ORIGIN_WHITELIST = (
             'localhost:3000',
      )
      ...
      

      You can find more configuration options in the django-cors-headers docs.

      Save the file and exit the editor when you are finished.

      Still in the ~/djangoreactproject directory, make a new Django application called customers:

      • python manage.py startapp customers

      This will contain the models and views for managing customers. Models define the fields and behaviors of our application data, while views enable our application to properly handle web requests and return the required responses.

      Next, add this application to the list of installed applications in your project's settings.py file so Django will recognize it as part of the project. Open settings.py again:

      • nano ~/djangoreactproject/djangoreactproject/settings.py

      Add the customers application:

      ~/djangoreactproject/djangoreactproject/settings.py

      ...
      INSTALLED_APPS = [
          ...
          'rest_framework',
          'corsheaders',
          'customers'
      ]
      ...
      

      Next, migrate the database and start the local development server. Migrations are Django’s way of propagating the changes you make to your models into your database schema. These changes can include things like adding a field or deleting a model, for example. For more on models and migrations, see How To Create Django Models.

      Migrate the database:

      Start the local development server:

      • python manage.py runserver

      You will see output similar to the following:

      Output

      Performing system checks... System check identified no issues (0 silenced). October 22, 2018 - 15:14:50 Django version 2.1.2, using settings 'djangoreactproject.settings' Starting development server at http://127.0.0.1:8000/ Quit the server with CONTROL-C.

      Your web application will be running from http://127.0.0.1:8000. If you navigate to this address in your web browser you should see the following page:

      Django demo page

      At this point, leave the application running and open a new terminal to continue developing the project.

      Step 3 — Creating the React Frontend

      In this section, we're going to create the front-end application of our project using React.

      React has an official utility that allows you to quickly generate React projects without having to configure Webpack directly. Webpack is a module bundler used to bundle web assets such as JavaScript code, CSS, and images. Typically, before you can use Webpack you need to set various configuration options, but thanks to the create-react-app utility you don't have to deal with Webpack directly until you decide you need more control. To run create-react-app you can use npx, a tool that executes npm package binaries.

      In your second terminal, make sure you are in your project directory:

      Create a React project called frontend using create-react-app and npx:

      • npx create-react-app frontend

      Next, navigate inside your React application and start the development server:

      • cd ~/djangoreactproject/frontend
      • npm start

      You application will be running from http://localhost:3000/:

      React demo page

      Leave the React development server running and open another terminal window to proceed.

      To see the directory structure of the entire project at this point, navigate to the root folder and run tree again:

      • cd ~/djangoreactproject
      • tree

      You'll see a structure like this:

      Output

      ├── customers │ ├── admin.py │ ├── apps.py │ ├── __init__.py │ ├── migrations │ │ └── __init__.py │ ├── models.py │ ├── tests.py │ └── views.py ├── djangoreactproject │ ├── __init__.py │ ├── __pycache__ │ ├── settings.py │ ├── urls.py │ └── wsgi.py ├── frontend │ ├── package.json │ ├── public │ │ ├── favicon.ico │ │ ├── index.html │ │ └── manifest.json │ ├── README.md │ ├── src │ │ ├── App.css │ │ ├── App.js │ │ ├── App.test.js │ │ ├── index.css │ │ ├── index.js │ │ ├── logo.svg │ │ └── registerServiceWorker.js │ └── yarn.lock └── manage.py

      Our application will use Bootstrap 4 to style the React interface, so we will include it in the frontend/src/App.css file, which manages our CSS settings. Open the file:

      • nano ~/djangoreactproject/frontend/src/App.css

      Add the following import to the beginning of the file. You can delete the file's existing content, though that's not required:

      ~/djangoreactproject/frontend/src/App.css

      @import  'https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css';
      

      Here, @import is a CSS instruction that's used to import style rules from other style sheets.

      Now that we have created both the back-end and front-end applications, let's create the Customer model and some demo data.

      Step 4 — Creating the Customer Model and Initial Data

      After creating the Django application and the React frontend, our next step will be to create the Customer model, which represents the database table that will hold information about customers. You don't need any SQL since the Django Object Relational Mapper (ORM) will handle database operations by mapping Python classes and variables to SQL tables and columns. In this way the Django ORM abstracts SQL interactions with the database through a Python interface.

      Activate your virtual environment again:

      • cd ~
      • source env/bin/activate

      Move to the customers directory, and open models.py, a Python file that holds the models of your application:

      • cd ~/djangoreactproject/customers/
      • nano models.py

      The file will contain the following content:

      ~/djangoreactproject/customers/models.py

      from django.db import models
      # Create your models here.
      

      The Customer model's API is already imported in the file thanks to the from django.db import models import statement. You will now add the Customer class, which extends models.Model. Each model in Django is a Python class that extends django.db.models.Model.

      The Customer model will have these database fields:

      • first_name — The first name of the customer.
      • last_name — The last name of the customer.
      • email — The email address of the customer.
      • phone — The phone number of the customer.
      • address — The address of the customer.
      • description — The description of the customer.
      • createdAt — The date when the customer is added.

      We will also add the __str__() function, which defines how the model will be displayed. In our case, it will be with the customer's first name. For more on constructing classes and defining objects, please see How To Construct Classes and Define Objects in Python 3.

      Add the following code to the file:

      ~/djangoreactproject/customers/models.py

      from django.db import models
      
      class Customer(models.Model):
          first_name = models.CharField("First name", max_length=255)
          last_name = models.CharField("Last name", max_length=255)
          email = models.EmailField()
          phone = models.CharField(max_length=20)
          address =  models.TextField(blank=True, null=True)
          description = models.TextField(blank=True, null=True)
          createdAt = models.DateTimeField("Created At", auto_now_add=True)
      
          def __str__(self):
              return self.first_name
      

      Next, migrate the database to create the database tables. The makemigrations command creates the migration files where model changes will be added, and migrate applies the changes in the migrations files to the database.

      Navigate back to the project's root folder:

      Run the following to create the migration files:

      • python manage.py makemigrations

      You will get output that looks like this:

      Output

      customers/migrations/0001_initial.py - Create model Customer

      Apply these changes to the database:

      You will see output indicating a successful migration:

      Output

      Operations to perform: Apply all migrations: admin, auth, contenttypes, customers, sessions Running migrations: Applying customers.0001_initial... OK

      Next, you will use a data migration file to create initial customer data. A data migration file is a migration that adds or alters data in the database. Create an empty data migration file for the customers application:

      • python manage.py makemigrations --empty --name customers customers

      You will see the following confirmation with the name of your migration file:

      Output

      Migrations for 'customers': customers/migrations/0002_customers.py

      Note that the name of your migration file is 0002_customers.py.

      Next, navigate inside the migrations folder of the customers application:

      • cd ~/djangoreactproject/customers/migrations

      Open the created migration file:

      This is the initial content of the file:

      ~/djangoreactproject/customers/migrations/0002_customers.py

      from django.db import migrations
      
      class Migration(migrations.Migration):
          dependencies = [
              ('customers', '0001_initial'),
          ]
          operations = [
          ]        
      

      The import statement imports the migrations API, a Django API for creating migrations, from django.db, a built-in package that contains classes for working with databases.

      The Migration class is a Python class that describes the operations that are executed when migrating databases. This class extends migrations.Migration and has two lists:

      • dependencies: Contains the dependent migrations.
      • operations: Contains the operations that will be executed when we apply the migration.

      Next, add a method to create demo customer data. Add the following method before the definition of the Migration class:

      ~/djangoreactproject/customers/migrations/0002_customers.py

      ...
      def create_data(apps, schema_editor):
          Customer = apps.get_model('customers', 'Customer')
          Customer(first_name="Customer 001", last_name="Customer 001", email="customer001@email.com", phone="00000000", address="Customer 000 Address", description= "Customer 001 description").save()
      
      ...
      

      In this method, we are grabbing the Customer class of our customers app and creating a demo customer to insert into the database.

      To get the Customer class, which will enable the creation of new customers, we use the get_model() method of the apps object. The apps object represents the registry of installed applications and their database models.

      The apps object will be passed from the RunPython() method when we use it to run create_data(). Add the migrations.RunPython() method to the empty operations list:

      ~/djangoreactproject/customers/migrations/0002_customers.py

      
      ...
          operations = [
              migrations.RunPython(create_data),
          ]  
      

      RunPython() is part of the Migrations API that allows you to run custom Python code in a migration. Our operations list specifies that this method will be executed when we apply the migration.

      This is the complete file:

      ~/djangoreactproject/customers/migrations/0002_customers.py

      from django.db import migrations
      
      def create_data(apps, schema_editor):
          Customer = apps.get_model('customers', 'Customer')
          Customer(first_name="Customer 001", last_name="Customer 001", email="customer001@email.com", phone="00000000", address="Customer 000 Address", description= "Customer 001 description").save()
      
      class Migration(migrations.Migration):
          dependencies = [
              ('customers', '0001_initial'),
          ]
          operations = [
              migrations.RunPython(create_data),
          ]        
      

      For more information on data migrations, see the documentation on data migrations in Django

      To migrate your database, first navigate back to the root folder of your project:

      Migrate your database to create the demo data:

      You will see output that confirms the migration:

      Output

      Operations to perform: Apply all migrations: admin, auth, contenttypes, customers, sessions Running migrations: Applying customers.0002_customers... OK

      For more details on this process, refer back to How To Create Django Models.

      With the Customer model and demo data created, we can move on to building the REST API.

      Step 5 — Creating the REST API

      In this step we'll create the REST API using the Django REST Framework. We'll create several different API views. An API view is a function that handles an API request or call, while an API endpoint is a unique URL that represents a touchpoint with the REST system. For example, when the user sends a GET request to an API endpoint, Django calls the corresponding function or API view to handle the request and return any possible results.

      We'll also make use of serializers. A serializer in the Django REST Framework allows complex model instances and QuerySets to be converted into JSON format for API consumption. The serializer class can also work in the other direction, providing mechanisms for parsing and deserializing data into Django models and QuerySets.

      Our API endpoints will include:

      • api/customers: This endpoint is used to create customers and returns paginated sets of customers.
      • api/customers/<pk>: This endpoint is used to get, update, and delete single customers by primary key or id.

      We'll also create URLs in the project's urls.py file for the corresponding endpoints (i.e api/customers and api/customers/<pk>).

      Let's start by creating the serializer class for our Customer model.

      Adding the Serializer Class

      Creating a serializer class for our Customer model is necessary for transforming customer instances and QuerySets to and from JSON. To create the serializer class, first make a serializers.py file inside the customers application:

      • cd ~/djangoreactproject/customers/
      • nano serializers.py

      Add the following code to import the serializers API and Customer model:

      ~/djangoreactproject/customers/serializers.py

      from rest_framework import serializers
      from .models import Customer
      

      Next, create a serializer class that extends serializers.ModelSerializer and specifies the fields that will be serialized:

      ~/djangoreactproject/customers/serializers.py

      
      ...
      class CustomerSerializer(serializers.ModelSerializer):
      
          class Meta:
              model = Customer 
              fields = ('pk','first_name', 'last_name', 'email', 'phone','address','description')
      

      The Meta class specifies the model and fields to serialize: pk,first_name, last_name, email, phone, address,description.

      This is the full content of the file:

      ~/djangoreactproject/customers/serializers.py

      from rest_framework import serializers
      from .models import Customer
      
      class CustomerSerializer(serializers.ModelSerializer):
      
          class Meta:
              model = Customer 
              fields = ('pk','first_name', 'last_name', 'email', 'phone','address','description')
      

      Now that we've created our serializer class, we can add the API views.

      Adding the API Views

      In this section, we'll create the API views for our application that will be called by Django when the user visits the endpoint corresponding to the view function.

      Open ~/djangoreactproject/customers/views.py:

      • nano ~/djangoreactproject/customers/views.py

      Delete what's there and add the following imports:

      ~/djangoreactproject/customers/views.py

      from rest_framework.response import Response
      from rest_framework.decorators import api_view
      from rest_framework import status
      
      from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
      from .models import Customer 
      from .serializers import *
      

      We are importing the serializer we created, along with the Customer model and the Django and Django REST Framework APIs.

      Next, add the view for processing POST and GET HTTP requests:

      ~/djangoreactproject/customers/views.py

      ...
      
      @api_view(['GET', 'POST'])
      def customers_list(request):
          """
       List  customers, or create a new customer.
       """
          if request.method == 'GET':
              data = []
              nextPage = 1
              previousPage = 1
              customers = Customer.objects.all()
              page = request.GET.get('page', 1)
              paginator = Paginator(customers, 10)
              try:
                  data = paginator.page(page)
              except PageNotAnInteger:
                  data = paginator.page(1)
              except EmptyPage:
                  data = paginator.page(paginator.num_pages)
      
              serializer = CustomerSerializer(data,context={'request': request} ,many=True)
              if data.has_next():
                  nextPage = data.next_page_number()
              if data.has_previous():
                  previousPage = data.previous_page_number()
      
              return Response({'data': serializer.data , 'count': paginator.count, 'numpages' : paginator.num_pages, 'nextlink': '/api/customers/?page=' + str(nextPage), 'prevlink': '/api/customers/?page=' + str(previousPage)})
      
          elif request.method == 'POST':
              serializer = CustomerSerializer(data=request.data)
              if serializer.is_valid():
                  serializer.save()
                  return Response(serializer.data, status=status.HTTP_201_CREATED)
              return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)
      

      First we use the @api_view(['GET', 'POST']) decorator to create an API view that can accept GET and POST requests. A decorator is a function that takes another function and dynamically extends it.

      In the method body we use the request.method variable to check the current HTTP method and execute the corresponding logic depending on the request type:

      • If it's a GET request, the method paginates the data using Django Paginator, and returns the first page of data after serialization, the count of available customers, the number of available pages, and the links to the previous and next pages. Paginator is a built-in Django class that paginates a list of data into pages and provides methods to access the items for each page.
      • If it's a POST request, the method serializes the received customer data and then calls the save() method of the serializer object. It then returns a Response object, an instance of HttpResponse, with a 201 status code. Each view you create is responsible for returing an HttpResponse object. The save() method saves the serialized data in the database.

      For more about HttpResponse and views, see this discussion of creating view functions.

      Now add the API view that will be responsible for processing the GET, PUT, and DELETE requests for getting, updating, and deleting customers by pk (primary key):

      ~/djangoreactproject/customers/views.py

      
      ...
      @api_view(['GET', 'PUT', 'DELETE'])
      def customers_detail(request, pk):
       """
       Retrieve, update or delete a customer by id/pk.
       """
          try:
              customer = Customer.objects.get(pk=pk)
          except Customer.DoesNotExist:
              return Response(status=status.HTTP_404_NOT_FOUND)
      
          if request.method == 'GET':
              serializer = CustomerSerializer(customer,context={'request': request})
              return Response(serializer.data)
      
          elif request.method == 'PUT':
              serializer = CustomerSerializer(customer, data=request.data,context={'request': request})
              if serializer.is_valid():
                  serializer.save()
                  return Response(serializer.data)
              return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)
      
          elif request.method == 'DELETE':
              customer.delete()
              return Response(status=status.HTTP_204_NO_CONTENT)
      

      The method is decorated with @api_view(['GET', 'PUT', 'DELETE']) to denote that it's an API view that can accept GET, PUT, and DELETE requests.

      The check in the request.method field verifies the request method, and depending on its value calls the right logic:

      • If it's a GET request, customer data is serialized and sent using a Response object.
      • If it's a PUT request, the method creates a serializer for new customer data. Next, it calls the save() method of the created serializer object. Finally, it sends a Response object with the updated customer.
      • If it's a DELETE request, the method calls the delete() method of the customer object to delete it, then returns a Response object with no data.

      The completed file looks like this:

      ~/djangoreactproject/customers/views.py

      from rest_framework.response import Response
      from rest_framework.decorators import api_view
      from rest_framework import status
      
      from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
      from .models import Customer 
      from .serializers import *
      
      
      @api_view(['GET', 'POST'])
      def customers_list(request):
          """
       List  customers, or create a new customer.
       """
          if request.method == 'GET':
              data = []
              nextPage = 1
              previousPage = 1
              customers = Customer.objects.all()
              page = request.GET.get('page', 1)
              paginator = Paginator(customers, 5)
              try:
                  data = paginator.page(page)
              except PageNotAnInteger:
                  data = paginator.page(1)
              except EmptyPage:
                  data = paginator.page(paginator.num_pages)
      
              serializer = CustomerSerializer(data,context={'request': request} ,many=True)
              if data.has_next():
                  nextPage = data.next_page_number()
              if data.has_previous():
                  previousPage = data.previous_page_number()
      
              return Response({'data': serializer.data , 'count': paginator.count, 'numpages' : paginator.num_pages, 'nextlink': '/api/customers/?page=' + str(nextPage), 'prevlink': '/api/customers/?page=' + str(previousPage)})
      
          elif request.method == 'POST':
              serializer = CustomerSerializer(data=request.data)
              if serializer.is_valid():
                  serializer.save()
                  return Response(serializer.data, status=status.HTTP_201_CREATED)
              return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)
      
      @api_view(['GET', 'PUT', 'DELETE'])
      def customers_detail(request, pk):
          """
       Retrieve, update or delete a customer by id/pk.
       """
          try:
              customer = Customer.objects.get(pk=pk)
          except Customer.DoesNotExist:
              return Response(status=status.HTTP_404_NOT_FOUND)
      
          if request.method == 'GET':
              serializer = CustomerSerializer(customer,context={'request': request})
              return Response(serializer.data)
      
          elif request.method == 'PUT':
              serializer = CustomerSerializer(customer, data=request.data,context={'request': request})
              if serializer.is_valid():
                  serializer.save()
                  return Response(serializer.data)
              return Response(serializer.errors, status=status.HTTP_400_BAD_REQUEST)
      
          elif request.method == 'DELETE':
              customer.delete()
              return Response(status=status.HTTP_204_NO_CONTENT)
      

      We can now move on to creating our endpoints.

      Adding API Endpoints

      We will now create the API endpoints: api/customers/, for querying and creating customers, and api/customers/<pk>, for getting, updating, or deleting single customers by their pk.

      Open ~/djangoreactproject/djangoreactproject/urls.py:

      • nano ~/djangoreactproject/djangoreactproject/urls.py

      Leave what's there, but add the import to the customers views at the top of the file:

      ~/djangoreactproject/djangoreactproject/urls.py

      from django.contrib import admin
      from django.urls import path
      from customers import views
      from django.conf.urls import url
      

      Next, add the api/customers/ and api/customers/<pk> URLs to the urlpatterns list that contains the application's URLs:

      ~/djangoreactproject/djangoreactproject/urls.py

      ...
      
      urlpatterns = [
          path('admin/', admin.site.urls),
          url(r'^api/customers/$', views.customers_list),
          url(r'^api/customers/(?P<pk>[0-9]+)$', views.customers_detail),
      ]
      

      With our REST endpoints created, let's see how we can consume them.

      Step 6 — Consuming the REST API with Axios

      In this step, we'll install Axios, the HTTP client we'll use to make API calls. We'll also create a class to consume the API endpoints we've created.

      First, deactivate your virtual environment:

      Next, navigate to your frontend folder:

      • cd ~/djangoreactproject/frontend

      Install axios from npm using:

      The --save option adds the axios dependency to your application's package.json file.

      Next, create a JavaScript file called CustomersService.js, which will contain the code to call the REST APIs. We'll make this inside the src folder, where the application code for our project will live:

      • cd src
      • nano CustomersService.js

      Add the following code, which contains methods to connect to the Django REST API:

      ~/djangoreactproject/frontend/src/CustomersService.js

      import axios from 'axios';
      const API_URL = 'http://localhost:8000';
      
      export default class CustomersService{
      
          constructor(){}
      
      
          getCustomers() {
              const url = `${API_URL}/api/customers/`;
              return axios.get(url).then(response => response.data);
          }  
          getCustomersByURL(link){
              const url = `${API_URL}${link}`;
              return axios.get(url).then(response => response.data);
          }
          getCustomer(pk) {
              const url = `${API_URL}/api/customers/${pk}`;
              return axios.get(url).then(response => response.data);
          }
          deleteCustomer(customer){
              const url = `${API_URL}/api/customers/${customer.pk}`;
              return axios.delete(url);
          }
          createCustomer(customer){
              const url = `${API_URL}/api/customers/`;
              return axios.post(url,customer);
          }
          updateCustomer(customer){
              const url = `${API_URL}/api/customers/${customer.pk}`;
              return axios.put(url,customer);
          }
      }
      

      The CustomersService class will call the following Axios methods:

      • getCustomers(): Gets first page of customers.
      • getCustomersByURL(): Gets customers by URL. This makes it possible to get the next pages of customers by passing links such as /api/customers/?page=2.
      • getCustomer(): Gets a customer by primary key.
      • createCustomer(): Creates a customer.
      • updateCustomer(): Updates a customer.
      • deleteCustomer(): Deletes a customer.

      We can now display the data from our API in our React UI interface by creating a CustomersList component.

      Step 7 — Displaying Data from the API in the React Application

      In this step, we'll create the CustomersList React component. A React component represents a part of the UI; it also lets you split the UI into independent, reusable pieces.

      Begin by creating CustomersList.js in frontend/src:

      • nano ~/djangoreactproject/frontend/src/CustomersList.js

      Start by importing React and Component to create a React component:

      ~/djangoreactproject/frontend/src/CustomersList.js

      import  React, { Component } from  'react';
      

      Next, import and instantiate the CustomersService module you created in the previous step, which provides methods that interface with the REST API backend:

      ~/djangoreactproject/frontend/src/CustomersList.js

      
      ...
      import  CustomersService  from  './CustomersService';
      
      const  customersService  =  new  CustomersService();
      

      Next, create a CustomersList component that extends Component to call the REST API. A React component should extend or subclass the Component class. For more about E6 classes and inheritence, please see our tutorial on Understanding Classes in JavaScript.

      Add the following code to create a React component that extends react.Component:

      ~/djangoreactproject/frontend/src/CustomersList.js

      
      ...
      class  CustomersList  extends  Component {
      
          constructor(props) {
              super(props);
              this.state  = {
                  customers: [],
                  nextPageURL:  ''
              };
              this.nextPage  =  this.nextPage.bind(this);
              this.handleDelete  =  this.handleDelete.bind(this);
          }
      }
      export  default  CustomersList;
      

      Inside the constructor, we are initializing the state object. This holds the state variables of our component using an empty customers array. This array will hold customers and a nextPageURL that will hold the URL of the next page to retrieve from the back-end API. We are also binding the nextPage() and handleDelete() methods to this so they will be accessible from the HTML code.

      Next, add the componentDidMount() method and a call to getCustomers() within the CustomersList class, before the closing curly brace.

      The componentDidMount() method is a lifecycle method of the component that is called when the component is created and inserted into the DOM. getCustomers() calls the Customers Service object to get the first page of data and the link of the next page from the Django backend:

      ~/djangoreactproject/frontend/src/CustomersList.js

      
      ...
      componentDidMount() {
          var  self  =  this;
          customersService.getCustomers().then(function (result) {
              self.setState({ customers:  result.data, nextPageURL:  result.nextlink})
          });
      }
      

      Now add the handleDelete() method, which handles deleting a customer, below componentDidMount():

      ~/djangoreactproject/frontend/src/CustomersList.js

      
      ...
      handleDelete(e,pk){
          var  self  =  this;
          customersService.deleteCustomer({pk :  pk}).then(()=>{
              var  newArr  =  self.state.customers.filter(function(obj) {
                  return  obj.pk  !==  pk;
              });
              self.setState({customers:  newArr})
          });
      }
      

      The handleDelete() method calls the deleteCustomer() method to delete a customer using its pk (primary key). If the operation is successful, the customers array is filtered out for the removed customer.

      Next, add a nextPage() method to get the data for the next page and update the next page link:

      ~/djangoreactproject/frontend/src/CustomersList.js

      
      ...
      nextPage(){
          var  self  =  this;
          customersService.getCustomersByURL(this.state.nextPageURL).then((result) => {
              self.setState({ customers:  result.data, nextPageURL:  result.nextlink})
          });
      }
      

      The nextPage() method calls a getCustomersByURL() method, which takes the next page URL from the state object, this.state.nextPageURL, and updates the customers array with the returned data.

      Finally, add the component render() method, which renders a table of customers from the component state:

      ~/djangoreactproject/frontend/src/CustomersList.js

      
      ...
      render() {
      
          return (
          <div  className="customers--list">
              <table  className="table">
                  <thead  key="thead">
                  <tr>
                      <th>#</th>
                      <th>First Name</th>
                      <th>Last Name</th>
                      <th>Phone</th>
                      <th>Email</th>
                      <th>Address</th>
                      <th>Description</th>
                      <th>Actions</th>
                  </tr>
                  </thead>
                  <tbody>
                      {this.state.customers.map( c  =>
                      <tr  key={c.pk}>
                          <td>{c.pk}  </td>
                          <td>{c.first_name}</td>
                          <td>{c.last_name}</td>
                          <td>{c.phone}</td>
                          <td>{c.email}</td>
                          <td>{c.address}</td>
                          <td>{c.description}</td>
                          <td>
                          <button  onClick={(e)=>  this.handleDelete(e,c.pk) }> Delete</button>
                          <a  href={"/customer/" + c.pk}> Update</a>
                          </td>
                      </tr>)}
                  </tbody>
              </table>
              <button  className="btn btn-primary"  onClick=  {  this.nextPage  }>Next</button>
          </div>
          );
      }
      

      This is the full content of the file:

      ~/djangoreactproject/frontend/src/CustomersList.js

      import  React, { Component } from  'react';
      import  CustomersService  from  './CustomersService';
      
      const  customersService  =  new  CustomersService();
      
      class  CustomersList  extends  Component {
      
      constructor(props) {
          super(props);
          this.state  = {
              customers: [],
              nextPageURL:  ''
          };
          this.nextPage  =  this.nextPage.bind(this);
          this.handleDelete  =  this.handleDelete.bind(this);
      }
      
      componentDidMount() {
          var  self  =  this;
          customersService.getCustomers().then(function (result) {
              console.log(result);
              self.setState({ customers:  result.data, nextPageURL:  result.nextlink})
          });
      }
      handleDelete(e,pk){
          var  self  =  this;
          customersService.deleteCustomer({pk :  pk}).then(()=>{
              var  newArr  =  self.state.customers.filter(function(obj) {
                  return  obj.pk  !==  pk;
              });
      
              self.setState({customers:  newArr})
          });
      }
      
      nextPage(){
          var  self  =  this;
          console.log(this.state.nextPageURL);        
          customersService.getCustomersByURL(this.state.nextPageURL).then((result) => {
              self.setState({ customers:  result.data, nextPageURL:  result.nextlink})
          });
      }
      render() {
      
          return (
              <div  className="customers--list">
                  <table  className="table">
                  <thead  key="thead">
                  <tr>
                      <th>#</th>
                      <th>First Name</th>
                      <th>Last Name</th>
                      <th>Phone</th>
                      <th>Email</th>
                      <th>Address</th>
                      <th>Description</th>
                      <th>Actions</th>
                  </tr>
                  </thead>
                  <tbody>
                  {this.state.customers.map( c  =>
                      <tr  key={c.pk}>
                      <td>{c.pk}  </td>
                      <td>{c.first_name}</td>
                      <td>{c.last_name}</td>
                      <td>{c.phone}</td>
                      <td>{c.email}</td>
                      <td>{c.address}</td>
                      <td>{c.description}</td>
                      <td>
                      <button  onClick={(e)=>  this.handleDelete(e,c.pk) }> Delete</button>
                      <a  href={"/customer/" + c.pk}> Update</a>
                      </td>
                  </tr>)}
                  </tbody>
                  </table>
                  <button  className="btn btn-primary"  onClick=  {  this.nextPage  }>Next</button>
              </div>
              );
        }
      }
      export  default  CustomersList;
      

      Now that we've created the CustomersList component for displaying the list of customers, we can add the component that handles customer creation and updates.

      Step 8 — Adding the Customer Create and Update React Component

      In this step, we'll create the CustomerCreateUpdate component, which will handle creating and updating customers. It will do this by providing a form that users can use to either enter data about a new customer or update an existing entry.

      In frontend/src, create a CustomerCreateUpdate.js file:

      • nano ~/djangoreactproject/frontend/src/CustomerCreateUpdate.js

      Add the following code to create a React component, importing React and Component:

      ~/djangoreactproject/frontend/src/CustomerCreateUpdate.js

      import  React, { Component } from  'react';
      

      We can also import and instantiate the CustomersService class we created in the previous step, which provides methods that interface with the REST API backend:

      ~/djangoreactproject/frontend/src/CustomerCreateUpdate.js

      ...
      import  CustomersService  from  './CustomersService';
      
      const  customersService  =  new  CustomersService();
      

      Next, create a CustomerCreateUpdate component that extends Component to create and update customers:

      ~/djangoreactproject/frontend/src/CustomerCreateUpdate.js

      
      ...
      class  CustomerCreateUpdate  extends  Component {
      
          constructor(props) {
              super(props);
          }
      
      }
      export default CustomerCreateUpdate;
      

      Within the class definition, add the render() method of the component, which renders an HTML form that takes information about the customer:

      ~/djangoreactproject/frontend/src/CustomerCreateUpdate.js

      
      ...
      render() {
              return (
                <form onSubmit={this.handleSubmit}>
                <div className="form-group">
                  <label>
                    First Name:</label>
                    <input className="form-control" type="text" ref='firstName' />
      
                  <label>
                    Last Name:</label>
                    <input className="form-control" type="text" ref='lastName'/>
      
                  <label>
                    Phone:</label>
                    <input className="form-control" type="text" ref='phone' />
      
                  <label>
                    Email:</label>
                    <input className="form-control" type="text" ref='email' />
      
                  <label>
                    Address:</label>
                    <input className="form-control" type="text" ref='address' />
      
                  <label>
                    Description:</label>
                    <textarea className="form-control" ref='description' ></textarea>
      
      
                  <input className="btn btn-primary" type="submit" value="Submit" />
                  </div>
                </form>
              );
        }
      

      For each form input element, the method adds a ref property to access and set the value of the form element.

      Next, above the render() method, define a handleSubmit(event) method so that you have the proper functionality when a user clicks on the submit button:

      ~/djangoreactproject/frontend/src/CustomerCreateUpdate.js

      
      ...
      handleSubmit(event) {
          const { match: { params } } =  this.props;
          if(params  &&  params.pk){
              this.handleUpdate(params.pk);
          }
          else
          {
              this.handleCreate();
          }
          event.preventDefault();
      }
      
      ...
      

      The handleSubmit(event) method handles the form submission and, depending on the route, calls either the handleUpdate(pk) method to update the customer with the passed pk, or the handleCreate() method to create a new customer. We will define these methods shortly.

      Back on the component constructor, bind the newly added handleSubmit() method to this so you can access it in your form:

      ~/djangoreactproject/frontend/src/CustomerCreateUpdate.js

      ...
      class CustomerCreateUpdate extends Component {
      
      constructor(props) {
          super(props);
          this.handleSubmit = this.handleSubmit.bind(this);
      }
      ...
      

      Next, define the handleCreate() method to create a customer from the form data. Above the handleSubmit(event) method, add the following code:

      ~/djangoreactproject/frontend/src/CustomerCreateUpdate.js

      
      ...
      handleCreate(){
          customersService.createCustomer(
              {
              "first_name":  this.refs.firstName.value,
              "last_name":  this.refs.lastName.value,
              "email":  this.refs.email.value,
              "phone":  this.refs.phone.value,
              "address":  this.refs.address.value,
              "description":  this.refs.description.value
              }).then((result)=>{
                      alert("Customer created!");
              }).catch(()=>{
                      alert('There was an error! Please re-check your form.');
              });
      }
      
      ...
      

      The handleCreate() method will be used to create a customer from inputted data. It calls the corresponding CustomersService.createCustomer() method that makes the actual API call to the backend to create a customer.

      Next, below the handleCreate() method, define the handleUpdate(pk) method to implement updates:

      ~/djangoreactproject/frontend/src/CustomerCreateUpdate.js

      
      ...
      handleUpdate(pk){
      customersService.updateCustomer(
          {
          "pk":  pk,
          "first_name":  this.refs.firstName.value,
          "last_name":  this.refs.lastName.value,
          "email":  this.refs.email.value,
          "phone":  this.refs.phone.value,
          "address":  this.refs.address.value,
          "description":  this.refs.description.value
          }
          ).then((result)=>{
      
              alert("Customer updated!");
          }).catch(()=>{
              alert('There was an error! Please re-check your form.');
          });
      }
      

      The updateCustomer() method will update a customer by pk using the new information from the customer information form. It calls the customersService.updateCustomer() method.

      Next, add a componentDidMount() method. If the the user visits a customer/:pk route, we want to fill the form with information related to the customer using the primary key from the URL. To do that, we can add the getCustomer(pk) method after the component gets mounted in the lifecycle event of componentDidMount(). Add the following code below the component constructor to add this method:

      ~/djangoreactproject/frontend/src/CustomerCreateUpdate.js

      
      ...
      componentDidMount(){
          const { match: { params } } =  this.props;
          if(params  &&  params.pk)
          {
              customersService.getCustomer(params.pk).then((c)=>{
                  this.refs.firstName.value  =  c.first_name;
                  this.refs.lastName.value  =  c.last_name;
                  this.refs.email.value  =  c.email;
                  this.refs.phone.value  =  c.phone;
                  this.refs.address.value  =  c.address;
                  this.refs.description.value  =  c.description;
              })
          }
      }
      

      This is the full content of the file:

      ~/djangoreactproject/frontend/src/CustomerCreateUpdate.js

      import React, { Component } from 'react';
      import CustomersService from './CustomersService';
      
      const customersService = new CustomersService();
      
      class CustomerCreateUpdate extends Component {
          constructor(props) {
              super(props);
      
              this.handleSubmit = this.handleSubmit.bind(this);
            }
      
            componentDidMount(){
              const { match: { params } } = this.props;
              if(params && params.pk)
              {
                customersService.getCustomer(params.pk).then((c)=>{
                  this.refs.firstName.value = c.first_name;
                  this.refs.lastName.value = c.last_name;
                  this.refs.email.value = c.email;
                  this.refs.phone.value = c.phone;
                  this.refs.address.value = c.address;
                  this.refs.description.value = c.description;
                })
              }
            }
      
            handleCreate(){
              customersService.createCustomer(
                {
                  "first_name": this.refs.firstName.value,
                  "last_name": this.refs.lastName.value,
                  "email": this.refs.email.value,
                  "phone": this.refs.phone.value,
                  "address": this.refs.address.value,
                  "description": this.refs.description.value
              }          
              ).then((result)=>{
                alert("Customer created!");
              }).catch(()=>{
                alert('There was an error! Please re-check your form.');
              });
            }
            handleUpdate(pk){
              customersService.updateCustomer(
                {
                  "pk": pk,
                  "first_name": this.refs.firstName.value,
                  "last_name": this.refs.lastName.value,
                  "email": this.refs.email.value,
                  "phone": this.refs.phone.value,
                  "address": this.refs.address.value,
                  "description": this.refs.description.value
              }          
              ).then((result)=>{
                console.log(result);
                alert("Customer updated!");
              }).catch(()=>{
                alert('There was an error! Please re-check your form.');
              });
            }
            handleSubmit(event) {
              const { match: { params } } = this.props;
      
              if(params && params.pk){
                this.handleUpdate(params.pk);
              }
              else
              {
                this.handleCreate();
              }
      
              event.preventDefault();
            }
      
            render() {
              return (
                <form onSubmit={this.handleSubmit}>
                <div className="form-group">
                  <label>
                    First Name:</label>
                    <input className="form-control" type="text" ref='firstName' />
      
                  <label>
                    Last Name:</label>
                    <input className="form-control" type="text" ref='lastName'/>
      
                  <label>
                    Phone:</label>
                    <input className="form-control" type="text" ref='phone' />
      
                  <label>
                    Email:</label>
                    <input className="form-control" type="text" ref='email' />
      
                  <label>
                    Address:</label>
                    <input className="form-control" type="text" ref='address' />
      
                  <label>
                    Description:</label>
                    <textarea className="form-control" ref='description' ></textarea>
      
      
                  <input className="btn btn-primary" type="submit" value="Submit" />
                  </div>
                </form>
              );
            }  
      }
      
      export default CustomerCreateUpdate;
      

      With the CustomerCreateUpdate component created, we can update the main App component to add links to the different components we've created.

      Step 9 — Updating the Main App Component

      In this section, we'll update the App component of our application to create links to the components we've created in the previous steps.

      From the frontend folder, run the following command to install the React Router, which allows you to add routing and navigation between various React components:

      • cd ~/djangoreactproject/frontend
      • npm install --save react-router-dom

      Next, open ~/djangoreactproject/frontend/src/App.js:

      • nano ~/djangoreactproject/frontend/src/App.js

      Delete everything that's there and add the following code to import the necessary classes for adding routing. These include BrowserRouter, which creates a Router component, and Route, which creates a route component:

      ~/djangoreactproject/frontend/src/App.js

      import  React, { Component } from  'react';
      import { BrowserRouter } from  'react-router-dom'
      import { Route, Link } from  'react-router-dom'
      import  CustomersList  from  './CustomersList'
      import  CustomerCreateUpdate  from  './CustomerCreateUpdate'
      import  './App.css';
      

      BrowserRouter keeps the UI in sync with the URL using the HTML5 history API.

      Next, create a base layout that provides the base component to be wrapped by the BrowserRouter component:

      ~/djangoreactproject/frontend/src/App.js

      ...
      
      const  BaseLayout  = () => (
      <div  className="container-fluid">
          <nav  className="navbar navbar-expand-lg navbar-light bg-light">
              <a  className="navbar-brand"  href="#">Django React Demo</a>
              <button  className="navbar-toggler"  type="button"  data-toggle="collapse"  data-target="#navbarNavAltMarkup"  aria-controls="navbarNavAltMarkup"  aria-expanded="false"  aria-label="Toggle navigation">
              <span  className="navbar-toggler-icon"></span>
          </button>
          <div  className="collapse navbar-collapse"  id="navbarNavAltMarkup">
              <div  className="navbar-nav">
                  <a  className="nav-item nav-link"  href="/">CUSTOMERS</a>
                  <a  className="nav-item nav-link"  href="http://www.digitalocean.com/customer">CREATE CUSTOMER</a>
              </div>
          </div>
          </nav>
          <div  className="content">
              <Route  path="/"  exact  component={CustomersList}  />
              <Route  path="/customer/:pk"  component={CustomerCreateUpdate}  />
              <Route  path="/customer/"  exact  component={CustomerCreateUpdate}  />
          </div>
      </div>
      )
      

      We use the Route component to define the routes of our application; the component the router should load once a match is found. Each route needs a path to specify the path to be matched and a component to specify the component to load. The exact property tells the router to match the exact path.

      Finally, create the App component, the root or top-level component of our React application:

      ~/djangoreactproject/frontend/src/App.js

      ...
      
      class  App  extends  Component {
      
      render() {
          return (
          <BrowserRouter>
              <BaseLayout/>
          </BrowserRouter>
          );
      }
      }
      export  default  App;
      

      We have wrapped the BaseLayout component with the BrowserRouter component since our app is meant to run in the browser.

      The completed file looks like this:

      ~/djangoreactproject/frontend/src/App.js

      import React, { Component } from 'react';
      import { BrowserRouter } from 'react-router-dom'
      import { Route, Link } from 'react-router-dom'
      
      import  CustomersList from './CustomersList'
      import  CustomerCreateUpdate  from './CustomerCreateUpdate'
      import './App.css';
      
      const BaseLayout = () => (
        <div className="container-fluid">
      <nav className="navbar navbar-expand-lg navbar-light bg-light">
        <a className="navbar-brand" href="#">Django React Demo</a>
        <button className="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNavAltMarkup" aria-controls="navbarNavAltMarkup" aria-expanded="false" aria-label="Toggle navigation">
          <span className="navbar-toggler-icon"></span>
        </button>
        <div className="collapse navbar-collapse" id="navbarNavAltMarkup">
          <div className="navbar-nav">
            <a className="nav-item nav-link" href="/">CUSTOMERS</a>
            <a className="nav-item nav-link" href="http://www.digitalocean.com/customer">CREATE CUSTOMER</a>
      
          </div>
        </div>
      </nav>  
      
          <div className="content">
            <Route path="/" exact component={CustomersList} />
            <Route path="/customer/:pk"  component={CustomerCreateUpdate} />
            <Route path="/customer/" exact component={CustomerCreateUpdate} />
      
          </div>
      
        </div>
      )
      
      class App extends Component {
        render() {
          return (
            <BrowserRouter>
              <BaseLayout/>
            </BrowserRouter>
          );
        }
      }
      
      export default App;
      

      After adding routing to our application, we are now ready to test the application. Navigate to http://localhost:3000. You should see the first page of the application:

      Application Home Page

      With this application in place, you now have the base for a CRM application.

      Conclusion

      In this tutorial, you created a demo application using Django and React. You used the Django REST framework to build the REST API, Axios to consume the API, and Bootstrap 4 to style your CSS. You can find the source code of this project in this GitHub repository.

      This tutorial setup used separate front-end and back-end apps. For a different approach to integrating React with Django, check this tutorial and this tutorial.

      For more information about building an application with Django, you can follow the Django development series. You can also look at the official Django docs.



      Source link

      How To Provision and Manage Remote Docker Hosts with Docker Machine on Ubuntu 18.04


      Introduction

      [Docker Machine](/) is a tool that makes it easy to provision and manage multiple Docker hosts remotely from your personal computer. Such servers are commonly referred to as Dockerized hosts and are used to run Docker containers.

      While Docker Machine can be installed on a local or a remote system, the most common approach is to install it on your local computer (native installation or virtual machine) and use it to provision Dockerized remote servers.

      Though Docker Machine can be installed on most Linux distributions as well as macOS and Windows, in this tutorial, you’ll install it on your local machine running Ubuntu 18.04 and use it to provision Dockerized DigitalOcean Droplets. If you don’t have a local Ubuntu 18.04 machine, you can follow these instructions on any Ubuntu 18.04 server.

      Prerequisites

      To follow this tutorial, you will need the following:

      • A local machine or server running Ubuntu 18.04 with Docker installed. See How To Install and Use Docker on Ubuntu 18.04 for instructions.
      • A DigitalOcean API token. If you don’t have one, generate it using this guide. When you generate a token, be sure that it has read-write scope. That is the default, so if you do not change any options while generating it, it will have read-write capabilities.

      Step 1 — Installing Docker Machine

      In order to use Docker Machine, you must first install it locally. On Ubuntu, this means downloading a handful of scripts from the official Docker repository on GitHub.

      To download and install the Docker Machine binary, type:

      • wget https://github.com/docker/machine/releases/download/v0.15.0/docker-machine-$(uname -s)-$(uname -m)

      The name of the file should be docker-machine-Linux-x86_64. Rename it to docker-machine to make it easier to work with:

      • mv docker-machine-Linux-x86_64 docker-machine

      Make it executable:

      Move or copy it to the /usr/local/bin directory so that it will be available as a system command:

      • sudo mv docker-machine /usr/local/bin

      Check the version, which will indicate that it's properly installed:

      You'll see output similar to this, displaying the version number and build:

      Output

      docker-machine version 0.15.0, build b48dc28d

      Docker Machine is installed. Let's install some additional helper tools to make Docker Machine easier to work with.

      Step 2 — Installing Additional Docker Machine Scripts

      There are three Bash scripts in the Docker Machine GitHub repository you can install to make working with the docker and docker-machine commands easier. When installed, these scripts provide command completion and prompt customization.

      In this step, you'll install these three scripts into the /etc/bash_completion.d directory on your local machine by downloading them directly from the Docker Machine GitHub repository.

      Note: Before downloading and installing a script from the internet in a system-wide location, you should inspect the script's contents first by viewing the source URL in your browser.

      The first script allows you to see the active machine in your prompt. This comes in handy when you are working with and switching between multiple Dockerized machines. The script is called docker-machine-prompt.bash. Download it

      • sudo wget https://raw.githubusercontent.com/docker/machine/master/contrib/completion/bash/docker-machine-prompt.bash -O /etc/bash_completion.d/docker-machine-prompt.bash

      To complete the installation of this file, you'll have to modify the value for the PS1 variable in your .bashrc file. The PS1 variable is a special shell variable used to modify the Bash command prompt. Open ~/.bashrc in your editor:

      Within that file, there are three lines that begin with PS1. They should look just like these:

      ~/.bashrc

      PS1='${debian_chroot:+($debian_chroot)}[33[01;32m]u@h[33[00m]:[33[01;34m]w[33[00m]$ '
      
      ...
      
      PS1='${debian_chroot:+($debian_chroot)}u@h:w$ '
      
      ...
      
      PS1="[e]0;${debian_chroot:+($debian_chroot)}u@h: wa]$PS1"
      

      For each line, insert $(__docker_machine_ps1 " [%s]") near the end, as shown in the following example:

      ~/.bashrc

      PS1='${debian_chroot:+($debian_chroot)}[33[01;32m]u@h[33[00m]:[33[01;34m]w[33[00m]$(__docker_machine_ps1 " [%s]")$ '
      
      ...
      
      PS1='${debian_chroot:+($debian_chroot)}u@h:w$(__docker_machine_ps1 " [%s]")$ '
      
      ...
      
      PS1="[e]0;${debian_chroot:+($debian_chroot)}u@h: wa]$(__docker_machine_ps1 " [%s]")$PS1"
      

      Save and close the file.

      The second script is called docker-machine-wrapper.bash. It adds a use subcommand to the docker-machine command, making it significantly easier to switch between Docker hosts. To download it, type:

      • sudo wget https://raw.githubusercontent.com/docker/machine/master/contrib/completion/bash/docker-machine-wrapper.bash -O /etc/bash_completion.d/docker-machine-wrapper.bash

      The third script is called docker-machine.bash. It adds bash completion for docker-machine commands. Download it using:

      • sudo wget https://raw.githubusercontent.com/docker/machine/master/contrib/completion/bash/docker-machine.bash -O /etc/bash_completion.d/docker-machine.bash

      To apply the changes you've made so far, close, then reopen your terminal. If you're logged into the machine via SSH, exit the session and log in again, and you'll have command completion for the docker and docker-machine commands.

      Let's test things out by creating a new Docker host with Docker Machine.

      Step 3 — Provisioning a Dockerized Host Using Docker Machine

      Now that you have Docker and Docker Machine running on your local machine, you can provision a Dockerized Droplet on your DigitalOcean account using Docker Machine's docker-machine create command. If you've not done so already, assign your DigitalOcean API token to an environment variable:

      • export DOTOKEN=your-api-token

      NOTE: This tutorial uses DOTOKEN as the bash variable for the DO API token. The variable name does not have to be DOTOKEN, and it does not have to be in all caps.

      To make the variable permanent, put it in your ~/.bashrc file. This step is optional, but it is necessary if you want to the value to persist across shell sessions.

      Open that file with nano:

      Add this line to the file:

      ~/.bashrc

      export DOTOKEN=your-api-token
      

      To activate the variable in the current terminal session, type:

      To call the docker-machine create command successfully you must specify the driver you wish to use, as well as a machine name. The driver is the adapter for the infrastructure you're going to create. There are drivers for cloud infrastructure providers, as well as drivers for various virtualization platforms.

      We'll use the digitalocean driver. Depending on the driver you select, you'll need to provide additional options to create a machine. The digitalocean driver requires the API token (or the variable that evaluates to it) as its argument, along with the name for the machine you want to create.

      To create your first machine, type this command to create a DigitalOcean Droplet called docker-01:

      • docker-machine create --driver digitalocean --digitalocean-access-token $DOTOKEN docker-01

      You'll see this output as Docker Machine creates the Droplet:

      Output

      ... Installing Docker... Copying certs to the local machine directory... Copying certs to the remote machine... Setting Docker configuration on the remote daemon... Checking connection to Docker... Docker is up and running! To see how to connect your Docker Client to the Docker Engine running on this virtual machine, run: docker-machine env ubuntu1804-docker

      Docker Machine creates an SSH key pair for the new host so it can access the server remotely. The Droplet is provisioned with an operating system and Docker is installed. When the command is complete, your Docker Droplet is up and running.

      To see the newly-created machine from the command line, type:

      The output will be similar to this, indicating that the new Docker host is running:

      Output

      NAME ACTIVE DRIVER STATE URL SWARM DOCKER ERRORS docker-01 - digitalocean Running tcp://209.97.155.178:2376 v18.06.1-ce

      Now let's look at how to specify the operating system when we create a machine.

      Step 4 — Specifying the Base OS and Droplet Options When Creating a Dockerized Host

      By default, the base operating system used when creating a Dockerized host with Docker Machine is supposed to be the latest Ubuntu LTS. However, at the time of this publication, the docker-machine create command is still using Ubuntu 16.04 LTS as the base operating system, even though Ubuntu 18.04 is the latest LTS edition. So if you need to run Ubuntu 18.04 on a recently-provisioned machine, you'll have to specify Ubuntu along with the desired version by passing the --digitalocean-image flag to the docker-machine create command.

      For example, to create a machine using Ubuntu 18.04, type:

      • docker-machine create --driver digitalocean --digitalocean-image ubuntu-18-04-x64 --digitalocean-access-token $DOTOKEN docker-ubuntu-1804

      You're not limited to a version of Ubuntu. You can create a machine using any operating system supported on DigitalOcean. For example, to create a machine using Debian 8, type:

      • docker-machine create --driver digitalocean --digitalocean-image debian-8-x64 --digitalocean-access-token $DOTOKEN docker-debian

      To provision a Dockerized host using CentOS 7 as the base OS, specify centos-7-0-x86 as the image name, like so:

      • docker-machine create --driver digitalocean --digitalocean-image centos-7-0-x64 --digitalocean-access-token $DOTOKEN docker-centos7

      The base operating system is not the only choice you have. You can also specify the size of the Droplet. By default, it is the smallest Droplet, which has 1 GB of RAM, a single CPU, and a 25 GB SSD.

      Find the size of the Droplet you want to use by looking up the corresponding slug in the DigitalOcean API documentation.

      For example, to provision a machine with 2 GB of RAM, two CPUs, and a 60 GB SSD, use the slug s-2vcpu-2gb:

      • docker-machine create --driver digitalocean --digitalocean-size s-2vcpu-2gb --digitalocean-access-token $DOTOKEN docker-03

      To see all the flags specific to creating a Docker Machine using the DigitalOcean driver, type:

      • docker-machine create --driver digitalocean -h

      Tip: If you refresh the Droplet page of your DigitalOcean dashboard, you will see the new machines you created using the docker-machine command.

      Now let's explore some of the other Docker Machine commands.

      Step 5 — Executing Additional Docker Machine Commands

      You've seen how to provision a Dockerized host using the create subcommand, and how to list the hosts available to Docker Machine using the ls subcommand. In this step, you'll learn a few more useful subcommands.

      To obtain detailed information about a Dockerized host, use the inspect subcommand, like so:

      • docker-machine inspect docker-01

      The output includes lines like the ones in the following output. The Image line reveals the version of the Linux distribution used and the Size line indicates the size slug:

      Output

      ... { "ConfigVersion": 3, "Driver": { "IPAddress": "203.0.113.71", "MachineName": "docker-01", "SSHUser": "root", "SSHPort": 22, ... "Image": "ubuntu-16-04-x64", "Size": "s-1vcpu-1gb", ... }, ---

      To print the connection configuration for a host, type:

      • docker-machine config docker-01

      The output will be similar to this:

      Output

      --tlsverify --tlscacert="/home/kamit/.docker/machine/certs/ca.pem" --tlscert="/home/kamit/.docker/machine/certs/cert.pem" --tlskey="/home/kamit/.docker/machine/certs/key.pem" -H=tcp://203.0.113.71:2376

      The last line in the output of the docker-machine config command reveals the IP address of the host, but you can also get that piece of information by typing:

      • docker-machine ip docker-01

      If you need to power down a remote host, you can use docker-machine to stop it:

      • docker-machine stop docker-01

      Verify that it is stopped:

      The output shows that the status of the machine has changed:

      Ouput

      NAME ACTIVE DRIVER STATE URL SWARM DOCKER ERRORS docker-01 - digitalocean Stopped Unknown

      To start it again, use the start subcommand:

      • docker-machine start docker-01

      Then review its status again:

      You will see that the STATE is now set Running for the host:

      Ouput

      NAME ACTIVE DRIVER STATE URL SWARM DOCKER ERRORS docker-01 - digitalocean Running tcp://203.0.113.71:2376 v18.06.1-ce

      Next let's look at how to interact with the remote host using SSH.

      Step 6 — Executing Commands on a Dockerized Host via SSH

      At this point, you've been getting information about your machines, but you can do more than that. For example, you can execute native Linux commands on a Docker host by using the ssh subcommand of docker-machine from your local system. This section explains how to perform ssh commands via docker-machine as well as how to open an SSH session to a Dockerized host.

      Assuming that you've provisioned a machine with Ubuntu as the operating system, execute the following command from your local system to update the package database on the Docker host:

      • docker-machine ssh docker-01 apt-get update

      You can even apply available updates using:

      • docker-machine ssh docker-01 apt-get upgrade

      Not sure what kernel your remote Docker host is using? Type the following:

      • docker-machine ssh docker-01 uname -r

      Finally, you can log in to the remote host with the docker machine ssh command:

      docker-machine ssh docker-01
      

      You'll be logged in as the root user and you'll see something similar to the following:

      Welcome to Ubuntu 16.04.5 LTS (GNU/Linux 4.4.0-131-generic x86_64)
      
       * Documentation:  https://help.ubuntu.com
       * Management:     https://landscape.canonical.com
       * Support:        https://ubuntu.com/advantage
      
        Get cloud support with Ubuntu Advantage Cloud Guest:
          http://www.ubuntu.com/business/services/cloud
      
      14 packages can be updated.
      10 updates are security updates.
      

      Log out by typing exit to return to your local machine.

      Next, we'll direct Docker's commands at our remote host.

      Step 7 — Activating a Dockerized Host

      Activating a Docker host connects your local Docker client to that system, which makes it possible to run normal docker commands on the remote system.

      First, use Docker Machine to create a new Docker host called docker-ubuntu using Ubuntu 18.04:

      • docker-machine create --driver digitalocean --digitalocean-image ubuntu-18-04-x64 --digitalocean-access-token $DOTOKEN docker-ubuntu

      To activate a Docker host, type the following command:

      • eval $(docker-machine env machine-name)

      Alternatively, you can activate it by using this command:

      • docker-machine use machine-name

      Tip When working with multiple Docker hosts, the docker-machine use command is the easiest method of switching from one to the other.

      After typing any of these commands, your prompt will change to indicate that your Docker client is pointing to the remote Docker host. It will take this form. The name of the host will be at the end of the prompt:

      username@localmachine:~ [docker-01]$
      

      Now any docker command you type at this command prompt will be executed on that remote host.

      Execute docker-machine ls again:

      You'll see an asterisk under the ACTIVE column for docker-01:

      Output

      NAME ACTIVE DRIVER STATE URL SWARM DOCKER ERRORS docker-01 * digitalocean Running tcp://203.0.113.71:2376 v18.06.1-ce

      To exit from the remote Docker host, type the following:

      Your prompt will no longer show the active host.

      Now let's create containers on the remote machine.

      Step 8 — Creating Docker Containers on a Remote Dockerized Host

      So far, you have provisioned a Dockerized Droplet on your DigitalOcean account and you've activated it — that is, your Docker client is pointing to it. The next logical step is to spin up containers on it. As an example, let's try running the official Nginx container.

      Use docker-machine use to select your remote machine:

      • docker-machine use docker-01

      Now execute this command to run an Nginx container on that machine:

      • docker run -d -p 8080:80 --name httpserver nginx

      In this command, we're mapping port 80 in the Nginx container to port 8080 on the Dockerized host so that we can access the default Nginx page from anywhere.

      Once the container builds, you will be able to access the default Nginx page by pointing your web browser to http://docker_machine_ip:8080.

      While the Docker host is still activated (as seen by its name in the prompt), you can list the images on that host:

      The output includes the Nginx image you just used:

      Output

      REPOSITORY TAG IMAGE ID CREATED SIZE nginx latest 71c43202b8ac 3 hours ago 109MB

      You can also list the active or running containers on the host:

      If the Nginx container you ran in this step is the only active container, the output will look like this:

      Output

      CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES d3064c237372 nginx "nginx -g 'daemon of…" About a minute ago Up About a minute 0.0.0.0:8080->80/tcp httpserver

      If you intend to create containers on a remote machine, your Docker client must be pointing to it — that is, it must be the active machine in the terminal that you're using. Otherwise you'll be creating the container on your local machine. Again, let your command prompt be your guide.

      Docker Machine can create and manage remote hosts, and it can also remove them.

      Step 9 – Removing Docker Hosts

      You can use Docker Machine to remove a Docker host you've created. Use the docker-machine rm command to remove the docker-01 host you created:

      • docker-machine rm docker-01

      The Droplet is deleted along with the SSH key created for it. List the hosts again:

      This time, you won't see the docker-01 host listed in the output. And if you've only created one host, you won't see any output at all.

      Be sure to execute the command docker-machine use -u to point your local Docker daemon back to your local machine.

      Step 10 — Disabling Crash Reporting (Optional)

      By default, whenever an attempt to provision a Dockerized host using Docker Machine fails, or Docker Machine crashes, some diagnostic information is sent to a Docker account on Bugsnag. If you're not comfortable with this, you can disable the reporting by creating an empty file called no-error-report in your local computer's .docker/machine directory.

      To create the file, type:

      • touch ~/.docker/machine/no-error-report

      Check the file for error messages if provisioning fails or Docker Machine crashes.

      Conclusion

      You've installed Docker Machine and used it to provision multiple Docker hosts on DigitalOcean remotely from your local system. From here you should be able to provision as many Dockerized hosts on your DigitalOcean account as you need.

      For more on Docker Machine, visit the official documentation page. The three Bash scripts downloaded in this tutorial are hosted on this GitHub page.



      Source link

      How to Manage an SQL Database


      Introduction

      SQL databases come installed with all the commands you need to add, modify, delete, and query your data. This cheat sheet-style guide provides a quick reference to some of the most commonly-used SQL commands.

      How to Use This Guide:

      • This guide is in cheat sheet format with self-contained command-line snippets
      • Jump to any section that is relevant to the task you are trying to complete
      • When you see highlighted text in this guide’s commands, keep in mind that this text should refer to the columns, tables, and data in your own database.
      • Throughout this guide, the example data values given are all wrapped in apostrophes ('). In SQL, it is necessary to wrap any data values that consist of strings in apostrophes. This isn’t required for numeric data, but it also won’t cause any issues if you do include apostrophes.

      Please note that, while SQL is recognized as a standard, most SQL database programs have their own proprietary extensions. This guide uses MySQL as the example relational database management system (RDBMS), but the commands given will work with other relational database programs, including PostgreSQL, MariaDB, and SQLite. Where there are significant differences between RDBMSs, we have included the alternative commands.

      Opening up the Database Prompt (using Socket/Trust Authentication)

      By default on Ubuntu 18.04, the root MySQL user can authenticate without a password using the following command:

      To open up a PostgreSQL prompt, use the following command. This example will log you in as the postgres user, which is the included superuser role, but you can replace that with any already-created role:

      Opening up the Database Prompt (using Password Authentication)

      If your root MySQL user is set to authenticate with a password, you can do so with the following command:

      If you've already set up a non-root user account for your database, you can also use this method to log in as that user:

      The above command will prompt you for your password after you run it. If you'd like to supply your password as part of the command, immediately follow the -p option with your password, with no space between them:

      Creating a Database

      The following command creates a database with default settings.

      • CREATE DATABASE database_name;

      If you want your database to use a character set and collation different than the defaults, you can specify those using this syntax:

      • CREATE DATABASE database_name CHARACTER SET character_set COLLATE collation;

      Listing Databases

      To see what databases exist in your MySQL or MariaDB installation, run the following command:

      In PostgreSQL, you can see what databases have been created with the following command:

      Deleting a Database

      To delete a database, including any tables and data held within it, run a command that follows this structure:

      • DROP DATABASE IF EXISTS database;

      Creating a User

      To create a user profile for your database without specifying any privileges for it, run the following command:

      • CREATE USER username IDENTIFIED BY 'password';

      PostgreSQL uses a similar, but slightly different, syntax:

      • CREATE USER user WITH PASSWORD 'password';

      If you want to create a new user and grant them privileges in one command, you can do so by issuing a GRANT statement. The following command creates a new user and grants them full privileges to every database and table in the RDBMS:

      • GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

      Deleting a User

      Use the following syntax to delete a database user profile:

      • DROP USER IF EXISTS username;

      Note that this command will not by default delete any tables created by the deleted user, and attempts to access such tables may result in errors.

      Selecting a Database

      Before you can create a table, you first have to tell the RDBMS the database in which you'd like to create it. In MySQL and MariaDB, do so with the following syntax:

      In PostgreSQL, you must use the following command to select your desired database:

      Creating a Table

      The following command structure creates a new table with the name table, and includes two columns, each with their own specific data type:

      • CREATE TABLE table ( column_1 column_1_data_type, column_2 column_2_data_taype );

      Deleting a Table

      To delete a table entirely, including all its data, run the following:

      • DROP TABLE IF EXISTS table

      Inserting Data into a Table

      Use the following syntax to populate a table with one row of data:

      • INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_A', 'data_B', 'data_C' );

      You can also populate a table with multiple rows of data using a single command, like this:

      • INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_1A', 'data_1B', 'data_1C' ), ( 'data_2A', 'data_2B', 'data_2C' ), ( 'data_3A', 'data_3B', 'data_3C' );

      Deleting Data from a Table

      To delete a row of data from a table, use the following command structure. Note that value should be the value held in the specified column in the row that you want to delete:

      • DELETE FROM table WHERE column='value';

      Note: If you do not include a WHERE clause in a DELETE statement, as in the following example, it will delete all the data held in a table, but not the columns or the table itself:

      Changing Data in a Table

      Use the following syntax to update the data held in a given row. Note that the WHERE clause at the end of the command tells SQL which row to update. value is the value held in column_A that aligns with the row you want to change.

      Note: If you fail to include a WHERE clause in an UPDATE statement, the command will replace the data held in every row of the table.

      • UPDATE table SET column_1 = value_1, column_2 = value_2 WHERE column_A=value;

      Inserting a Column

      The following command syntax will add a new column to a table:

      • ALTER TABLE table ADD COLUMN column data_type;

      Deleting a Column

      A command following this structure will delete a column from a table:

      • ALTER TABLE table DROP COLUMN column;

      Performing Basic Queries

      To view all the data from a single column in a table, use the following syntax:

      • SELECT column FROM table;

      To query multiple columns from the same table, separate the column names with a comma:

      • SELECT column_1, column_2 FROM table;

      You can also query every column in a table by replacing the names of the columns with an asterisk (*). In SQL, asterisks act as placeholders to represent “all”:

      Using WHERE Clauses

      You can narrow down the results of a query by appending the SELECT statement with a WHERE clause, like this:

      • SELECT column FROM table WHERE conditions_that_apply;

      For example, you can query all the data from a single row with a syntax like the following. Note that value should be a value held in both the specified column and the row you want to query:

      • SELECT * FROM table WHERE column = value;

      Working with Comparison Operators

      A 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

      Working with Wildcards

      SQL allows the use of wildcard characters. 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.

      Asterisks (*) are placeholders that represent “all,” this will query every column in a table:

      Percentage signs (%) represent zero or more unknown characters.

      • SELECT * FROM table WHERE column LIKE val%;

      Underscores (_) are used to represent a single unknown character:

      • SELECT * FROM table WHERE column LIKE v_lue;

      Counting Entries in a Column

      The COUNT function is used to find the number of entries in a given column. The following syntax will return the total number of values held in column:

      • SELECT COUNT(column) FROM table;

      You can narrow down the results of a COUNT function by appending a WHERE clause, like this:

      • SELECT COUNT(column) FROM table WHERE column=value;

      Finding the Average Value in a Column

      The AVG function is used to find the average (in this case, the mean) amongst values held in a specific column. Note that the AVG function will only work with columns holding numeric values; when used on a column holding string values, it may return an error or 0:

      • SELECT AVG(column) FROM table;

      Finding the Sum of Values in a Column

      The SUM function is used to find the sum total of all the numeric values held in a column:

      • SELECT SUM(column) FROM table;

      As with the AVG function, if you run the SUM function on a column holding string values it may return an error or just 0, depending on your RDBMS.

      Finding the Largest Value in a Column

      To find the largest numeric value in a column or the last value alphabetically, use the MAX function:

      • SELECT MAX(column) FROM table;

      Finding the Smallest Value in a Column

      To find the smallest numeric value in a column or the first value alphabetically, use the MIN function:

      • SELECT MIN(column) FROM table;

      Sorting Results with ORDER BY Clauses

      An ORDER BY clause is used to sort query results. The following query syntax returns the values from column_1 and column_2 and sorts the results by the values held in column_1 in ascending order or, for string values, in alphabetical order:

      • SELECT column_1, column_2 FROM table ORDER BY column_1;

      To perform the same action, but order the results in descending or reverse alphabetical order, append the query with DESC:

      • SELECT column_1, column_2 FROM table ORDER BY column_1 DESC;

      Sorting Results with GROUP BY Clauses

      The GROUP BY clause is similar to the ORDER BY clause, but it is used to sort the results of a query that includes an aggregate function such as COUNT, MAX, MIN, or SUM. On their own, the aggregate functions described in the previous section will only return a single value. However, you can view the results of an aggregate function performed on every matching value in a column by including a GROUP BY clause.

      The following syntax will count the number of matching values in column_2 and group them in ascending or alphabetical order:

      • SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2;

      To perform the same action, but group the results in descending or reverse alphabetical order, append the query with DESC:

      • SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2 DESC;

      Querying Multiple Tables with JOIN Clauses

      JOIN clauses are used to create result-sets that combine rows from two or more tables. A JOIN clause will only work if the two tables each have a column with an identical name and data type, as in this example:

      • SELECT table_1.column_1, table_2.column_2 FROM table_1 JOIN table_2 ON table_1.common_column=table_2.common_column;

      This is an example of an INNER JOIN clause. An INNER JOIN will return all the records that have matching values in both tables, but won't show any records that don't have matching values.

      It's possible to return all the records from one of two tables, including values that do not have a corresponding match in the other table, by using an outer JOIN clause. Outer JOIN clauses are written as either LEFT JOIN or RIGHT 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 JOIN clauses, the left table is the one referenced in the FROM clause, and the right table is any other table referenced after the JOIN statement. The following will show every record from table_1 and only the matching values from table_2. Any values that do not have a match in table_2 will appear as NULL in the result-set:

      • SELECT table_1.column_1, table_2.column_2 FROM table_1 LEFT JOIN table_2 ON table_1.common_column=table_2.common_column;

      A RIGHT JOIN clause functions the same as a LEFT JOIN, but it prints the all the results from the right table, and only the matching values from the left:

      • SELECT table_1.column_1, table_2.column_2 FROM table_1 RIGHT JOIN table_2 ON table_1.common_column=table_2.common_column;

      Combining Multiple SELECT Statements with UNION Clauses

      A UNION operator is useful for combining the results of two (or more) SELECT statements into a single result-set:

      • SELECT column_1 FROM table UNION SELECT column_2 FROM table;

      Additionally, the UNION clause can combine two (or more) SELECT statements querying different tables into the same result-set:

      • SELECT column FROM table_1 UNION SELECT column FROM table_2;

      Conclusion

      This guide covers some of the more common commands in SQL used to manage databases, users, and tables, and query the contents held in those tables. There are, however, many combinations of clauses and operators that all produce unique result-sets. If you're looking for a more comprehensive guide to working with SQL, we encourage you to check out Oracle's Database SQL Reference.

      Additionally, if there are common SQL commands you'd like to see in this guide, please ask or make suggestions in the comments below.



      Source link