Setting up PostgreSQL Waterline adapter

Summary

To set up PostgreSQL in a Sails.js application:

Install the sails-postgresql adapter using npm:
```
npm install sails-postgresql
```

Configure the datastore in config/datastores.js:

module.exports.datastores = {
  default: {
    adapter: 'sails-postgresql',
    url: 'postgresql://demo:demo@localhost:5432/demo',
  },
};

Set up the database and user using psql with these commands:

CREATE DATABASE demo;
CREATE USER demo WITH ENCRYPTED PASSWORD 'demo';
GRANT ALL PRIVILEGES ON DATABASE demo TO demo;

Test the connection by lifting your app (sails lift) and verifying the database using a client like Beekeeper Studio.
Create and fetch records using Waterline queries like User.create() and User.find().

This approach ensures a consistent environment for development and production, especially when PostgreSQL is your preferred database.

Transcript

So let's take a look at how to set up PostgreSQL in a Sails application. So we've discussed that Sails uses adapters in order to connect to different data stores. So we're going to be using the sails-postgresql adapter. I'm just going to walk you through how to set it up. This is the same setup I use when I'm working on Sails applications, like sailscasts.com. So I'm just going to show you how I do it.

First of all, you are going to need to grab the sails-postgresql adapter from NPM. And you can do that by just running:

npm i sails-postgresql

So I'm in here, I'm in the demo Sails project for this course. I'm going to run npm i sails-postgresql like so, just save it. And that's just going to run a quick npm install, grab it from NPM, and we'll have that adapter in there.

So while that's running, we can go to Visual Studio Code, which is our project. The file you need to edit is the config/datastores.js file. This one, datastores.js. And in here, you just need to... so by default, you might see sails-mysql is commented out.

What you need is to tell Sails that the adapter you need is the sails-postgresql adapter and the URL, which is the connection string for this adapter.

All right. Or for this database. So, in PostgreSQL’s case, this is the general form of how it's going to look like:

postgresql://<user>:<password>@<host>:<port>/<database>

So let's fill this in. So I'm going to fill this in. I'm going to just call the user, let's say, demo, just going to use demo and the password called demo. The host should be localhost because this is on my local machine. And the port is the default PostgreSQL port, which is 5432. Then the database, we could just call it demo.

This is how I mostly tackle setting up databases for a project. I like naming the database, the user, and the password the same thing locally. So it's just easy to remember. For example, if I'm working on Sailscasts locally, the user will be sailscasts, the password could be sailscasts, and the database will definitely be sailscasts.

Now that this is settled, we don't really have these databases or these users with those passwords. So let's do that.

The way I would do it is: I'll go back to Warp in my terminal, and I'm going to open the psql tool. The psql tool is like the Node REPL for PostgreSQL. It allows you to quickly enter interactive PostgreSQL commands.

I'm going to go to psql, and it's going to start me up as the user KOO. So what I want to do is:

Create a database.
Create a user.
Grant permission for that database to that user.

So we have this sort of namespace user-database kind of setup.

Here’s how it looks:

CREATE DATABASE demo;
CREATE USER demo WITH ENCRYPTED PASSWORD 'demo';
GRANT ALL PRIVILEGES ON DATABASE demo TO demo;

You’ll see messages like Created database and Created role. Now, I’m going to quit psql.

Let’s go back to our application and check. So, do we have a datastore? Yeah, we do.

We did create this model previously when we were testing out the local disk. So if I go back to Warp and I run:

sails lift

Yeah, okay. It's lifted.

Let’s test this database. I'm going to go to Beekeeper Studio, which is the database app I use. Here, I'm just going to log in with the user so that we could see if the connection works.

Test the connection, and you can see the connection looks good because we just created that user and the database.

You can see we have the user table, which is migrated from the application. There’s no data here currently.

Let’s fix that. Just quickly fix this to see. What I would do, probably just to quickly check or create a new record, is go back to Warp, clean this, and I could:

User.create({ name: 'Demo User' });

We should have a user created. Let’s fetch that user to confirm:

User.find();

We’ll go back to Beekeeper Studio and refresh this. You can see we have the user created in our PostgreSQL database.

That’s how you set up the PostgreSQL adapter for your Sails project.

Most of the time, I’m working locally with Sails Disk, but if you want the same environment for production and development, or you just prefer using PostgreSQL, this is the way to go.

To recap:

Run npm i sails-postgresql to install the adapter.
Edit config/datastores.js to specify the adapter and connection string.
Use psql to create the database, user, and grant privileges.
Test the connection with a database client like Beekeeper Studio.
Create and fetch records to verify the setup.

That’s it!

Summary

Transcript

Full Course