Database Connection in Python, Go, and JavaScript
Databases are an essential part of almost any project today. Database interactions are especially familiar to system and database administrators, DevOps/SRE professionals, and software developers. While administrators typically deploy one or multiple database instances and configure the necessary connection parameters for applications, developers need to connect directly to the database within their code. This article explores how to connect to databases using different programming languages.
Prerequisites
We will provide examples for connecting to MySQL, PostgreSQL, Redis, MongoDB, and ClickHouse databases using Python, Go, and JavaScript.
To follow this guide, you will need:
A database deployed on a server or in the cloud.
Installed environments for Python, Go, and JavaScript, depending on your application programming language.
Additionally for Python: pip installed.
Additionally for JavaScript: Node.js and npm installed.
Database Connection in Python
MySQL and Python
For connecting to MySQL databases, we can use a Python driver called MySQL Connector.
Install the driver using pip:
pip install mysql-connector-python
Initialize a new connection:
Import the mysql.connector library and the Error class to handle specific connection errors. Create a function named create_connection, passing the database address (host), user name (user), and user password (password).
To establish the connection, define a class called create_connection that receives the variable names containing the database connection details.
import mysql.connector
from mysql.connector import Error
def create_connection(host_name, user_name, user_password):
connection = None
try:
connection = mysql.connector.connect(
host="91.206.179.29",
user="gen_user",
password="m-EE6Wm}z@wCKe"
)
print("Successfully connected to MySQL Server!")
except Error as e:
print(f"The error '{e}' occurred")
return connection
def execute_query(connection, query):
cursor = connection.cursor()
try:
cursor.execute(query)
connection.commit()
print("Query executed successfully")
except Error as e:
print(f"The error '{e}' occurred")
connection = create_connection("91.206.179.29", "gen_user", "m-EE6Wm}z@wCKe")
Run the script.
If everything works correctly, you will see the "Successfully connected to MySQL Server!" message. If any errors occur, the console will display error code and description.
Create a new table:
Connect to the database using the connection.database class, specifying the name of the database. Note that the database should already exist.
To create a table, initialize a variable create_table_query containing the SQL CREATE TABLE query. For data insertion, initialize another variable insert_data_query with the SQL INSERT INTO query.
To execute each query, use the execute_query class, which takes the database connection string and the variable containing the SQL query.
connection.database = 'test_db'
create_table_query = """
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT NOT NULL
)
"""
execute_query(connection, create_table_query)
insert_data_query = """
INSERT INTO users (name, age) VALUES
('Alice', 30),
('Bob', 25)
"""
execute_query(connection, insert_data_query)
if connection.is_connected():
connection.close()
print("Connection closed")
Run the script.
PostgreSQL and Python
Python offers several plugins for connecting to PostgreSQL, but the most popular one is psycopg2, which we will use here.
Psycopg2 is one of the most frequently used Python plugins for PostgreSQL connections. One of its key advantages is its support for multithreading which allows you to maintain the database connection across multiple threads.
Install psycopg2 using pip (if not already installed):
pip install psycopg2-binary
Connect to PostgreSQL.
Import the Python psycopg2 package and create a function create_new_conn, using the try block. Establish the connection with the psycopg2.connect function, which requires the database name, user name, password, and database address as input.
To initialize the connection, use the create_new_conn() function. Here’s the full code example for connecting to a database:
import psycopg2
from psycopg2 import OperationalError
def create_new_conn():
conn_to_postgres = None
while not conn_to_postgres:
try:
conn_to_postgres = psycopg2.connect(
default_db="default_db",
default_user="gen_user",
password_for_default_user="PasswordForDefautUser9893#",
db_address="91.206.179.128"
)
print("The connection to PostgreSQL has been successfully established!")
except OperationalError as e:
print(e)
return conn_to_postgres
conn_to_postgres = create_new_conn()
Run the script:
python3 connect_to_postgres.py
If successful, you will see the "The connection to PostgreSQL has been successfully established!" message. .
Next, create a table named books, which will have three columns. Use the cursor class for SQL expressions, such as creating database objects.
If the query involves adding or modifying data, you must call the conn_to_postgres.commit() function afterward to apply the changes.
import psycopg2
from psycopg2 import OperationalError
def create_new_conn():
conn_to_postgres = None
while not conn_to_postgres:
try:
conn_to_postgres = psycopg2.connect(
default_db="default_db",
default_user="gen_user",
password_for_default_user="PasswordForDefautUser9893#",
db_address="91.206.179.128"
)
except OperationalError as e:
print(e)
return conn_to_postgres
conn_to_postgres = create_new_conn()
cursor = conn_to_postgres.cursor()
cursor.execute("""
CREATE TABLE books
(
book_id INT PRIMARY KEY NOT NULL,
book_name VARCHAR(255) NOT NULL,
book_author VARCHAR(255) NOT NULL
)
""")
conn_to_postgres.commit()
print("Table Created successfully")
Run the script:
python3 create_table.py
Now, let’s run INSERT INTO to add a new line:
cursor.execute("""
INSERT INTO books (book_id,book_name,book_author) VALUES
(1, 'Long Walk to Freedom', 'Nelson_Mandela')
""")
The full code is below:
import psycopg2
from psycopg2 import OperationalError
def create_new_conn():
conn_to_postgres = None
while not conn_to_postgres:
try:
conn_to_postgres = psycopg2.connect(
default_db="default_db",
default_user="gen_user",
password_for_default_user="PasswordForDefautUser9893#",
db_address="91.206.179.128"
)
except OperationalError as e:
print(e)
return conn_to_postgres
conn_to_postgres = create_new_conn()
cursor = conn_to_postgres.cursor()
cursor.execute("""
INSERT INTO books (book_id,book_name,book_author) VALUES
(1, 'Long Walk to Freedom', 'Nelson_Mandela')
""")
conn_to_postgres.commit()
conn_to_postgres.close()
print("Data inserted successfully")
Run the script:
python3 insert-data.py
Redis and Python
Redis belongs to the class of NoSQL databases, where data is stored in memory rather than on hard drives. It uses a key-value format for data storage. Redis has a wide range of applications, from data storage and caching to serving as a message broker.
We will use the redis-py (or simply redis) library for connecting to Redis.
Install the Redis library using pip:
pip install redis
Connecting to a Redis instance:
Use a try block structure for connection, specifying the function redis.StrictRedis where you provide the Redis address, port, and user password.
import redis
try:
connect_to_redis_server = redis.StrictRedis(
redis_db_host=91.206.179.128,
redis_db_port=6379,
redis_user_password='PasswordForRedis6379')
print connect_to_redis_server
connect_to_redis_server.ping()
print 'Successfully connected to Redis Server!'
except Exception as ex:
print 'Error:', ex
exit('Failed to connect to Redis server.')
Run the script:
python3 connect_to_redis.py
If successful, you will see a message like "Successfully connected to Redis Server!".
Unlike relational databases, Redis stores data in a key-value format. The key uniquely identifies the corresponding value.
Use the set method to create a new record. The example below creates a record with the key City and the value Berlin:
print('Create new record:', connect_to_redis_server.set("City", "Berlin"))
Use the get method to retrieve the value associated with a key:
print('Print record using record key:', connect_to_redis_server.get("City"))
Use the delete method to remove a record by its key:
print('Delete record with key:', connect_to_redis_server.delete("City"))
The complete code fragment is below.
import redis
try:
connect_to_redis_server = redis.StrictRedis(
redis_db_host=91.206.179.128,
redis_db_port=6379,
redis_user_password='PasswordForRedis6379')
print ('New record created:', connect_to_redis_server.set("City", "Berlin"))
print ('Print created record using record key', connect_to_redis_server.get("City"))
print ('Delete created record with key :', connect_to_redis_server.delete("City"))
except Exception as ex:
print ('Error:', ex)
MongoDB and Python
MongoDB is another widely used NoSQL database that belongs to the document-oriented category. Data is organized as JSON-like documents.
To connect to a MongoDB database with Python, the recommended library is PyMongo, which provides a synchronous API.
Install the PyMongo plugin:
pip3 install pymongo
Connect to MongoDB server using the following Python code.
Import the pymongo module and use the MongoClient class to specify the database server address. To establish a connection to the MongoDB server, use a try block for error handling:
import pymongo
connect_to_mongo = pymongo.MongoClient("mongodb://91.206.179.29:27017/")
first_db = connect_to_mongo["mongo-db1"]
try:
first_db.command("serverStatus")
except Exception as e:
print(e)
else:
print("Successfully connected to MongoDB Server!")
connect_to_mongo.close()
Run:
python3 connect_mongodb.py
If the connection is successfully established, the script will return the message: "Successfully connected to MongoDB Server!"
Add data to MongoDB. To add data, you need to create a dictionary. Let's create a dictionary named record1, containing three keys:
record1 = {
"name": "Alex",
"age": 25,
"location": "London"
}
To insert the dictionary data, use the insert_one method in MongoDB.
insertrecord = collection1.insert_one(record1)
import pymongo
connect_to_mongo = pymongo.MongoClient("mongodb://91.206.179.29:27017/")
db1 = connect_to_mongo["newdb"]
collection1 = db1["userdata"]
record1 = {
"name": "Alex",
"age": 25,
"location": "London"
}
insertrecord = collection1.insert_one(record1)
print(insertrecord)
Run the script:
python3 connect_mongodb.py
ClickHouse and Python
ClickHouse is a columnar NoSQL database where data is stored in columns rather than rows. It is widely used for handling analytical queries.
Install the ClickHouse driver for Python. There is a dedicated plugin for ClickHouse called clickhouse-driver. Install the driver using the pip package manager:
pip install clickhouse-driver
Connect to ClickHouse.
To initialize a connection with ClickHouse, you need to import the Client class from the clickhouse_driver library.
To execute SQL queries, use the client.execute function. You also need to specify the engine. For more details on supported engines in ClickHouse, you can refer to the official documentation. We'll use the default engine, MergeTree.
Next, create a new table called users and insert two columns with data. To list the data to be added to the table, use the tuple data type.
After executing the necessary queries, make sure to close the connection to the database using the client.disconnect() method.
The final code will look like this:
from clickhouse_driver import Client
client = Client(host=91.206.179.128',
user='root',
password='P@$$w0rd123',
port=9000)
client.execute('''
CREATE TABLE IF NOT EXISTS Users (
id UInt32,
name String,
) ENGINE = MergeTree()
ORDER BY id
''')
data = [
(1, 'Alice'),
(2, 'Mary')
]
client.execute('INSERT INTO Users (id, name) VALUES', data)
result = client.execute('SELECT * FROM Users')
for row in result:
print(row)
client.disconnect()
Database Connection in Go
Go is one of the youngest programming languages, developed in 2009 by Google. It is widely used in developing microservice architectures and network utilities. For example, services like Docker and Kubernetes are written in Go.
Go supports integrating all popular databases, including PostgreSQL, Redis, MongoDB, MySQL, ClickHouse, etc.
MySQL and Go
For working with the MySQL databases in Go, use the go-sql-driver/mysql driver.
Create a new directory for storing project files and navigate into it:
mkdir mysql-connect && cd mysql-connect
Create a go.mod file to store the dependencies:
go mod init golang-connect-mysql
Download the MySQL driver using the go get command:
go get -u github.com/go-sql-driver/mysql
Create a new file named main.go. Specify the database connection details in the dsn variable:
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
dsn := "root:password@tcp(localhost:3306)/testdb"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatal(err)
}
defer db.Close()
if err := db.Ping(); err != nil {
log.Fatal(err)
}
fmt.Println("Successfully connected to the database!")
query := "INSERT INTO users (name, age) VALUES (?, ?)"
result, err := db.Exec(query, "Alex", 25)
if err != nil {
log.Fatal(err)
}
lastInsertID, err := result.LastInsertId()
if err != nil {
log.Fatal(err)
}
fmt.Printf("Inserted data with ID: %d\n", lastInsertID)
}
PostgreSQL and Go
To connect to PostgreSQL, use the pq driver. Before installing the driver, let's prepare our environment.
Create a new directory for storing the project files and navigate into it:
mkdir postgres-connect && cd postgres-connect
Since we will be working with dependencies, we need to create a go.mod file to store them:
go mod init golang-connect-postgres
Download the pq driver using the go get command:
go get github.com/lib/pq
Create a new file named main.go.
In addition to importing the pq library, it is necessary to add the database/sql library as Go does not come with official database drivers by default. The database/sql library consists of general, independent interfaces for working with databases.
It is also important to note the underscore (empty identifier) when importing the pq module:
_ "github.com/lib/pq"
The empty identifier is used to avoid the "unused import" error, as in this case, we only need the driver to be registered in database/sql.
The fmt package is required to output data to the standard output stream, for example, to the console. To open a connection to the database, the sql.Open function is used, which takes the connection string (connStr) and the driver name (postgres). The connection string specifies the username, database name, password, and host address:
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/lib/pq"
)
func main() {
connStr := "user=golang dbname=db_for_golang password=Golanguserfordb0206$ host=47.45.249.146 sslmode=disable"
db, err := sql.Open("postgres", connStr)
if err != nil {
log.Fatal(err)
}
defer db.Close()
err = db.Ping()
if err != nil {
log.Fatal(err)
}
fmt.Println("Successfully connected to PostgreSQL!")
}
Compile and run:
go run main.go
If everything works correctly, the terminal will display the message Successfully connected to PostgreSQL!
Now, let's look at an example of how to insert data into a table.
First, we need to create a table in the database.
When using Hostman cloud databases, you can copy the PostgreSQL connection string displayed in the "Connections" section of the Hostman web interface. Make sure that the postgresql-client utility is installed on your device beforehand. Enter the psql shell and connect to the previously created database:
\c db_for_golang
Create a table named Cities with three fields — city_id, city_name, and city_population:
CREATE TABLE Cities (
city_id INT PRIMARY KEY,
city_name VARCHAR(45) NOT NULL,
city_population INT NOT NULL);
Grant full privileges to the created table for the user:
GRANT ALL PRIVILEGES ON TABLE cities TO golang;
The function db.Prepare is used to prepare data. It specifies the query for insertion in advance. To insert data, use the function stmt.Exec. In Go, it's common to use plain SQL without using the ORM (Object-Relational Mapping) approach.
stmt, err := db.Prepare("INSERT INTO Cities(city_id, city_name, city_population) VALUES($1, $2, $3)")
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
_, err = stmt.Exec(1, "Toronto", 279435)
if err != nil {
log.Fatal(err)
}
fmt.Println("Data inserted successfully!")
}
If all works correctly, you will see: Data inserted successfully!
Redis and Go
To connect to Redis, you need to use the go-redis driver.
Сreate a new directory:
mkdir connect-to-redis && cd connect-to-redis
Prepare the dependency file:
go mod init golang-connect-redis
And optimize them:
go mod tidy
Download the go-redis module:
go get github.com/go-redis/redis/v8
To connect to Redis, use the redis.Options function to specify the address and port of the Redis server. Since Redis does not use authentication by default, you can leave the Password field empty and use the default database (database 0):
package main
import (
"context"
"fmt"
"log"
"github.com/go-redis/redis/v8"
)
func main() {
rdb := redis.NewClient(&redis.Options{
Addr: "91.206.179.128:6379",
Password: "",
DB: 0,
})
ctx := context.Background()
_, err := rdb.Ping(ctx).Result()
if err != nil {
log.Fatalf("Couldn't connect to Redis: %v", err)
}
fmt.Println("Successfully connected to Redis!")
}
You should see the message «Successfully connected to Redis!»
MongoDB and Go
To work with MongoDB, we'll use the mongo driver.
Create a new directory to store the project structure:
mkdir connect-to-mongodb && cd connect-to-mongodb
Initialize the dependency file:
go mod init golang-connect-mongodb
Download the mongo library:
go get go.mongodb.org/mongo-driver/mongo
Connect to MongoDB using the options.Client().ApplyURI method.
It takes a connection string such as mongodb://91.206.179.29:27017, where 91.206.179.29 is the MongoDB server address and 27017 is the port for connecting to MongoDB. The options.Client().ApplyURI string is used only for specifying connection data. To check the connection status, you can use another function, client.Ping, which shows the success or failure of the connection:
package main
import (
"context"
"fmt"
"log"
"time"
"go.mongodb.org/mongo-driver/mongo"
"go.mongodb.org/mongo-driver/mongo/options"
)
func main() {
clientOptions := options.Client().ApplyURI("mongodb://91.206.179.29:27017")
client, err := mongo.Connect(context.TODO(), clientOptions)
if err != nil {
log.Fatalf("Couldn't connect to MongoDB server: %v", err)
}
fmt.Println("successfully connected to MongoDB!")
ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
defer cancel()
err = client.Ping(ctx, nil)
if err != nil {
log.Fatalf("Could not ping MongoDB server: %v", err)
}
fmt.Println("Ping MongoDB server successfully!")
}
You should see the message:
successfully connected to MongoDB!Ping MongoDB server successfully
MongoDB uses collections to store data. You can create collections using the .Collection function.
Below, we will create a database called first-database and a collection called first-collection. The collection will have a new document, containing three keys: user-name, user-age, and user-email.
collection := client.Database("first-database").Collection("first-collection")
document := map[string]interface{}{
"user-name": "Alice",
"user-age": 25,
"user-email": "[email protected]",
}
insertResult, err := collection.InsertOne(ctx, document)
if err != nil {
log.Fatalf("Couldn't insert new document: %v", err)
}
fmt.Printf("Inserted new document with ID: %v\n", insertResult.InsertedID)
if err := client.Disconnect(ctx); err != nil {
log.Fatalf("Could not disconnect from MongoDB: %v", err)
}
fmt.Println("Disconnected from MongoDB!")
}
If successful, you will see the Inserted new document message with the document ID.
ClickHouse and Go
To work with ClickHouse, use the clickhouse-go driver.
Create a new directory to store the project files and navigate to it:
clickhouse-connect && cd clickhouse-connect
Create a go.mod file to store the dependencies:
go mod init golang-connect-clickhouse
Download the Clickhouse driver using the command:
go get github.com/ClickHouse/clickhouse-go/v2
Create a new file named main.go, where you will specify the connection data to ClickHouse.
package main
import (
"database/sql"
"log"
"github.com/ClickHouse/clickhouse-go/v2"
)
func main() {
dsn := "tcp://localhost:9000?username=user1&password=PasswordForuser175465&database=new_db"
db, err := sql.Open("clickhouse", dsn)
if err != nil {
log.Fatal(err)
}
defer db.Close()
if err := db.Ping(); err != nil {
log.Fatal(err)
}
log.Println("Connected to ClickHouse!")
}
Database Connection in JavaScript
In JavaScript, all connections to external services are made using the Node.js platform. Make sure that you have Node.js and the npm package manager installed on your device.
MySQL and JavaScript
To work with MySQL, use the mysql2 driver.
Create a directory where we will store the project files:
mkdir js-mysql-connect && cd js-mysql-connect
Initialize the project:
npm init -y
Install the mysql2 library:
npm install mysql2
Use the following code to connect to MySQL:
const mysql = require('mysql2');
const connection_to_mysql = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'PasswordForRoot74463',
database: db1,
});
connection_to_mysql.connect((err) => {
if (err) {
console.error('Error connecting to MySQL:', err.message);
return;
}
console.log('Successfully connected to MySQL Server!');
connection_to_mysql.end((endErr) => {
if (endErr) {
console.error('Error closing the connection_to_mysql:', endErr.message);
} else {
console.log('Connection closed.');
}
});
});
PostgreSQL and JavaScript
Connecting to PostgreSQL is done using the pg library.
Create a directory where we will store the project files:
mkdir js-postgres-connect && cd js-postgres-connect
Initialize the project:
npm init -y
Install the pg library:
npm install pg
To connect to PostgreSQL, first import the pg library. Then, create a constant where you specify variables for the database address, username, password, database name, and port. Use the new pg.Client class to pass the connection data.
We will create a table called cities and add two records into it. To do this, we will use the queryDatabase function, which contains the SQL queries.
const pg = require('pg');
const config = {
postgresql_server_host: '91.206.179.29',
postgresql_user: 'gen_user',
postgresql_user_password: 'PasswordForGenUser56467$',
postgresql_database_name: 'default_db',
postgresql_database_port: 5432,
};
const client = new pg.Client(config);
client.connect(err => {
if (err) throw err;
else {
queryDatabase();
}
});
function queryDatabase() {
const query = `
DROP TABLE IF EXISTS cities;
CREATE TABLE cities (id serial PRIMARY KEY, name VARCHAR(80), population INTEGER);
INSERT INTO cities (name, population) VALUES ('Berlin', 3645000);
INSERT INTO cities (name, population) VALUES ('Paris', 2161000);
`;
client
.query(query)
.then(() => {
console.log('Table created successfully!');
client.end(console.log('Closed client connection'));
})
.catch(err => console.log(err))
.then(() => {
console.log('Finished execution, exiting now');
process.exit();
});
}
Use this command to run the code:
node connect-to-postgres.js
Redis and JavaScript
To work with Redis, use the ioredis library.
Create a directory to store the project files:
mkdir js-redis-connect && cd js-redis-connect
Initialize the project:
npm init -y
Install the ioredis library:
npm install ioredis
To connect to Redis, import the ioredis library. Then create a constant named redis and specify the Redis server address.
Inserting data, i.e., creating key-value objects, is done using an asynchronous function named setData, which takes two values — key and value, corresponding to the data format of the Redis system.
const Redis = require('ioredis');
const redis = new Redis({
host: '91.206.179.29',
port: 6379,
password: 'UY+p8e?Kxmqqfa',
});
async function setData(key, value) {
try {
await redis.set(key, value);
console.log('Data successfully set');
} catch (error) {
console.error('Error setting data:', error);
}
}
async function getData(key) {
try {
const value = await redis.get(key);
console.log('Data retrieved');
return value;
} catch (error) {
console.error('Error getting data:', error);
}
}
(async () => {
await redis.select(1);
await setData('user', 'alex');
await getData('user');
redis.disconnect();
})();
Run:
node connect-to-redis.js
MongoDB and JavaScript
To work with MongoDB, use the mongodb driver.
Create a directory for storing the project files:
mkdir js-mongodb-connect && cd js-mongodb-connect
Initialize the project:
npm init -y
Install the mongodb library:
npm install mongodb
To connect to MongoDB, import the mongodb library. Specify the database address in the constant uri and pass the address into the MongoClient class.
const { MongoClient } = require('mongodb');
const uri = "mongodb://91.206.179.29:27017";
const client = new MongoClient(uri, { useNewUrlParser: true, useUnifiedTopology: true });
async function connectToDatabase() {
try {
await client.connect();
console.log("Successfully connected to MongoDB!");
const database = client.db("myDatabase");
const collection = database.collection("myCollection");
const documents = await collection.find({}).toArray();
console.log("Documents found:", documents);
} catch (error) {
console.error("Error connecting to MongoDB:", error);
} finally {
await client.close();
console.log("Connection closed.");
}
}
connectToDatabase();
ClickHouse and JavaScript
To work with ClickHouse, use the clickhouse/client driver.
Create a directory where we will store the project files:
mkdir js-clickhouse-connect && cd js-clickhouse-connect
Initialize the project:
npm init -y
Install the @clickhouse/client library:
npm install @clickhouse/client
To connect to ClickHouse, use the code below where we set the connection details and execute a simple SQL query that will return the first 10 records from the system table named system.tables:
const { ClickHouse } = require('@clickhouse/client');
const client = new ClickHouse({
host: 'http://localhost:8123',
username: 'default',
password: 'PasswordforDefaultUser45435',
database: 'default',
});
async function connectAndQuery() {
try {
console.log('Successfully connected to ClickHouse Server!');
const rows = await client.query({
query: 'SELECT * FROM system.tables LIMIT 10',
format: 'JSON',
}).then((result) => result.json());
console.log('Query results:', rows);
} catch (error) {
console.error('Error Successfully connected to ClickHouse Server! or running the query:', error);
} finally {
console.log('Done.');
}
}
connectAndQuery();
Conclusion
In today's article, we thoroughly explored how to connect to PostgreSQL, Redis, MongoDB, MySQL, and ClickHouse databases using Python, Go, and JavaScript. These languages can be used to create both web applications and microservices that utilize databases in their operation.
18 February 2025 · 23 min to read