Skip to main content

AutoCRUD

AutoCRUD looks at tables, and their indexes and creates a functional interface allowing you to work with tables in a similar fashion to KV-stores.

This is easier than writing your own CRUD methods, and easier that an ORM, as it creates flexible database access:

  • Without you writing any SQL or wrapper functions
  • Without you specifiying any 'model' or 'pojo' data holders
  • Against your existing database, without making any schema changes
  • Without learning yet another 'schema language'

All you need are tables with indexes. Yes, primary keys count!

Requirements​

You are going to need:

  • NodeJs >= 18
  • PostgreSQL >= 14

These examples assume you are running locally with your shell able to connect to your PostgreSQL with:

psql

Create a new database dvdrental on your local PostgeSQL server.

curl "https://embracesql.github.io/dvdrental.sql" | psql

Steps​

Here is a step by step guide to create a command line node program to access the dvdrental sample database.

Generate an Node Database​

Go into a nice blank directory.

npm install @embracesql/express
npm pkg set type=module
mkdir -p ./src
npx embracesqlcli generate node --database postgres://postgres:postgres@localhost/dvdrental > ./src/dvdrental.ts

Code up a Node Program​

We provide handy tsconfig settings that work great with tsx and typescript 5+.

Create two files as shown.

tsconfig.json
{
  "extends": "@embracesql/shared/tsconfig/tsconfig.json"
}
./src/autocrud.ts
import { Database } from "./dvdrental";

// connect
const database = await Database.connect(
  "postgres://postgres:postgres@localhost:5432/dvdrental",
);
// (C) creating a new row
const theBob = await database.Public.Tables.Actor.create({
  firstName: "Bob",
  lastName: "Hope",
});
console.log(theBob);
// (R) read by primary key
const theBobAgain = await database.Public.Tables.Actor.ActorPkey.read({
  actorId: theBob.actorId,
});
console.log(theBobAgain);

// (U) or upserting -- no new row, turns into an update
theBob.firstName = "Robert";
const theRobert = await database.Public.Tables.Actor.create(theBob);
console.log(theRobert);

// (U) or good old fashioned update by key
const theRoberto = await database.Public.Tables.Actor.ActorPkey.update(
  { actorId: theRobert.actorId },
  { firstName: "Roberto" },
);
console.log(theRoberto);

// (D) finally delete
const deleted = await database.Public.Tables.Actor.ActorPkey.delete({
  actorId: theRoberto.actorId,
});
// the modified row is always returned -- which is very convenient
// to use in client application state
console.log(deleted);

await database.disconnect();

Test It!​

Start that server:

npx tsx ./src/autocrud.ts

Details​

AutoCRUD generates per schema, per table, per index function calls to:

  • Create via INSERT, with ON CONFLICT support
  • Read via SELECT
  • Update via UPDATE
  • Delete via DELETE

You call these functions with TypeScript, with strong types for inputs and outputs -- and of course intellisense.

This isn't like an ORM where you 'make sql with functions', it's like more like the database is exposed as a key-value store by unique index, along with result sets of objects for non-unique indexes.

You aren't generating SQL at runtime, instead the sql is pregenerated. This helps on performance, and debuggability - the generated database access code is just a single TypeScript file. Go ahead and set breakpoints in it!

All methods utilize Postgres RETURNING so you get the rows modified back in a single trip to the database. You'll appreciate this if you have an active database with triggers and functions that modifiy columns separate from your TypeScript code. Simply cache/stash/return the modified rows!

Unique indexes, like primary keys, return a single row {Schema}.{Table} -- saving you the [0] game.

Non unique indexes return a List<{Schema}.{Table}>.

Reads​

Reads work along indexes with methods generated of the format {Schema}.{Table}.by{IndexColumns}.

If you need another way to read rows, just add an index.

Deletes​

Deletes work along indexes with methods generated of the format {Schema}.{Table}.deleteBy{IndexColumns}.

Deletes get rid of rows. Nothing surprising here, unless you have foreign keys without ON DELETE CASCADE, in which case you won't actually be deleting and rows after all 😉.

Updates​

Deletes work along indexes with methods generated of the format {Schema}.{Table}.updateBy{IndexColumns}.

Updates allow partial of full updates, passing in values that can be any subset of columns that you like. The update statement is smart in that it will take your new passed in values, or leave the existing database value without generating a dynamic sql string.

This does rely on TypeScript properties you do NOT want to change to be undefined on the passed in values. The easiest way to get this is to have an inline {column: value} passed to values for what you want to change, and omit columns you do not want to change.

To null a column, pass null for the value like {email: null}.

If you have an active database with constraints and triggers, updates can fail, raising an exception back.

Creates​

Creates have one method per table generated of the format {Schema}.{Table}.create.

Pass in values, get a whole new row from the database. If the row exists, it will upsert automatically.

Creating rows recognizes defaults.

Primary keys often have defaults defined with

  • SMALLSERIAL
  • SERIAL
  • BIGSERIAL
  • DEFAULT nextval() from a sequence.

These keys are created from defaults in the database. This means primary key attributes are optional in TypeScript, and you probably want to leave them undefined in order to have the database create you a primary key.

Columns beyond the primary key can have defaults as well, created_date is a common pattern here. To get the database default, just don't pass a value for the column.

The good news is the RETURNING row will show you the primary key as created by the database.

You can pass in values with the primary key - particularly when you intend to get an upsert. Or if you are on purpose 'setting' the primary key and bypassing the default.

Upserts​

Creates automatically upsert, turning the INSERT into an UPDATE, RETURNING the modified row.

Transactions​

What would a database library be without transactions?

Just pass a callback function, throw to abort, return clean to commit.

./src/transactions.ts
import { Database } from "./dvdrental";

// connect
const database = await Database.connect(
  "postgres://postgres:postgres@localhost:5432/dvdrental",
);

// update -- auto commits when the block exists
// this is a no-news-is-good-news transaction approach
const updatedCustomer = await database.withTransaction(async (db) => {
  return await db.Public.Tables.Customer.CustomerPkey.update(
    {
      customerId: 1,
    },
    { activebool: false },
  );
});
console.log(updatedCustomer);

// or to not update in a 'failed' transaction
try {
  await database.withTransaction(async (db) => {
    await db.Public.Tables.Customer.CustomerPkey.update(
      {
        customerId: 1,
      },
      { activebool: true },
    );
    throw new Error("aha");
  });
} catch {
  console.log("no noes... 😱");
}

// and let's verify that
const notUpdatedCustomer =
  await database.Public.Tables.Customer.CustomerPkey.read({
    customerId: 1,
  });
console.log(notUpdatedCustomer);

await database.disconnect();