8 ms·

Hello everyone! Author here. I didn't expect anyone to find this repo, much less post it on Hacker News! This project is inactive for two main reasons: - SQLi

Hello everyone! Author here. I didn't expect anyone to find this repo, much less post it on Hacker News! This project is inactive for two main reasons: - SQLite is not a great general-purpose SQL engine. Poor performance of joins is a serious problem that I couldn't solve. The virtual table support is good but not quite good enough; not enough parts of the query are pushed down into the virtual table interface to permit efficient querying of remote tables. Many "ALTER" features are not implemented in SQLite which is a tough sell for experimental data manipulation. - T-SQL, the procedural language I chose to implement atop SQLite, is not a great general-purpose programming language. Using C# in LINQpad is a more pleasant experience for experimentally messing around with data. R Studio is a good option if you need statistical functions. I think several good solutions in this problem space exist. A local install of SQL Server Express can be linked to remote servers, allowing you to join local tables to remote ones. That setup serves nearly all of SQL Notebook's use cases better than SQL Notebook does. LINQpad is also very convenient for a lot of use cases. I appreciate the interest! I may spin off the import/export functionality into its own app someday, as I had a lot of plans in that area, but I think SQL Notebook as it stands is a bit too flawed to develop fully.

j_s 9y ago
Sounds like a summary of this comment should be added to the README / web page as a disclaimer!
infogulch 9y ago
I feel like rewriting this on top of SQL Server Express would still be valuable. Setting up and managing linked servers manually, especially with non-SQL Server databases can be a pita especially considering managing their drivers etc. Also, SQL Server is awful at importing/exporting data directly. Almost all forms are broken in various ways and are a source of endless grief. The only reliable built-in way is to use SSIS which is like using a 200-ton tank for a paperweight.
- jimktrains2 9y ago
  > I feel like rewriting this on top of SQL Server Express Why choose a proprietary engine? Why not use PostgreSQL or even MySQL?
  - krylon 9y ago
    There are many business applications that target SQL Server exclusively. On the one hand, I find that highly annoying. On the other hand, even if you don't like Microsoft, SQL Server is fairly awesome, performance-wise and stability-wise. But still, there are many applications where you cannot really choose what database engine you want to use.
  - frik 9y ago
    I can recommend HeidiSQL, a useful GUI for MySQL, MariaDB, MSSQL and Postgres. https://en.wikipedia.org/wiki/HeidiSQL https://en.wikipedia.org/wiki/HeidiSQL They probably meant SQL Server Compact, which is a smaller package and comparable to SQLite. It was meant to power WinFS back in 2006. But it's closed source and now legacy. https://en.wikipedia.org/wiki/SQL_Server_Compact https://en.wikipedia.org/wiki/SQL_Server_Compact
  - infogulch 9y ago
    I suppose PostgreSQL+FDW would be just as good as SQL Server Express+Linked Servers technically. You end up trading users' MSSQL experience for pgsql. I wonder if it would be easy to support both backends and be able to switch between them depending on the query and which features and integrations you want.
- Pxtl 9y ago
  > Also, SQL Server is awful at importing/exporting data directly. Almost all forms are broken in various ways and are a source of endless grief. The only reliable built-in way is to use SSIS which is like using a 200-ton tank for a paperweight. So much this. BCP is inferior to 5 lines of C# code, the tools for importing and exporting data in SSMS are buggy and cumbersome, SSIS is a gigantic monster. Simple "Export the results of my query into an INSERT statement" is a moronically obvious workflow and is non-existent. I've read that the tools division of MSSQL was running on a skeleton crew for a long time, and it shows.
  - mistermann 9y ago
    Despite everyone saying SSMS is hands down the best database IDE, while this may be true it is also true that MSSQL tooling is a train wreck compared to what it could be. It almost seems Microsoft intends to keep it this way, perhaps because they'd have to finally admit to the decade of neglect? Who knows the exact reason, but it's a sad situation.
    - infogulch 9y ago
      What kind of tooling are you speaking of? Honest question.
      - Pxtl 9y ago
        Sql Server Reporting Services is great but the server website was obviously mid-2000s until very recently, even containing markup that wouldnt render in Chrome. SSIS is a hellscape of unnecessary designers for an ETL framework, and generates ultra-brittle packages that crash if the slightest schema tweaks happen. SSDT stores your schema and publishes diffs for easy deployment, but will gobble up all your RAM for half an hour to compile... if it doesn't crash. And SSMS has the worst tabbed interface ever for managing queries.
        infogulch 9y ago
        I agree with you on all points. Products on the periphery of SQL Server have been severely neglected for years. There is a replacement for some of SSRS in PowerBI and SSAS, which is actually pretty nice. If I'm not mistaken, people serious about version control and deployment in SQL Server use redgate software. My question is how other databases fare in these areas. I'm not familiar with any solutions for Reporting UI, ETL, and Schema Version Control in MySQL or PostgreSQL. What built-in tools do other databases have that outperform SQL Server's (obviously crumbling) functionality?
iagovar 9y ago
Do you recommend it for a noob on data analysis? I'd basically set up a MySQL or PostgreSQL somewhere else (like a vps) and load and download data from there.
daveorzach 9y ago
Can your software use ODBC connection (specifically System DSN sources)? I'm waiting for my IT dept to install .NET 4.6 so I can't try your software right now.
mtwshngtn 9y ago
Is it not possible for you to adapt the code to use a different SQL engine as the backend? Or is it tightly integrated?
dstroot 9y ago
For those looking at more extensible options I highly recommend SQLPad: https://github.com/rickbergfalk/sqlpad https://github.com/rickbergfalk/sqlpad A web app for writing and running SQL queries and visualizing the results. Supports Postgres, MySQL, SQL Server, Crate, Vertica, and Presto. Written in Node.js.
awkwardtortoise 9y ago
> T-SQL, the procedural language I chose to implement atop SQLite, is not a great general-purpose programming language. T-SQL ( I'm assuming the microsoft variant ) is not a procedural language, it's a declarative language. > Using C# in LINQpad is a more pleasant experience for experimentally messing around with data. This is not true. T-SQL is as close to the data as you can get. C#/LINQ/EF/etc are great for business layer/presentation layer. But for messing around with data, you can't really do any better than T-SQL. Trust me on this. If you are a front-end developer, then LINQpad is great in building .NET code/CRUD libraries/etc for dealing with data, but if you want to work with data, T-SQL is where it is at. Developing expertise in T-SQL ( it's not as easy as people think ) will open up a new way of understand data/programming/thinking. SQL Server allows importing of .net code/functions which you can run on the sql server via T-SQL so there isn't really much you can't do with T-SQL. > A local install of SQL Server Express can be linked to remote servers, allowing you to join local tables to remote ones. SQL Server Developer Edition is free now. That is a great starting RDBMs to learn databases on windows environment. It has almost everything other than enterprise level features ( clustering/replication/etc ) that most people don't need.
- Pxtl 9y ago
  > T-SQL ( I'm assuming the microsoft variant ) is not a procedural language, it's a declarative language. I think he's referring to the terrible terrible terrible procedural components of T-SQL - cursors, local variables, IF/THEN control blocks, etc. Right Tool For The Job. Sql is good for querying/storing data, so use it for that. Then let the C# handle manipulating the data and applying rules and whatnot.
  - treebeard901 9y ago
    Since SQL Server 2005, modern alternatives to the procedural components have been added to the product. Even with these features existing it does not make any sense to call SQL a procedural language. Choosing the right tool for the job is certainly important. I notice many people write off SQL almost immediately when it is mentioned here. Often they come from a different language and do not see the value in the relational model. It's unfortunate too because there is real value in being able to go between a relational mindset seamlessly to an object oriented or functional one.
coleifer 9y ago
SQLite definitely presents some unique challenges, despite it generally being the "simplest" relational database. I've written a web-based SQLite database browser, which you can find here: https://github.com/coleifer/sqlite-web https://github.com/coleifer/sqlite-web -- importantly, it supports all the `ALTER TABLE` statements you'd expect (add column, drop column, rename column, etc). This is done by renaming the table temporarily, creating a new table with the desired changes, then copying the data into the new table. Kinda clunky, but it works :)
- tenken 9y ago
  doesnt support expressions on DEFAULT keyword of create table syntax. :(
- kalleboo 9y ago
  Which is basically how MySQL ALTER TABLE works internally anyway...
Pxtl 9y ago
> T-SQL, the procedural language I chose to implement atop SQLite, is not a great general-purpose programming language The day every .NET shop on Earth admits this is the day I'll be a happy man. A SQL server is not for writing business code.
- deleted 9y ago
  [deleted]
- yawaramin 9y ago
  You mean MSSQL? Because Postgres has an excellent procedural language built-in, as well as support for lots of others....
  - Pxtl 9y ago
    Perhaps it does. I've dealt with horrible procedural code in t-sql and pl/SQL and maybe Postgres is better but I'm in no hurry to find out.
    - yawaramin 9y ago
      I'm a Scala developer by profession and have dealt with horrible Scala code. People can write horribly in any language.
  - tomc1985 9y ago
    I agree with this but also with the other child comment; it is really really easy to write unreadable plpgsql
nwienert 9y ago
You may be interested in Bedrock, which makes SQLite more performant and clusterable http://bedrockdb.com/ http://bedrockdb.com/
- tedmiston 9y ago
  What an interesting project. Until now SQLite at scale has sounded like an oxymoron to me.

j_s 9y ago

Sounds like a summary of this comment should be added to the README / web page as a disclaimer!

infogulch 9y ago

I feel like rewriting this on top of SQL Server Express would still be valuable. Setting up and managing linked servers manually, especially with non-SQL Server databases can be a pita especially considering managing their drivers etc. Also, SQL Server is awful at importing/exporting data directly. Almost all forms are broken in various ways and are a source of endless grief. The only reliable built-in way is to use SSIS which is like using a 200-ton tank for a paperweight.

jimktrains2 9y ago
> I feel like rewriting this on top of SQL Server Express Why choose a proprietary engine? Why not use PostgreSQL or even MySQL?
- krylon 9y ago
  There are many business applications that target SQL Server exclusively. On the one hand, I find that highly annoying. On the other hand, even if you don't like Microsoft, SQL Server is fairly awesome, performance-wise and stability-wise. But still, there are many applications where you cannot really choose what database engine you want to use.
- frik 9y ago
  I can recommend HeidiSQL, a useful GUI for MySQL, MariaDB, MSSQL and Postgres. https://en.wikipedia.org/wiki/HeidiSQL https://en.wikipedia.org/wiki/HeidiSQL They probably meant SQL Server Compact, which is a smaller package and comparable to SQLite. It was meant to power WinFS back in 2006. But it's closed source and now legacy. https://en.wikipedia.org/wiki/SQL_Server_Compact https://en.wikipedia.org/wiki/SQL_Server_Compact
- infogulch 9y ago
  I suppose PostgreSQL+FDW would be just as good as SQL Server Express+Linked Servers technically. You end up trading users' MSSQL experience for pgsql. I wonder if it would be easy to support both backends and be able to switch between them depending on the query and which features and integrations you want.
Pxtl 9y ago
> Also, SQL Server is awful at importing/exporting data directly. Almost all forms are broken in various ways and are a source of endless grief. The only reliable built-in way is to use SSIS which is like using a 200-ton tank for a paperweight. So much this. BCP is inferior to 5 lines of C# code, the tools for importing and exporting data in SSMS are buggy and cumbersome, SSIS is a gigantic monster. Simple "Export the results of my query into an INSERT statement" is a moronically obvious workflow and is non-existent. I've read that the tools division of MSSQL was running on a skeleton crew for a long time, and it shows.
- mistermann 9y ago
  Despite everyone saying SSMS is hands down the best database IDE, while this may be true it is also true that MSSQL tooling is a train wreck compared to what it could be. It almost seems Microsoft intends to keep it this way, perhaps because they'd have to finally admit to the decade of neglect? Who knows the exact reason, but it's a sad situation.
  - infogulch 9y ago
    What kind of tooling are you speaking of? Honest question.
    - Pxtl 9y ago
      Sql Server Reporting Services is great but the server website was obviously mid-2000s until very recently, even containing markup that wouldnt render in Chrome. SSIS is a hellscape of unnecessary designers for an ETL framework, and generates ultra-brittle packages that crash if the slightest schema tweaks happen. SSDT stores your schema and publishes diffs for easy deployment, but will gobble up all your RAM for half an hour to compile... if it doesn't crash. And SSMS has the worst tabbed interface ever for managing queries.
      - infogulch 9y ago
        I agree with you on all points. Products on the periphery of SQL Server have been severely neglected for years. There is a replacement for some of SSRS in PowerBI and SSAS, which is actually pretty nice. If I'm not mistaken, people serious about version control and deployment in SQL Server use redgate software. My question is how other databases fare in these areas. I'm not familiar with any solutions for Reporting UI, ETL, and Schema Version Control in MySQL or PostgreSQL. What built-in tools do other databases have that outperform SQL Server's (obviously crumbling) functionality?

iagovar 9y ago

Do you recommend it for a noob on data analysis? I'd basically set up a MySQL or PostgreSQL somewhere else (like a vps) and load and download data from there.

daveorzach 9y ago

Can your software use ODBC connection (specifically System DSN sources)? I'm waiting for my IT dept to install .NET 4.6 so I can't try your software right now.

mtwshngtn 9y ago

Is it not possible for you to adapt the code to use a different SQL engine as the backend? Or is it tightly integrated?

dstroot 9y ago

For those looking at more extensible options I highly recommend SQLPad: https://github.com/rickbergfalk/sqlpad https://github.com/rickbergfalk/sqlpad A web app for writing and running SQL queries and visualizing the results. Supports Postgres, MySQL, SQL Server, Crate, Vertica, and Presto. Written in Node.js.

awkwardtortoise 9y ago

> T-SQL, the procedural language I chose to implement atop SQLite, is not a great general-purpose programming language. T-SQL ( I'm assuming the microsoft variant ) is not a procedural language, it's a declarative language. > Using C# in LINQpad is a more pleasant experience for experimentally messing around with data. This is not true. T-SQL is as close to the data as you can get. C#/LINQ/EF/etc are great for business layer/presentation layer. But for messing around with data, you can't really do any better than T-SQL. Trust me on this. If you are a front-end developer, then LINQpad is great in building .NET code/CRUD libraries/etc for dealing with data, but if you want to work with data, T-SQL is where it is at. Developing expertise in T-SQL ( it's not as easy as people think ) will open up a new way of understand data/programming/thinking. SQL Server allows importing of .net code/functions which you can run on the sql server via T-SQL so there isn't really much you can't do with T-SQL. > A local install of SQL Server Express can be linked to remote servers, allowing you to join local tables to remote ones. SQL Server Developer Edition is free now. That is a great starting RDBMs to learn databases on windows environment. It has almost everything other than enterprise level features ( clustering/replication/etc ) that most people don't need.

Pxtl 9y ago
> T-SQL ( I'm assuming the microsoft variant ) is not a procedural language, it's a declarative language. I think he's referring to the terrible terrible terrible procedural components of T-SQL - cursors, local variables, IF/THEN control blocks, etc. Right Tool For The Job. Sql is good for querying/storing data, so use it for that. Then let the C# handle manipulating the data and applying rules and whatnot.
- treebeard901 9y ago
  Since SQL Server 2005, modern alternatives to the procedural components have been added to the product. Even with these features existing it does not make any sense to call SQL a procedural language. Choosing the right tool for the job is certainly important. I notice many people write off SQL almost immediately when it is mentioned here. Often they come from a different language and do not see the value in the relational model. It's unfortunate too because there is real value in being able to go between a relational mindset seamlessly to an object oriented or functional one.

coleifer 9y ago

SQLite definitely presents some unique challenges, despite it generally being the "simplest" relational database. I've written a web-based SQLite database browser, which you can find here: https://github.com/coleifer/sqlite-web https://github.com/coleifer/sqlite-web -- importantly, it supports all the `ALTER TABLE` statements you'd expect (add column, drop column, rename column, etc). This is done by renaming the table temporarily, creating a new table with the desired changes, then copying the data into the new table. Kinda clunky, but it works :)

tenken 9y ago
doesnt support expressions on DEFAULT keyword of create table syntax. :(
kalleboo 9y ago
Which is basically how MySQL ALTER TABLE works internally anyway...

Pxtl 9y ago

> T-SQL, the procedural language I chose to implement atop SQLite, is not a great general-purpose programming language The day every .NET shop on Earth admits this is the day I'll be a happy man. A SQL server is not for writing business code.

deleted 9y ago
[deleted]
yawaramin 9y ago
You mean MSSQL? Because Postgres has an excellent procedural language built-in, as well as support for lots of others....
- Pxtl 9y ago
  Perhaps it does. I've dealt with horrible procedural code in t-sql and pl/SQL and maybe Postgres is better but I'm in no hurry to find out.
  - yawaramin 9y ago
    I'm a Scala developer by profession and have dealt with horrible Scala code. People can write horribly in any language.
- tomc1985 9y ago
  I agree with this but also with the other child comment; it is really really easy to write unreadable plpgsql

nwienert 9y ago

You may be interested in Bedrock, which makes SQLite more performant and clusterable http://bedrockdb.com/ http://bedrockdb.com/

tedmiston 9y ago
What an interesting project. Until now SQLite at scale has sounded like an oxymoron to me.