
Sqlite to Rest - mooreds
https://github.com/olsonpm/sqlite-to-rest
======
hprotagonist
Jeff Knupp's sandman2
([https://github.com/jeffknupp/sandman2](https://github.com/jeffknupp/sandman2))
has long been my go-to project for when i need to do this.

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](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...

~~~
networked
There are many other projects like this, too. I have a list at
[https://github.com/dbohdan/automatic-
api/](https://github.com/dbohdan/automatic-api/).

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](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.

~~~
mooreds
Thanks for that list. There is so much data locked up in databases that could
benefit from being turned into an API. To say nothing of CSVs and PDFs.

------
jatsek
I guess it’s a good place to mention
[https://github.com/PostgREST/postgrest](https://github.com/PostgREST/postgrest)
as well

~~~
CameronNemo
Was looking for this one. Implemented in Haskell too!

------
everdev
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).

~~~
ahvetm
Backend services are about more than serving data, wouldn't you say?

~~~
dpacmittal
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.

~~~
al2o3cr

        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...

------
tga
Hasura ([https://hasura.io](https://hasura.io)) is an awesome similar project
for getting a GraphQL API (including live subscriptions) automatically from a
PostgreSQL database.

------
Quarrelsome
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
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.

~~~
umvi
It seems like you can get SQLITE_BUSY even if you are a reader if someone else
happens to be writing...

I want to like SQLite but that just seems like a deal breaker

~~~
SQLite
Only in ROLLBACK journalling mode. Set "PRAGMA journal_mode=WAL" and this does
not happen.

------
ovi256
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.

------
punnerud
Note: This is for NodeJS.

Is there some good practice/frameworks to go the other way around? Often find
my self crawling data to a database.

~~~
punnerud
Part of it in a different (earlier) HN-post:
[https://news.ycombinator.com/item?id=20399294](https://news.ycombinator.com/item?id=20399294)

------
lf-non
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).

[1] [https://gql-dal.github.io/greldal/](https://gql-dal.github.io/greldal/)

------
kbumsik
I'm not familiar with those kind of approach but why not just use SQL? I'm
genuinely curious.

~~~
Quarrelsome
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.

~~~
TickleSteve
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.

------
blacksqr
[http://htsql.org/](http://htsql.org/)

"HTSQL is a comprehensive navigational query language for relational
databases."

------
finchisko
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?

------
oaiey
I just read OData yet another way. OData at least pretends to be a standard
;).

------
lovetocode
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?

------
felixfbecker
The search query parameter syntax with support for >=, <=, !=, etc is
interesting

------
gloflo
What would example HTTP queries look like?

------
stochastimus
I’m so glad to see this work and other projects like it. The relational
database doesn’t need to be abandoned to put any interface over it you like.

------
coconut_crab
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.

~~~
anyzen
So I wasn't the only one... I initially read "Rust". :)

