Get started using PostgreSQL or MongoDB with Python on Windows

This step-by-step guide will help you get started connecting your Python app to a database. We chose to focus on two popular options: PostgreSQL and MongoDB.

Differences between MongoDB and PostgreSQL

Two popular choices for a database system are MongoDB and PostgreSQL.

MongoDB is a NoSQL document database designed to work with JSON and store schema-free data. It's good for flexibility and unstructured data, caching real-time analytics, and horizontal scaling.

PostgreSQL (sometimes referred to as Postgres) is a SQL relational database with an emphasis on extensibility and standards compliance. It can handle JSON now too, but it is generally better for structured data, vertical scaling, and ACID-compliant needs like eCommerce and financial transactions.

Schemas:

PostgreSQL: Table | Column | Value | Records.

MongoDB (NoSQL): Collection | Key | Value | Document.

The sort of database you choose should depend on the type of application you will be using the database with. We recommend that you look up the advantages and disadvantages of structured and unstructured databases and choose based on your use case. There are also several other database systems to consider beyond PostgreSQL and MongoDB.

Note

You may also want to consider how integrated the framework and tools you're using are with a particular database system. The Django web framework seems to be better integrated with PostgreSQL (see the Django docs and psycopg2). The Flask web framework seems to be better integrated with MongoDB (see MongoEngine and PyMongo).

Install PostgreSQL

To install PostgreSQL:

  1. Open your WSL terminal (ie. Ubuntu 18.04).
  2. Update your Ubuntu packages: sudo apt update
  3. Once the packages have updated, install PostgreSQL (and the -contrib package which has some helpful utilities) with: sudo apt install postgresql postgresql-contrib
  4. Confirm installation and get the version number: psql --version

There are 3 commands you need to know once PostgreSQL is installed:

  1. sudo service postgresql status for checking the status of your database.
  2. sudo service postgresql start to start running your database.
  3. sudo service postgresql stop to stop running your database.

PostgreSQL User Setup

The default admin user, postgres, needs a password assigned in order to connect to a database. To set a password:

  1. Enter the command: sudo passwd postgres
  2. You will get a prompt to enter your new password.
  3. Close and reopen your terminal.

Run PostgreSQL with psql shell

psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file. In addition, it provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.

To start the psql shell:

  1. Start your postgres service: sudo service postgresql start
  2. Connect to the postgres service and open the psql shell: sudo -u postgres psql

Once you have successfully entered the psql shell, you will see your command line change to look like this: postgres=#

Note

Alternatively, you can open the psql shell by switching to the postgres user with: su - postgres and then entering the command: psql.

To exit postgres=# enter: \q or use the shortcut key: Ctrl+D

To see what user accounts have been created on your PostgreSQL installation, use from your WSL terminal: psql -c "\du" ...or just \du if you have the psql shell open. This command will display columns: Account User Name, List of Roles Attributes, and Member of role group(s). To exit back to the command line, enter: q.

VS Code support for PostgreSQL

VS Code supports working with PostgreSQL databases via the PostgreSQL extension, you can create, connect to, manage and query PostgreSQL databases from within VS Code.

Install MongoDB

To install MongoDB:

  1. Open your WSL terminal (ie. Ubuntu 18.04).
  2. Update your Ubuntu packages: sudo apt update
  3. Once the packages have updated, install MongoDB with: sudo apt-get install mongodb
  4. Confirm installation and get the version number: mongod --version

There are 3 commands you need to know once MongoDB is installed:

  1. sudo service mongodb status for checking the status of your database.
  2. sudo service mongodb start to start running your database.
  3. sudo service mongodb stop to stop running your database.

Note

You might see the command sudo systemctl status mongodb used in tutorials or articles. In order to remain lightweight, WSL does not include systemd (a service management system in Linux). Instead, it uses SysVinit to start services on your machine. You shouldn't notice a difference, but if a tutorial recommends using sudo systemctl, instead use: sudo /etc/init.d/. For example, sudo systemctl status mongodb, for WSL would be sudo /etc/inid.d/mongodb status ...or you can also use sudo service mongodb status.

Run your Mongo database in a local server

  1. Check the status of your database: sudo service mongodb status You should see a [Fail] response, unless you've already started your database.

  2. Start your database: sudo service mongodb start You should now see an [OK] response.

  3. Verify by connecting to the database server and running a diagnostic command: mongo --eval 'db.runCommand({ connectionStatus: 1 })' This will output the current database version, the server address and port, and the output of the status command. A value of 1 for the "ok" field in the response indicates that the server is working.

  4. To stop your MongoDB service from running, enter: sudo service mongodb stop

Note

MongoDB has several default parameters, including storing data in /data/db and running on port 27017. Also, mongod is the daemon (host process for the database) and mongo is the command-line shell that connects to a specific instance of mongod.

VS Code support for MongoDB

VS Code supports working with MongoDB databases via the Azure CosmosDB extension, you can create, connect to, manage and query MongoDB databases from within VS Code.

To learn more, visit the VS Code docs: Working with MongoDB.

Set up profile aliases

Typing out sudo service mongodb start or sudo service postgres start and sudo -u postgrest psql can get tedious. However, you could consider setting up aliases in your .profile file on WSL to make these commands quicker to use and easier to remember.

To set up your own custom alias, or shortcut, for executing these commands:

  1. Open your WSL terminal and enter cd ~ to be sure you're in the root directory.

  2. Open the .profile file, which controls the settings for your terminal, with the terminal text editor, Nano: sudo nano .profile

  3. At the bottom of the file (don't change the # set PATH settings), add the following:

    # My Aliases
    alias start-pg='sudo service postgresql start'
    alias run-pg='sudo -u postgres psql'
    

This will allow you to enter start-pg to start running the postgresql service and run-pg to open the psql shell. You can change start-pg and run-pg to whatever names you want, just be careful not to overwrite a command that postgres already uses!

  1. Once you've added your new aliases, exit the Nano text editor using Ctrl+X -- select Y (Yes) when prompted to save and Enter (leaving the file name as .profile).
  2. Close and re-open your WSL terminal, then try your new alias commands.