Using Programming Languages to query Data Bases

Now that we successfully have installed PostreSQL, created our tables and inserted some data into it, we want to use the data of those tables in our programs. For PostgreSQL nearly any modern programming language has libraries that allow us to query these tables. In this section we want to try to query the Database first with Python and then with JavaScript.

Using Pyhon to query the database

For this example I assume you have installed Python v3 on your machine. You also need to install the psycopg2 library by running the command pip install psycopg2.

Lets create a file named app.py on our local machine with the following code:

import psycopg2

# Establish a connection to the PostgreSQL database
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    database="postgres",
    user="postgres",
    password="root"
)

# Create a cursor object to execute SQL queries
cur = conn.cursor()

# Execute the SELECT query to fetch all records from the "messages" table
cur.execute("SELECT * FROM messages")

# Fetch all the rows returned by the query
rows = cur.fetchall()

# Iterate over the rows and print the data
for row in rows:
    print("ID:", row[0])
    print("Sender:", row[1])
    print("Title:", row[2])
    print("Message:", row[3])
    print("Time:", row[4])
    print()

# Close the cursor and connection
cur.close()
conn.close()

Now if we run the script by using the command python app.py we should get the following output:

$ python app.py 
ID: 2
Sender: John
Title: Call at 2pm
Message: We arranged a call at 2pm
Time: 2023-05-03 17:09:21.098497

Using JavaScript to query the database

Now let's use JavaScript for querying the database. I expect that you have installed NodeJS on your machine for that tutorial.

Create a new directory on your machine, open a console and run the following command: npm init. This creates a new JavaScript project in this directory. NPM (which is the package manager for NodeJS, like pip for Python) will ask you some questions, just click enter every time, it is not important for this example. After you have answered all the questions NPM has asked you run the command npm install pg which will install the pg library that is needed for our script.

Now let's create a file named app.js with the following content:

const { Client } = require('pg');

// Create a new PostgreSQL client instance
const client = new Client({
  user: 'postgres',
  password: 'root',
  host: 'localhost',
  database: 'postgres',
  port: 5432, // Default PostgreSQL port
});

// Connect to the PostgreSQL database
client.connect()
  .then(() => {
    // Execute the SELECT query to fetch all records from the "messages" table
    return client.query('SELECT * FROM messages');
  })
  .then((result) => {
    const rows = result.rows;

    // Iterate over the rows and log the data
    rows.forEach((row) => {
      console.log('ID:', row.id);
      console.log('Sender:', row.sender);
      console.log('Title:', row.title);
      console.log('Message:', row.message);
      console.log('Time:', row.time);
      console.log();
    });
  })
  .catch((error) => {
    console.error('Error:', error);
  })
  .finally(() => {
    // Disconnect the client and release resources
    client.end();
  });

Execute the script by typing the following command in your console: node app.js. The result on the console should look like this:

$ node app.js
ID: 2
Sender: John
Title: Call at 2pm
Message: We arranged a call at 2pm
Time: 2023-05-03T15:09:21.098Z

That's great. We can now write software that is able to execute SQL queries, retrieve data from our database and much more. It is your term now to try more SQL querys. You should:

• Insert new data into the messages table

• Update existing records on the messages table

• Delete records from the messages table