-
-
Notifications
You must be signed in to change notification settings - Fork 51
Local Database
If you are working on issues that require making changes to the database schema or reference data (i.e., data that is considered part of the application, such as lookup lists, or, in the case of Food Oasis, Categories), then you should work with a copy of the shared development database on your local machine, which we will refer to as a Local Database.
-
Install Postgres
-
If you develop on an IOS, Linux or Windows Pro edition machine, you can Install Docker Desktop for your machine and run a Postgres docker image.
-
Alternatively, on any machine, you can install postgres on your native O/S as described in the Running Postgres on Your Native Platform section below.
-
Windows Home Edition does not run docker very well as of July 2020, so we recommend running Postgres natively.
-
If you already have any instance of Postgres installed, you may just use your existing instance for development - you will just need to adjust the connection parameters accordingly.
-
-
Create a food database on Postgres
-
Load the Food Oasis sample database into the food database.
-
Configure the development environment to connect to the local database by editing the .env file with your local database connection string information.
-
Run database migrations to update the database with existing migration scripts to bring it up to date.
The following sections provide more detailed instructions for each of these steps.
We are currently running Postgres version 11.6 in production, but you may install a more current version locally, if you wish.
Install Docker Desktop from here and follow the provided instructions. This will install Docker client and a local Docker daemon (server) that can host docker containers.
The following command will run if docker is set up and ready to go:
docker version
Then you can download the official postgis-enabled Postgres image from Docker Hub and create a local container named foodoasisdb by running:
sudo docker run --name foodoasisdb \
-e POSTGRES_PASSWORD=pgpass \
-d -p 5433:5432 postgis/postgis:11-2.5
(Omit sudo on Windows)
This may take a little while, as it is installing Postgres on your docker server.
If you then run
docker ps
you should see a container with the name foodoasisdb running.
The connection parameters for a Postgres instance installed this way will be:
POSTGRES_HOST=localhost
POSTGRES_PORT=5433
POSTGRES_USERNAME=postgres
POSTGRES_PASSWORD=pgpass
When creating the container, I specified mapping port 5433 to avoid conflicts with an existing default Postgres installation, which would be using the default port of 5432.
Even if you always use this local docker instance for your development database, you may want to also install Postgres natively as described in the next section, so you can use psql and pgAdmin as clients to manipulate the database.
To install Postgres directly on your native machine, just download and install Postgres by following the instructions here. Specify pgpass as the password for the postgres user when prompted. Be sure to choose the options to install any client tools (e.g., pgAdmin and psql), if the installation has such options. If this is the first time you have installed Postgres on your machine, the default port number will be 5432. If you have installed a Postgres previously, the install process will suggest a different port number, which you can accept.
You need to also install the PostGIS extension. On Windows, this can be installed by running the "Stack Builder" option in the installer, and checking the PostGIS Bundle option when prompted for the "applications you would like to install".
By default, the connection parameters for a database instance installed this way will be:
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_USERNAME=postgres
POSTGRES_PASSWORD=pgpass
though we will install a database named food below.
If you are running on Windows, you will need to add the postgres bin directory to your Windows PATH environment variable. The path to be added is C:\Program Files\PostgreSQL\<version number>\bin. Swap <version number> with the installed Postgres version. This allow the Git Bash or cmd shell to find the psql command.
If you followed the above instructions for installing natively, then you can connect via the psql utility and create a new database named foodoasis like this:
$ psql --host localhost --port 5432 --username postgres
psql (12.3)
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# create database food;
CREATE DATABASE
psotgres=# \q
$
If you installed using Docker, then pass in port 5433 to connect via the psql utility.
$ psql --host localhost --port 5433 --username postgres
Alternatively, you can use a tool like DBeaver or pgAdmin to create a database named food with the default database creation settings.
A script file to populate the sample database can be found at /db/demo-db/foodoasis.sql. To use psql to run this script, run the following command from your terminal in the server directory:
psql -f db/demo-db/foodoasis.sql --host localhost --port 5433 --username postgres --dbname food
This loads a sample version of the database that was captured at one point in time. We now need to update this database with migration scripts that have been applied since that time as described in the Database Migration section below.
If you don't want to use psql to load the sample database, you can use DBeaver, pgAdmin, or another client tool to create a connection to the database, then open and run the same script found at `/db/demo-db/foodoasis.sql to create and populate the starter database.
Getting the Food Oasis application and migrations to connect to your local database is primarily a matter of getting the related .env settings set up properly for your local database. If you followed the above instructions for local database installation, the environment variables for using the Local Database will be
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=food
POSTGRES_USERNAME=postgres
POSTGRES_PASSWORD=pgpass
Use POSTGRES_PORT=5433 instead if you installed Postgres to a docker container as described above.
Since I tend to switch around which local database I am using and also occasionally connect to the shared development server, I will generally have a few sets of connection parameter settings in my .env file, and uncomment only the set I need at that particular time.
Open a terminal on the server directory of the repo and run
npm run migrate up
This will run the SQL scripts in the /migrations folder to modify the starter database with all the migration scripts in the github repo that bring the database up to date with the current develop branch.
At this point, you should be able to run the application as usual, and it will be using the local database.
When you need to make changes to the database schema or reference data, you will need to write a new migration script to effect the changes you want and try it out with your local database.
See Database-Migrations for instructions on how to work with migrations.
When you submit your PR, the migration file will be included in your PR and get saved to the develop branch in the repo. The developer who merges to the develop branch should then be able to run migrations against the shared development database to apply your changes as part of the merge.
If for any reason, you corrupt your local database, you can simply drop the foodoasisdb database from your Local Database Server, re-create it empty and run migrations again to start over.