One place for hosting & domains

      Ubuntu

      How To Install and Use SQLite on Ubuntu 20.04


      How To Install and Use SQLite on Ubuntu 20.04

      The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program.

      Introduction

      SQLite is a free, cross-platform database management system. It is popular for its efficiency and ability to interface with many different programming languages.

      In this tutorial you will install SQLite on Ubuntu 20.04. You will then create a database, read data from it, insert items, delete items, and join items from separate tables.

      Note: This tutorial includes practical instructions for installing and using SQLite. It does not cover larger conceptual issues and production concerns, such as when one should, or should not, consider using a SQLite database. For an excellent overview of popular relational databases and how they compare, check out our article, SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.

      Additionally, many languages maintain integrations with SQLite. For instructions on using SQLite inside your Python code, check out our tutorial, How To Use the sqlite3 Module in Python 3.

      Prerequisites

      To complete this tutorial, you will need:

      Step 1 — Installing SQLite on Ubuntu 20.04

      To install the SQLite command-line interface on Ubuntu, first update your package list:

      Now install SQLite:

      • sudo apt-get install sqlite3

      To verify the installation, check the software’s version:

      You will receive an output like this:

      Output

      3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1

      With SQLite installed, you are now ready to create a new database.

      Step 2 — Creating a SQLite Database

      In this step you will create a database containing different sharks and their attributes. To create the database, open your terminal and run this sqlite3 command:

      sqlite3 sharks.db
      

      This will create a new database named sharks. If the file sharks.db already exists, SQLite will open a connection to it; if it does not exist, SQLite will create it.

      You will receive an output like this:

      SQLite version 3.31.1 2020-01-27 19:55:54
      Enter ".help" for usage hints.
      

      Following this, your prompt will change. A new prefix, sqlite>, now appears:

      With your Shark database created, you will now create a new table and populate it with data.

      Step 3 — Creating a SQLite Table

      SQLite databases are organized into tables. Tables store information. To better visualize a table, one can imagine rows and columns.

      The rest of this tutorial will follow a common convention for entering SQLite commands. SQLite commands are uppercase and user information is lowercase. Lines must end with a semi-colon.

      Now let’s create a table and some columns for various data:

      • An ID
      • The shark’s name
      • The shark’s type
      • The shark’s average length (in centimeters)

      Use the following command to create the table:

      • CREATE TABLE sharks(id integer NOT NULL, name text NOT NULL, sharktype text NOT NULL, length integer NOT NULL);

      Using NOT NULL makes that field required. We will discuss NOT NULL in greater detail in the next section.

      After creating the table, an empty prompt will return. Now let’s insert some values into it.

      Inserting Values into Tables

      In SQLite, the command for inserting values into a table follows this general form:

      INSERT INTO tablename VALUES(values go here);

      Where tablename is the name of your table, and values are within parentheses.

      Now insert three rows of VALUES into your sharks table:

      • INSERT INTO sharks VALUES (1, "Sammy", "Greenland Shark", 427);
      • INSERT INTO sharks VALUES (2, "Alyoshka", "Great White Shark", 600);
      • INSERT INTO sharks VALUES (3, "Himari", "Megaladon", 1800);

      Because you earlier specified NOT NULL for each of the variables in your table, you must enter a value for each.

      For example, try adding another shark without setting its length:

      • INSERT INTO sharks VALUES (4, "Faiza", "Hammerhead Shark");

      You will receive this error:

      Output

      Error: table sharks has 4 columns but 3 values were supplied

      In this step you created a table and inserted values into it. In the next step you will read from your database table.

      Step 4 — Reading Tables in SQLite

      In this step, we will focus on the most basic methods of reading data from a table. Recognize that SQLite provides more specific methods for viewing data in tables.

      To view your table with all of the inserted values, use SELECT:

      You will see the previously inserted entries:

      Output

      1|Sammy|Greenland Shark|427 2|Alyoshka|Great White Shark|600 3|Himari|Megaladon|1800

      To view an entry based on its id (The values we set manually), add the WHERE command to your query:

      • SELECT * FROM sharks WHERE id IS 1;

      This will return the shark whose id equals 1:

      Output

      1|Sammy|Greenland Shark|427

      Let’s take a closer look at this command.

      1. First, we SELECT all (*) values from our database, sharks.
      2. Then we look at all id values.
      3. Then we return all table entries where id is equal to 1.

      So far you have created a table, inserted data into it, and queried that saved data. Now you will update the existing table.

      Step 5 — Updating Tables in SQLite

      In the following two sections you will first add a new column into your existing table and then update existing values in the table.

      Adding Columns to SQLite Tables

      SQLite allows you to change your table using the ALTER TABLE command. This means that you can create new rows and columns, or modify existing rows and columns.

      Use ALTER TABLE to create a new column. This new column will track each shark’s age in years:

      • ALTER TABLE sharks ADD COLUMN age integer;

      You now have a fifth column, age.

      Updating Values in SQLite Tables

      Using the UPDATE command, add new age values for each of your sharks:

      • UPDATE sharks SET age = 272 WHERE id=1;
      • UPDATE sharks SET age = 70 WHERE id=2;
      • UPDATE sharks SET age = 40 WHERE id=3;

      Output

      1|Sammy|Greenland Shark|427|272 2|Alyoshka|Great White Shark|600|70 3|Himari|Megaladon|1800|40

      In this step you altered your table’s composition and then updated values inside the table. In the next step you will delete information from a table.

      Step 6 — Deleting Information in SQLite

      In this step you will delete entries in your table based on the evaluation of an argument.

      In the following command you are querying your database and requesting that that it delete all sharks in your sharks table whose age is less than 200:

      • DELETE FROM sharks WHERE age <= 200;

      Typing SELECT * FROM sharks; will verify that Alyoshka and Himari, who were each less than 200 years old, were deleted. Only Sammy the Greenland Shark remains:

      Output

      1|Sammy|Greenland Shark|427|272

      Step 7 — Joining Information in SQLite

      Let’s imagine that we had two tables: our current sharks table and an endangered table. Now what if the endangered table had an id value that mapped to the ids in your sharks table, and it also had a status value that indicated each shark’s conservation status?

      If you wanted to query data from both tables, you could use one of SQLite’s four join commands:

      • INNER JOIN
      • OUTER JOIN
      • LEFT JOIN
      • CROSS JOIN

      Let’s create that second table and then use INNER JOIN to join some data.

      First, create your endangered table:

      • CREATE TABLE endangered (id integer NOT NULL, status text NOT NULL);
      • INSERT INTO endangered VALUES (1, "near threatened");

      Now join your tables:

      SELECT * FROM sharks INNER JOIN endangered on sharks.id = endangered.id;

      Your output will look like this:

      Output

      1|Sammy|Greenland Shark|427|272|1|near threatened

      Note that the output also includes the id value from endangered. You can specify desired output with a more explicit command:

      • SELECT sharks.id, sharks.name, sharks.sharktype, sharks.length, sharks.age, endangered.status FROM sharks INNER JOIN endangered on sharks.id = endangered.id;

      This time the output excludes the second id value:

      Output

      1|Sammy|Greenland Shark|427|272|near threatened

      You have now successfully joined information from multiple tables.

      Conclusion

      SQLite is a useful tool for database management. One can quickly create a database and manipulate it with various commands. Following this tutorial, you now have a basic understanding of SQLite and you are prepared dive deeper into this database management system.

      For an excellent overview of Relational Databases systems and how they compare, check out our article, SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.

      Additionally, many languages maintain integrations with SQLite. For instructions on using SQLite inside your Python code, check out our tutorial, How To Use the sqlite3 Module in Python 3.

      For specific help with SQLite’s syntax, the official documentation is another excellent resource.



      Source link

      How to Optimize WordPress on Ubuntu 20.04


      Introduction

      Optimizing WordPress installations gives the clients and individuals who use your sites the performance, speed, and flexibility they’ve come to expect with WordPress. Whether you’re managing a personal site or a suite of installations for various clients, taking the time to optimize your WordPress installations increases efficiency and performance.

      In this tutorial, you’ll explore how to optimize WordPress installations in a way that’s built for scale, including guidance on configuration, speed, and overall performance.

      Prerequisites

      This is a conceptual article sharing different ways to approach optimization of a WordPress installation on Ubuntu 20.04. While this tutorial references the use of a managed solution via our WordPress 1-Click App, there are many different starting points, including:

      Whichever you choose, this tutorial will start with the assumption that you have or are prepared to install a fully-working WordPress installation configured with an administrative user on Ubuntu 20.04.

      Step 1 — Consider Your Installation

      During the installation and creation of your WordPress installation there are a few variables to take into account, including the location of your potential users, the scope of your WordPress site or suite of sites, and the maintenance and security preferences set that allow your site to be continually optimized. Taking the time to dive into each thoughtfully before building out your site will save time and benefit your WordPress installation as it grows.

      Considering Your Site’s Potential

      The first step in optimizing your WordPress site is to have a deep understanding of how you intend to use and grow your site. Will it be one site, or a network of sites? Is your site a static or dynamic website? Answering these questions before setting up your installation can inform some of your initial decisions regarding hosting, storage size, and performance.

      For example, if you’d like to build a personal blog, caching and optimizing images and visual content is important to consider. If you intend to create a community or ecommerce site with concurrent visitors and frequently changing data, considerations for server resources should be made. Being thoughtful about your intention for your WordPress installation from the start will guide the usefulness of security and performance tweaks made to your site, and lend to an overall more efficient installation.

      Optimizing Installation Preferences

      There are a few preferences that are important to consider while installing WordPress that can reduce latency and increase performance on your site.

      Hosting and Included Software

      First, select a hosting provider that provides the latest WordPress, Apache, MySQL, and PHP software with firewall and SSL certificate capabilities. A reliable and modern hosting provider will give you the best start for your LAMP stack installation. With shared hosting, be aware of server usage and customers per server to ensure the best performance for your site. Choosing the right hosting provider for your needs will help you prevent downtime and performance errors.

      Location and Latency

      Be aware of the location of your servers or datacenters when starting a new WordPress installation, and choose the location that best suits the need of your site and general location of your visitors and users. Latency, the time it takes for data to be transmitted between your site and users, fluctuates based on location. The WordPress documentation on site analytic tools explains how to track visitor location data, as well as the number of visits to your site. Having an idea from the start about where your visitors are from can help determine where to host your site and provide them with a faster browsing experience.

      Step 2 — Consider Your Theme

      There are a wide range of available themes that can be used or customized for WordPress. Many themes can be configured with user-friendly drag and drop interfaces, integrated with custom plugins and more. When setting up your WordPress site, it’s a good idea to initially consider only the essential features that you’ll use for the lifecycle of your site, adding more as you grow.

      Optimizing Theme Configuration

      Starting with a lightweight theme can help your installation to load more efficiently. A theme will require fewer database calls and by keeping your site free of unnecessary code, your users will have fewer delays in site speed and performance.

      For any theme selected, be sure to turn off or disable any features offered with the theme that you won’t need or use. These can be preferences offered in the Appearance section of the WordPress dashboard, typically under Theme Editor or Customize. Turning off features you don’t use reduces the number of requests and calls happening to query for data in the background.

      While there are a number of free and paid options for WordPress themes available online, many use page builders that add excess shortcode and unused code that will affect the performance of your site. Consider your use case when deciding whether or not to use a page builder, as they typically include a lot of extra processes that will have an impact on your site’s speed.

      Considering Plugin Use

      WordPress plugins offer extended functionality for WordPress installations through added code that allows users to customize their installations to suit their specific needs. There are over 56,000 currently available plugins, making them an appealing way to add additional features to a WordPress site.

      While plugins can increase the efficiency of your site, care should be taken in selecting quality plugins that are maintained and updated regularly. Because many plugins not only add code to your site but entries to your WordPress installation’s database, using too many plugins may cause site speed issues over time.

      Step 3 — Optimize for Security and Performance

      Once you have installed all of the plugins, widgets, and additional features you’d like to add to your WordPress installation, there are a few more optimization options to try within the WordPress dashboard that could positively impact your site’s speed and performance.

      Tweaking WordPress Settings

      First, be sure to change your site’s login address. Because most WordPress administrative login pages end in /wp-admin, this page is often prone to attacks. There are a number of tools available that enable you to change your login URL — be sure to select the one that works best for your use case.

      Next, consider the Site Health tool, located in the Tools section of your WordPress dashboard:

      picture of WordPress Site Health page

      Consider the results shown, and follow the instructions found in each dropdown on the Status tab to improve security or performance as mentioned within the tabs.

      Using the built-in configuration offered in the WordPress dashboard ensures that you’ve covered all of the readily available configuration tweaks for your installation.

      Caching for Site Speed

      Caching can also help improve your WordPress site’s performance and speed. Caching, a core design feature of the HTTP protocol meant to minimize network traffic while improving the perceived responsiveness of the system as a whole, can be used to help minimize load times when implemented on your site. WordPress offers a number of caching plugins that are helpful in maintaining a snapshot of your site to serve static HTML elements, reducing the amount of PHP calls and improving page load speed.

      Conclusion

      In this tutorial you explored a number of different techniques that you can use to make your WordPress installation on Ubuntu 20.04 faster and more efficient. Following the suggestions in this tutorial will help ensure that your site’s performance isn’t an issue as you grow in users and content on your site.

      To learn more about some of the security practices and WordPress optimization tips that are mentioned in this guide, visit our tutorial, “How To Configure Secure Updates and Installations in WordPress on Ubuntu 20.04”.



      Source link

      How To Deploy a React Application with Nginx on Ubuntu 20.04


      The author selected Creative Commons to receive a donation as part of the Write for DOnations program.

      Introduction

      You can quickly deploy React applications to a server using the default Create React App build tool. The build script compiles the application into a single directory containing all of the JavaScript code, images, styles, and HTML files. With the assets in a single location, you can deploy to a web server with minimal configuration.

      In this tutorial, you’ll deploy a React application on your local machine to an Ubuntu 20.04 server running Nginx. You’ll build an application using Create React App, use an Nginx config file to determine where to deploy files, and securely copy the build directory and its contents to the server. By the end of this tutorial, you’ll be able to build and deploy a React application.

      Prerequisites

      • On your local machine, you will need a development environment running Node.js; this tutorial was tested on Node.js version 10.22.0 and npm version 6.14.6. To install this on macOS or Ubuntu 20.04, follow the steps in How to Install Node.js and Create a Local Development Environment on macOS or the Installing Using a PPA section of How To Install Node.js on Ubuntu 20.04.

      • One Ubuntu 20.04 server for deployment, set up by following this initial server setup for Ubuntu 20.04 tutorial, including a sudo-enabled non-root user, a firewall, and SSH access from your local machine. To gain SSH access on a DigitalOcean Droplet, read through How to Connect to Droplets with SSH.

      • A registered domain name. This tutorial will use your_domain throughout. You can purchase a domain name from Namecheap, get one for free with Freenom, or use the domain registrar of your choice.

      • Both of the following DNS records set up for your server. If you are using DigitalOcean, please see our DNS documentation for details on how to add them.

        • An A record with your_domain pointing to your server’s public IP address.
        • An A record with www.your_domain pointing to your server’s public IP address.
      • Nginx installed by following How To Install Nginx on Ubuntu 20.04. Be sure that you have a server block for your domain. This tutorial will use /etc/nginx/sites-available/your_domain as an example.

      • It is recommended that you also secure your server with an HTTPS certificate. You can do this with the How To Secure Nginx with Let’s Encrypt on Ubuntu 20.04 tutorial.

      • You will also need a basic knowledge of JavaScript, HTML, and CSS, which you can find in our How To Build a Website With HTML series, How To Build a Website With CSS series, and in How To Code in JavaScript.

      Step 1 — Creating a React Project

      In this step, you’ll create an application using Create React App and build a deployable version of the boilerplate app.

      To start, create a new application using Create React App in your local environment. In a terminal, run the command to build an application. In this tutorial, the project will be called react-deploy:

      • npx create-react-app react-deploy

      The npx command will run a Node package without downloading it to your machine. The create-react-app script will install all of the dependencies needed for your React app and will build a base project in the react-deploy directory. For more on Create React App, check out out the tutorial How To Set Up a React Project with Create React App.

      The code will run for a few minutes as it downloads and installs the dependencies. When it is complete, you will receive a success message. Your version may be slightly different if you use yarn instead of npm:

      Output

      Success! Created react-deploy at your_file_path/react-deploy Inside that directory, you can run several commands: npm start Starts the development server. npm build Bundles the app into static files for production. npm test Starts the test runner. npm eject Removes this tool and copies build dependencies, configuration files and scripts into the app directory. If you do this, you can’t go back! We suggest that you begin by typing: cd react-deploy npm start Happy hacking!

      Following the suggestion in the output, first move into the project folder:

      Now that you have a base project, run it locally to test how it will appear on the server. Run the project using the npm start script:

      When the command runs, you’ll receive output with the local server info:

      Output

      Compiled successfully! You can now view react-deploy in the browser. Local: http://localhost:3000 On Your Network: http://192.168.1.110:3000 Note that the development build is not optimized. To create a production build, use npm build.

      Open a browser and navigate to http://localhost:3000. You will be able to access the boilerplate React app:

      React project template running locally

      Stop the project by entering either CTRL+C or ⌘+C in a terminal.

      Now that you have a project that runs successfully in a browser, you need to create a production build. Run the create-react-app build script with the following:

      This command will compile the JavaScript and assets into the build directory. When the command finishes, you will receive some output with data about your build. Notice that the filenames include a hash, so your output will be slightly different:

      Output

      Creating an optimized production build... Compiled successfully. File sizes after gzip: 41.21 KB build/static/js/2.82f639e7.chunk.js 1.4 KB build/static/js/3.9fbaa076.chunk.js 1.17 KB build/static/js/runtime-main.1caef30b.js 593 B build/static/js/main.e8c17c7d.chunk.js 546 B build/static/css/main.ab7136cd.chunk.css The project was built assuming it is hosted at /. You can control this with the homepage field in your package.json. The build folder is ready to be deployed. You may serve it with a static server: serve -s build Find out more about deployment here: https://cra.link/deployment

      The build directory will now include compiled and minified versions of all the files you need for your project. At this point, you don’t need to worry about anything outside of the build directory. All you need to do is deploy the directory to a server.

      In this step, you created a new React application. You verified that the application runs locally and you built a production version using the Create React App build script. In the next step, you’ll log onto your server to learn where to copy the build directory.

      Step 2 — Determining Deployment File Location on your Ubuntu Server

      In this step, you’ll start to deploy your React application to a server. But before you can upload the files, you’ll need to determine the correct file location on your deployment server. This tutorial uses Nginx as a web server, but the approach is the same with Apache. The main difference is that the configuration files will be in a different directory.

      To find the directory the web server will use as the root for your project, log in to your server using ssh:

      Once on the server, look for your web server configuration in /etc/nginx/sites-enabled. There is also a directory called sites-allowed; this directory includes configurations that are not necessarily activated. Once you find the configuration file, display the output in your terminal with the following command:

      • cat /etc/nginx/sites-enabled/your_domain

      If your site has no HTTPS certificate, you will receive a result similar to this:

      Output

      server { listen 80; listen [::]:80; root /var/www/your_domain/html; index index.html index.htm index.nginx-debian.html; server_name your_domain www.your_domain; location / { try_files $uri $uri/ =404; } }

      If you followed the Let’s Encrypt prerequisite to secure your Ubuntu 20.04 server, you will receive this output:

      Output

      server { root /var/www/your_domain/html; index index.html index.htm index.nginx-debian.html; server_name your_domain www.your_domain; location / { try_files $uri $uri/ =404; } listen [::]:443 ssl ipv6only=on; # managed by Certbot listen 443 ssl; # managed by Certbot ssl_certificate /etc/letsencrypt/live/your_domain/fullchain.pem; # managed by Certbot ssl_certificate_key /etc/letsencrypt/live/your_domain/privkey.pem; # managed by Certbot include /etc/letsencrypt/options-ssl-nginx.conf; # managed by Certbot ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem; # managed by Certbot } server { if ($host = www.your_domain) { return 301 https://$host$request_uri; } # managed by Certbot if ($host = your_domain) { return 301 https://$host$request_uri; } # managed by Certbot listen 80; listen [::]:80; server_name your_domain www.your_domain; return 404; # managed by Certbot }

      In either case, the most important field for deploying your React app is root. This points HTTP requests to the /var/www/your_domain/html directory. That means you will copy your files to that location. In the next line, you can see that Nginx will look for an index.html file. If you look in your local build directory, you will see an index.html file that will serve as the main entry point.

      Log off the Ubuntu 20.04 server and go back to your local development environment.

      Now that you know the file location that Nginx will serve, you can upload your build.

      Step 3 — Uploading Build Files with scp

      At this point, your build files are ready to go. All you need to do is copy them to the server. A quick way to do this is to use scp to copy your files to the correct location. The scp command is a secure way to copy files to a remote server from a terminal. The command uses your ssh key if it is configured. Otherwise, you will be prompted for a username and password.

      The command format will be scp files_to_copy username@server_ip:path_on_server. The first argument will be the files you want to copy. In this case, you are copying all of the files in the build directory. The second argument is a combination of your credentials and the destination path. The destination path will be the same as the root in your Nginx config:/var/www/your_domain/html.

      Copy all the build files using the * wildcard to /var/www/your_domain/html:

      • scp -r ./build/* username@server_ip:/var/www/your_domain/html

      When you run the command, you will receive output showing that your files are uploaded. Your results will be slightly different:

      Output

      asset-manifest.json 100% 1092 22.0KB/s 00:00 favicon.ico 100% 3870 80.5KB/s 00:00 index.html 100% 3032 61.1KB/s 00:00 logo192.png 100% 5347 59.9KB/s 00:00 logo512.png 100% 9664 69.5KB/s 00:00 manifest.json 100% 492 10.4KB/s 00:00 robots.txt 100% 67 1.0KB/s 00:00 main.ab7136cd.chunk.css 100% 943 20.8KB/s 00:00 main.ab7136cd.chunk.css.map 100% 1490 31.2KB/s 00:00 runtime-main.1caef30b.js.map 100% 12KB 90.3KB/s 00:00 3.9fbaa076.chunk.js 100% 3561 67.2KB/s 00:00 2.82f639e7.chunk.js.map 100% 313KB 156.1KB/s 00:02 runtime-main.1caef30b.js 100% 2372 45.8KB/s 00:00 main.e8c17c7d.chunk.js.map 100% 2436 50.9KB/s 00:00 3.9fbaa076.chunk.js.map 100% 7690 146.7KB/s 00:00 2.82f639e7.chunk.js 100% 128KB 226.5KB/s 00:00 2.82f639e7.chunk.js.LICENSE.txt 100% 1043 21.6KB/s 00:00 main.e8c17c7d.chunk.js 100% 1045 21.7KB/s 00:00 logo.103b5fa1.svg 100% 2671 56.8KB/s 00:00

      When the command completes, you are finished. Since a React project is built of static files that only need a browser, you don’t have to configure any further server-side language. Open a browser and navigate to your domain name. When you do, you will find your React project:

      Browser with React Project on Server

      In this step, you deployed a React application to a server. You learned how to identify the root web directory on your server and you copied the files with scp. When the files finished uploading, you were able to view your project in a web browser.

      Conclusion

      Deploying React applications is a quick process when you use Create React App. You run the build command to create a directory of all the files you need for a deployment. After running the build, you copy the files to the correct location on the server, pushing your application live to the web.

      If you would like to read more React tutorials, check out our React Topic page, or return to the How To Code in React.js series page.



      Source link