mysql - Need some assistance in writing this insert into query, what's an effective way to do it? -

i putting recipe app time instead of mean stack using postgresql database. goes until time me write insert , downhill there, have written code several different ways , these errors have received:

server stareted on port 3000 events.js:141       throw er; // unhandled 'error' event       ^  error: null value in column "id" violates not-null constraint 

tried else , got:

server stareted on port 3000 events.js:141       throw er; // unhandled 'error' event       ^  error: syntax error @ or near "where" 

the above when thought woud work adding id =

server stareted on port 3000 error running query { [error: there no parameter $1]   name: 'error',   length: 87,   severity: 'error',   code: '42p02',   detail: undefined,   hint: undefined,   position: '59',   internalposition: undefined,   internalquery: undefined,   where: undefined,   schema: undefined,   table: undefined,   column: undefined,   datatype: undefined,   constraint: undefined,   file: 'parse_expr.c',   line: '824',   routine: 'transformparamref' } 

tried yet else , got:

events.js:141       throw er; // unhandled 'error' event       ^  error: insert has more expressions target columns 

i @ loss going on or how rearrange code, mean thought straightforward missing primary id, after trying of above spent quite frankly, don't know go here , yes have looked @ other posts here.

here code views/app.js:

var express = require('express'),     path = require('path'),     bodyparser = require('body-parser'),     cons = require('consolidate'),     dust = require('dustjs-helpers'),     pg = require('pg'),     app = express();  // create config configure both pooling behavior // , client options // note: config optional , environment variables // read if config not present var config = {   user: 'aleatoire', //env var: pguser   database: 'recipebookdb', //env var: pgdatabase   password: '', //env var: pgpassword   port: 5432, //env var: pgport   max: 10, // max number of clients in pool   idletimeoutmillis: 30000, // how long client allowed remain idle before being closed };   //this initializes connection pool //it keep idle connections open 30 seconds //and set limit of maximum 10 idle clients var pool = new pg.pool(config);   // assign dust engine .dust files app.engine('dust', cons.dust);  // set default ext .dust app.set('view engine', 'dust'); app.set('views', __dirname + '/views');  // set public folder app.use(express.static(path.join(__dirname, 'public')));  // body parser middleware app.use(bodyparser.json()); app.use(bodyparser.urlencoded({extended: false}));  app.get('/', function(req, res){     // run query can acquire client pool,     // run query on client, , return client pool     pool.connect(function(err, client, done) {       if(err) {         return console.error('error fetching client pool', err);       }       client.query('select * recipes', function(err, result) {          if(err) {           return console.error('error running query', err);         }         res.render('index', {recipes: result.rows});         //call `done()` release client pool         done();        });     });  });  app.post('/add', function(req, res){     // run query can acquire client pool,     // run query on client, , return client pool     pool.connect(function(err, client, done) {       if(err) {         return console.error('error fetching client pool', err);       }       client.query('insert recipes(name, ingredients, directions) values($1, $2, $3, $4)', [req.body.name, req.body.ingredients,          req.body.directions]);          done();         res.redirect('/');     }); });  // server app.listen(3000, function(){     console.log('server stareted on port 3000'); }); 

here package.json file:

{   "name": "recipebook",   "version": "1.0.0",   "description": "a recipe web app nodejs , postgresql",   "main": "app.js",   "scripts": {     "test": "echo \"error: no test specified\" && exit 1"   },   "author": "daniel cortes",   "license": "isc",   "dependencies": {     "body-parser": "^1.15.2",     "consolidate": "*",     "dust": "*",     "dustjs-helpers": "*",     "dustjs-linkedin": "*",     "express": "*",     "pg": "*"   } } 

here views/layout.dust:

  <!doctype html>     <html>     <head>         <title>recipebook</title>         <link rel="stylesheet" href="/css/bootstrap.css">         <link rel ="stylesheet" href="/css/style.css">     </head>     <body>         <div class="container">             <div class="row">                 <div class="col-md-offset-2 col-md-7">                     {+body /}                 </div>             </div>         </div>         <script src="https://code.jquery.com/jquery-1.12.4.min.js" integrity="sha256-zosebrlbnqzlpnkikedrpv7loy9c27hhq+xp8a4mxaq="   crossorigin="anonymous"></script>         <script src="/js/bootstrap.js"></script>     </body>     </html> 

here views/index.dust:

{>"layout" /}  {<body}     <button  type="button" class="btn btn-primary btn-block" data-toggle="modal" data-target="#formmodal">         add recipe     </button>      <br />      {#recipes}         <div class="well">             <h4>{name}              <button class="btn btn-default pull-right" data-toggle="collapse" href="#recipe_{id}" aria-expanded="false" aria-controls="recipe_{id}">                 <span class="glyphicon glyphicon-triangle-bottom" aria-hidden="true"></span>             </button></h4>              <div class="collapse" id="recipe_{id}">               <br />               <br />               <p><strong>ingredients: </strong>{ingredients}</p>               <p><strong>directions: </strong>{directions}</p>               <br />               <hr />               <button class="btn btn-default edit-recipe">                 <span class="glyphicon glyphicon-edit" aria-hidden="true"></span>               </button>               <button class="btn btn-danger delete-recipe">                 <span class="glyphicon glyphicon-remove" aria-hidden="true"></span>               </button>             </div>         </div>     {/recipes}      <!-- add form modal -->     <div class="modal fade" id="formmodal" tabindex="-1" role="dialog" aria-labelledby="formmodallabel">       <div class="modal-dialog" role="document">         <div class="modal-content">             <form method="post" action="/add">               <div class="modal-header">                 <button type="button" class="close" data-dismiss="modal" aria-label="close"><span aria-hidden="true">&times;</span></button>                 <h4 class="modal-title" id="mymodallabel">add recipe</h4>               </div>               <div class="modal-body">                 <div class="form-group">                     <label>recipe name</label>                     <input type="text" class="form-control" name="name" />                 </div>                 <div class="form-group">                     <label>ingredients</label>                     <textarea name="ingredients" class="form-control"></textarea>                 </div>                 <div class="form-group">                     <label>directions</label>                     <textarea class="form-control" name="directions"></textarea>                 </div>               </div>               <div class="modal-footer">                 <button type="button" class="btn btn-default" data-dismiss="modal">close</button>                 <input type="submit" class="btn btn-primary" value="save" />               </div>           </form>         </div>       </div>     </div> {/body} 

as per code given, table structure seems like

create table public.recipes( id integer not null, --(actually not null not required here, primary key) name character(255),  ingredients text,  directions text,  constraint recipes_pkey primary key (id)); 

here id primary key , want auto increment.

there 2 ways achieve this

1) change id data type integer serial

create table public.recipes( id serial, name character varying(255),  ingredients text,  directions text,  constraint recipes_pkey primary key (id)); 

you can call insert 3 parameters.

insert recipes(name, ingredients, directions) values($1, $2, $3)', [req.body.name, req.body.ingredients,          req.body.directions]); 

2) use sequence

in case serial good.

change data type of id column , hope works.