Database Setup for Smart Village Project: A Complete Windows Guide

As a student working on the Smart Village project at St. Joseph Engineering College, Mangalore, I wanted to share my experience setting up a cloud database with PostgreSQL and integrating it with Django. This guide will walk you through the entire process I followed, from creating a cloud database to verifying the connection.

Why Choose a Cloud Database?

Before diving into the setup, let me explain why I opted for a cloud database solution. A cloud-hosted database eliminates the need for local database installations, provides better accessibility across different environments, and ensures your data is available from anywhere. For this project, I chose PostgreSQL hosted on Neon.tech due to its robust features and free tier availability.

Step 1: Creating a Cloud Database with Neon.tech

Setting up your Neon account:

1. Navigate to neon.tech and create a free account
2. Once logged in, click on "Create Project"
3. Enter your project name: "smart-village" (or your preferred name)
4. Select your preferred region (choose the closest one for better performance)
5. Choose PostgreSQL version (latest stable version recommended)
6. Click "Create Project"

Getting your database credentials:

After project creation, Neon will provide you with connection details:

• Host: Your database endpoint URL
• Database name: Usually matches your project name
• Username: Generated automatically
• Password: Provided during setup (save this securely!)
• Port: Default is 5432

Make sure to copy and store these credentials safely as you'll need them in the following steps.

Step 2: Local Setup with Docker and Adminer

Installing Docker Desktop:

1. Visit the Docker Desktop website
2. Download the version compatible with your Windows system
3. Run the installer and follow the setup wizard
4. During installation, Docker may prompt you to enable WSL 2 (Windows Subsystem for Linux)
5. Allow the WSL 2 update when prompted
6. Restart your computer if required
7. Launch Docker Desktop and wait for it to start completely

Running Adminer with Docker:

Once Docker is running, open Command Prompt or PowerShell as administrator and run:

docker run -p 8080:8080 adminer

This command will:

• Download the Adminer image (if not already present)
• Run Adminer on port 8080
• Make it accessible via your web browser

Accessing your database through Adminer:

1. Open your web browser and navigate to http://localhost:8080
2. You'll see the Adminer login interface
3. Fill in your Neon database credentials:
   • System: PostgreSQL
   • Server: Your Neon host URL
   • Username: Your Neon username
   • Password: Your Neon password
   • Database: Your database name
4. Click "Login"

Step 3: Connecting Database to Django Backend

Installing required packages:

First, ensure you have the PostgreSQL adapter for Django:

pip install psycopg2-binary

Configuring Django settings:

In your Django project's settings.py file, update the DATABASES configuration:

DATABASES = {
 'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'NAME': 'your_database_name',
    'USER': 'your_username',
    'PASSWORD': 'your_password',
    'HOST': 'your_neon_host_url',
    'PORT': '5432',
    'OPTIONS': {
        'sslmode': 'require',
     },
 }
}

Environment variables (recommended approach):

For better security, create a .env file in your project root:

DB_NAME=your_database_name
DB_USER=your_username
DB_PASSWORD=your_password
DB_HOST=your_neon_host_url
DB_PORT=5432

Then update your settings.py:

import os
load_dotenv()

DATABASES = {
 'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'NAME': os.getenv('DB_NAME'),
    'USER': os.getenv('DB_USER'),
    'PASSWORD': os.getenv('DB_PASSWORD'),
    'HOST': os.getenv('DB_HOST'),
    'PORT': os.getenv('DB_PORT'),
    'OPTIONS': {
        'sslmode': 'require',
    },
 }
}

Step 4: Verifying the Database Connection

Creating a test model:

Create a simple model in your Django app to test the connection. In your models.py:

from django.db import models

class TestModel(models.Model):
name = models.CharField(max_length=100)
created_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
    return self.name

Running migrations:

Execute the following commands in your project directory:

Create migration files:

python manage.py makemigrations

Apply migrations to database

python manage.py migrate

Verifying in Adminer:

1. Return to Adminer in your browser (http://localhost:8080)
2. Refresh the database connection
3. Navigate to the tables section
4. You should see your newly created tables, including the TestModel table
5. Click on the table to view its structure and confirm it matches your Django model

Additional verification steps:

Run Django's database check command:

python manage.py check --database default

Test the connection programmatically:

python manage.py shell

In the Django shell:

from django.db import connection
cursor = connection.cursor()
cursor.execute("SELECT version()")
print(cursor.fetchone())

Conclusion

This setup provides a robust foundation for your project with a cloud-hosted PostgreSQL database that's accessible from anywhere. The combination of Neon.tech's reliable hosting, Docker's containerized Adminer interface, and Django's powerful ORM creates an efficient development environment.

Remember to keep your database credentials secure, regularly backup your data, and monitor your usage to stay within the free tier limits. As your project grows, you can easily scale your database resources through Neon's dashboard.

The cloud-based approach ensures that team members can collaborate effectively, and the setup remains consistent across different development environments. Hope this post helped you to setup your database requirement.