Imagine a recycling bin where you clearly put a label saying “Plastic Bottles Only”, but when someone drops a piece of paper in, it silently accepts it without a single word of protest?

This is the spine-chilling experience when developers meet the SQLite type system for the first time.

If you are used to the strict customs officer style of PostgreSQL (where incorrect types are directly denied entry), SQLite’s casualness might make you question your life.

Even more terrifyingly, when you want to modify a table structure, it will tell you:

“The table cannot be modified directly. Please build a new house, move the furniture over, and then blow up the old house.”

SQLite Under the Hood Has Only 5 Storage Classes

No matter what fancy type names you declare in CREATE TABLE (VARCHAR(255), BIGINT, DECIMAL), SQLite under the hood only recognizes these 5 storage classes:

The types you set on columns are merely “suggestions” to SQLite, not “mandatory rules”.

This is called “Type Affinity”. SQLite will try to convert your data into the suggested type, but if it cannot, it will just stuff the original data in anyway without throwing any errors.

You can declare an age INTEGER column in SQLite and then insert the string 'forever eighteen'; it will accept it with pleasure.

Three Easiest Type Pitfalls to Step Into

Pitfall 1: No Native Boolean

SQLite has no boolean type. True and False can only be represented by the integers 1 and 0.

When you fetch data from SQLite using Node.js, you will get the number 1 or 0, not true or false.

If you directly perform a check like if (user.is_admin === true), it will never be true.

Pitfall 2: No Date/Time Type

SQLite has no date/time type. You can only store time as:

Never store dates in arbitrary custom formats like 2026/5/19 6:00 PM, otherwise data migration in the future will be a disaster.

Pitfall 3: Inserting a String into an Integer Column Won’t Throw an Error

In PostgreSQL, inserting a string into an INTEGER column throws an error immediately. But SQLite will only try to convert it silently, and if it fails, it accepts it as-is.

This means dirty data might quietly sneak into your database until one day your program crashes due to receiving an unexpected type, only then will you discover the issue.

Defensive Programming: Treating a Casual Database with a Strict Attitude

Faced with SQLite’s casualness, you must build strict defense mechanisms in Node.js development:

Moving the “gatekeeper of data validation” from the database layer to the Node.js application layer is a key strategy to leverage SQLite’s development speed while ensuring future scalability.

When using an ORM, as long as you declare type: 'boolean' in your code, the ORM automatically converts it to 1/0 when saving to SQLite, and converts it back to true/false when reading, perfectly masking the underlying type differences.

ALTER TABLE is Half-Baked: What Can and Cannot Be Modified

SQLite’s support for modifying table structures is very limited:

Once you need to make any of the “unsupported” modifications, SQLite requires you to execute the “recreate and move” strategy.

The Four-Step Recreate & Move: SQLite’s Table Upgrade Way

Since it cannot be modified directly, the standard practice recommended by the official docs is building a new house, moving the furniture, blowing up the old house, and posting the new doorplate:

These four steps must be executed in one breath; any power outage or application crash midway will result in data loss.

Ensuring Upgrades Don’t Lose Data: Two Safety Lines of Defense

Defense Line 1: Physical Defense, Copy the File Directly

SQLite is essentially just a file. Before executing any schema changes, simply make a copy of the .db file as a backup.

const fs = require('fs');
fs.copyFileSync('my_project.db', 'my_project_backup.db');

If things go wrong, replacing the file restores everything in an instant. This is an advantage that other large databases cannot offer.

Defense Line 2: Transaction Wrapper, The Database Time Machine

Wrap all migration steps inside a single Transaction; if any step fails, the entire process will automatically roll back as if nothing ever happened.

const Database = require('better-sqlite3');
const db = new Database('my_project.db');

const migrateData = db.transaction(() => {
  db.prepare(`
    CREATE TABLE users_new (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      age INTEGER NOT NULL DEFAULT 18
    )
  `).run();

  db.prepare(`
    INSERT INTO users_new (id, name, age)
    SELECT id, name, COALESCE(age, 18) FROM users
  `).run();

  db.prepare('DROP TABLE users').run();
  db.prepare('ALTER TABLE users_new RENAME TO users').run();
});

try {
  migrateData();
  console.log('Table upgraded successfully');
} catch (error) {
  console.error('Upgrade failed, data safely restored:', error.message);
}

“Backup file + Transaction binding” is the airbag of your database migration.

Control the Casual SQLite with a Strict Attitude

Harness the casual SQLite with a strict application-layer architecture to enjoy its lightning-fast development speed while avoiding future technical debt.

SQLite’s type system is highly casual, and ALTER TABLE has many limitations.

However, as long as you perform TypeScript type checking + Zod validation + ORM abstraction on the Node.js side, paired with the safety strategy of physical backup + Transaction, you can safely enjoy the development efficiency brought by SQLite while paving the way to migrate to PostgreSQL in the future.

Reference

• Datatypes In SQLite
• SQLite 数据类型 | 菜鸟教程
• Transaction
• Query Language Understood by SQLite
• SQLite - Wikipedia