I don't have to very often, but it is _very_ handy when I do!
Semi-related (kind of the inverse) tool is simon w's datasette (https://github.com/simonw/datasette) which gives you a readonly view into a sqlite database, conveniently exposed as a website. I've fired that up in meetings before to general shock and acclaim...
Added: This type of software lacks a standard name and an established "format" (like HTTP servers or word processors have a "format"), so it is hard to discover other projects even when you are aware of a few. I've taken to calling them "automatic API servers" and promoted the term a bit. Some projects have adopted the GitHub topic "automatic-api" (https://github.com/topics/automatic-api) as a result. My hope is that a name and a search tag will help with discovery and make it more of a distinct type of product.
It was in response to the comment mentioning Datasette, which takes data from a source and puts it into a database. Presto allows you to access data from various sources using SQL syntax.
sqlite doesn't need the full server, it runs directly off a file on disk so there's minimal setup. However sqlite is supposed to be single-user only so idk.
The single user in this case is the webserver. The webserver can itself serve multiple users if done in a single-threaded fashion.
SQLite can also support multiple threads reading, but is limited to file-locking for write-access IIRC.
If someone is interested in a similar solution for GraphQL, GRelDAL [1] (authored by me) is an alternative (though it is slightly lower level and requires mapping of data sources through code).
Also given support for multiple databases, it is possible to start out with sqlite and later migrate to a database server (postgres or mysql).
Does this have hardcore locking in it? Last I knew sqlite doesn't support multi-users so this would just fall over with more than one client concurrently inserting data unless you throttle them into synchronous access.
SQLite supports multiple simultaneous connections to the
database from separate processes. It just does not let
more than one connection write at a time. SQLite takes care
of serializing access for you - you don't have to put
any synchronization code in your application. Your app
just needs to be prepared to retry an insert if it gets
back an SQLITE_BUSY result code.
It took me awhile to parse the title because Rest isn't in all caps. Initially I thought sqlite is going to be retired or something... but then again English is not my native language.
Does this work for any table without any modification or just the beer table? Also documentation is bit weird. Eg API section starts with some require a then there are two CLI commands (generateSkeleton, getSqliteRouter). Does it mean I can generate skeleton without using CLI?
I always found REST (and GraphQL or any API for that matter) tedious as it's basically abstracting SQL queries.
To my knowledge the abstraction is necessary to mitigate SQL injection and data permissions.
With RLS in Postgres, I think the data access problem has largely been solved where someone with the wrong JWT simply can't query other people's data if RLS is setup properly.
However, I don't know of a way to lock down a PostgresDB enough for clients to be able to write their own SQL statements.
It's easy enough to remove operations like DROP TABLE xx; but it seems like for a client to safely query a database we're stuck writing these boilerplate abstractions (or auto-generating them now).
With front-end development getting increasingly popular, I think backend is starting to become just a thin wrapper around database. If database can be securely locked down enough, a lot of business logic can be shifted to the front-end.
Say for example you have a web front end, a mobile app, and a service receiving web hooks that all want to execute the same action. If you have that in a client layer, you'll be writing it 3 times.
Moving logic to the client comes with pros, too (like being easier to work offline), but it definitely has downsides as well.
If database can be securely locked down enough, a lot of
business logic can be shifted to the front-end.
Unless "locked down enough" includes "with all the business logic embedded in the DB", this is going to fail in exciting ways with technically-savvy users...
I think that's true for very simple CRUD apps (basically what this is designed to replace). For more complex apps, it gives you room to:
* Implement decent monitoring. I've had very poor luck trying to monitor SQL query performance directly, especially if you need to tie particular queries to a user action, and monitor the time taken to perform a complex action.
* Implement more sane resiliency, by giving the ability to connect to multiple databases.
* Alter your data storage format or backend without needing to change the presentation (i.e. the same REST call could suddenly go to Postgres instead of MongoDB, and the client doesn't need to change)
* Easily integrate with other apps/features. SSO is a good example of this, where you can use the same authentication system across multiple APIs relatively easily.
That's just a few. SQL is a really great interface to data. SQL is (in my opinion) a poor user interface to business logic. Something like "reset a user's password" makes sense in REST. It will likely continue to make sense, and probably even look exactly the same, for a long while.
That is often not true of SQL, unless you accept arbitrary constraints like "we can never use anything other than RLS for authorization". Not that RLS is a bad implementation, but things change. Leaving yourself some flexibility is usually a good idea (within reason, like anything you can shoot yourself in the foot trying to make your app too flexible).
* only simple CRUD apps - I've implemented with this type of stack a service similar to Parse/Firebase. I've also designed/implemented something like Basecamp+Freshbooks (in traditional PHP style, not a toy/demo app) and i know this type of stack can power just fine services like that (with a lot less code), i.e. your statement "simple CRUD apps" is not true
* monitoring - easily implemented in the proxy (can be as simple as $request_time in logs)
* multiple databases (I take it you mean read replicas) - tie each process (very lightweight process ... something like 5Mb memory) like this one to a particular db replica than use roundrobin in nginx upstream. So, to go from one db to multiple one you just need a different production config, no new code.
* use views/stored procedures to expose as an api then change your underlying tables (data storage format) to your hearts desire.
* Integrate 3rd ... - You are thinking/comparing tools like this to languages/stacks like php/node/ruby when infact you should compare them to things like active record. In traditional stack you don't integrate 3rd party at the same level as active record (in active record code), you do it either above or below this level. It's the same here only above means client code or (scriptable) proxy level (see openresty and njs) and below means database level (PostgreSQL is much more then SELECT ) or some worker process that reacts to system events that can be as simple as a events table or as complex as message broker.
SELECT * FROM reset_user_password() works in sql too
Hasura (https://hasura.io) is an awesome similar project for getting a GraphQL API (including live subscriptions) automatically from a PostgreSQL database.
A neat project and some of the other comments have shared similar projects for other databases. How do projects like this address security concerns? Specifically, mass assignment attacks?
In the Python world, Django Rest Framework does this (and a lot more!). The out-of-box getting started experience is not as smooth though, as you need to define serializers and views objects too. Of course, they're probably going to be useful for more later.
45 comments
[ 2.8 ms ] story [ 101 ms ] threadI don't have to very often, but it is _very_ handy when I do!
Semi-related (kind of the inverse) tool is simon w's datasette (https://github.com/simonw/datasette) which gives you a readonly view into a sqlite database, conveniently exposed as a website. I've fired that up in meetings before to general shock and acclaim...
That's awesome. Thanks for the tip!
Added: This type of software lacks a standard name and an established "format" (like HTTP servers or word processors have a "format"), so it is hard to discover other projects even when you are aware of a few. I've taken to calling them "automatic API servers" and promoted the term a bit. Some projects have adopted the GitHub topic "automatic-api" (https://github.com/topics/automatic-api) as a result. My hope is that a name and a search tag will help with discovery and make it more of a distinct type of product.
If you ever saw AWS Athena, that's what it is based on.
Still quite different to Presto which looks more like Ibis.
Is there some good practice/frameworks to go the other way around? Often find my self crawling data to a database.
Also given support for multiple databases, it is possible to start out with sqlite and later migrate to a database server (postgres or mysql).
[1] https://gql-dal.github.io/greldal/
I want to like SQLite but that just seems like a deal breaker
To my knowledge the abstraction is necessary to mitigate SQL injection and data permissions.
With RLS in Postgres, I think the data access problem has largely been solved where someone with the wrong JWT simply can't query other people's data if RLS is setup properly.
However, I don't know of a way to lock down a PostgresDB enough for clients to be able to write their own SQL statements.
It's easy enough to remove operations like DROP TABLE xx; but it seems like for a client to safely query a database we're stuck writing these boilerplate abstractions (or auto-generating them now).
Say for example you have a web front end, a mobile app, and a service receiving web hooks that all want to execute the same action. If you have that in a client layer, you'll be writing it 3 times.
Moving logic to the client comes with pros, too (like being easier to work offline), but it definitely has downsides as well.
* Implement decent monitoring. I've had very poor luck trying to monitor SQL query performance directly, especially if you need to tie particular queries to a user action, and monitor the time taken to perform a complex action.
* Implement more sane resiliency, by giving the ability to connect to multiple databases.
* Alter your data storage format or backend without needing to change the presentation (i.e. the same REST call could suddenly go to Postgres instead of MongoDB, and the client doesn't need to change)
* Easily integrate with other apps/features. SSO is a good example of this, where you can use the same authentication system across multiple APIs relatively easily.
That's just a few. SQL is a really great interface to data. SQL is (in my opinion) a poor user interface to business logic. Something like "reset a user's password" makes sense in REST. It will likely continue to make sense, and probably even look exactly the same, for a long while.
That is often not true of SQL, unless you accept arbitrary constraints like "we can never use anything other than RLS for authorization". Not that RLS is a bad implementation, but things change. Leaving yourself some flexibility is usually a good idea (within reason, like anything you can shoot yourself in the foot trying to make your app too flexible).
* monitoring - easily implemented in the proxy (can be as simple as $request_time in logs)
* multiple databases (I take it you mean read replicas) - tie each process (very lightweight process ... something like 5Mb memory) like this one to a particular db replica than use roundrobin in nginx upstream. So, to go from one db to multiple one you just need a different production config, no new code.
* use views/stored procedures to expose as an api then change your underlying tables (data storage format) to your hearts desire.
* Integrate 3rd ... - You are thinking/comparing tools like this to languages/stacks like php/node/ruby when infact you should compare them to things like active record. In traditional stack you don't integrate 3rd party at the same level as active record (in active record code), you do it either above or below this level. It's the same here only above means client code or (scriptable) proxy level (see openresty and njs) and below means database level (PostgreSQL is much more then SELECT ) or some worker process that reacts to system events that can be as simple as a events table or as complex as message broker.
SELECT * FROM reset_user_password() works in sql too
"HTSQL is a comprehensive navigational query language for relational databases."