One place for hosting & domains

      PostgreSQL

      How To Use a PostgreSQL Database in a Flask Application


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

      Introduction

      In web applications, you usually need a database, which is an organized collection of data. You use a database to store and maintain persistent data that can be retrieved and manipulated efficiently. For example, in a social media application, you have a database where user data (personal information, posts, comments, followers) is stored in a way that can be efficiently manipulated. You can add data to a database, retrieve it, modify it, or delete it, depending on different requirements and conditions. In a web application, these requirements might be a user adding a new post, deleting a post, or deleting their account, which might or might not delete their posts. The actions you perform to manipulate data will depend on specific features in your application. For example, you might not want users to add posts with no titles.

      Flask is a lightweight Python web framework that provides useful tools and features for creating web applications in the Python Language. PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It’s standards-compliant and has many advanced features such as reliable transactions and concurrency without read locks.

      In this tutorial, you’ll build a small book review web application that demonstrates how to use the psycopg2 library, a PostgreSQL database adapter that allows you to interact with your PostgreSQL database in Python. You’ll use it with Flask to perform basic tasks, such as connecting to a database server, creating tables, inserting data to a table, and retrieving data from a table.

      Prerequisites

      Step 1 — Creating the PostgreSQL Database and User

      In this step, you’ll create a database called flask_db and a database user called sammy for your Flask application.

      During the Postgres installation, an operating system user named postgres was created to correspond to the postgres PostgreSQL administrative user. You need to use this user to perform administrative tasks. You can use sudo and pass in the username with the -iu option.

      Log in to an interactive Postgres session using the following command:

      You will be given a PostgreSQL prompt where you can set up your requirements.

      First, create a database for your project:

      • CREATE DATABASE flask_db;

      Note: Every Postgres statement must end with a semi-colon, so make sure that your command ends with one if you are experiencing issues.

      Next, create a database user for our project. Make sure to select a secure password:

      • CREATE USER sammy WITH PASSWORD "https://www.digitalocean.com/community/tutorials/password';

      Then give this new user access to administer your new database:

      • GRANT ALL PRIVILEGES ON DATABASE flask_db TO sammy;

      To confirm the database was created, get the list of databases by typing the following command:

      You’ll see flask_db in the list of databases.

      When you are finished, exit out of the PostgreSQL prompt by typing:

      Postgres is now set up so that you can connect to and manage its database information via Python using the psycopg2 library. Next, you’ll install this library alongside the Flask package.

      Step 2 — Installing Flask and psycopg2

      In this step, you will install Flask and the psycopg2 library so that you can interact with your database using Python.

      With your virtual environment activated, use pip to install Flask and the psycopg2 library:

      • pip install Flask psycopg2-binary

      Once the installation is successfully finished, you’ll see a line similar to the following at the end of the output:

      Output

      Successfully installed Flask-2.0.2 Jinja2-3.0.3 MarkupSafe-2.0.1 Werkzeug-2.0.2 click-8.0.3 itsdangerous-2.0.1 psycopg2-binary-2.9.2

      You now have the required packages installed on your virtual environment. Next, you’ll connect to and set up your database.

      Step 3 — Setting up a Database

      In this step, you’ll create a Python file in your flask_app project directory to connect to the flask_db database, create a table for storing books, and insert some books with reviews into it.

      First with your programming environment activated, open a new file called init_db.py in your flask_app directory.

      This file will open a connection to the flask_db database, create a table called books, and populate the table using sample data. Add the following code to it:

      flask_app/init_db.py

      import os
      import psycopg2
      
      conn = psycopg2.connect(
              host="localhost",
              database="flask_db",
              user=os.environ['DB_USERNAME'],
              password=os.environ['DB_PASSWORD'])
      
      # Open a cursor to perform database operations
      cur = conn.cursor()
      
      # Execute a command: this creates a new table
      cur.execute('DROP TABLE IF EXISTS books;')
      cur.execute('CREATE TABLE books (id serial PRIMARY KEY,'
                                       'title varchar (150) NOT NULL,'
                                       'author varchar (50) NOT NULL,'
                                       'pages_num integer NOT NULL,'
                                       'review text,'
                                       'date_added date DEFAULT CURRENT_TIMESTAMP);'
                                       )
      
      # Insert data into the table
      
      cur.execute('INSERT INTO books (title, author, pages_num, review)'
                  'VALUES (%s, %s, %s, %s)',
                  ('A Tale of Two Cities',
                   'Charles Dickens',
                   489,
                   'A great classic!')
                  )
      
      
      cur.execute('INSERT INTO books (title, author, pages_num, review)'
                  'VALUES (%s, %s, %s, %s)',
                  ('Anna Karenina',
                   'Leo Tolstoy',
                   864,
                   'Another great classic!')
                  )
      
      conn.commit()
      
      cur.close()
      conn.close()
      

      Save and close the file.

      In this file, you first import the os module you’ll use to access environment variables where you’ll store your database username and password so that they are not visible in your source code.

      You import the psycopg2 library. Then you open a connection to the flask_db database using the psycopg2.connect() function. You specify the host, which is the localhost in this case. You pass the database name to the database parameter.

      You provide your username and password via the os.environ object, which gives you access to environment variables you set in your programming environment. You will store the database username in an environment variable called DB_USERNAME and the password in an environment variable called DB_PASSWORD. This allows you to store your username and password outside your source code, so that your sensitive information is not leaked when the source code is saved in source control or uploaded to a server on the internet. Even if an attacker gains access to your source code, they will not gain access to the database.

      You create a cursor called cur using the connection.cursor() method, which allows Python code to execute PostgreSQL commands in a database session.

      You use the cursor’s execute() method to delete the books table if it already exists. This avoids the possibility of another table named books existing, which might result in confusing behavior (for example, if it has different columns). This isn’t the case here, because you haven’t created the table yet, so the SQL command won’t be executed. Note that this will delete all of the existing data whenever you execute this init_db.py file. For our purposes, you will only execute this file once to initiate the database, but you might want to execute it again to delete whatever data you inserted and start with the initial sample data again.

      Then you use CREATE TABLE books to create a table named books with the following columns:

      • id: An ID of the serial type, which is an autoincrementing integer. This column represents a primary key you specify using the PRIMARY KEY keywords. The database will assign a unique value to this key for each entry.
      • title: The book’s title of the varchar type, which is a character type of variable length with a limit. varchar (150) means that the title can be up to 150 characters long. NOT NULL signifies that this column can’t be empty.
      • author: The book’s author, with a limit of 50 characters. NOT NULL signifies that this column can’t be empty.
      • pages_num: An integer representing the number of pages the book has. NOT NULL signifies that this column can’t be empty.
      • review: The book review. The text type signifies that the review can be text of any length.
      • date_added: The date the book was added to the table. DEFAULT sets the default value of the column to CURRENT_TIMESTAMP, which is the time at which the book was added to the database. Just like id, you don’t need to specify a value for this column, as it will be automatically filled in.

      After creating the table, you use the cursor’s execute() method to insert two books into the table, A Tale of Two Cities by Charles Dickens, and Anna Karenina by Leo Tolstoy. You use the %s placeholder to pass the values to the SQL statement. psycopg2 handles the insertion in the background in a way that prevents SQL Injection attacks.

      Once you finish inserting book data into your table, you use the connection.commit() method to commit the transaction and apply the changes to the database. Then you clean things up by closing the cursor with cur.close(), and the connection with conn.close().

      For the database connection to be established, set the DB_USERNAME and DB_PASSWORD environment variables by running the following commands. Remember to use your own username and password:

      • export DB_USERNAME="https://www.digitalocean.com/community/tutorials/sammy"
      • export DB_PASSWORD="https://www.digitalocean.com/community/tutorials/password"

      Now, run your init_db.py file in the terminal using the python command:

      Once the file finishes execution with no errors, a new books table will be added to your flask_db database.

      Log in to an interactive Postgres session to check out the new books table.

      Connect to the flask_db database using the c command:

      Then use a SELECT statement to get the titles and authors of books from the books table:

      • SELECT title, author FROM books;

      You’ll see an output like the following:

              title         |      author
      ----------------------+------------------
       A Tale of Two Cities | Charles Dickens
       Anna Karenina        | Leo Tolstoy
      

      Quit the interactive session with q.

      Next, you’ll create a small Flask application, connect to the database, retrieve the two book reviews you inserted into the database, and display them on the index page.

      Step 4 — Displaying Books

      In this step, you’ll create a Flask application with an index page that retrieves the books that are in the database, and display them.

      With your programming environment activated and Flask installed, open a file called app.py for editing inside your flask_app directory:

      This file will set up your database connection and create a single Flask route to use that connection. Add the following code to the file:

      flask_app/app.py

      import os
      import psycopg2
      from flask import Flask, render_template
      
      app = Flask(__name__)
      
      def get_db_connection():
          conn = psycopg2.connect(host="localhost",
                                  database="https://www.digitalocean.com/community/tutorials/flask_db",
                                  user=os.environ['DB_USERNAME'],
                                  password=os.environ['DB_PASSWORD'])
          return conn
      
      
      @app.route('/')
      def index():
          conn = get_db_connection()
          cur = conn.cursor()
          cur.execute('SELECT * FROM books;')
          books = cur.fetchall()
          cur.close()
          conn.close()
          return render_template('index.html', books=books)
      

      Save and close the file.

      Here, you import the os module, the psycopg2 library, and the Flask class and the render_template() from the flask package. You make a Flask application instance called app.

      You define a function called get_db_connection(), which opens a connection to the flask_db database using the user and password you store in your DB_USERNAME and DB_PASSWORD environment variables. The function returns the conn connection object you’ll be using to access the database.

      Then you create a main / route and an index() view function using the app.route() decorator. In the index() view function, you open a database connection using the get_db_connection() function, you create a cursor, and execute the SELECT * FROM books; SQL statement to get all the books that are in the database. You use the fetchall() method to save the data in a variable called books. Then you close the cursor and the connection. Lastly, you return a call to the render_template() function to render a template file called index.html passing it the list of books you fetched from the database in the books variable.

      To display the books you have in your database on the index page, you will first create a base template, which will have all the basic HTML code other templates will also use to avoid code repetition. Then you’ll create the index.html template file you rendered in your index() function. To learn more about templates, see How to Use Templates in a Flask Application.

      Create a templates directory, then open a new template called base.html:

      • mkdir templates
      • nano templates/base.html

      Add the following code inside the base.html file:

      flask_app/templates/base.html

      <!DOCTYPE html>
      <html lang="en">
      <head>
          <meta charset="UTF-8">
          <title>{% block title %} {% endblock %}- FlaskApp</title>
          <style>
              nav a {
                  color: #d64161;
                  font-size: 3em;
                  margin-left: 50px;
                  text-decoration: none;
              }
      
              .book {
                  padding: 20px;
                  margin: 10px;
                  background-color: #f7f4f4;
              }
      
              .review {
                      margin-left: 50px;
                      font-size: 20px;
              }
      
          </style>
      </head>
      <body>
          <nav>
              <a href="https://www.digitalocean.com/community/tutorials/{{ url_for('index') }}">FlaskApp</a>
              <a href="#">About</a>
          </nav>
          <hr>
          <div class="content">
              {% block content %} {% endblock %}
          </div>
      </body>
      </html>
      

      Save and close the file.

      This base template has all the HTML boilerplate you’ll need to reuse in your other templates. The title block will be replaced to set a title for each page, and the content block will be replaced with the content of each page. The navigation bar has two links, one for the index page where you use the url_for() helper function to link to the index() view function, and the other for an About page if you choose to include one in your application.

      Next, open a template called index.html. This is the template you referenced in the app.py file:

      • nano templates/index.html

      Add the following code to it:

      flask_app/templates/index.html

      
      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} Books {% endblock %}</h1>
          {% for book in books %}
              <div class="book">
                  <h3>#{{ book[0] }} - {{ book[1] }} BY {{ book[2] }}</h3>
                  <i><p>({{ book[3] }} pages)</p></i>
                  <p class="review">{{ book[4] }}</p>
                  <i><p>Added {{ book[5] }}</p></i>
              </div>
          {% endfor %}
      {% endblock %}
      

      Save and close the file.

      In this file, you extend the base template, and replace the contents of the content block. You use an <h1> heading that also serves as a title.

      You use a Jinja for loop in the line {% for book in books %} to go through each book in the books list. You display the book ID, which is the first item using book[0]. You then display the book title, author, number of pages, review, and the date the book was added.

      While in your flask_app directory with your virtual environment activated, tell Flask about the application (app.py in this case) using the FLASK_APP environment variable. Then set the FLASK_ENV environment variable to development to run the application in development mode and get access to the debugger. For more information about the Flask debugger, see How To Handle Errors in a Flask Application. Use the following commands to do this:

      • export FLASK_APP=app
      • export FLASK_ENV=development

      Make sure you set the DB_USERNAME and DB_PASSWORD environment variables if you haven’t already:

      • export DB_USERNAME="https://www.digitalocean.com/community/tutorials/sammy"
      • export DB_PASSWORD="https://www.digitalocean.com/community/tutorials/password"

      Next, run the application:

      With the development server running, visit the following URL using your browser:

      http://127.0.0.1:5000/
      

      You’ll see the books you added to the database on the first initiation.

      Index Page

      You’ve displayed the books in your database on the index page. You now need to allow users to add new books. You’ll add a new route for adding books in the next step.

      Step 5 — Adding New Books

      In this step, you’ll create a new route for adding new books and reviews to the database.

      You’ll add a page with a web form where users enter the book title, book author, the number of pages, and the book review.

      Leave the development server running and open a new terminal window.

      First, open your app.py file:

      For handling the web form, you’ll need to import a few things from the flask package:

      • The global request object to access submitted data.
      • The url_for() function to generate URLs.
      • The redirect() function to redirect users to the index page after adding a book to the database.

      Add these imports to the first line in the file:

      flask_app/app.py

      
      from flask import Flask, render_template, request, url_for, redirect
      
      # ...
      

      Then add the following route at the end of the app.py file:

      flask_app/app.py

      
      # ...
      
      
      @app.route('/create/', methods=('GET', 'POST'))
      def create():
          return render_template('create.html')
      

      Save and close the file.

      In this route, you pass the tuple ('GET', 'POST') to the methods parameter to allow both GET and POST requests. GET requests are used to retrieve data from the server. POST requests are used to post data to a specific route. By default, only GET requests are allowed. When the user first requests the /create route using a GET request, a template file called create.html will be rendered. You will later edit this route to handle POST requests for when users fill and submit the web form for adding new books.

      Open the new create.html template:

      • nano templates/create.html

      Add the following code to it:

      flask_app/templates/create.html

      {% extends 'base.html' %}
      
      {% block content %}
          <h1>{% block title %} Add a New Book {% endblock %}</h1>
          <form method="post">
              <p>
                  <label for="title">Title</label>
                  <input type="text" name="title"
                         placeholder="Book title">
                  </input>
              </p>
      
              <p>
                  <label for="author">Author</label>
                  <input type="text" name="author"
                         placeholder="Book author">
                  </input>
              </p>
      
              <p>
                  <label for="pages_num">Number of pages</label>
                  <input type="number" name="pages_num"
                         placeholder="Number of pages">
                  </input>
              </p>
              <p>
              <label for="review">Review</label>
              <br>
              <textarea name="review"
                        placeholder="Review"
                        rows="15"
                        cols="60"
                        ></textarea>
              </p>
              <p>
                  <button type="submit">Submit</button>
              </p>
          </form>
      {% endblock %}
      

      Save and close the file.

      You extend the base template, set a heading as a title, and use a <form> tag with the attribute method set to post to indicate that the form will submit a POST request.

      You have a text field with the name title, which you’ll use to access the title data in your /create route.

      You have a text field for the author, a number field for the number of pages, and a text area for the book review.

      Last, you have a Submit button at the end of the form.

      Now, with the development server running, use your browser to navigate to the /create route:

      http://127.0.0.1:5000/create
      

      You will see an Add a New Book page with an input field for a book title, one for its author, and one for the number of pages the book has, a text area for the book’s review, and a Submit button.

      Add a New Book

      If you fill in the form and submit it, sending a POST request to the server, nothing happens because you did not handle POST requests on the /create route.

      Open app.py to handle the POST request the user submits:

      Edit the /create route to look as follows:

      flask_app/app.py

      
      # ...
      
      @app.route('/create/', methods=('GET', 'POST'))
      def create():
          if request.method == 'POST':
              title = request.form['title']
              author = request.form['author']
              pages_num = int(request.form['pages_num'])
              review = request.form['review']
      
              conn = get_db_connection()
              cur = conn.cursor()
              cur.execute('INSERT INTO books (title, author, pages_num, review)'
                          'VALUES (%s, %s, %s, %s)',
                          (title, author, pages_num, review))
              conn.commit()
              cur.close()
              conn.close()
              return redirect(url_for('index'))
      
          return render_template('create.html')
      

      Save and close the file.

      You handle POST requests inside the if request.method == 'POST' condition. You extract the title, author, number of pages, and the review the user submits from the request.form object.

      You open a database using the get_db_connection() function, and create a cursor. Then you execute an INSERT INTO SQL statement to insert the title, author, number of pages, and review the user submitted into the books table.

      You commit the transaction and close the cursor and connection.

      Lastly, you redirect the user to the index page where they can see the newly added book below the existing books.

      With the development server running, use your browser to navigate to the /create route:

      http://127.0.0.1:5000/create
      

      Fill in the form with some data and submit it.

      You’ll be redirected to the index page where you’ll see your new book review.

      Next, you’ll add a link to the Create page in the navigation bar. Open base.html:

      Edit the file to look as follows:

      flask_app/templates/base.html

      
      <!DOCTYPE html>
      <html lang="en">
      <head>
          <meta charset="UTF-8">
          <title>{% block title %} {% endblock %} - FlaskApp</title>
          <style>
              nav a {
                  color: #d64161;
                  font-size: 3em;
                  margin-left: 50px;
                  text-decoration: none;
              }
      
              .book {
                  padding: 20px;
                  margin: 10px;
                  background-color: #f7f4f4;
              }
      
              .review {
                      margin-left: 50px;
                      font-size: 20px;
              }
      
          </style>
      </head>
      <body>
          <nav>
              <a href="https://www.digitalocean.com/community/tutorials/{{ url_for("index') }}">FlaskApp</a>
              <a href="https://www.digitalocean.com/community/tutorials/{{ url_for("create') }}">Create</a>
              <a href="#">About</a>
          </nav>
          <hr>
          <div class="content">
              {% block content %} {% endblock %}
          </div>
      </body>
      </html>
      

      Save and close the file.

      Here, you add a new <a> link to the navigation bar that points to the Create page.

      Refresh your index page and you’ll see the new link in the navigation bar.

      You now have a page with a web form for adding new book reviews. For more on web forms, see How To Use Web Forms in a Flask Application. For a more advanced and more secure method of managing web forms, see How To Use and Validate Web Forms with Flask-WTF.

      Conclusion

      You built a small web application for book reviews that communicates with a PostgreSQL database. You have basic database functionality in your Flask application, such as adding new data to the database, retrieving data, and displaying it on a page.

      If you would like to read more about Flask, check out the other tutorials in the Flask series.



      Source link

      How To Use PostgreSQL With Node.js on Ubuntu 20.04


      The author selected Society of Women Engineers to receive a donation as part of the Write for DOnations program.

      Introduction

      The Node.js ecosystem provides a set of tools for interfacing with databases. One of those tools is node-postgres, which contains modules that allow Node.js to interface with the PostgreSQL database. Using node-postgres, you will be able to write Node.js programs that can access and store data in a PostgreSQL database.

      In this tutorial, you’ll use node-postgres to connect and query the PostgreSQL (Postgres in short) database. First, you’ll create a database user and the database in Postgres. You will then connect your application to the Postgres database using the node-postgres module. Afterwards, you will use node-postgres to insert, retrieve, and modify data in the PostgreSQL database.

      Prerequisites

      To complete this tutorial, you will need:

      Step 1 – Setting Up the Project Directory

      In this step, you will create the directory for the node application and install node-postgres using npm. This directory is where you will work on building your PostgreSQL database and configuration files to interact.

      Create the directory for your project using the mkdir command:

      Navigate into the newly created directory using the cd command:

      Initialize the directory with a package.json file using the npm init command:

      The -y flag creates a default package.json file.

      Next, install the node-postgres module with npm install:

      You’ve now set up the directory for your project and installed node-postgres as a dependency. You’re now ready to create a user and a database in Postgres.

      Step 2 — Creating A Database User and a Database in PostgreSQL

      In this step, you’ll create a database user and the database for your application.

      When you install Postgres on Ubuntu for the first time, it creates a user postgres on your system, a database user named postgres, and a database postgres. The user postgres allows you to open a PostgreSQL session where you can do administrative tasks such as creating users and databases.

      PostgreSQL uses ident authentication connection scheme which allows a user on Ubuntu to login to the Postgres shell as long as the username is similar to the Postgres user. Since you already have a postgres user on Ubuntu and a postgres user in PostgreSQL created on your behalf, you’ll be able to log in to the Postgres shell.

      To login, switch the Ubuntu user to postgres with sudo and login into the Postgres shell using the psql command:

      The command’s arguments represents:

      • -u: a flag that switches the user to the given user on Ubuntu. Passing postgres user as an argument will switch the user on Ubuntu to postgres.
      • psql: a Postgres interactive terminal program where you can enter SQL commands to create databases, roles, tables, and many more.

      Once you login into the Postgres shell, your terminal will look like the following:

      postgres is the name of the database you’ll be interacting with and the # denotes that you’re logged in as a superuser.

      For the Node application, you’ll create a separate user and database that the application will use to connect to Postgres.

      To do that, create a new role with a strong password:

      • CREATE USER fish_user WITH PASSWORD 'password';

      A role in Postgres can be considered as a user or group depending on your use case. In this tutorial, you’ll use it as a user.

      Next, create a database and assign ownership to the user you created:

      • CREATE DATABASE fish OWNER fish_user;

      Assigning the database ownership to fish_user grants the role privileges to create, drop, and insert data into the tables in the fish database.

      With the user and database created, exit out of the Postgres interactive shell:

      To login into the Postgres shell as fish_user, you need to create a user on Ubuntu with a name similar to the Postgres user you created.

      Create a user with the adduser command:

      You have now created a user on Ubuntu, a PostgreSQL user, and a database for your Node application. Next, you’ll log in to the PostgreSQL interactive shell using the fish_user and create a table.

      Step 3 — Opening A Postgres Shell With a Role and Creating a Table

      In this section, you’ll open the Postgres shell with the user you created in the previous section on Ubuntu. Once you login into the shell, you’ll create a table for the Node.js app.

      To open the shell as the fish_user, enter the following command:

      • sudo -u fish_user psql -d fish

      sudo -u fish_user switches your Ubuntu user to fish_user and then runs the psql command as that user. The -d flag specifies the database you want to connect to, which is fish in this case. If you don’t specify the database, psql will try to connect to fish_user database by default, which it won’t find and it will throw an error.

      Once you’re logged in the psql shell, your shell prompt will look like the following:

      fish denotes that you’re now connected to the fish database.

      You can verify the connection using the conninfo command:

      You will receive output similar to the following:

      Output

      You are connected to database "fish" as user "fish_user" via socket in "/var/run/postgresql" at port "5432".

      The output confirms that you have indeed logged in as a fish_user and you’re connected to the fish database.

      Next, you’ll create a table that will contain the data your application will insert.

      The table you’ll create will keep track of shark names and their colors. When populated with data, it will look like the following:

      id name color
      1 sammy blue
      2 jose teal

      Using the SQL create table command, create a table:

      • CREATE TABLE shark(
      • id SERIAL PRIMARY KEY,
      • name VARCHAR(50) NOT NULL,
      • color VARCHAR(50) NOT NULL);

      The CREATE TABLE shark command creates a table with 3 columns:

      • id: an auto-incrementing field and primary key for the table. Each time you insert a row, Postgres will increment and populate the id value.

      • name and color: fields that can store 50 characters. NOT NULL is a constraint that prevents the fields from being empty.

      Verify if the table has been created with the right owner:

      The dt command list all tables in the database.

      When you run the command, the output will resemble the following:

               List of relations
       Schema | Name  | Type  |   Owner
      --------+-------+-------+-----------
       public | shark | table | fish_user
      (1 row)
      
      

      The output confirms that the fish_user owns the shark table.

      Now exit out of the Postgres shell:

      It will take you back to the project directory.

      With the table created, you’ll use the node-postgres module to connect to Postgres.

      Step 4 — Connecting To a Postgres Database

      In this step, you’ll use node-postgres to connect your Node.js application to the PostgreSQL database. To do that, you’ll use node-postgres to create a connection pool. A connection pool functions as a cache for database connections allowing your app to reuse the connections for all the database requests. This can speed up your application and save your server resources.

      Create and open a db.js file in your preferred editor. In this tutorial, you’ll use nano, a terminal text editor:

      In your db.js file, require in the node-postgres module and use destructuring assignment to extract a class Pool from node-postgres.

      node_pg_app/db.js

      const { Pool } = require('pg')
      
      

      Next, create a Pool instance to create a connection pool:

      node_pg_app/db.js

      const { Pool} = require('pg')
      
      const pool = new Pool({
        user: 'fish_user',
        database: 'fish',
        password: 'password',
        port: 5432,
        host: 'localhost',
      })
      
      

      When you create the Pool instance, you pass a configuration object as an argument. This object contains the details node-postgres will use to establish a connection to Postgres.

      The object defines the following properties:

      • user: the user you created in Postgres.
      • database: the name of the database you created in Postgres.
      • password: the password for the user fish_user.
      • port: the port Postgres is listening on. 5432 is the default port.
      • host: the Postgres server you want node-postgres to connect to. Passing it localhost will connect the node-postgres to the Postgres server installed on your system. If your Postgres server resided on another droplet, your host would look like this:host: server_ip_address.

      Note: In production, it’s recommended to keep the configuration values in a different file, such as the .env file. This file is then added to the .gitignore file if using Git to avoid tracking it with version control. The advantage is that it hides sensitive information, such as your password, user, and database from attackers.

      Once you create the instance, the database connection is established and the Pool object is stored in the pool variable. To use this anywhere in your app, you will need to export it. In your db.js file, require in and define an instance of the Pool object, and set its properties and values:

      node_pg_app/db.js

      const { Pool } = require("pg");
      
      const pool = new Pool({
        user: "fish_user",
        database: "fish",
        password: "password",
        port: 5432,
        host: "localhost",
      });
      
      module.exports = { pool };
      
      

      Save the file and exit nano by pressing CTRL+X. Enter y to save the changes, and confirm your file name by pressing ENTER or RETURN key on Mac.

      Now that you’ve connected your application to Postgres, you’ll use this connection to insert data in Postgres.

      Step 5 — Inserting Data Into the Postgres Database

      In this step, you’ll create a program that adds data into the PostgreSQL database using the connection pool you created in the db.js file. To ensure that the program inserts different data each time it runs, you’ll give it functionality to accept command-line arguments. When running the program, you’ll pass it the name and color of the shark.

      Create and open insertData.js file in your editor:

      In your insertData.js file, add the following code to make the script process command-line arguments:

      node_pg_app/insertData.js

      const { pool } = require("./db");
      
      async function insertData() {
        const [name, color] = process.argv.slice(2);
        console.log(name, color);
      }
      
      insertData();
      

      First, you require in the pool object from the db.js file. This allows your program to use the database connection to query the database.

      Next, you declare the insertData() function as an asynchronous function with the async keyword. This lets you use the await keyword to make database requests asynchronous.

      Within the insertData() function, you use the process module to access the command-line arguments. The Node.js process.argv method returns all arguments in an array including the node and insertData.js arguments.

      For example, when you run the script on the terminal with node insertData.js sammy blue, the process.argv method will return an array: ['node', 'insertData.js', 'sammy', 'blue'] (the array has been edited for brevity).

      To skip the first two elements: node and insertData.js, you append JavaScript’s slice() method to the process.argv method. This returns elements starting from index 2 onwards. These arguments are then destructured into name and color variables.

      Save your file and exit nano with CTRL+X. Run the file using node and pass it the arguments sammy, and blue:

      • node insertData.js sammy blue

      After running the command, you will see the following output:

      Output

      sammy blue

      The function can now access the name and shark color from the command-line arguments. Next, you’ll modify the insertData() function to insert data into the shark table.

      Open the insertData.js file in your text editor again and add the highlighted code:

      node_pg_app/insertData.js

      const { pool } = require("./db");
      
      async function insertData() {
        const [name, color] = process.argv.slice(2);
        const res = await pool.query(
            "INSERT INTO shark (name, color) VALUES ($1, $2)",
            [name, color]
          );
        console.log(`Added a shark with the name ${name}`);
      }
      
      insertData();
      

      Now, the insertData() function defines the name and color of the shark. Next, it awaits the pool.query method from node-postgres that takes an SQL statement INSERT INTO shark (name, color) ... as the first argument. The SQL statement inserts a record into the shark table. It uses what’s called a parameterized query. $1, and $2 corresponds to the name and color variables in the array provided in the pool.query() method as a second argument: [name, color]. When Postgres is executing the statement, the variables are substituted safely protecting your application from SQL injection. After the query executes, the function logs a success message using console.log().

      Before you run the script, wrap the code inside insertData() function in a try...catch block to handle runtime errors:

      node_pg_app/insertData.js

      const { pool } = require("./db");
      
      async function insertData() {
        const [name, color] = process.argv.slice(2);
        try {
          const res = await pool.query(
            "INSERT INTO shark (name, color) VALUES ($1, $2)",
            [name, color]
          );
          console.log(`Added a shark with the name ${name}`);
        } catch (error) {
          console.error(error)
        }
      }
      
      insertData()
      

      When the function runs, the code inside the try block executes. If successful, the function will skip the catch block and exit. However, if an error is triggered inside the try block, the catch block will execute and log the error in the console.

      Your program can now take command-line arguments and use them to insert a record into the shark table.

      Save and exit out of your text editor. Run the insertData.js file with sammy and blue as command-line arguments:

      • node insertData.js sammy blue

      You’ll receive the following output:

      Output

      Added a shark with the name sammy

      Running the command insert’s a record in the shark table with the name sammy and the color blue.

      Next, execute the file again with jose and teal as command-line arguments:

      • node insertData.js jose teal

      Your output will look similar to the following:

      Output

      Added a shark with the name jose

      This confirms you inserted another record into the shark table with the name jose and the color teal.

      You’ve now inserted two records in the shark table. In the next step, you’ll retrieve the data from the database.

      Step 6 — Retrieving Data From the Postgres Database

      In this step, you’ll retrieve all records in the shark table using node-postgres, and log them into the console.

      Create and open a file retrieveData.js in your favorite editor:

      In your retrieveData.js, add the following code to retrieve data from the database:

      node_pg_app/retrieveData.js

      const { pool } = require("./db");
      
      async function retrieveData() {
        try {
          const res = await pool.query("SELECT * FROM shark");
          console.log(res.rows);
        } catch (error) {
          console.error(error);
        }
      }
      
      retrieveData()
      

      The retrieveData() function reads all rows in the shark table and logs them in the console. Within the function try block, you invoke the pool.query() method from node-postgres with an SQL statement as an argument. The SQL statement SELECT * FROM shark retrieves all records in the shark table. Once they’re retrieved, the console.log() statement logs the rows.

      If an error is triggered, execution will skip to the catch block, and log the error. In the last line, you invoke the retrieveData() function.

      Next, save and close your editor. Run the retrieveData.js file:

      You will see output similar to this:

      Output

      [ { id: 1, name: 'sammy', color: 'blue' }, { id: 2, name: 'jose', color: 'teal' } ]

      node-postgres returns the table rows in a JSON-like object. These objects are stored in an array.

      You can now retrieve data from the database. You’ll now modify data in the table using node-postgres.

      Step 7 — Modifying Data In the Postgres Database

      In this step, you’ll use node-postgres to modify data in the Postgres database. This will allow you to change the data in any of the shark table records.

      You’ll create a script that takes two command-line arguments: id and name. You will use the id value to select the record you want in the table. The name argument will be the new value for the record whose name you want to change.

      Create and open the modifyData.js file:

      In your modifyData.js file, add the following code to modify a record in the shark table:

      node_pg_app/modifyingData.js

      const { pool } = require("./db");
      
      async function modifyData() {
        const [id, name] = process.argv.slice(2);
        try {
          const res = await pool.query("UPDATE shark SET name = $1 WHERE id = $2", [
            name,
            id,
          ]);
          console.log(`Updated the shark name to ${name}`);
        } catch (error) {
          console.error(error);
        }
      }
      
      modifyData();
      

      First, you require the pool object from the db.js file in your modifyData.js file.

      Next, you define an asynchronous function modifyData() to modify a record in Postgres. Inside the function, you define two variables id and name from the command-line arguments using the destructuring assignment.

      Within the try block, you invoke the pool.query method from node-postgres by passing it an SQL statement as the first argument. On the UPDATE SQL statement, the WHERE clause selects the record that matches the id value. Once selected, SET name = $1 changes the value in the name field to the new value.

      Next, console.log logs a message that executes once the record name has been changed. Finally, you call the modifyData() function on the last line.

      Save and exit out of the file using CTRL+X. Run the modifyData.js file with 2 and san as the arguments:

      You will receive the following output:

      Output

      Updated the shark name to san

      To confirm that the record name has been changed from jose to san, run the retrieveData.js file:

      You will get output similar to the following:

      Output

      output [ { id: 1, name: 'sammy', color: 'blue' }, { id: 2, name: 'san', color: 'teal' } ]

      You should now see that the record with the id 2 now has a new name san replacing jose.

      With that done, you’ve now successfully updated a record in the database using node-postgres.

      Conclusion

      In this tutorial, you used node-postgres to connect and query a Postgres database. You began by creating a user and database in Postgres. You then created a table, connected your application to Postgres using node-postgres, and inserted, retrieved, and modified data in Postgres using the node-postgres module.

      For more information about node-postgres, visit their documentation. To improve your Node.js skills, you can explore the How To Code in Node.js series.



      Source link

      Using Prisma With PostgreSQL


      How to Join

      This Tech Talk is free and open to everyone. Register below to get a link to join the live stream or receive the video recording after it airs.

      Date Time RSVP
      May 19, 2021 11:00–12:00 p.m. ET / 3:00–4:00 p.m. GMT

      About the Talk

      Connecting and grabbing data from our databases is always a difficult task. We will use Prisma (which just came out of beta) to model, connect to, grab data, and view our database contents.

      What You’ll Learn

      • How to model a database schema
      • How to CRUD on a database
      • How to view contents of a database

      This Talk Is Designed For

      Developers that want to connect to their database more easily. Prisma will make database connections easy.

      Prerequisites

      • Knowledge of connecting to databases
      • Basic knowledge of databases

      Resources

      Prisma.io [Docs]
      Set up Prisma [Quickstart]
      DigitalOcean Managed Databases [Docs]

      To join the live Tech Talk, register here.



      Source link