Schemity - An offline ERD designer with real database migration support (MySQL & PostgreSQL)

Hey everyone,

I’ve been working on a desktop app called Schemity - an ERD (Entity Relationship Diagram) tool for database designers and developers.

Most ERD tools I’ve tried are either cloud-only, have rigid relations, hard to use or don’t actually connect to a real database. Schemity tries to fill that gap.

What it does:

  • Design database schemas visually with entities, fields, and relationships
  • Connect to MySQL or PostgreSQL (direct or via SSH tunnel)
  • Reverse-engineer an existing database into a diagram instantly
  • Generate and apply SQL migrations directly from diagram changes
  • Works fully offline - data is stored as local JSON files, no cloud, no account required

A few things I’m proud of:

  • Snap guides, entity color coding, custom relationship line waypoints
  • Unique-together, check constraints, and index management
  • Full undo/redo, copy/paste across diagrams, multi-tab editing
  • Keyboard-first design with vim-style navigation (h/j/k/l)
  • Entity templates for reusable field sets

Available on macOS, Windows, and Linux.

Would love to hear feedback, especially from anyone doing schema-heavy work. Happy to answer questions!

https://schemity.com

2 Likes

It looks great!
I’m not very familiar with RDBs, so all I can say is that your work looks excellent! :+1:

Thanks Joe! Really appreciate it :pray:

Schemity came out of my own frustration - every ERD tool I used always had something that didn’t fit my needs. I just wanted something that kept up with how I actually model domains and design my database, so I built it for myself. If you ever get to try it, I’d love to know what you think!

I completely understand your approach, which stemmed from frustration. I’ve been retired since 2015 and advise a few people for fun (e.g., here with Daynhauhoc) and occasionally develop apps for personal use.

I, too, became frustrated with RDBs, as their design is simply outdated in today’s IT world with objects, big data, images, sounds, and AI repositories. And like you, I’m tired of entity modeling, entity relationships, and all the unnecessary complexity associated with them. Therefore, I’m not very familiar with (modern) relational databases. Consequently, I lost interest in relational databases and RDB development since 2015 and developed my own object-oriented database (OODB) based on Java. The objects are independent entities and unaffected by external influences such as faulty updates or data loss due to partial deletions, etc.

Nevertheless, I would very much like to take a closer look at your “Schemity” (a nice nomination!), but my knowledge of RDBs is too rusty to adequately assess it. However, if you agree, I would try to introduce your Schemity to some forums in the US/EU for evaluation.

Thank you so much, Joe! I’d really appreciate it if you could introduce Schemity to some forums in the US/EU - that kind of exposure means a lot at this stage.

I’m also genuinely curious about your OODB work. The way you describe it - objects as independent entities, unaffected by faulty updates or partial deletions - reminds me a lot of the Aggregate pattern in Domain-Driven Design, where a root object owns and protects its internals, and all changes must go through it to preserve consistency. Is that something similar to what you had in mind when designing it?

You’re welcome!

Object is easy to understand as a complete immutable entity, and an OODB is comparable to a data warehouse containing various objects. Each object, much like a person, is identified by an unique name assigned to it. In relational databases (RDBs), this object name corresponds to the primary key (unique). The problems with RDBs begin with the fact that data can be accessed using many alternative keys. This exacerbates the situation.

A data warehouse contains objects of varying sizes, which can also include big data (such as audio or GIF/Video data). Therefore, an OODB is not so abstract as to be confusing or difficult to understand when viewed as a data warehouse.

For example, VinGroup is the OODB (data warehouse) that contains VinFast, VinHome, VinPearl, and so on. Each object—for example, VinFast—is a frame for different object instances such as VF3, VF6, VF8, and so forth. So, when you say VF3, you’re referring to the small, Jeep-like VinFast car. VF3 is an unique name. If a VF3 object needs to be repaired, only VF3 components are used, not those of other models. The object frame and the object instance ensure data consistency and correctness. By this, I mean “independent entities and unaffected by external influences such as faulty updates or data loss due to partial deletions, etc.”

An object instance is a stack of serializable data that can be easily created in C#, Python, Java, or even C++. Here’s an example in Java (POJO, Plain Old Java Object):

// The object
public class Tutorial implements java.io.Serializable {
  public int id;
  public String name;
}

// The application
import java.io.*;
public class JavaExample {
public static void main(String[] args) throws Exception {
  // Creating and initializing
  Tutorial obj = new Tutorial();
  obj.id = 1;
  obj.name = "java";
// Save to C:\example.txt
  ObjectOutputStream oos = new ObjectOutputStream(new    FileOutputStream("Example.txt", false));
  oos.writeObject(obj);
  oos.close();

// Read from C:\example.txt
  ObjectInputStream ois = new ObjectInputStream(new FileInputStream("Example.txt"));
  Tutorial does = (Tutorial) ois.readObject();
  ois.close();
  System.out.println("ID:" + tut.id + "Name:" + tut.name);
  }
}

or in PYTHON

import pickle
tutorial={"id:":1, "name:":"Python"}

f=open("examplePython.txt","wb")
pickle.dump(tutorial,f)
f.close()

f=open("examplePython.txt","rb")
d=pickle.load(f)
print (d)
f.close()

The data is stored in a RandomAccessFile, which contains two sections:

  • Object names and attributes (name length, data size, and pointers to the data in the data area)
  • Serialized data

RAF

1 Like

An OODB example: the zoo app for my grandson. This app uses big data (animal sounds, images, and wiki descriptions – all from the internet). On the far left is the OODB server running in JavaFX, the browser is a JavaFX WebView application, and the zoo app itself is a Swing application.

When an animal button is clicked, Zoo displays the image of the selected animal, and an MP3 file plays in the background, explaining to the child, for example, how a bison bellows.


The Animal Object:

public class Animal implements java.io.Serializable {
  private final long serialVersionUID = 1234L;
  public String name, pic, des, snd;
  
  public Animal(String[] an) {
    this.name = an[0];
    this.pic = an[1];
    this.des = an[2];
    this.snd = an[3];
  }
  public Animal(String name, String pic, String des, String snd) {
    this.name = name;
    this.pic = pic;
    this.des = des;
    this.snd = snd;
  }
  public Process browse( ) {
    if (des.indexOf("://") > 0 || !(new File(des)).exists()) try {
      return Runtime.getRuntime().exec("java Browser "+des+" "+name);
    } catch (Exception e) { }
    return null;
  }
  public boolean show( ) {
    try {
      if (Desktop.isDesktopSupported()) {
        Desktop desktop = Desktop.getDesktop();
        if (desktop.isSupported(Desktop.Action.OPEN)) 
        desktop.open(new File(des));
        return true;
      }        
    } catch (Exception e) { }
    return false;
  }

  public ImageIcon getImage( ) {
    try{
      int w, h;
      ByteArrayInputStream bis = null;
      if (pic.indexOf("://") > 0) {
        byte[] buf = new byte[65536];
        ByteArrayOutputStream bao = new ByteArrayOutputStream();
        InputStream is = (new URL(pic)).openStream();
        while ((w = is.read(buf)) != -1) bao.write(buf, 0, w);
        bis = new ByteArrayInputStream(bao.toByteArray());
      } else if ((new File(pic)).exists()) {
        FileInputStream fis = new FileInputStream(pic);
        byte[] buf = new byte[fis.available()];
        w = fis.read(buf);
        bis = new ByteArrayInputStream(buf, 0, w);
      } else { // something wrong with urlImage
        return null;
      }
      BufferedImage img = ImageIO.read(bis);
      double ratio = 1d;
      w = img.getWidth();
      h = img.getHeight();
      // resizing the image ?
      if (h > 700)  ratio = 0.55d;
      else if (h > 600)  ratio = 0.6d;
      else if (h > 500)  ratio = 0.7d;
      if (ratio == 1d) {
        if (w > 800) ratio = 0.6d;
        else if (w > 700) ratio = 0.7d;
      }
      if (ratio < 1d) {
        // resizing the image
        w = (int)(w * ratio);
        h = (int)(h * ratio);
        Image image = img.getScaledInstance(w, h, Image.SCALE_SMOOTH);
        img = new BufferedImage(w, h, BufferedImage.TYPE_INT_RGB);
        img.getGraphics().drawImage(image, 0, 0 , null);
      }
      return new ImageIcon(img);
    } catch (Exception e) { }
    return null;
  }
  // key is an URI with ending .mp3/.au/.wav
  // or                        =mp3/=au/=wav
  public void playSound( ) {
    if (snd == null || snd.length() == 0) {
      JOptionPane.showMessageDialog(null, name+" has NO SoundTrack. Sorry!!!");
      return;
    }
    java.util.concurrent.ForkJoinPool.commonPool().execute(()->{
      ODBInputStream bai;
      byte[] buf = new byte[32768];
      try {
        InputStream inp;
        File fi = new File(snd);
        int b = snd.indexOf("://");
        // download the song file ?
        if (snd.endsWith("mp3")) {
          MP3Player player;
          if (b > 0 || !fi.exists()) player = new MP3Player(new URL(snd));
          else player = new MP3Player(fi);
          player.play();
        } else if (snd.endsWith("wav")) {
          if (b > 0 || !fi.exists()) inp = (new URL(snd)).openStream();
          else inp = (InputStream)new FileInputStream(fi);
          ODBIOStream bao = new ODBIOStream();
          while ((b = inp.read(buf)) > 0) bao.write(buf, 0, b);
          inp.close();
          bai = new ODBInputStream(bao.toByteArray());
          AudioInputStream as = AudioSystem.getAudioInputStream(bai);
          AudioFormat af = as.getFormat();
          // get the Info of wav.file
          SourceDataLine sl = (SourceDataLine) AudioSystem.
          getLine(new DataLine.Info(SourceDataLine.class, af));
          sl.open(af);
          sl.start();
          // play the Sound.wav
          buf = new byte[bai.available()];
          b = as.read(buf);
          sl.write(buf, 0, b);
          sl.drain();
          sl.close();
          as.close();
        } else { // Ending With .au
          JOptionPane.showMessageDialog(null, "Sorry that Sun.audio Package is no more available...");
        }
      } catch (Exception e) {
        JOptionPane.showMessageDialog(null, "Unable to mame or to render the noise");
      }
    });
  }
}
2 Likes
83% thành viên diễn đàn không hỏi bài tập, còn bạn thì sao?