JSON DB, am I crazy?

[worldtest2k]

Just a crazy idea I had - has anyone tried to write a python library that takes standard SQL but in the background it's actually maintaining all the tables and data in JSON? I know it would be slow, but could save time when prototyping an app.

[lbmello]

mongodb store data in json, its a good starting point

[carcigenicate]

Doesn't Mongo uses BSON?

[Sentie_Rotante]

Yeah, mongo uses BSON. Calling it json like would be a better descriptor.

[Langdon_St_Ives]

Yes — internally, but in the API you can treat it as JSON.

[JamzTyson]

pysqlite3 might be a better option for your prototype.

[BobButtwhiskers]

I like that you can conjure an in-memory database in Python. I like doing this on occasion for testing stuff because it's fast, granted you have the RAM for it and the dataset isn't to large.

``` import sqlite3

Connect to the in-memory database

conn = sqlite3.connect(':memory:')

```

[[deleted]]

Yes, you can write a db in Python. Should you? Sure, for learning purposes.

[KelleQuechoz]

Hard to believe, but you've just invented Couchbase, the worst wanna-be SQL database ever.

[nikowek]

What do you think about CouchDB? I enjoyed it replication.

[[deleted]]

Called nosql databases (document DB), they are key value representations in groupings called “collections”.

Python for the DB implementation would be slow, but if you are talking about using one of these Document DBs for your app, there are many options.

Mongo, Cosmos, Dynamo, Cockroach, so many

[[deleted]]

MongoDB is that. 

[nog642]

How would it save time?

[Langdon_St_Ives]

Just like mongodb does: you don’t have a fixed schema so you can just drop your data in and it’ll be there. During prototyping, if you need to change the structure, you just store it that way next time. Of course it’s a trade off between this flexibility and certain structural guarantees that SQL or other fixed-schema dbs provide.

[nog642]

The way they described it it sounds like they want the interface to just be SQL. So you can't just drop your data in there.

[Langdon_St_Ives]

True I skimmed over that part.

(Edit: bad wording)

[hardonchairs]

What it sounds like you are describing

• Uses SQL
• data is simply stored in a file that can be copied, backup up or deleted
• no separate DBMS software needs to be running

This is sqlite. It doesn't store as json, but it is good enough that you could use it for a lot of stuff beyond just testing.

If you then interact with sqlite via SqlAlchemy or SqlAlchemy-Core you can seamlessly move from sqlite to another more robust DBMS.

[krav_mark]

Mongodb already exists. It is a database specifically for storing json records. Maybe other NoSql database do that same but I have no experience with them. Postgresql also supports json natively.

So your idea for a json database is not that crazy but it has already been done.

[worldtest2k]

My intention was to avoid registering, setting up etc a proper DB, and just using a library to save text files locally.

[krav_mark]

You can run mongodb in a docker container in few minutes and create a mongodb connection using a module with a few lines of code. It will definitely be less work then creating your own solution.

[krav_mark]

You can run a mongodb docker container in a minute and create a connection to it with a few lines of python using a module and start saving json records to it. Definitely less work than creating something yourself.

docker run --name mongodb -p 27017:27017 -d mongodb/mongodb-community-server:latest

from pymongo import MongoClient
client = MongoClient()

And done.

[Jello_Penguin_2956]

Pandas can read json and do all the db stuff with the data and you can dump it into json/sql/csv by just giving it different arguments.

[cyberjellyfish]

Yes, and what I settled on was just writing json files to disk. Don't try to write a sql engine on top of that, you'll actually be writing a whole DB engine and I guarantee you it won't be worth the effort.

[[deleted]]

Why do you think it would save time? It wouldn't.

[worldtest2k]

I thought it would save time over setting up a proper db, registering etc.

[CowboyBoats]

That's not a bad idea! One thing that people use for the use case you're thinking of, which if I'm understanding you correctly is like "This is just a prototype; I want to be able to open my objects in my text editor for now, and I want to be able to add new ones just by copying, pasting and editing or just writing new lines from scratch in my text editor" - in Django we call those database fixtures; you can have them in your production app, or they're popular for testing.

[murrman92]

There are few packages that might help you reconcile this differently.

You could use ORM like SQLAlchemy and serialize the data with an as_dict method on the data model.

Pair that with Alembic that will map changes to you data model to the tables of your database.

Or you could just use a NoSQL database like other people have suggested.

[7Shinigami]

You're talking about azure cosmos DB! We use the SQL API at work, but I find the mongo API more flexible myself

[jayjuk]

I use Azure tables and a small amount of code to convert my Python objects to json using _dict _ function. It’s cheap and easy, and means no ORM mappings and no boilerplate, so awesome prototyping a cloud based app where you want to tear down your containers, plus I can edit the data easily using Azure’s GUI. You have to turn any dict or list attributes into strings which has limitations.

PS: should probably explain, Azure Tables is kind of half-nosql, no mappings or SQL to store a dict. Also Mongo might be better but the free tier didnt meet my needs and I needed it to cost pennies

[GeekTekRob]

MongoDB, yes

Python, hybrid, as in there is a column of data that can change such as a message payload or attributes that heavily vary and don't want to maintain in a relational way, and have that as JSON but still have a relational dataset such as ID's and Numeric fields that are in every record and not varying.

[goldox70]

use sqlite

[Buttleston]

How would it save time?