
Envelope – Postgres database to web app with just HTML and SQL - justintocci
http://envelope.xyz/
======
justintocci
We are happy to announce the release of Envelope v1.0.0 today.

Envelope is a complete platform that makes building web apps on top of
PostgreSQL easy.

Envelope is free, open source, offered under a PostgreSQL style license,
compatible with modern and mobile browsers, production ready and available
today at envelope.xyz.

All you need to know is SQL, and some HTML and you can publish your database
as a web application with Envelope!

Envelope source code is available on Github but free installers are available
for Mac, Linux and FreeBSD. Visit envelope.xyz and click on "Install" to get
your copy.

~~~
bdcravens
> All you need to know is SQL, and some HTML

So it's a reimagined ColdFusion? :-)

~~~
justintocci
I admit, I don't use CF.

I looked at the Wikipedia entry for it and I see its heavy on server side
programming. Envelope replaces server side programming.

CF uses server side programming to make components whereas Envelope uses
Javascript web components on the client side.

Their seems to be a lot of overlap in expected use cases. Internal apps, data-
driven apps.

I apologize, I can't really assert much else do to lack of experience with CF.

~~~
bdcravens
Aside from implementation details, I think the premise is the same: simple
data access using SQL and rendering using familiar HTML-like syntax for markup
and logic. CF of course came about at a time when browsers were far less
capable (1995), so rendering is of course on server. Additionally, I think CF
probably has more middleware, but I really haven't given Envelope much of a
look (you really need a standard docs page)

~~~
justintocci
Check out the live demo. The docs are very extensive, but they work best
inside the IDE.

------
reustle
Really cool project, not so cool website. Here's some feedback:

* Took me a minute to realize I can't scroll when my mouse is outside the center container

* Drop the sticky header/footer (let them scroll with the content)

* Add a bit more padding / line-spacing on the content on most pages (especially the Install page)

* Optimize (scale down?) the images on the support page so they load quickly (they seem pretty heavy)

Great work, though!

~~~
justintocci
Fixed! Thank you for the feedback! I apologize for the look, none of us here
are designers but we try hard.

People that aren't seeing the changes need to hit refresh on their browser. I
apologize for the confusion.

~~~
reustle
I am no designer either, which is why every project of mine starts with
Bootstrap :D

------
Canada
I have a suggestion: Put a demo on your web site.

edit: Another suggestion... use a proper source of random numbers in
init_aes_key_iv(). Using the time isn't good.

~~~
justintocci
We moved to:

    
    
        time(NULL) * getpid() * clock()
    

Thanks!

~~~
joelthelion
That's still not a good idea. Use your OS' random source.

~~~
justintocci
We wanted to avoid anything controversial. Do we need to make this
configurable to make everyone happy?

[https://www.schneier.com/blog/archives/2013/10/insecurities_...](https://www.schneier.com/blog/archives/2013/10/insecurities_in.html)

~~~
MrRadar
Even if it isn't perfect /dev/random is a hell of a lot more random than just
taking the current time stamp and PID.

Edit: For reference, this is _exactly_ how the Netscape's original SSL
implementation was broken:
[https://web.archive.org/web/20141214141822/http://www.cs.ber...](https://web.archive.org/web/20141214141822/http://www.cs.berkeley.edu/~daw/papers/ddj-
netscape.html)

~~~
justintocci
Understood. Working on it now.

~~~
justintocci
Fixed!

------
MrRadar
Did nobody else think of the old SQL on Rails (April Fools) screencast as soon
as they saw this headline?
[https://www.youtube.com/watch?v=0_PK1eDQyVg](https://www.youtube.com/watch?v=0_PK1eDQyVg)

~~~
justintocci
Funny!

------
justintocci
Special thanks to Domingo Alvarez Duarte 'mingodad' for submitting the first
community pull request. Merged in three minutes!

~~~
mingodad
That's the beauty of open source ! Cheers !

------
ufmace
How is using Postgres DB users as website users working for you? Is having
thousands or millions of users practical, or is this intended more for a
limited-use in house application? Is it practical to allow new users to
register on the site and have a site admin manage their roles and permissions
through a web interface, or do DBAs need to manage that stuff manually in
psql?

I worked on an internal website once on MS stack where management wanted to
try and authorize access to the webapp through access permissions to the
backend MSSQL database, with the Windows Domain user getting picked up
automatically through IE. It was a mess to get even kinda working and to
debug, and we were never able to get it all the way working.

~~~
justintocci
> How is using Postgres DB users as website users working for you?

Fricken awesome. Very easy to sleep at night. We can do a complete security
audit in fifteen minutes.

> Is having thousands or millions of users practical, or is this intended more
> for a limited-use in house application?

Envelope is a very small C binary. You'll find it scales way better than
PostgreSQL itself and as such you should be able to apply similar strategies.
You'll find your database design is way more important than the Envelope
overhead. If you were going to open a site to millions of users I would
consider pooling users into groups so you could pool PostgreSQL connections.
Again, the bottleneck isn't Envelope, but PostgreSQL.

> Is it practical to allow new users to register on the site and have a site
> admin manage their roles and permissions through a web interface, or do DBAs
> need to manage that stuff manually in psql?

We don't use psql. Envelope includes Postage, a sort of web based PGAdmin.
Developers can use that to administrate the database. We've also written a
'User Admin' app for non-developers that we've installed into some of our
client Envelope instances. This allows the customer to administrate their own
users.

> ...management wanted to try and authorize access to the webapp through
> access permissions to the backend MSSQL database, with the Windows Domain
> user getting picked up automatically through IE...we were never able to get
> it all the way working.

Sorry to hear that. Our app was originally written in just a couple of hours
but I didn't even know you could get the Domain User in IE. I'll have to try
that.

~~~
acmecorps
Hey, do you have a link for postage?

~~~
justintocci
Open the live demo. There are two links. One for postage connecting to your
development copy and the one connecting to the production database.

------
foolinaround
This is an awesome tool, and reduces complexity for the easiest of the CRUD
applications.

however, for anything that needs even slightly more complicated scenarios such
as decisioning etc -- the SQL gets really complicated to build and test.

I am suggesting that this could be extended with a possible rules engine like
drools, which is bundled in, and uses an object model that is the same as the
table structure.

Data is fetched from the db, this data is reasoned on in the rules engine (and
filtered) and this data is then either updated to the db ( sent to back tier )
or displayed on the screen ( sent to front tier ).

Hence a complicated SQL query can be broken into a bunch of rules that are
reused across scenarios.

~~~
justintocci
We have complete ERP systems with kitting, lot control inventory, etc. all of
it straight forward and easy to maintain. Plpgsql is made for this stuff.

We also like having all the display handled in the html with web components.
We really like the separation. I Don't think we can help you.

------
lucideer
Is "Greyspots" just Polymer? There seems to be no real info on the site about
it?

~~~
michaeltocci
No, our custom elements does sound similar to Polymer but there are two main
differences: we don't use ShadowDOM (polymer does) and our elements are for
creating Postgres backed web apps.

Greyspots has two parts: the front end and the Envelope Server. The front end
is comprised of custom HTML tags and a JS library (We use X-tags to implement
custom tags). The Envelope Server is a middleware replacement that exposes a
fixed API from the front end to a Postgres database.

~~~
lucideer
Ah I was more thinking Greyspots referred exclusively to the front end JS
library, not both sides. Cheers for the clarification.

------
slagfart
I'm a DBA who wants to make the occasional simple web-app, mostly for a small
number of internal users at my company. This looks great! Thank you :)

------
jqm
This looks cool, but if I'm already going to write the HTML and the SQL I'll
just write the app. I already have a framework and method to do that anyway
and I don't have to learn a new system or be locked to some way of doing
things that may prevent customization and expansion.

Not to bag on the effort though. This may come in handy for someone.

~~~
justintocci
I don't understand. What we've done here is replace the web app architecture
with a desktop app architecture. Desktop apps don't have customization or
expansion problems so I wouldn't expect any here. Could you give me an
example?

~~~
jqm
Well, to be fair, I was never fan of Access and do think it has customization
and expansion problems. I have never built an app using Access, but I have
gone through a few broken Access apps and re-written them into web apps. In my
limited experience it allows clicker people to build themselves into trouble
while making it difficult for them to fix what they build. Just my opinion
though. I'm not complaining because I've made money re-writing some messes and
making them functional and beautiful.

Dragonwriter's reply (below) sort of sums up my view also. I am not a fan of
jamming all the logic in the SQL and like a middle layer... actually need a
middle layer to carry out operations on the data before it is presented. I
would prefer not to use JS for this.

But again.. I'm sure this project has some uses and would be handy for some
people. It's a cool idea and a cool project and I commend your effort. It's
just not something I personally would chose to use. Please don't take that the
wrong way....

~~~
justintocci
> actually need a middle layer to carry out operations on the data before it
> is presented.

I'm of the school that says you should fetch the data you want because the
database is optimized for data operations. can you give me an example of a
data operation that's best done in middleware?

~~~
jqm
Just about anything in which the SQL is considerably more complex or a lot
longer than then the corresponding middleware code.

Just about anything in which the SQL query is considerably more difficult to
debug or get intermediate verification than in the corresponding code.

SQL does many things well. So far, it doesn't do everything as well as
middleware code. It isn't as easy to use for every use case as middleware
code. Long complex SQL queries are a nightmare to unravel. Middleware code can
more easily (in my experience) be broken up into smaller steps that can be
manipulated and tested independently.

~~~
justintocci
Postgis is an example of a very extensive code base in Postgres. I've listed a
bunch of apps that Envelope is great for. You haven't given me an example of
what you are talking about so I can't really respond to your comments.

------
samfisher83
I couldn't imagine wanting to use sql to make a site.

~~~
justintocci
You write HTML documents to make the apps. Those are then stored to the file
system like normal. When you load the app in the browser, the components (just
custom HTML tags) connect through Envelope to your database.

Also, it's not for static web sites, it's for web applications. For example
accounting, wiki, calendar, address book etc.

You'd never write any of the site in SQL. You do write your VIEWs, queries and
any functions you need in SQL. Then you connect to those things with some of
your components.

~~~
dragonwriter
> You write HTML documents to make the apps.

The front-end of the apps. Since the application layer in a typical n-tier
architecture is replaced with a generic, application-neutral layer, the back-
end business logic is, of necessity, all moved into the database (unless you
propose moving that into the front end, which I suspect you don't.)

This isn't the first time this type of thing has been proposed, and its not
necessarily a bad idea, but there are a number of concerns associated with it
(particularly, that scaling out a typical middle-tier is often easier than
scaling out Postgres, so this approach may limit scalability of the resulting
apps.)

~~~
justintocci
> Since the application layer in a typical n-tier architecture is replaced
> with a generic, application-neutral layer, the back-end business logic is,
> of necessity, all moved into the database

Yes! Just like a typical desktop app! What we've done here is just simplify
the web architecture down to a typical desktop architecture.

> This isn't the first time this type of thing has been proposed, and its not
> necessarily a bad idea,

All us desktop application developers agree. This isn't new at all. We use it
every day. MS Access, .NET, Borland, Filemaker and many other tools used the
exact same architecture for many many years.

> but there are a number of concerns associated with it (particularly, that
> scaling out a typical middle-tier is often easier than scaling out Postgres,
> so this approach may limit scalability of the resulting apps.)

Hmmm. I am not a middle tier scale person so I can't speak to comparison. I
will point out you could easily have one Envelope serving out to ten
PostgreSQL databases before you need to introduce another Envelope instance.
Meanwile, Envelope is a microservice so scaling is just a simple NGINX config
change. This looks relevent: [http://spin.atomicobject.com/2012/02/28/load-
balancing-and-r...](http://spin.atomicobject.com/2012/02/28/load-balancing-
and-reverse-proxying-with-nginx/)

~~~
dragonwriter
> Yes! Just like a typical desktop app!

A typical desktop app has the application logic in an application language,
not the backend DB (if it even has a backend DB.)

> What we've done here is just simplify the web architecture down to a typical
> desktop architecture.

Relational DB backend + declarative front-end (HTML or otherwise) with no
application logic in an intermediate layer between those isn't the typical
desktop architecture, either.

> All us desktop application developers agree.

I mean, specifically, the RDBMS back-end with all business logic, generic
middle-layer (possibly built into the database), and HTML front-end
architecture for web apps has been proposed and provided before. But not
really taken off.

~~~
justintocci
Let me give you an example. MS Access and a database. Sure I _could_ use the
database as a dumb store and write reams of VBA, but that's not how I do it. I
write functions in plpgsql and have minimal code in VBA. In my practice VBA
and JavaScript are used in very much the same way.

One of the great things about writing apps this way is you can easily migrate
your MS Access database to the web. Or use both at the same time, say, MS
Access for the desktop and Envelope for mobile users. Then when someone hits a
"ship" button the same code runs regardless. This is the original Oracle model
published in their very earliest manuals.

I know lots of people don't take advantage of the database for stored
procedures/functions but I wasn't thinking about them when I wrote my previous
comment. I apologize.

------
nestlequ1k
Spent some time browsing around the demo. Sorry to be critical. This doesn't
seem to be a web app that anyone in their right mind would want to use.

It's all fun and games until your DB administrator gives you an Envelope built
app that you have to use every day.

~~~
justintocci
Could you be more specific? We've got customers working with apps every day
and they are happy. If they weren't happy we wouldn't have had time to release
it as open source...

