Database Management Tools

Database management is a skill that is quickly becoming a necessity for many professionals around the globe. If you aren't a software developer, yet you find yourself working with databases regularly, this note should be a great resource to give you a very high level understanding of relational databases and the tools we use to work with them. Hopefully I can also point you in the right direction in order for you to go out and begin learning how to use these tools. If you are reading this, I am going to assume that you know what a database is. The briefest description I can think of would be that a database is an organized collection of data. Pretty simple concept right? Well, things can (and DO) start to get complicated when you want to work with the data. Whether you want to add, analyze, delete or work with the data in any way, you're going to need some sort of an interface to do so. Many of these interfaces are commonly referred to as Relational Database Management Systems. Our RDBMS of choice here at Standard Code is PostgreSQL. The most direct way to interact with your database using PostgreSQL is right in the name, SQL. SQL stands for Structured Query Language, and it is just that. It allows you to communicate with Postgres in a language that it understands, and we do this in order to query the database. SQL commands can be entered from the command line using the CLI that comes with PostgreSQL, psql. Using the command line is great if you are experienced in managing a database and comfortable writing raw SQL queries. However, if your not a professional software developer you are most likely not comfortable using the command line, and that's totally okay. There are tools that provide a graphical user interface (GUI) that allow you to see the data, organized in tables. These tools not only allow you to view the data, you can also add, delete, and edit the data. One of the tools available that we at Standard Code recommend is Postico. Postico is great for zipping around the database to quickly find records, view tables and their structure, and even insert, delete, or update records. You can download and use Postico for free. The free tier will allow you to save up to five favorite databases. If you need to save more than that, you can pay about $40 to have the ability to save unlimited favorites. Once you have the application installed, getting started is simple. First thing you will need to do is connect to a PostgreSQL database. You can do this by adding a new favorite, do this by clicking the 'new favorite' button in the bottom left corner. This will pull up a form on the right side of the window. There will be six fields for you to fill out. Let's fill those out. 'Nickname' is arbitrary, and can be whatever you want to call the database as a reference for yourself. 'Host' refers to where the database is. If the database is on the computer that you are on, 'Host' will be 'localhost'. If the database is hosted somewhere else, like a remote server, 'Host' will be the URL of the server. For example, if your database is hosted up on Amazon, the URL will be something like: 'something.something.us-east-1.rds.amazonaws.com'. 'Port' will be 5432. 'User' is the username associated with the database, as well as 'Password'. Lastly, 'Database' is the name of the specific database that you want to connect to. Once the fields are filled out, click the 'connect' button. Once connected, using Postico is a breeze to learn. It has a very intuitive interface, and it should be very clear how to navigate around the database. You will first see all of your tables in the database displayed as icons, with the name of the table below. Double clicking a table will open up that table, showing you all the glorious data it contains. Each column head is an attribute of the records, and each row is an individual record. If you right click on a cell, it will give you some options such as Insert, Delete, or Duplicate . Choose an option, and at the bottom of the window will be a prompt that asks you to either discard or save the changes. If you choose 'save', the database will be updated and reflect the changes you just made. And just like that, you have interacted with your database, altered data all by yourself, without opening up the command line.