Skip to content

Files

134 lines (82 loc) · 7.5 KB

File metadata and controls

134 lines (82 loc) · 7.5 KB

Level 250 - Week 2/A - Database deployment

By the end of this level you will have your database running in the cloud.

Please scroll down to the section which corresponds to the Cloud provider you picked at level 150 on guidance on setting up the database: Render, Netlify, Fly.io

Render

If you have been following the level 150 guide you should already have a database created for you on Render. We need to be able to connect to it and then upload the database schema.

For security reasons by default Render databases can only be connected to from other Render instances, but not from the outside world. We need to temporarily change this so we can upload our initial database schema.

To do that first go to your Render Dashboard and select your video recommendations database. Once it is open scroll down to the Access Control section. Click Add Source and then the small Use my IP address button inside the Source block. You can now click Save.

Uploading database

To install your database you can either copy the External Database URL to use it in your preferred SQL application, or you can use the command line to do that.

First, copy the PSQL command section on the dashboard. Once you have that paste it in your terminal. You should get something like this

PGPASSWORD=xxx psql -h xxx.render.com -U cyf_ists_user cyf_ists

You want to use this command to load up your database, so make sure to pipe in your initdb script:

PGPASSWORD=xxx psql -h xxx.render.com -U cyf_ists_user cyf_ists < db/initdb.sql

Make sure this runs successfully, and you might want to verify that the tables and example values are all there.

Revoking access

Databases should not be accessible over the internet if possible, so it's advised that you go to the Render dashboard again and revoke access for your IP in the Access Control section.

Netlify

Netlify doesn't support databases on it's own so you need to chose a different provider. There are multiple providers that offer free PostgreSQL databases, from these ElephantSQL and Supabase are two options we suggest you use.

As usual with free tiers each has it's own pros and cons:

Name Pros Cons
ElephantSQL Very easy to set up Free tier is fairly limited
Supabase Very generous free tier
Web based administration interface
More complex setup procedures
Stops in case there is a week without any activity

If you cannot choose on your own ElephantSQL might be a better option for the Full Stack Assessment, however if you have other projects they might need the more generous tier of Supabase. Do note it stops if there aren't any database activity on your website for a week, but you can always restart it if needed.

ElephantSQL

ElephantSQL is a database-as-a-service provider allowing you to host PostgreSQL databases. It has a free tier allowing you to host very small databases ideal for tiny hobby projects.

To set up your database do the following:

  1. Sign up for a new account on their sign-up page: https://customer.elephantsql.com/signup Feel free to use "Sign Up with GitHub" for ease of access.
  2. Click the "Create new instance" button.
  3. On the "Plan" page you will only be able to select the "Tiny Turtle (Free)" plan. Do that.
  4. Set the name of the database to video-recommendations.
  5. No need to set tags, continue to the next page
  6. On the region page select "US-East-1 (Northern Virginia)"
  7. Finally click "Review" then "Create Instance"

Once you do that you will be able to select your video-recommendations instance from the list. Do that and you'll find all of the details you need to connect to the database.

Supabase

Supabase is a cloud provider providing multiple services. One of it's core services are PostgreSQL databases. Supabase's free tier is much more generous to ElephantSQL, however if the database is not accessed for a week it will hibernate the database and you will need to manually restart it on the frontend.

To set up your database do the following:

  1. Go to their website at https://supabase.com/ and click "Start your project"
  2. Sign up using GitHub
  3. On the resulting page it will ask you to create your first project.
  4. Set the name of the database to video-recommendations
  5. On the database password part click "Generate a password". Make sure you copy and store this value in a secure place, preferably a password manager
  6. As a region pick "East US (Northern Virginia)"
  7. Finally click "Create a new project"

Once your project is ready go to the project's dashboard. Then on the left hand side click Settings (it's the icon with a cogwheel) and then go to the "Database" tab.

One the "Database" tab you will find your connection details.

Uploading your initial data to the database

Regardless which option you have picked you will need to gather the following details:

  1. The server. This will be something like flora.db.elephantsql.com for ElephantSQL and something like db.gfdgdfeydxfsdkhfgsdg.supabase.co for Supabase
  2. The username. This will be something like yqunykyc for ElephantSQL and postgres for Supabase. The username and the database name will be the same in both cases.
  3. Your password. On ElephantSQL you can find on the database website. For Supabase this is what you have entered when creating the database.

Now you have all of the details that you need to use on the command line to import your script:

psql -h server_from_above -U username_from_above database_name_from_above < db/initdb.sql

Postgres will ask you for your password, make sure to provide it and then check that the code runs successfully, and you might want to verify that the tables and example values are all there.

Fly.io

If you have been following the level 150 guide you should already have a database created for you on Fly.io. We need to be able to connect to it and then upload the database schema.

Your database can hold data for multiple applications, so first you need to get a list of them:

flyctl postgres db list -a YOURNAME-videorec-db

(Make sure you use your database's name after the -a that you have set up in level 150)

On the list you will find under the NAME column three values: postgres, repmgr and finally the name of your application's datastore. It will be something like YOURNAME_videorec - same as your application name but all of the dashes are replaced with underscores.

Take a note of this name as you will need it later.

Uploading database

To upload your initial schema you will need to use flyctl to connect to your database:

flyctl postgres connect -a YOURNAME-videorec-db -d YOURNAME_videorec

Where the first value is the name of the database you set up in level 150, and the second value if the datastore name you obtained in the last section.

The command above will start you up with a proper psql console where you can run commands.

You can also pipe in the initdb.sql you created earlier:

flyctl postgres connect -a YOURNAME-videorec-db -d YOURNAME_videorec < db/initdb.sql

This will initialize your database with the initial data you prepared. You can also use this command to reset your database if needed.