Knex

Knex Knex.js is a "batteries included" SQL query builder for Postgres, MSSQL, MySQL, MariaDB, SQLite3, and Oracle designed to be flexible, portable, and fun to use. It features both traditional node style callbacks as well as a promise interface for cleaner async flow control, a stream interface, full featured query and schema builders, transaction support (with save-points), connection pooling and standardized responses between different query clients and dialects. Knex is a query builder, designed to be used with Postgres, MySQL, MariaDB, SQLite3, and Oracle. It is also the powerful tool that some object relational mapping libraries, such as Bookshelf, are built on. Knex.js create boilerplate code and give you data to work with. Comes with lots of commands used to create the boilerplate code. Let's you use database management in JavaScript. Fulfills this role through its SQL analogous syntax, user friendly documentation and queried data formatting. Dependencies needed to run Knex are Express, Nodemon, Postgres.

Seeds: Database seeding is the initial seeding of a database with data. This is often an automated process that is executed upon the initial setup of an application. The data can be dummy data/placeholder or necessary data.

Migrations: Establishes table types and locations for all data. Essentially the “Skeleton” of the table.

Knex Reference Commands:

.select() .where() .first() .insert() .returning() .then() .update() .delete()

Psql/PostgreSQL/Knex.js Setup and Installation

Better workflow/Setup?: https://github.com/gSchool/g55_learning_path/blob/master/week_8/deploy_crud.md

Database → just the name only. Table → databases can have multiple tables Migration → the structure/scheme of the table (what data types will it have and how many rows/cols) Seeds → dummy data

1. Create the database: createdb dbname

2. Install dependencies: npm install -S knex pg psql

3. Creates the knex file and configuration settings for development and production. knex init

module.exports = { development: { client: "pg", connection: "postgres:/// this is your db name" },

production: { client: "postgresql", connection: process.env.DATABASE_URL } };

4. Database-connection.js file (Always contains these two lines) const CONFIG = require("./knexfile")[process.env.NODE_ENV || "development"]; module.exports = require("knex")(CONFIG);

5. “Migrations” ← table skeleton structure/establish rows/cols and data types. knex migrate:make table name

exports.up = function(knex, Promise) { return knex.schema.createTable("table name", table => {

     // migration table schema goes here

}); };

exports.down = function(knex, Promise) { return knex.schema.dropTableIfExists("table name"); };

6. Migrations: updates the table with the scheme established in the migrations file. knex migrate:latest

   Test table schema by: psql datebase name, select * from database name

7. “Seeding” ← dummy placeholder data that will be entered into the migration/table. It’s important to name the seeds something similar to the table name. Suggested syntax for example is 01_tableName.

knex seed:make 01_resolution

exports.seed = function(knex, Promise) { // Deletes ALL existing entries return knex("table name") .del() .then(function() { // Inserts seed entries return knex("table name").insert([ { id: 1, name: "Black and Tan", roaster: "Ink", aroma: 3 }, ]); }) .then(() => { // starts the next index at 4 return knex.raw("ALTER SEQUENCE table_name_id_seq RESTART WITH 4;"); }); };

8. knex seed:run

Note: will connect to a db ← /c (must be used to connect to the database) doing this will change the far left “name” on the command line.

At this point all data should be properly shown in the psql database.

9. Queries.js file ← where the CRUD functionality lives.

const database = require("./database-connection"); module.exports = { list() { }, read() { }, create() { }, update() { }, delete() { } };

---

Steps for Database Deployment via Heroku:

1.Create separate github repo for project that is now OUTSIDE the original fork/clone.

2. Heroku create / add / commit / push

3. In Heroku GUI: Install/configure “add-ons” Heroku Postgres > Hobby Dev

Push to Heroku DB - this just creates the database initially - not best practice for updating when the table is large.

heroku addons:create heroku-postgresql:hobby-dev

Resetting Heroku Data (update) to invoke reset heroku pg:push db_sfdoc DATABASE_URL --app dbsfdoc heroku pg:reset ← reset Enter table name → dbsfdoc heroku pg:push db_sfdoc DATABASE_URL --app dbsfdoc ← one more update needed

heroku pg:push protected-meadow-94841 DATABASE_URL --app protected-meadow-94841