
Ask HN: Is there a CRUD front end for databases (especially SQLite)? - Tomte
I&#x27;m currently looking for a program (a simple executable) that &quot;opens&quot; an SQLite database and (via introspection of the schema) without any further configuration allows simple CRUD operations on the database.<p>Yes, there is DB Browser and a gazillion other database administration frontends, but it should really be limited to CRUD operations. No changing the table, the schema, the indexes. Simple UI.<p>For users that have no idea about SQL or databases.<p>Is there anything like that already done and ready to use?
======
westurner
There are lots of apps that do database introspection. Some also generate
forms on the fly, but eventually it's necessary to: specify a forms widget for
a particular field because SQL schema only describes the data and not the UI;
and specify security authorization restrictions on who can create, read,
update, or delete data.

And then you want to write arbitrary queries to filter on columns that aren't
indexed; but it's really dangerous to allow clients to run arbitrary SQL
queries because there basically are no row/object-level database permissions
(the application must enforce row-level permissions).

Datasette is a great tool for read-only database introspection and queries of
SQLite databases.
[https://github.com/simonw/datasette](https://github.com/simonw/datasette)

Sandman2 generates a REST API for an arbitrary database.
[https://github.com/jeffknupp/sandman2](https://github.com/jeffknupp/sandman2)

You can generate Django models and then write admin.py files for each
model/table that you want to expose in the django.contrib.admin interface.

There are a number of apps for providing a GraphQL API given introspection of
a database that occurs at every startup or at runtime; but that doesn't solve
for row-level permissions (or web forms)

If you have an OpenAPI spec for the REST API that runs atop The database, you
can generate forms ("scaffolding") from the OpenAPI spec and then customize
those with form widgets; optionally with something like json-schema.

It's not safe to allow introspected CRUD like e.g. phpMyAdmin for anything but
development. If there are no e.g. foreign-key constraints specified in the SQL
schema,a blindly-introspected UI very easily results in database corruption
due to invalid foreign key references (because the SQL schema doesn't specify
what table.column a foreign key references).

Django models, for example, unify SQL schema and forms UI in models.py;
admin.py is optional but really useful for scaffolding (such as when you're
doing manual testing because you haven't yet written automated tests)
[https://docs.djangoproject.com/en/2.2/ref/contrib/admin/#mod...](https://docs.djangoproject.com/en/2.2/ref/contrib/admin/#modeladmin-
options)

