Telescope Prisma Client

This package is used to manage the postgres database and publish a package that can be used as a dependency in other projects.

A future improvement could be to also generate the python client.

Usage

Update schema.prisma, then run npm run fullpublish to update the db, generate the client, publish the package and update ./api and ./outreach-system. You might need to run npm login first, and you must be part of Telescope's organization on npm.

Since sandbox and prod have their migration history in a bit of a weird state, importing a dump from there and trying to generate a migration on top of it might show some errors. Overall it's better to generate migrations over "fresh" local databases.

Make sure your local postgres db url has for host host.docker.internal

Local use without publishing

If you made changes to the schema that you would like to test, say for example, on the api, you would have to publish the telescope-prisma-client package on npm and then install the updated version to access the changes. Obviously that's not ideal because there might be changes that you don't want to release for everyone using the package.

To create a local version of the package, you have two very similar options. In /prisma, you can either run npm run generate, which generates the prisma client files under /prisma/src/generatedClient, or you can run npm run prepublish, which does the exact same thing but also copies the files to the /prisma/dist folder.

After that, if you want to access this newly generated package, you need to go into the package.json of the module where you want to access it, say the api, and do this:

{
  "dependencies": {
   "telescope-prisma-client": "file:../prisma",
  }
}

Then, just run npm install and your changes should be accessible!

Note that the previous example works only if you ran npm run prepublish. If you used the npm run generate option the file path in the dependencies needs to be "file:../prisma/src/generatedClient".

Deployment

When you're ready to deploy migration changes you'll need to edit the .env file (or create a new one from the .env.example) and set the POSTGRES_DATABASE_URL variable to the correct url depending on the environment you're deploying to.

After that just run npm run migrate:deploy and the new migrations will be ran into the correct database.

If you're having issues reaching the server database when running the previous command, you might need to whitelist your IP, contact an admin.

Cloud sql proxy

Install the cloud sql proxy (npm run proxy:install for m1 mac or just install from the official docs). Then run npm run proxy:sandbox to start the proxy on sandbox. If you see an unauthorized error, you might need to run gcloud auth login first.

Ingesting a dump

To ingest a dump, you need to have the cloud sql proxy running, then run: pg_restore -U postgres -p 5433 -h 127.0.0.1 -d postgres-telescope-sandbox-database -1 < dump-sandbox.sqlc

Importing a dump from sandbox locally

Start the proxy, then run:

pg_dump -U postgres -p 5433 -h 127.0.0.1 -d postgres-telescope-sandbox-database -F c > dump-sandbox.sqlc

psql -U postgres -h 127.0.0.1 -p 5432 -c "CREATE ROLE cloudsqlsuperuser;" (if not done already)

Create a database locally, e.g. "TSS-restore-sandbox", then run: pg_restore -U postgres -p 5432 -h 127.0.0.1 -d TSS-restore-sandbox < dump-sandbox.sqlc

Troubleshooting

If you add dbgenerated to an attribute you must run prisma db pull after applying the migration to make the schema match what was actually generated in the database. It's a prisma issue, but for more info check this.