You speak like a green girl / unsifted in such perilous circumstances.

February 11, 2016 3:35 PM

I would like to create a database, using MS SQL, MySQL or SQLite. I would also really like to create some sort of user interface on top of the database so people can enter information in forms and be able to view reports where the information is sorted and filtered in various ways to make the important stuff a little more easily readable.

I would love it if I could use JavaScript for this since that's what I'm familiar with. Could I create some sort of browser-based GUI? Ideally it would be something hosted on our local server as only one location requires access, but it would be great if it could be securely expanded to non-local hosting in the distant future.

But I'm new to JavaScript and even conceptually I have a hard time thinking how this would work since JSON APIs seems so linear and SQL databases are relational, which I need. I've been advised that PHP and/or Java would do what I want, but my heart belongs to JavaScript, so I don't want to leave it if I don't have to. Is what I ask possible? I feel like my naivete here may deserve some "Oh you poor innocent child..." responses, as I may be underestimating the difficulty of this project. Ha.

posted by FiveSecondRule to Computers & Internet (13 answers total)

You can probably do it all in javascript. Have you looked into node.js? I don't know much about javascript but my understanding is that it's a way for you to do backend-type stuff in javascript.

If you decide you need to do it in a non-javascript language, do yourself a favor and do not do it in PHP. IMO PHP is a very dead-end language. A lot of prototype stuff gets written in PHP, by people who in my experience mostly regret it later.

I don't particularly love Java either.

I think I'd probably recommend something like Ruby on Rails or maybe a python-based web solution like Flask or Django.
posted by RustyBrooks at 3:42 PM on February 11, 2016 [1 favorite]

are you planning on using javascript on the server? do you understand the difference between server and client? (sorry if this seems insulting!)

i really don't understand what you want in any detail, but it's just possible a tool like htsql may be what you need.
posted by andrewcooke at 3:43 PM on February 11, 2016

Best answer: This it not really my area, but you can likely get most of the way there plodding your way through an Express, some Node ORM (a little googling turned up Sequelize and Node-ORM2.

This is definitely something you can do. It'll take a lot longer than you expect. (I also have no real experience with backend Javascript, so no idea of the learning curve.)

I think Rails is complete overkill for something like this (granted, I'm not a web developer and I think Rails is overkill for everything). Flask or Sinatra would be reasonable, in Python or Ruby, respectively.
posted by hoyland at 3:44 PM on February 11, 2016

If this is going to be on the public internet (or an even slightly risky intranet), you must be aware of how SQL Injection works and how to prevent it, or your data will be damaged or stolen and the server will possibly be hacked.
posted by Candleman at 4:04 PM on February 11, 2016 [1 favorite]

Browsers change, and the kind of Javascript system necessary to make a GUI semi-browser-neutral will a) take a lot of space/bandwidth/time to load, and b) will likely not be maintained forever and will break on some future browser. Also, every single piece of code written is likely to be used for far more years than its author predicts. Just...think about that when considering your system.

I think best practice would probably be a system with enough server-side HTML generation that it will work, with some added client-side Javascript to make it nicer (but no client/browser-based Javascript that will be missed too much if it stopped working).

OR you might find a pre-written database front-end that will take care of most of this for you.
posted by amtho at 4:06 PM on February 11, 2016

Best answer: As far as databases go, SQLite is the easiest since there's no installation required, and you don't have to worry about permissions or any of that nonsense.

If you just want to get a thing working, there is probably an existing product that does this (I don't know of any offhand, but that HTSQL thing above looks promising). If you're doing this to learn, it's doable, but you have a long road ahead of you.

It's entirely plausible to do this in JS on the server, using Node.js. Node is both simpler (because no browser compatibility or DOM to worry about) and hairier (because tons of choices and design patterns) than JS in the browser.

It's a huge topic, but I suggest looking into the following:

• Async/Callbacks/Promises -- this is a Big One and probably the hardest to wrap one's head around. You'll need this for both database operations on the backend and HTTP requests on the frontend.
• NPM (Node Package Manager)
• REST principles and Express.js

(Also, I highly recommend using a UNIX-based OS like Linux or OS X. Node works in Windows, but lots of packages, utilities, and tutorials for it assume a UNIX environment.)
posted by neckro23 at 4:07 PM on February 11, 2016

Depending on how complex the filtering it might be possible to do this in google docs. Google sheets has a "filter" formula that's like doing a live query.
posted by bleep at 4:49 PM on February 11, 2016

Since I do work very similar to this for a living (albeit on a larger scale), I'd feel bad if I didn't at least point out that good old FileMaker Pro can be a very flexible, easy to use and easy to develop front end to MS SQL and MySQL.

Obviously, if your goal is to learn ways to code this manually, if you are on a tight budget, or if you simply don't want to buy a commercial software package, this won't be your solution. But if you just want to get this up and running quickly and relatively painlessly and then share your solution with your users, you should at least look at it. Doing this in FMP is very straightforward, and its environment makes it very easy to add or modify your solution at any point.
posted by mosk at 5:51 PM on February 11, 2016 [1 favorite]

In a client/server environment, apps like this, Microsoft Access was the go-to software for years. Still works, of course, though it's not fashionable. I see that you can do web apps with Access and Access Services. I have no experience with this personally.

The problem with saying you are going to do it in JavaScript is that it really means you are doing it in JavaScript and HTML (including style sheets) and SQL, and maybe something I forgot.
posted by SemiSalt at 5:52 PM on February 11, 2016 [1 favorite]

As someon said upthread, you don't want to take on building a web app unless you understand security (and, presumably performant code and query management, if your app is going to get much use). If your goal is to learn to write software, this sounds like it might be a fun project (though I think most web folks would build it as a combination of JavaScript and back end code such as php, Java, rails, perl, .NET, or heck, even ColdFusion). If your goal is to get something up by next Tuesday without crying tears of frustration, you may want to use something off the shelf. I'm sure there are a lot of options; one I discovered a couple of days ago is Gravity View, a WordPress plugin that works with Gravity Forms to allow people to enter, filter, and display form data.
posted by instamatic at 8:45 PM on February 11, 2016 [1 favorite]

Here's a quick overview of client side vs server side vs hybrid web development, which you might find useful. If you're not confident in your skills with respect to coding secure, efficient code, you might explore using packages to manage some of the basic plumbing.
posted by instamatic at 8:53 PM on February 11, 2016

Best answer: I would like to create a database, using MS SQL, MySQL or SQLite.

If this is on the internet, in web browsers, I do not recommend SQLite. It's great for development, but inherently single user. It will fall down when two people try to browse your site simultaneously.

If it's at all possible, I recommend adjusting your requirements to PostgreSQL, which has a set of JSON oriented features.

SQL injection fears are real, but prepared statements are a simple and effective means of defeating this class of security flaw. I'm not a node developer, but node-postgres appears to support prepared statements while giving you the javascript environment you're comfortable with.
posted by pwnguin at 1:26 AM on February 12, 2016 [1 favorite]

Response by poster: Sequelize/PostgreSQL look very promising. And given how my previous attempt at node.js server on Windows resulted in my all-electronics-must-die-I-hate-computers rampage of 2015, I think I'll take neckro23's advice and try it on Linux. Thanks!
posted by FiveSecondRule at 11:12 PM on February 13, 2016