Jamandbreadd, I would tell your husband that it will be a very big concern that you will not be earning money when you move to hipster-heaven Brooklyn.
Start writing down some numbers and have some discussions about money with him.
How much income do the two of you bring home right now, combined? Write it down. How much income will he be bringing home each month in the new job in New York? Remind him that you will not be earning money. Does he know precisely what he will be receiving each month, after taxes and health insurance premium payments have been deducted?
Will his company pay for moving expenses for all three of you? Will health insurance cover all three of you? If it's hard to find a good school, private school for her will be expensive.
Presumably he's thinking he wants to live in Williamsburg, Brooklyn. Is that where his friend lives? All of the cool neighbourhoods in Brooklyn are very expensive and you'll need a two-bedroom apartment. Can you start researching the cost of monthly rent for 2-bedroom apartments? Also, the cost of heat, electricity, wifi, food shopping.
Don't be afraid to stand your ground and have some discussions about income and costs. As you say, he tends to be naive and he's excited about this job opportunity but it's not fair for him to tell you, "oh, things will all work out, you're just scared, blah, blah.". I would be very concerned about you having to sacrifice your job.