EWAI-SERVER DATABASE SETUP

EWAI uses a PostgreSQL database to store the PTD (Power Telemetry Data) message streams as time-series datasets for the energy data assets defined in the marketplace. The TimescaleDB time-series extensions may be utilized in the future, but they are not currently implemented.

  1. Link to PostgreSQL (v13.1+): https://www.postgresql.org/
  2. Link to TimescaleDB: https://www.timescale.com/
  3. pgcrypto extension: https://www.postgresql.org/docs/current/pgcrypto.html

CREATING THE DB

Create an empty PostgreSQL db for use by EWAI:

  1. Install PostgreSQL as desired (standalone, cloud, docker, etc.), e.g.:
docker-compose up -d
  1. Create an empty db (this example assumes ewai is the db name, and assumes user 'postgres' with password 'password' has access to that database) and also install the pgcrypto extension, e.g.:
psql -d "postgresql://postgres:password@localhost:5432"
create database ewai;
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
quit

With your empty db created, next use one (but not both) of the following methods below to initialize the db. It doesn't matter which one you use. Some prefer SQL scripts, some prefer code-first. EWAI uses the Prisma ORM to manage entity-sql conversions for the database. See: https://prisma.io.

INITIALIZING THE DB - DB FIRST (FROM SQL SCRIPT)

  1. To initialize the EWAI db tables using the provided db_create.sql script, run from the root of the EWAI project folder:
psql -d "postgresql://postgres:password@localhost:5432/ewai" -f ./src/db/db_create.sql

Your EWAI database is now ready for use (tables created but empty).

INITIALIZING THE DB - CODE FIRST (USING PRISMA)

  1. To initialize the EWAI db using Prisma ORM migration, first make sure the EWAI_DBCONN .env file EWAI_DBCONN value is setup to properly connect to your PostgreSQL instance and the EWAI db, e.g.:
EWAI_DBCONN=postgresql://postgres:password@localhost:5432/ewai?schema=public
  1. Run Prisma Migrate (from the root of the EWAI project folder)
yarn prisma migrate dev --preview-feature

Your EWAI database is now ready for use (tables created but empty).

Generating ORM Classes (Prisma)

The following command is used to update TypeScript classes if you make changes to the Prisma schema.prisma file.

yarn prisma generate
yarn build

Notes:

  1. You do not need to create the EWAI API user, as it will be created for you automatically the first time you start the EWAI API by using the information (username/password) you specify in the EWAI .env file. That username/password can be anything you want - it is not a PostgreSQL db user, it is a user that the EWAI-LISTEN and EWAI-MARKET use to authenticate to the EWAI-SERVER API.

  2. You should not need to run the Prisma introspect command. The 'yarn prisma introspect' command was originally used to generate the ./prisma/schema.prisma file from the db schema using a database first dev approach. Do not run introspect again unless you make db changes or you will overwrite the ./prisma/schema.prisma file! The schema.prisma file was manually edited after it was generated (with introspect) to convert class name and fields from snake_case to camelCase

Note: after running prisma introspect, you need to edit fields that say "dbgenerated()" to be "uuid()" for uuid initialization. Prisma doesn't seem to know how to do this, e.g.:

// The value of this field is generated by the database as: `gen_random_uuid()`.
uuid String @unique @default(dbgenerated()) -> uuid String @unique @default(uuid())

Also, a copy of the good schema is saved in schema.prisma.save (just in case), as the schema.prisma file is overwritten every time you run introspect, and if something goes wrong it's good to have a copy of the last known good version of it saved somewhere.