Migrating from SQLite

sqlite3 mydb.db <<EOF
.mode json
.once users.json
SELECT * FROM users;
EOF

const sqlite3 = require("sqlite3");
const fs = require("fs");

const db = new sqlite3.Database("mydb.db");

db.all("SELECT * FROM users", (err, rows) => {
  fs.writeFileSync("users.json", JSON.stringify(rows, null, 2));
});

db.close();

toollessdb import mydb users users.json

-- SQLite
SELECT * FROM users WHERE age >= 18

// Toolless
users.find({ age: { $gte: 18 } }).toArray();

-- SQLite
SELECT * FROM users ORDER BY age DESC LIMIT 10

// Toolless
users.find({}).sort({ age: -1 }).limit(10).toArray();

-- SQLite
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')

// Toolless
users.insertOne({ name: "Alice", email: "alice@example.com" });

-- SQLite
UPDATE users SET role = 'admin' WHERE email = 'alice@example.com'

// Toolless
users.updateOne({ email: "alice@example.com" }, { $set: { role: "admin" } });

-- SQLite
DELETE FROM users WHERE active = 0

// Toolless
users.deleteMany({ active: false });

const transform = (rows) => {
  return rows.map((row) => {
    const doc = {};
    for (const [key, value] of Object.entries(row)) {
      if (value !== null) {
        doc[key] = value;
      }
    }
    return doc;
  });
};

const users = require("./users.json");

const transformed = users.map((user) => ({
  _id: `user_${user.id}`,
  name: user.name,
  email: user.email,
}));

// Join users and posts
const posts = require("./posts.json");
const users = require("./users.json");

const userMap = Object.fromEntries(users.map((u) => [u.id, u]));

const denormalized = posts.map((post) => ({
  _id: `post_${post.id}`,
  title: post.title,
  author: {
    _id: `user_${post.user_id}`,
    ...userMap[post.user_id],
  },
}));