Sqlite to Rest | Hacker News Reader

Sqlite to Rest

github.com

194 pointsmooreds44 comments

Jeff Knupp's 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) 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...

There are many other projects like this, too. I have a list at 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) 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.

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.

There is also Presto: https://prestodb.github.io/

If you ever saw AWS Athena, that's what it is based on.

Perhaps I'm missing something, but I don't see how Presto fits in with these other projects.

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.

I think you were thinking of dataset (https://dataset.readthedocs.io/en/latest/) rather than datasette mentioned in the parent.

Still quite different to Presto which looks more like Ibis.

> simon w's datasette

That's awesome. Thanks for the tip!

I guess it’s a good place to mention https://github.com/PostgREST/postgrest as well

Was looking for this one. Implemented in Haskell too!

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

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

Rest also means you don’t care about the database schema.

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

Most are, but I have definitely worked on projects that weren't

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.

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

Then you can't use it across apps, though.

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.

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

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

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

Isn't that worse though? Having to add retry logic seems to be more complex than a lock or singleton.

It does have a lock, and you can set a busy timeout to wait instead of immediately getting an error from SQLite.

ah, thanks for that. I appreciate knowing more.

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.

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.

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

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/

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

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.

http://htsql.org/

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

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 just read OData yet another way. OData at least pretends to be a standard ;).

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?

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

What would example HTTP queries look like?

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.

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.

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

Technically, REST should be spelled ReST anyway. ;)

Reply on news.ycombinator.com