PostgreSQL, Sequelize, and You Part 1 — The Basics (and Installing on Windows)

Using PostgreSQL with the ORM Sequelize can initially be quite a challenge, but if you are using Windows, simply getting the files set up can be the biggest challenge the first time.

Installing Postgres is relatively simple (download it and follow the install wizard), but getting the required node modules to install can be annoying if everything isn’t already set up properly. To install the modules both Python(At the time of this writing using version 2.7.10 is recommended, 3.4.3 willnot work) and Visual Basic are required. When installing Python, make sure to select to add Python to the “path.” This allows Python to be called on in the terminal and is needed for npm to use Python to install packages.

After both of these are installed the required packages can be installed.

pg-hstore seems to be required for sequelize to work properly, even if you aren’t calling on its functions, which can lead to lots of headaches if you aren’t aware.

Using pgAdminIII is relatively straightforward, but there are a couple things to know: you should probably leave the port that the database listens to on port 5432 (the default) as it seems some of the features require it to be on that port, and if you change the port during creation it be be…difficult to get it to change back.

Now that you have completed the first hard part of simply installing the necessary components, comes the challenge of learning how to use it. First thing that is necessary is to require sequelize and pg.

Next is connecting to the database (replacing databaseName, userName, and password with the proper values of course)

There is also something called a connection string, but using it with Sequelize and PostgreSQL hasn’t worked out well for me.

After connecting to the database comes setting up a model. A model is what defines how a table in the database should look.

Obviously this is using placeholder names and should be changed to something descriptive. The words STRING, BOOLEAN, and NUMBER are datatypes which tell sequelize what type of data to tell Postgres the information is. Other datatypes can be seen here.

Next comes creating rows in the tables. This is done with the Name.create (or Name.findOrCreate — this first looks to see if a row with the same values as you’re inputting exists already) function (where Name is the variable your model is saved as in your JavaScript file.

After you have somethings in the database, you may want to be able to retrieve that information (being the purpose of a database and all). To do this you will want to use one of the find functions. Name.findOne will return only one item, findAll will return all of them.

After interfacing with the database in some way you have the option (which you pretty much always want to use) to create a callback function. The inputs to this callback function will be what the database returns after a query.

If there is only a single item passed back from the database, you should use either .then or .done, both do the same thing. For certain queries, such as findOrCreate, you need to use .spread. .spread will separate the results into separate inputs, making them usable.

In findOrCreate’s case the item found or created will return along with a boolean saying whether or not it was created.

This should cover most the basics of getting started with using Sequelize with PostgresQL. Part 2 will cover some slightly more advanced parts of Sequelize.