patio

    4.2.1 • Public • Published

    Build Status Coverage Status

    NPM

    Patio

    Patio is a Sequel inspired query engine.

    Installation

    To install patio run

    npm install comb patio

    If you want to use the patio executable for migrations

    npm install -g patio

    Running Tests

    To run the tests

    grunt test
    

    To run just the postgres tests

    grunt test-pg
    

    To run just the mysql tests

    grunt test-mysql
    

    Running Tests with Docker

    In order to provide a consistent test environment and make it easier to test, we have included a Dockerfile and a docker-compose.yml to make it easy to test in an isolated environment. You can do so with:

    docker-compose build
    docker-compose up -d mysql postgres
    sleep 10 # Wait for databases to come up
    docker-compose up patio

    Why Use Patio?

    Patio is different because it allows the developers to choose the level of abtraction they are comfortable with.

    If you want to use the ORM functionality you can. If you don't you can just use the Database and Datasets as a querying API, and if you need to you can write plain SQL

    Concepts

    1. Model definitions are defined by the tables in the database.

      As you add models the definition is automatically defined from the table definition. This is particularly useful when you want to define your model from a schema designed using another tool (i.e. ActiveRecord, Sequel, etc...)

    2. Patio tries to stay out of your way when querying.

      When you define a model you still have the freedom to do any type of query you want.

      Only want certain columns?

      MyModel.select("id", "name", "created").forEach(function(record){
          //record only has the id, name, and created columns
      });

      You want to join with another table?

      MyModel.join("otherTable", {id: patio.sql.identifier("myModelId"}).forEach(function(record){
          //Record has columns from your join table now!
      });

      You want to run raw SQL?

      MyModel.db.run("select * from my_model where name = 'Bob'").all().chain(function(records){
          //all records with a name that equals bob.
      });

      You want to just query the database and not use a model?

      var DB = patio.connect("pg://test:test@127.0.0.1:5432/test_db");
      DB.from("myTable").filter({id: [1,2,3]}).all().function(records){
         //records with id IN (1,2,3)
      });

    Getting Started

    All the code for this example can be found here

    1. Create a new database

      PostgreSQL

      psql -c "CREATE DATABASE reademe_example"
      

      MySQL

      mysql -e "CREATE DATABASE readme_example"
      
    2. Create a migration

      mkdir migration
      patio migration-file -n createInitialTables ./migration
      

      This will add a migration name createdInitialTables in your migration directory.

    3. Add the following code to your migration

      module.exports = {
          //up is called when you migrate your database up
          up: function (db) {
              //create a table called state;
              return db
                  .createTable("state", function () {
                      this.primaryKey("id");
                      this.name(String);
                      this.population("integer");
                      this.founded(Date);
                      this.climate(String);
                      this.description("text");
                  })
                  .chain(function () {
                      //create another table called capital
                      return db.createTable("capital", function () {
                          this.primaryKey("id");
                          this.population("integer");
                          this.name(String);
                          this.founded(Date);
                          this.foreignKey("stateId", "state", {key: "id", onDelete: "CASCADE"});
                      });
                  });
          },
      
          //down is called when you migrate your database down
          down: function (db) {
              //drop the state and capital tables
              return db.dropTable("capital", "state");
          }
      };
    4. Run your migration

      patio migrate -v --camelize -u "<DB_CONNECTION_STRING>" -d ./migration
      
    5. Connect and query!

      var patio = require("patio");
      
      //set camelize = true if you want snakecase database columns as camelcase
      patio.camelize = true;
      patio.connect("pg://postgres@127.0.0.1:5432/readme_example");
      
      //define a State model with a relationship to capital
      var State = patio.addModel("state").oneToOne("capital");
      
      //define a Capital model with a relationship to State
      var Capital = patio.addModel("capital").manyToOne("state");
      
      //save a state
      State
          .save({
              name: "Nebraska",
              population: 1796619,
              founded: new Date(1867, 2, 4),
              climate: "continental",
              //notice the capital relationship is inline
              capital: {
                  name: "Lincoln",
                  founded: new Date(1856, 0, 1),
                  population: 258379
              }
          })
          .chain(function () {
              //save a Capital
              return Capital.save({
                  name: "Austin",
                  founded: new Date(1835, 0, 1),
                  population: 790390,
                  //define the state inline
                  state: {
                      name: "Texas",
                      population: 25674681,
                      founded: new Date(1845, 11, 29)
                  }
              });
          })
          .chain(function () {
              //Query all the states by name
              return State.order("name").forEach(function (state) {
                  //Get the associated capital
                  return state.capital.chain(function (capital) {
                      console.log("%s's capital is %s.", state.name, capital.name);
                  });
              });
          })
          .chain(process.exit, function (err) {
              console.log(err)
              process.exit(1);
          });

    Guides

    Features

    Install

    npm i patio

    DownloadsWeekly Downloads

    236

    Version

    4.2.1

    License

    none

    Unpacked Size

    1.07 MB

    Total Files

    73

    Last publish

    Collaborators

    • dustinsmith1024
    • aheuermann
    • mattcarter
    • damartin