The surefire way to connect to mysql in node.js with node-mysql

It is amazing that how much trouble I had trying to establish connection to local mysql server. A quick search on StackOverflow shows that I am not alone!

I think I found a surefire way to establish local mysql connections – use socket instead of TCP/IP. Briefly, there are two ways to establish mysql connections, either with TCP/IP (you need host and port number) or socket (you only need the filepath of the socket).

In theory, there shouldn’t be much difference between them. However, finding a file is definitely easier than finding the ip and the port number.

Here is the code to connect to a Mysql db from MAMP.


var mysql = require('mysql');

var connection = mysql.createConnection({
 user : 'root',
 password : "root",
socketPath : '/Applications/MAMP/tmp/mysql/mysql.sock',

//for non MAMP, might be

//socketPath : '/tmp/mysql.sock',
});

connection.connect(function(err) {
 if (err) {
 console.log('db_connection_err', err);
 return;
 }
});

connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
 if (err) throw err;
console.log('The solution is: ', rows[0].solution);

});

connection.end();

Standard

Day 27, more database

Today is the last day of the database sprint yet El and I barely finished the basic requirements. I can’t image any other group is significantly ahead of us since El and I worked on the sprint yesterday and both of us had a basic working knowledge of database coming into the sprint.

I have to say that Marcus really knew the database stuff and did a great job teaching us today.

Clarification on database index:

– Each row has a constant length. This is the result of each column (or cell) has a constant length and each row contains a fixed number of columns.  

– Index indeed provides constant lookup time, similar to HashTable.

Interview questions on designing schema for database:

Question: given this dataset, can you design a database schema for me?

Answer: I am afraid that I might need to know the query pattern too. The two criterion for a good schema are:

– the most frequent queries should run the fastest. Hence, query pattern is necessary to come up with a good design;

– data should be normalized to a reasonable degree. It is almost impractical to achieve  perfect data normalization in a production system. For example, 

//consider the following table

user_name, user_mom_name

Max, Alice

Andy, Emily

// at this moment, the data is fairly normalized

// however, if the most frequent query to this table is, given a username, what is his/her great, great, great, great, great mom’s name?

// then we might want to add an additional column, and precompute the “great, great, great, great, great mom’s name”

// adding a column de-normalize the data, but speeds the lookup

Marcus gave us a lecture on how to implement your own ORM for SQL.

Standard

Day 26, hiring day for the senior class

Today is Friday and it is the hiring day for the current senior class. It is one-day off for us, the junior class, but I decided to come in to check it out anyway.

The hiring day started around 2pm and ended around 6pm. It had two sessions, student presentation and “speed dating”. In the student presentation section, each group presented their group projects. In the “speed dating” section, each student occupied a work station and the employers rotated to talk to the students. Overall, a decent set up.

Oh, they ordered too much for dinner so a bunch of junior students including me got our very first free dinner at Hack Reactor. Oh yes, free food is always good. 

Personally, El and I worked on the database sprint in the morning and I spent the afternoon cleaning up my blog posts. Our own hiring day is only six weeks away and we need to step up our game.

Standard

Day 25, database

Today is the second day of the Node.js fullstack sprint, but El and I were done with it last night. Hence, we started working on the next sprint – the database sprint.

The last several sprints are built on top of each other. It is amazing. Initially, we built a chat client, with Parse.com as the backend. Then, we build a chat server with Node.js, replacing Parse.com. Then, we persisted our server side chat log to file system. Now, we are going to persist the server side chat log to databases.

It is a big hassle to talk to file system directly. Some drawbacks include:

– have to read / write the whole thing (we could break files into smaller files or try reading the file by index)

– no abstraction layer

– conflicts when the same file is read and written by multiple users at the same time

Hence, we need an abstraction layer on top of the file system. That system needs to:

– deal with tons of files and provide persistent storage.

– allow read from middle of the file and allow random access small piece of info in the middle of the file.

– keep a index of data locations

– data chunks have a fixed size

– provide speedy lookup, ideally in constant time

– support multiple programming language

– accept connection protocols similar to HTTP protocols

We briefly talked about SQL vs NoSQL. I am not very happy with the lecture. I feel like we over simplified the concept of a SQL database and created unnecessary confusion.

The purpose of index in a database:

– I had this faint idea of how index works and now I think I know better.

– Index, similar to the index pages in the back of the book, for example, the word “Long March” can be found in page 33.

– Image we have a table with 100 rows:

id, website, timestamp
1, google.com, day1
2, amazon.com, day2
3, ebay.com, day3
...
100, baidu.com, day5

– without an index, if we need to find out the timestamp for baidu.com, we would have to traverse all records before finding the result. The complexity would be O(n).

– There are multiple steps in using an index.

Step 1 is to build the index. We need to traverse the entire table and build a index. It is O(n) but we only need to do this once.

My native thoughts are index might be similar to a HashTable, hence

myIndex['google.com'] = 1;
myIndex['amazon.com'] = 2;
...
myIndex['baidu.com'] = 100;

Step 2 is to lookup the location from the index. Hopefully index behaves like a HashTable, with constant look up time. The time complexity here would be O(1).

In this case, we are looking for ‘baidu.com’, and it would return a location of 100

Step 3 is to calculate the location of the real record. Assuming each record occupy the same size, the location of “baidu.com” is: 100 * size_of_the_record

Step 4 is to visit the location of “baidu.com” directly, without visiting any of the other records.

Again, my very native understanding of how index would speed up lookups.

Relational vs non-relational databases:

Relational comes from relational algebra, a branch of mathematics dealing with sets. It makes sense since sets and tables are very similar. For example, they are unique.

SQL questions are common in job interviews. Basic syntax is SFW – select * from * where. Common topics are: normalization, schema design and foreign keys.

Types of collections between tables: 1 to 1, 1 to many, and many to many.

It is not a good idea to store arrays in a column because it is hard to index.

ORMs:

ORMs enable people to use SQL, but they might produce bad or slow SQLs.

Mongo:

– technology.

— Schemaless / doc oriented. Good for start-ups who change their schema all the time.

— auto-scaling / shading. However, auto-scaling only works with primary indices. At a large scale, you have to choose auto-scaling or secondary indices. Primary index means index based on the primary key(???).

— won’t tell you if a write fails

– user experience

– fashion

The Twitter dilemma. Natively, there are two tables.

// UserTable
user_id, user_name
1, shao
2, js
3, python

// TweetTable
tweet_id, tweet_text, timestamp, user_id
11, tweet1, time1, 1
12, tweet2, time2, 2
13, tweet3, time3, 1

//FollowTable
id, user_id, following_user
1, 1, 2
1, 1, 3

// to build shao's timeline, we would write
// pretty sure this is NOT the right syntax
SELECT * from TweetTable where user_id in (SELECT following_user from FollowTable where user_id = 1)

The problem is we need to re-build the entire index every time a new Tweet is created and the users are expecting results to be returned at almost real time. Enough for now, but I need to study more on this.

Standard

Pseudo classical, prototypal and functional

– functional inheritance -> prototypal inheritance –> pseudo classical inheritance –> hacked classical inheritance

Functional inheritance

– Functional can be achieved by any language who supports closure scope and dynamic object augmentation.

– The bad parts are duplicated properties and methods / impossible to achieve dynamic class modification.

– The good parts are: real private variables / callable instances.

Pseudo classical inheritance

– For pseudo classical, semi private variable is achieved by name convention, i.e. _variable_name.

– Comparison among pseudo classical, prototypal and functional

pseudo classical prototypal functional
start out simple no sort of yes
keep simple no yes yes
Method sharing Method sharing Method sharing with a performance penalty or capability security
Dynamic class modification Dynamic class modification Avoid using ‘this’
instanceOf works at all level instanceOf works at all level callable instance / instanceOf doesn’t work at all level / no prototypal masking
Optimized by the engine Avoid using ‘new’ and ‘.prototype’ Avoid using ‘new’ and ‘.prototype’
Standard

Different ways of doing cross origin requests

Cross origin requests:

Ajax -> CORS -> JSONP -> IFRAMES -> FLASH

Ajax:

– No cross origin by default

CORS:

– modern way

– A regular cross-origin request will turn automatically into a OPTION request. Server answers the OPTION request with either allow or disallow. The server response contains both headers and status code, although headers are way meaningful than status code in this case.

– The OPTION request is an extra round trip to the server, hence can be cached.

– The OPTION request is sent before the regular request is sent. The regular request won’t be sent until the OPTION request has been answered by the server.

JSONP:

– JSON with padding

– Implemented with cross domain script tag.

– The script tag is cross origin by nature.

– For fbapp.com, the basic version is:

<script src="http://facebook.com/all.js"></script>

– Slightly better version:

<script src="http://facebook.com/all.js?varName=result"></script>

– Best version(?):

<script>

var myFunc = function(fbData){

console.log(fbData);

}

document.write('<script src="facebook.com?callback=myFunc"></script>');

//facebook.com returns

//'myFunc( {"username":"shao", "gender":"male"} )'

//it is a JSON padded with a function call

//hence JSONP

</script>

– Always GET

– Note: document.write() is async; MIME is application/json(?)

IFRAMES:

– Old technique and still in use. Especially in China where IE6 still has 20% market share.

– Might be the only way to do cross-origin POST request in the client side.

– Check http://stocktwits.com/. Heard they did tons of those.

FLASH:

– Dinosaur. Steve Jobs was right.

Native ways to solve cross origin requests:

– Option 1: encrypt the cookie such that only requests originated from the same domain is valid.

– Option 2: white list / black list maintained by each website allowing cross-origin ajax request.

Standard

Node.js debugging

– Don’t use port 8080

– Run: node -debug-brk app.js

– Start node-inspector

– Visit the URL it outputs to you

– Good parts: can use ‘debugger / breakpoints’

– Bad parts: NO jasmine-node

– Bonus: heard that webstorm has a good Node debugging tool. Should check it out.

Standard

How to upload database to meteor.com

If you want to upload your local db to replay.meteor.com(replace replay.meteor.com with yourapp.meteor.com), follow the following steps:

Step 1, create 3 scripts file: export.sh, showdb.sh, restore.sh

For export.sh, write:

mongodump -h 127.0.0.1:82 -d meteor -o meteor
#port is 82 because I am running the local meteor at 80
#port should be your local port + 2, so the default would be 3000+2 = 3002

For showdb.sh, write:

meteor mongo --url replay.meteor.com

For restore.sh, write:

mongorestore -u client -p $1 -h production-db-a1.meteor.io:27017 -db replay_meteor_com meteor/meteor/ --objcheck --drop
#production-db-a1.meteor.io is the physical server and might be different for your app

Step 2:

sh export.sh
#this will export your db into a local file

Step 3:

sh showdb.sh
#output should be
#mongodb://client:d2f3d2a0-73eb-f641-a41b-e12517cb5a2a@production-db-a1.meteor.io:27017/replay_meteor_com
#copy d2f3d2a0-73eb-f641-a41b-e12517cb5a2a to your clipboard

Step 4:

sh restore.sh d2f3d2a0-73eb-f641-a41b-e12517cb5a2a
#that should get the local file and restore it to the server
#WARNING: it will drop your remote db at meteor.com

Note:

– The ‘d2f3d2a0-73eb-f641-a41b-e12517cb5a2a’ seems to work only once and expires in a minute. So, hurry up

– Pay attention to the server address since they do change from time to time

Standard

Hack Reactor Student Blogs

June 2013 Cohort:

Shaohttp://getshao.com
Guyhttp://guymorita.tumblr.com/
Gregblog.ideahaven.co
Jakehttp://eastbayjake.wordpress.com
Miikehttp://miike.posthaven.com
Andrewhttp://andrewspade.net/
Phillip: http://phillipalexander.io

April 2013 Cohort:

Jake Seip: http://seip.io/
Evan Peelle: http://fullstackgrowthhacker.blogspot.com
Alex Gaputin: http://googamanga.tumblr.com/
Eric Levin: http://ericrius1.tumblr.com/
Dylan O’Carroll: http://banjolina-jolie.tumblr.com
Brian Chu: http://www.brianchu.com/
Bianca Gandolfo: http://bgando.tumblr.com
Elle Beal: http://ellebeal.tumblr.com
Patrick Stapleton: http://blog.gdi2290.com
Charles Holbrow: http://charlesholbrow.com
Eric Levin: http://ericrius1.tumblr.com
Combiz Salehomoum: http://combizs.tumblr.com
Greg Palmer: http://gpalmer.me/

March 2013 Cohort
Andrew Magliozzi: http://pleasestealthisidea.com/
Selby Walker: http://selbywalker.com/
Blake Embrey: http://blakeembrey.com/
Mark Lee: http://markleeis.me/
Nima Mehanian: http://tumblr.mehanian.com/

January 2013 Cohort
Mark Wilbur: http://logicmason.com/
Mike Adams: http://mikeincode.com/
Coleman Foley: http://learningtocode.quora.com/
Howard Tang: http://howardtang.co.uk/
John Katsnelson: http://greenbunnybearrug.wordpress.com/
Tony Thomson: http://www.tonythomson.com/blog/
Gavin McDermott: http://gvnm.tumblr.com/
Andreas Nauleau: http://anauleau.tumblr.com/

November 2012 Cohort
CJ Winslow: http://youcanlearn.it/
Christen Thomson: http://whitepinedev.com/
Henry Zhu: http://henrycode.tumblr.com/

Source:

Shamelessly copied from: http://kevinhamiltonsmith.com/hack-reactor-student-blogs/

Standard

Day 24, full stack

Another Node.js sprint, covering the entire technology stack necessary to build a web application. Marcus made a great graph to show all the moving parts when a user clicks a button. Just like one of our classmates commented the other day, “only after attending this class that I begin to appreciate all the hard work needed to make a simple website work”.

Briefly, there are a client and a server.

Client side:

– The client can be a laptop or a desktop.

– user –> DOM –>$–> js code (inside a js env) –> local storage –> disk

————————— js code (inside a js env) –> ajax –> network interface card —> HTTP requests

– the js env can have client MVC frameworks such as Meteor, Ember, Angular or Backbone

Server side:

– URLs are either static or dynamic

– Static URLs for images tend to be offloaded to CDNs

– Dynamic URLs tends to hit RESTful endpoints

HTTP –> routers –> REST API –> js code (likely inside a MVC framework) –> ORM –> cache –> database

– ORMs such as Active Records

– cache such as memcached

For this sprint, we introduced an extra worker in addition to the traditional client-server-database 3-tier system. Workers in real life can be searching by hashtag in Twitter or getting timeline for user or data analytics workers.

We talked about service oriented architecture, where apps are broken into smaller pieces based on services. This brings modularity to the system, where as long as the interface of the module is consistent, the module itself can be completely rewritten.

The senior class presented their projects and they used all kinds of tools. Most popular ones are: Backbone, socket.io, D3. At least two teams are having problems removing zombie events in Backbone, hehe.

Standard