As discussed in #11, calculating schema information can be costly, especially for databases with a large number of schemas/tables.

We should cache the schema info and only regenerate it if the user specifically asks for it.

Hey Rick,

I'd like to propose a configuration system feature. This would allow administrators to change SqlPad's behaviour on runtime via key/value pairs defined in the configuration interface.

My initial implementation (screenshot) is here on a separate branch. I've also implemented a small feature using the config system.

Any thoughts on this? Do you think it's a worthy addition?

I'm working on a feature in sqlpad that I want to submit as a PR. I think I have it working on my local machine, but I want to run my branch in place of my company's live sqlpad instance so I can test it against real data.

I can't figure out how to get this working, though. I've tried checking out my branch on our servers and running

npm start --dir /path/to/pre_existing/data/dir --port 80 --passphrase MYPASSPHRASE

but I get

Error: ENOENT, open ' /path/to/pre_existing/data/dir/package.json'

Could you please add to the README a description of how to get this working?

Thanks a lot for this project.

Solution for issue #9 will allow user to download a .png file of the generated chart. The chart UI should provide an option to show the chart legend to help provide context that would have otherwise been provided on mouse hover.

When trying to use sort on charting it looks like the current version of underscore does not support it... maybe switch to lodash?

SQLPad is great!!!

One suggestion: SQLPad already have csv download option. Could have excel option too? This will eliminate one step, save in csv, import to Excel, send to user. There are this guy here:
https://github.com/mgcrea/node-xlsx

Very nice and simple.

Hi，I'd like to set mysql port 33068 on sqlpad ,rather than 3306.How to do?

I'd like to get some feedback on how charting is handled in SqlPad.

Currently SqlPad utilizes the dimple charting library, which is based off of d3. I'm in the process of exploring the switch to tauCharts, also based off d3. So far it looks really promising, and the API fits SqlPad's use case really well.

Should SqlPad support more than 1 charting library?

If so which ones and what's the limit? Maybe keep them all d3 based?

Or what about some sort of plugin system where charts can be configured and added on a per-instance basis of SqlPad?

Personally I lean toward keeping SqlPad focused on 1 charting library, so it can be optimized to a certain api, ensure things remain compatible, and keep things simple in general. However I think a plugin system could be useful... I don't know what that'd look like.

I'm open. Maybe someone has an elegant idea out there? Or maybe others would also like to keep SqlPad focused on 1 library?

Hi,
would be nice to have a Dockerfile direct in this repo and maybe and image on hub.docker.com

@dcelasun I'm going to be cleaning up the front-end query/chart editor code a bit. Just wanted to give you a heads up in case you are doing anything with it, so you don't end up with a bunch of conflicted code.

Hi, thank you for sharing this.

To create a connection on Azure for example, one need to set the encrypt option to true here, as defined here

It could be nice to be able to configure it somewhere, or to document it at least.

Cheers,

Followup from issue #6. I don't have the time to work on this enhancement right now, nor am I familiar enough with streams, but if I had the resources this is something I'd like to do...

Investigate streaming query results to the browser over websockets. The results would still need to be limited by max rows so we don't crash the browser. All uses of the runQuery functionality would need to support the streaming results, not just the core query editor (schema sidebar and connection test).

It would be something like the following:

• query results are streamed from database to nodejs (already done).
• rows then piped to a metadata categorizer/collector (the metadata would be sent to browser separately)
• rows stream is then duplicated/split (is this possible?)
  • one record stream goes into a record limiter thing. records under the limit are piped to browser
  • the other record stream gets piped to csv file (there's no reason the csv can't have all the records)

SlickGrid wouldn't load the records until the results are fully streamed to the browser. In the grids place, we could have a large font timer running, as well as a large-font record counter. These things would be a good indicator of progress for the end user.

With all these changes, it would also be nice if we could cancel or stop the query at any time from the UI. This depends on the database driver to some extent. Some drivers can be explicitly cancelled (SQL Server) while others need to just be killed/closed.

Thoughts?

the server will be cashed while querying a table ,if there are huge data in the table.Because the data isn't showed by page. Pls add the function and fix it . Thanks

Would it be helpful/cool if the charts created with SqlPad could be saved to a dashboard?

Here's how I'm envisioning it would work: when working on a query/chart, you have an option to save the chart to a dashboard. When opening the dashboard all the underlying queries would run automatically to refresh the data. Dashboards wouldn't be too configurable - it'd be more like a vertical listing of charts.

Maybe within the dashboard view, there could be an edit option to arrange the order of the charts...

This wouldn't be a full-blown replacement for the usual dashboarding software, nor would it be a thing that you'd throw up on a monitor and have it refreshing all day.

This would be more like a report that you could run, that'd be a collection of charts.

This is rather annoying in some cases when localization needs to be done through SQL because the date output is by day and not hour (which is required to properly localize). Can this be made into an option?

Hi Rick,

Congrats on the success of sqlpad. It's a very important project I think as there are only a few expensive solutions for this sort of thing in the market so your contribution is huge. Great work!

One suggestion I would like to make for making your logic a little cleaner is to return early and return often.

Example..

https://github.com/rickbergfalk/sqlpad/blob/master/routes/onboarding.js#L36

Instead of nesting the entire signup success logic in an else statement just return the error first.

return res.render('signup', {message: 'passwords are not match'});

Another example of where you can just return early and reduce the christmas tree affect...

https://github.com/rickbergfalk/sqlpad/blob/master/routes/onboarding.js#L71

I'm sorry if this comes off as petty but I do think that clean, easy to follow code helps to increase contribution rates.

Again, I think your work is fantastic. Just trying to help.

Sorry if this is already possible, but I couldn't find any way to use SqlPad behind Apache ProxyPass: the problem is that all the URIs in the returned contents uses absolute paths, so if you proxy https://www.example.com/sqlpad to SqlPad, the very first redirect would go to the (non existent) https://www.example.com/signup and not the expected https://www.example.com/sqlpad/signup.

AFAICS Express.js does support this, but I had never used it before, so I'm not sure how exactly to do this.

Trying to connect to an older mysql database

Same issue as: (resolved)
sequelize/sequelize#1847

Currently in SqlPad, if you forget your password you are out of luck. Eventually an admin/cli interface should be able to reset a user's password, allowing them to create a new one.

I deal with real-time data.
Being able to auto-refresh the query based on a selectable or inputted value would be helpful in visualizing the charts and/or data returned.

As you can see in this screen shot, unnamed columns have no width. So they don't get displayed.

I hope this helps.

I'm using postgres with materialized views. It would be nice if they showed up in the list of tables on the left of the query pages

@rickbergfalk Hey Rick, I just realized text selection is disabled on query results. I thought maybe you left it disabled on purpose, so I pushed it to a new branch. Should I merge it to master?

Running sqlpad with --debug should log additional information server side (and maybe eventually client-side too?).

For starters this could be logging all sql queries run (see issue #65).

Could we add something like this to save the chart image too?

function SaveChart()
{ TheCanvas=$("#canvas")[0];
if(TheCanvas.toBlob) {
TheCanvas.toBlob( function (blob) {
saveAs(blob, "chart.png");
}, 'image/png' ); }

if(!TheCanvas.toBlob){
var dataURL =TheCanvas.toDataURL('image/png') var w=window.open('about:blank','image from canvas');
w.document.write("Save As Image"); }

}

If a user not having any queries clicks on "Queries" menu link (/queries?createdBy=xxx%40yyy.com), she gets an empty list of queries. Created by filter shows that "Everyone" is selected in this case, which is misleading, because if the user either removes url parameter or clicks on any other user and then clicks back on "Everyone", she'll see all queries in the system (by all users).

Oracle has a Javascript Driver oracledb. Perhaps good to include it in this great application?

Just to flex my fingers I added support for the SAP Hana database. Anyone interested in this addition?

Disabling Google authentication streategy, since there's no GOOGLE_CLIENT_ID or no GOOGLE_CLIENT_SECRET in ENV.
events.js:141
throw er; // Unhandled 'error' event
^

Error: listen EACCES 0.0.0.0:80
at Object.exports._errnoException (util.js:837:11)
at exports._exceptionWithHostPort (util.js:860:20)
at Server._listen2 (net.js:1218:19)
at listen (net.js:1267:10)
at Server.listen (net.js:1363:5)
at Object. (/usr/local/Cellar/nvm/0.26.1/versions/node/v4.1.0/lib/node_modules/sqlpad/server.js:149:24)
at Module._compile (module.js:434:26)
at Object.Module._extensions..js (module.js:452:10)
at Module.load (module.js:355:32)
at Function.Module._load (module.js:310:12)

Hi, password for DB is mandatory when creating connection, but our test DB doesn't have password. Would it be possible to make password optional here?

Currently there are 3 ways of setting config values in Sqlpad - the UI, cli flags, and environment variables.

I think it'd be great if all 3 methods could accept the same config key/value setting where appropriate.

As discussed in #71, env variables would have the format SQLPAD_CONFIGKEYNAME. SqlPad command line flag --CONFIGKEYNAME should be honored as well.

Existing config key/values that don't fit the convention should be honored.

It has been a while since these have been visited.

Having completx table names (especially in vertica), it would help to be able to right click the table or column name and be able to paste the fully qualified path.

I'm thinking about getting rid of query tags. Anyone want to talk me out of it? :)

I thought they'd be useful, but now I'm not so sure. I haven't tagged most the queries/charts I've written, and there isn't really a way to mass-manage tags at all. If query tags are in fact useful, I think the UI should be built out a bit to make using them more pleasant - auto-complete, mass tag assignment, tag renaming, tag merging, etc. (something like gmail labels, except without the nested part)

Any thoughts?

cc @dcelasun

If there are no objections, I'd like to introduce VoltDB support in the next few weeks/months.

VoltDB is a bit different compared to other SQL databases, but the node client would allow us to at least introduce basic query support in sqlpad.

Here is the stacktrace.

Object [object Object] has no method 'handleEmptyQuery'

/usr/local/lib/node_modules/sqlpad/node_modules/pg/lib/client.js:118
self.activeQuery.handleEmptyQuery(con);
^
TypeError: Object [object Object] has no method 'handleEmptyQuery'
at null. (/usr/local/lib/node_modules/sqlpad/node_modules/pg/lib/client.js:118:24)
at EventEmitter.emit (events.js:95:17)
at Socket. (/usr/local/lib/node_modules/sqlpad/node_modules/pg/lib/connection.js:109:12)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket. (stream_readable.js:746:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable (_stream_readable.js:408:10)
at emitReadable (_stream_readable.js:404:5)
at readableAddChunk (_stream_readable.js:165:9)
at Socket.Readable.push (_stream_readable.js:127:10)

At work, I have three Microsoft SQL Server instances and each has multiple databases inside.

Some of my existing queries touch multiple databases. This works fine in SqlPad, but the schema view on the left is tied to a single database.

So, if I want to review the columns in some table, I have to create another connection which is tied to the database that table resides in.

Meanwhile, in SQL Server Management Studio, all the databases I have access to appear in the schema view on the left.

So, the feature I am requesting is an additional level of abstraction above 'database' in the schema view.

Not sure if there is a way to do this already.

On some of my scripts, it would be great to send the user a link based on programmatic variables (sending them to a saved query won't work because the script result is dynamic) and have the option to force a CSV download.

example: http://localhost:3000/querie/database_name?"select * from table_name",csv=true

Hello,

How can I connect to a MS SQL Server using a named instance and a specific port ?

I tried to pass the instance name in the server field as servername\instance and the port in the port field but with no success.

I saw in the Tedious driver documentation that instance and port option where mutually exclusive.
Do this means this is a no go ?

Regards
Olivier

I installed a local version of SqlPad, loaded it up in a browser, connected to a remote database, and ran a query with no problem.

I then installed a version of SqlPad on an EC2 instance, loaded it up in a browser, connected to the same remote database, tried the same query, and got the error "error: cannot insert multiple commands into a prepared statement". I found this page (brianc/node-postgres#33) which seems possibly related, but I couldn't tell for sure.

Is it possible that there is some dependency that is not listed in the package.json that I have locally but am missing on the server?

The query looks like:

/* create temporary table */
create temporary table users_from_acme_company (
    user_id uuid
);

/* select data into the temporary table */
INSERT INTO users_from_acme_company (user_id)
select user_id from users
join institutions_users iu on iu.user_id = users.id
join institutions on institutions.id = iu.institution_id
where institutions.name = 'ACME';

select * from users_from_acme_company;

Query result columns are correct but fine but after exporting to .xlsx it displays only date. CSV is also correct.
Datetime in this format: YYYY-MM-DDTHH:MM:SS.000Z
Example: 2015-11-11T12:30:45.000Z

Maybe it is just a function of xlsx that it doesn't understand this datetime format?

FYI, I am seeing this in my log:

Row limit hit - Attempting to cancel query...
Query cancelled successfully

I read somewhere that the default row count limit is 50,000. Even if it were only 1000, the fact is that none of the tables I have queried today contain more than a few dozen rows.

I think the row limit is being hit by some internal query that SqlPad is doing automatically. This might indicate a bug somewhere...

I'm using 1.12.0 installed via NPM. My DB is MS SQL Server. If you need other information or would like to recommend some settings I can change to get more detailed logs, just let me know.

Hi. I'm using Firefox 45.0a1 (2015-12-11) (64 bit, Nightly) on Windows 7.

The results table looks like this:

I hope this helps.

Hello,

This is not an issue, but I am having problems connecting to our network SQL Server (ELOGIN error). I believe this is because we are using Windows Authentication/Trusted Connection. Does SQLpad support this connection setting?

Thanks,
JD

For the tags input, I'm contemplating changing libraries from bootstrap tags input and typeahead/bloodhound to just Selectize.js.

I've been noticing some weirdness with bootstrap tags input and I remember it being a hassle to set up since the auto-complete depends on multiple libraries (which are in a state of change - typeahead was just rewritten).

Thoughts?

See previous discussion in issue #10

Lots of room for improvement for db info tree performance. For databases with few objects things are okay, but for large ones its a pretty rough experience.

Things to for sure do:

• Don't use jQuery hide/show, use CSS and addClass/removeClass instead
• use a view template instead of building each element individually (maybe even do this on server?)

And if that's still not good enough:

• Only get table columns when needed

when I run the sqlpad, I notice that every time when I reload the schema-info of the connections(I use a mysql connection),it will porduce a new cache-key in the cache.db file, so I think in the schema-info.js
if(!cache || reload) should change to if(!cache). In that case, when reload it, it will not produce a new cache record.

SqlPad doesn't support multiple result sets.

Here's the same query in SSMS: