Forked from rahmor/connect-postgres-to-express-with-knex.txt
Created
July 27, 2020 18:55
-
-
Save coleenhuang/1571fc5743dd445ca49eb2e8152dc268 to your computer and use it in GitHub Desktop.
Connection Postgres to Express with Knex
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Install pg, knex, postgrator-cli | |
| npm i pg knex | |
| npm i postgrator-cli -D | |
| Create Dev and Test DB for project | |
| createdb -U <username> <project-name-dev> | |
| createdb -U <username> <project-name-test> or | |
| postgres=# CREATE ROLE kevin WITH LOGIN; | |
| postgres=# CREATE DATABASE tax_returns OWNER kevin; | |
| Create postgrator-cli config file named postgrator-config.js | |
| require('dotenv') | |
| module.exports = { | |
| 'migrationDirectory': 'migrations', | |
| 'driver': 'pg', | |
| 'host': process.env.MIGRATION_DB_HOST, | |
| 'port': process.env.MIGRATION_DB_PORT, | |
| 'database': process.env.MIGRATION_DB_NAME, | |
| 'username': process.env.MIGRATION_DB_USER | |
| }; | |
| Add migrate script to package.json | |
| "migrate": "postgrator --config postgrator-config.js" | |
| Create migration folder in root of dir | |
| -001.do.some-sql-action.sql | |
| -001.undo.some-sql-action.sql | |
| - execute by running npm run migrate -- <num> | |
| - to rollback, subtract 1 from the migration number | |
| - running just npm run migrate will run all migrations | |
| Add necessary ENV vars to .env file | |
| -MIGRATION_DB_HOST=localhost | |
| -MIGRATION_DB_PORT=5432 | |
| -MIGRATION_DB_NAME=<project_name> | |
| -MIGRATION_DB_USER=<username> // i like using postgres here | |
| -TEST_<PROJECT_NAME>_DB_URL=postgres://<username:password>@<host>/<test-db-name> | |
| -DEV_<PROJECT_NAME>_DB_URL=postgres://<username:password>@<host>/<dev-db-name> | |
| Run migrations | |
| For Dev DB | |
| -npm run migrate | |
| For Test DB | |
| MIGRATION_DB_NAME=<test-db-name> npm run migrate | |
| Setup Knex Inside of server.js | |
| const db = knex({ | |
| client: 'pg', | |
| connection: <dev-db-url> | |
| }); | |
| Make db a global var on app | |
| app.set('db', db); | |
| Inside of Route file - Get access to db in routes | |
| const db = req.app.get('db'); | |
| Setup testing structure | |
| In file that needs access to db | |
| let db; | |
| before('establish db', () => { | |
| db = knex({ | |
| client: 'pg', | |
| connection: process.env.<test-db-url> | |
| }); | |
| }); | |
| Setup before and after hooks to seed and wipe data | |
| -before('wipe all table data', () => db('<table-name>').truncate()); | |
| -after('close db connection', () => db.destroy()); | |
| -beforeEach('make db available to request', () => app.set('db', db)); | |
| -afterEach('wipe all data from table', () => db('<table-name>').truncate()); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment