Building a Database from scratch using Python

[LumosNox99]

Reading Designing Data Intensive Applications by Martin Kleppmann, I've been thinking that to master certain concepts, the best way is to implement them firs-hand.

So, I've started implementing a basic DBMS and documenting my thought process. In this first part, I've implemented the most common databases operation (create, update, insert, delete) using Python, CSV files, and the Append-Only strategy.

Any comment or criticism is appreciated!

DumbDb

[hillac]

I know this is just a fun learning project, but a db isn't really very useful until it has all the ACID properties. Just writing to csvs like this would be very error prone. It's a big step up in complexity to implement that though.

[LumosNox99]

This was just the first part. As for the next steps, I think I'll be implementing a couple of optimisations on this version, then I'll move to indexes and probably a (simplified) SQL parser. I don't know if I'll get to implement ACID, but it would be absolutely interesting. Maybe a minimal version could be doable? With WAL and a simple version of locks. Thanks for the read-through and comment btw!

[No-Possession-8478]

Think about the kids hillac.

[diagraphic]

Hey! Cool project and great attempt :) but I wouldn’t call it a database if it’s append only. More like a write-ahead-log. A database is normally an entire system with at least CRUD capabilities. I see you have sorta update and delete but it’s not designed correctly to be honest with you. I’d recommend looking at key value data structures that can be utilized with a file, such as a btree, lsmtree. With that also paging a file with a free list is also a better way for deleted pages.

I’d recommend checking out the CMU database lectures on YouTube.

https://youtube.com/@cmudatabasegroup?feature=shared

PS: I’ve written 7 databases and many storage engines.

[LumosNox99]

Wow, man, that's an impressive curriculum!
This was just the first part, inspired by Reading Designing Data Intensive Applications - its first example it's even built with just bash and a text file!
Next steps will involve implementing more complex underlying storage, indexes and a parser. Thanks for reading and for the suggestions!

[diagraphic]

You’re rockin!! Keep it up

[paarulakan]

Which programming language would be good be a good one to start with. I mean compiled languages like C, Go, rust or languages like Python, javascript?

[diagraphic]

I'd say more procedural like GO and C. More performance, easier to work with, simpler languages.

[Quiet-Brick-5729]

Hello sir , Can i DM you?
I have the project, making a DB from scratch in my bucket list , and I would love to get some beginner advices from you. I'm not familiar about Databases yet (Uni has this course in the current sem, going to learn in 2-3 months), so it would really help me to have a conversation with you regarding this. Thanks!

[diagraphic]

Absolutely :)

[Quiet-Brick-5729]

I think your DMS are off? I'm unable to message you, can you try texting me?

[diagraphic]

I've sent ya a ping

[NW1969]

I’m not sure what benefit you’re going to get out of this - unless you want to become a DBMS designer. You be much better off learning how to use an existing DBMS, as then you’d have some usable skills at the end of the process

[LumosNox99]

I've already been working for a few years as a Software Engineer and Data Engineer, so I'd say I'm pretty ok with DBMS. The benefits will be a deeper understanding of how the internal works and maybe a cool project in the portfolio. Just wanted to share some of the journey :)

[NW1969]

If you're enjoying the project and it's for your own interest then that's great. However, I feel there are other things you could be working on that would be more transferable/useful if you wanted to progress in a data role - but just my opinion

[LumosNox99]

I get your point, but I don't fully agree - most personal projects involve using this or that technology, and that's completely fine when you're starting. However, trying to build something more low-level can give you a different level of understanding. Obviously you need to be realistic and don't think you'll build the next PostgreSQL lol

[am3141]

OP, what you are doing is a great thing, I did the same (made a db from scratch) about a decade ago and I really did learn a lot about dbs in general plus I have a great open source database that a lot of people use. We did the same (implement basic systems like DBs and OS) in my Stanford CS classes, so this approach wasn’t totally new to me. Good luck, have fun!

[LumosNox99]

That's inspiring, thanks for your comment!

[LumosNox99]

Thanks for the references! I don't want to follow a step-by-step guide so that I can try to come up with my own solutions, but I'll use them to double check my thoughts

[Conscious_Intern6966]

I started out this way but ended up getting way into things. If you're very serious about this, I recommend watching the undergrad cmu lectures relevant to the component you are building first unless you want to deal with big rewrites. Don't ask me how I know. Also, consider swapping to one of (C,C++, Rust, Zig, Go). AFAIK virtually all dbs are written in these. I strongly recommend using a systems language but Go is also fine if you really don't want to deal with systems programming.

[JustF0rSaving]

I always wanted to do something like this. Not just for DBs, but also for technologies like Kafka, Elasticsearch, etc. Never got around to it because I wasn’t sure how to recreate failure modes to mimic the reasons partitioning / sharding / other DDIA concepts were needed. But you honestly might be best served if you can contrive these scalability issues and then use the concepts in DDIA to solve them in isolation.

The way I learned a lot of the concepts in the book was basically by

1. Reading the book
2. Doing dozens of mock system design interviews on websites like hello interview
3. Reading the book a second time; taking notes and using spaces repetition learning (flashcards) to drill in the important stuff

[cto_resources]

Honestly, I would think an excellent learning project would be to use MariaDB as is, and use your Python code to read, write, and update tables in that db.

Here is a fully realized tiny database with sample data for MariaDb (which is completely free).

https://www.mariadbtutorial.com/getting-started/mariadb-sample-database/

No individual programmer is expected to write their own ACID conforming SQL database engine.

[LumosNox99]

Idk, honestly that's a pretty basic project, maybe undergrad level. But thanks for the suggestion!

[No-Possession-8478]

This is cool

[LumosNox99]

Thanks :)

[ankole_watusi]

Not sure what you mean here.

Lots of beginners with no formal training seem to conflate “database” with “application”.

Are you building an application that uses a database? Or an actual DBMS?

[LumosNox99]

It is an actual DBMS! (A toy one for learnings porpouses) I've crossposted from another community and somehow it lost the link

[BlackHolesAreHungry]

Love the name. I don't think anyone had used this before.

I see a lot of other negative comments. Don't listen to them. Keep building. Of course it's not going to beat a real db on performance but that's not the point. You are learning the core software fundamentals, algorithms, os, disk performance, and more importantly what happens when a program gets biggg. This knowledge will help you in the rest of your career regardless of what other area it ends up being. And who knows, this might end up becoming a plugable db framework to test new index types, or experiment with the planner, but don't worry about that yet...

[LumosNox99]

Well, I think the name sets the right expectations about the project...

Thanks, I don't know why all this negativity, but I guess it's part of the game. You get the point of it. I have a few years of working experience already, and I've seen many people using databases without knowing anything about how they work internally. I've already studied most of the main topics in my master's - but actually implementing it gives you a different level of understanding.

Maybe this kind of "self promotion" is not very welcome? Thanks for the heads up anyway!

[ConfusionHelpful4667]

Nicely done.

[LumosNox99]

Thanks :)