7 ms·
Show HN: I wrote a RDBMS (SQLite clone) from scratch in pure Python
I wrote a relational database management system (RDBMS) (sqlite clone) from scratch in pure Python.
- prmobiledev 3y agoGood for you! Want a medal?
- kindawinda 3y agoOkay this seems like the perfect project to test out the various AI repo update solutions. Anyone want to do this for fun, you can have your own agenda, I'm just bored! https://github.com/paul-gauthier/aider https://github.com/paul-gauthier/aider https://www.mentat.codes/ https://www.mentat.codes/ https://www.gitwit.dev/ https://www.gitwit.dev/ https://www.second.dev/ https://www.second.dev/
- kindawinda 3y agoWhy would you downvote this? Ive already turned it into a viable sqlite competitor. Shame on you.
- YZF 3y agomy 2c: Your comment seems to have nothing to do with the post and mostly has a bunch of links. If there is some relevance please make it clearer by editing your comment. Your projects might be very cool in which case just submit them to HN on their own instead of commenting. Commenting on other people's downvotes is usually not helpful. Accept the votes, learn from the experience and take that forward when participating in this community. The guidelines are pretty good, please read them as well: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
- memefrog 3y agoBecause you people that bring up the latest overhyped and useless buzzword nonsense on every single post are super tiring. We do not need a comment about "someone should apply AI/ML to this" on every post any more than we need a "this but NFT" or "this but blockchain" or "this but React" or "this but OOP" comment. Your comment is just noise.
- KeplerBoy 3y agoKudos, i bet that was a fun and rewarding experience. I know this was never meant to be fast, but could you produce some benchmarks for shits and giggles?
- bryancoxwell 3y agoOff topic but do you know of any good resources/talks/blog posts that cover how to create useful benchmarks?
- mburns 3y agoNot OP, but Brendan Gregg has written a lot about performance and benchmarking. * https://www.brendangregg.com/activebenchmarking.html https://www.brendangregg.com/activebenchmarking.html * https://www.brendangregg.com/blog/2018-06-30/benchmarking-checklist.html https://www.brendangregg.com/blog/2018-06-30/benchmarking-ch...
- bryancoxwell 3y agoGreat, thank you!
- spanspan 3y agoIt would be a fun exercise to implement something like TPC-C for learndb and see how this done.
- fjfuvucucuc 3y ago[dead]
- sgarland 3y agoThis is fantastic, and seems like a great way for someone (me) to learn DS&A better. I can explain how a B+tree works, but if you asked me to code it, I’d freeze up. I love databases and Python, so this was really interesting to walk through. Thanks for the post.
- spanspan 3y agoMost definitely. The b-tree implementation was the first motivation for starting the project. Especially, all the details around node rebalancing and splitting. And the fact that it was an on-disk structure, added another wrinkle to the thinking about the impl
- samsquire 3y agoThank you for sharing My perspective is that writing this kind of system in a language such as Python is actually a great thing because for myself Python is more widely readable and approachable compared to C++ or C which is what databases are often programmed in. If someone wants to be serious they can port it to a low level language . As it stands it's educational and useful for studying. I wrote a distributed pseudo multi model SQL/graph Cypher/Document and dyanmodb style database in Python https://GitHub.com/samsquire/hash-db https://GitHub.com/samsquire/hash-db for the same goal of learning how database engines could work in a distributed way.
- devman0 3y agoThis is why you see a community of pure Java RDBMSs (Hypersonic, H2, Derby, etc), if you don't need the big iron scale it's easier to ship/use the database or even embed it in memory if needed.
- fuzztester 3y agoYes. It's an interesting area, irrespective of implementation language. I had tried out one or two Java-based RDBMSes back in the day, via programs written in Java, for fun. I think one was HSQLDB. https://en.m.wikipedia.org/wiki/HSQLDB https://en.m.wikipedia.org/wiki/HSQLDB There was also another interesting one called PointBase, which was developed by Bruce Scott, an Oracle founder, and others. https://en.m.wikipedia.org/wiki/PointBase https://en.m.wikipedia.org/wiki/PointBase
- gabereiser 3y agoH2 is pretty great and fully supports JSONB and the like [1]. Full java-only RDBMS. [1]: https://www.h2database.com/html/main.html https://www.h2database.com/html/main.html
- p2detar 3y agoH2 is pretty solid. I used to ship a product with it as a default database. Customers could optionally use MySQL, MSSQL or Oracle, but for small businesses H2 was more than enough.
- keithalewis 3y agoIn what way is this a "SQLite clone"?
- tredre3 3y agoIn the way that it's an embedded RDBMS.
- spanspan 3y agoSingle file, embedded database with similar logical organization
- keithalewis 3y agoPerhaps a more accurate claim would be "SQLite inspired". Calling it a clone is misleading. Mad props to the author. Many Python programmers never had proper training in computer science, so it is encouraging to see people filling in the gaps of their knowledge.
- hluska 3y agoThis is a very early release, whereas SQLite has 22 years of releases. In that light, this is about the least charitable take on this. Someone in our community built something and had the courage to release it. Your criticism is unfair.
- esjeon 3y ago> Your criticism is unfair. I think it doesn't even get close to being a criticism, and it's certainly unclear if the goal is to literally clone SQLite or to implement SQLite-ish. This is a fair question.
- hluska 3y agoWhy did they have to use the term misleading? Why not be charitable?
- 3y ago
- xjsjxjsjsh 3y agoThe documentation is excellent.
- simonw 3y agoThanks to this post I learned about Lark, which looks like a really nice parser library for Python. The JSON tutorial on their site is excellent - shows how to build a basic parser for JSON, then goes into some great detail about how to improve its performance: https://lark-parser.readthedocs.io/en/latest/json_tutorial.html https://lark-parser.readthedocs.io/en/latest/json_tutorial.h... Here's the grammar used for the RDBMS project: https://github.com/spandanb/learndb-py/blob/master/learndb/lang_parser/grammar.py https://github.com/spandanb/learndb-py/blob/master/learndb/l...
- OJFord 3y agoDSL in a string? Is that 'really nice'? I haven't used or needed this in Python that I can think of, but surely we can do better than that? Even a dict with expected keys and construction via the bitwise or operator (which would roughly match the form of a lot of the grammar) would be better wouldn't it? Imports could be imports, just mixed in somehow. This is just first thoughts at a glance, maybe I'm missing something.
- erezsh 3y agoLark supports, and recommends, writing and storing the grammar in a .lark file. We have syntax highlighting support in all major IDEs, and even in github itself. For example, here is Lark's built-in grammar for Python: https://github.com/lark-parser/lark/blob/master/lark/grammars/python.lark https://github.com/lark-parser/lark/blob/master/lark/grammar... The rationale is that it's more terse and has less visual clutter than a DSL over Python, which makes it easier to read and write.
- tmpX7dMeXU 3y agoIt’s an existing, standard, language for describing grammars. Quite clearly the trade-off being made here. Seemingly not the one you would’ve made. Don’t act like it’s objectively bad.
- OJFord 3y agoin a string. I didn't say the DSL was novel. I learnt BNF too. (It does have extras that are either unique to it or standard beyond my familiarity though.)
- _tom_ 3y agoHow much of the SQLite test suite will pass?
- schemescape 3y agoDoes it support any ACID guarantees? Query planning/optimization? I’m not asking this to imply that it can/should. I just want to know what all was attempted besides b-trees and SQL. I’d like to do something like this someday. Nice work!
- spanspan 3y agoRe: ACID guarantees It doesn't have a notion of atomically batching multiple statements, i.e. transaction. But beyond that, it's a single file database, which can only have a single process (learndb instance) that is operating on the database (file). So you get consistency and isolation via being a single connection database. Durability, you get to the extent that the file system is durable. So it's somewhere on the ACIDity spectrum. Re: Query planning/optimization I haven't implemented this; but I've considered where the optimization could module sit: The parser spits out an AST. This or a derived intermediate representation could be optimized,i.e. the AST could be rewritten or nodes deleted, before the VM executes the AST.
- quickthrower2 3y agoNice! it is good to see past blind spots e.g. "you must write this kind of app in this kind of language, pure Python is high level it is only for glue". I wouldn't have thought to do a project like this because of all of those kinds of heuristics. And as other have said, a hackable DB in the same language as the app can have a lot of advantages.
- deleted 3y ago[deleted]
- indymike 3y agoFirst, great project. Second, the code is very readable. Finally thank you for excellent comments.
- malkia 3y agoLong long time ago someone rewrote/ported SQLite from C to C# -> https://code.google.com/archive/p/csharp-sqlite/wikis/LettersFromSQLite.wiki https://code.google.com/archive/p/csharp-sqlite/wikis/Letter... - note how welcome Dr. Richard Hipp was on the effort! Probably here on github -> https://github.com/CsharpDatabase/CsharpSQLite https://github.com/CsharpDatabase/CsharpSQLite - and possibly some more clones after that.
- kragen 3y agoi used to use gadfly, aaron watters's sql database in pure python; the first release was in 01994 i'm not sure current python is capable of running it
- jarulraj 3y agoReally cool project! I am curious about the benefits and limitations of using Python in this project as opposed to C++. How well is LearnDB able to support low-level concurrency control and storage management?
- spanspan 3y agoI began using python as a way to "mock" out the overall design; intending to re-implement it in rust. The main reasoning for using python: was the ability to focus on "high-level" concepts and speed of tinkering. This implements a single process, single thread, single connection database- so performance and low-level concurrency control were not explicit goals or really optimized for. For those (real-live concerns) rust or C++ are much better; but also come with their set of complexities.
- est 3y agolong time ago the Python2 stdlib shipped a ton of DB libs like BerkelyDB. https://wiki.python.org/moin/BerkeleyDB https://wiki.python.org/moin/BerkeleyDB
- networked 3y agoI was surprised to find out MicroPython had a database module based on Berkley DB in its standard library. It uses Berkley DB 1.x, which is the version of Berkley DB with a permissive license. https://docs.micropython.org/en/v1.20.0/library/btree.html https://docs.micropython.org/en/v1.20.0/library/btree.html
- p2detar 3y agoOn a tangent: is there something like mapDB [1] but for Python? 1 - https://mapdb.org https://mapdb.org
- Uptrenda 3y agoNice OP. How would you say this compares to the inbuilt 'sqlite3' module in Python? Is it more or less portable? Different features?
- ritzaco 3y agothe `sqlite3` module provides an interface to `sqlite` (https://www.sqlite.org/index.html https://www.sqlite.org/index.html), so that comparison doesn't really make sense. OP rewrote the actual database in Python, so (if it's a 1:1 equivalence), you would still use the python `sqlite3` module to connect to OP's project. As mentioned, it's an educational project, not really meant to be used as a replacement for sqlite in projects etc though.
- rvba 3y agoIs this really a relational database if it does not have JOINs?
- spanspan 3y agoIt does have joins
- rvba 3y agoThe splash page says that Learndb supports the following: select, from, where, group by, having, limit, order by I dont see "JOIN" there. So either the DB doesnt support it or the documentation (on the splash page!) is wrong. Thanks for the downvote.
- DANmode 3y ago> Thanks for the downvote. Please don't do this here. Additionally, it could have been anyone.
- flakiness 3y agoThis is very nice! SQLite is very hard to read, but this one is actually quite comprehensible. Especially the VM part: https://github.com/spandanb/learndb-py/blob/master/learndb/virtual_machine.py https://github.com/spandanb/learndb-py/blob/master/learndb/v... Compare it with this: https://github.com/sqlite/sqlite/blob/master/src/vdbe.c https://github.com/sqlite/sqlite/blob/master/src/vdbe.c That's said, I'm curious how complete this LearnDB is. SQLite is hard to read not only it's old but also it covers a lot of SQL and following SQL spec makes hings complicated. SQLite has great test suite so it's nice if you run the suit against this implementation.
- pyuser583 3y agoI know Python, but little C. I don’t recommend writing a DB in Python. So much of DB development is dealing with race conditions and consistency. Python files require an interpreter. Bad for a DB. And dependency management is a pain. Are you thinking a virtualenv for this?
- lolinder 3y agoThe project is entitled LearnDB, and this is how they describe their motivation in the first two paragraphs of the README: > > What I Cannot Create, I Do Not Understand -Richard Feynman > In the spirit of Feynman's immortal words, the goal of this project is to better understand the internals of databases by implementing a relational database management system (RDBMS) (sqlite clone) from scratch. > This project was motivated by a desire to: 1) understand databases more deeply and 2) work on a fun project. It sounds like they couldn't care less if it's production-ready.
- janc_ 3y agoAlso: what is "production ready"? Not every program is used in a context where performance, concurrency, etc. are relevant...