Looking for an ORM that works good with typescript and that runs on adJ OpenBSD 7.7 - pasosdeJesus/stable-sl GitHub Wiki

We experimented by creating a new project and by incorporating an ORM to https://github.com/pasosdeJesus/stable-sl

Prism

It requires some binaries and there are no binaries for OpenBSD/adJ. I notice that it doesn't support FreeBSD either although there has been some work for that since 2020: https://github.com/prisma/prisma/issues/3091 It is complex that prism requires rust that is well know for its low portability. For example FreeBSD in in Tier 2 but OpenBSD is not even in tier 2.5 but in tier 3: https://rust-lang.github.io/rustup-components-history/

sequelize

It works in new project. Also gives presedence to define in javascript, but gives more control on the name of the tables and columns. With sequelize-cli provides up and down migration. However sequelize-cli always pluralizes in the migration that it creates, forcing manual edition. And the biggest issue is incompatibility with typescript:

Error: Please install pg package manually
    at getQuoteToBuy (services/sle.ts:69:22)
    at GET (app/api/quote_to_buy/route.ts:44:42)
  67 |   try {
  68 |     console.log("Antes se, DB_URL=", process.env.DB_URL)
> 69 |     const sequelize = new Sequelize(process.env.DB_URL, {
     |                      ^
  70 |       define: {
  71 |         freezeTableName: true, // model and table with same name
  72 |       },

TypeORM

It works in new project. It gives presedence to define tables in javascript and from there create in the Database. It has up an dow migrations. Problematic to incorporate in existing typescript projects, we could solve building problems but not runtime problem:

{
  "error": {
    "message": "No metadata for \"BuyQuote\" was found."
  }
}

drizzle

Up to now is the only one we could make work at build and at runtime inside the typescript project, however advancing we have encounterd problems with migrations, push/pull and with types. For example renaming a table cannot create a migration:

    TypeError: Cannot read properties of undefined (reading 'columns')
    at preparePgAlterColumns (/var/www/htdocs/stable-sl-des/packages/coordinator/node_modules/drizzle-kit/bin.cjs:27984:63)
    at /var/www/htdocs/stable-sl-des/packages/coordinator/node_modules/drizzle-kit/bin.cjs:30211:16
    at Array.map (<anonymous>)
...
  • Seems that people is using more yarn drizzle-kit push after changing db/schema.ts. Seems very hard for production sites to replicate a change.
  • yarn drizzle-kit pull will not use the schema configured in drizzle.config.ts but will write at drizzle/schema.ts and drizzle/relations.ts. I guess that gives chance to compare with db/schema.ts and then copy?
  • yarn drizzle-kit pull is interesting. HOwever seems more focused in the approach code leads, because there is on way purchasequote that the generated schema.ts uses for Javascript purchaseQuote, unless that name is used for the table, forcing that every query will have to be done with quotes i.e "purchaseQuote" . Or maybe pull was conceived to be run only once and after change manually db/schema and run push
  • The lack of proper migratios has been acknowledged and discussed by the developers and the community: https://github.com/drizzle-team/drizzle-orm/discussions/1339

Kysely

https://kysely.dev/

Although Kysely claims not to be an ORM, we have found that along with kysely-codegen and kysely-ctl it allows:

  • To have up and down migrations and mananging them from the command line
  • To generate database interfaces in typescript from the PostgreSQL database allowing that the database leads the development.

It doesn't support aggregate functions among others, but it has an execute method that allows to run raw SQL. The current documentation is not so easy, for example it doesn't have an example on how to use it to start a project, but we do that here:

% mkdir kysely-p1
% cd kysely-p1
% git init
% pnpm init
% echo node_modules >> .gitignore
% pnpm add dotenv kysely kysely-postgres-js pg
% pnpm add -D @types/node @types/pg kysely-codegen kysely-ctl typescript
% pnpm i
% npx tsc --init --pretty -t es2017 -m nodenext

Then edit package.json and tsconfig.json and improve them (for example by removing comments).

At this point you can create a user for PostgreSQL (e.g kysely)

% doas su - _postgresql
% createuser kysely -s -h /var/www/var/run/postgresql/ -U postgres
% psql -h /var/www/var/run/postgresql/ -U postgres
> alter user kysely with password 'MiClave';
> \q
% exit 

And to simplify interaction with the database, configure the new user and password in ~/.pgpass with:

% echo "*:*:*:kysely:MiClave" >> ~/.pgpass

And create the database, for example kyselyp1:

% createdb -U kysely -h /var/www/var/run/postgresql kyselyp1

Create the file .env.template with example of the configuration of the database:

DB_HOST=/var/www/var/run/postgresql/                                                                                                                            
DB_NAME=nombre_db                                                                                                                                               
DB_USER=usuario                                                                                                                                                 
DB_PASSWORD=clave 
DATABASE_URL=postgres://$DB_USER:$DB_PASSWORD@localhost/$DB_NAME

Copy it to .env making sure to exclude it from uploading to the git repository and fill it with the correct information of the database:

% echo .env >> .gitignore
% cp .env.template .env
% vi .env

Creates a configuration file for kysely-ctl at .config/kysely.config.ts with the following contents:

import { PostgresDialect } from 'kysely'                                                                                                                        
import { defineConfig, getKnexTimestampPrefix } from 'kysely-ctl'                                                                                               
import 'dotenv/config'                                                                                                                                          
import { Pool } from 'pg';

export default defineConfig({                                                                                                                                   
  dialect: new PostgresDialect({                                                                                                                                
    pool: new Pool({                                                                                                                                            
      host: process.env.DB_HOST,                                                                                                                                
      database: process.env.DB_NAME,                                                                                                                            
      user: process.env.DB_USER,                                                                                                                                
      password: process.env.DB_PASSWORD,                                                                                                                        
      port: 5432,                                                                                                                                               
    }),                                                                                                                                                         
  }),                                                                                                                                                           
  migrations: {                                                                                                                                                 
    migrationFolder: "migrations",                                                                                                                              
    getMigrationPrefix: getKnexTimestampPrefix,                                                                                                                 
  },
})

Generate a template for your first migration:

$ ./node_modules/.bin/kysely migrate:make create_purchasequote

And edit the generated file in the migrations directory (for example 20250607212916_create_purchasequote.ts) to create the table in the up method and to delete it in the down method:

import type { Kysely } from 'kysely'                                                                                                                            
                                                                                                                                                                
export async function up(db: Kysely<any>): Promise<void> {                                                                                                      
  // For more info, see: https://kysely.dev/docs/migrations                                                                                                     
  await db.schema                                                                                                                                               
    .createTable('purchasequote')                                                                                                                               
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('senderPhone', 'varchar(15)', (col) => col.notNull())                                                                                            
    .addColumn('senderName', 'varchar(80)', (col) => col.notNull())                                                                                             
    .addColumn('senderWallet', 'varchar(50)', (col) => col.notNull())                                                                                           
    .addColumn('usdPriceInSle', 'real', (col) => col.notNull())                                                                                                 
    .addColumn('maximum', 'real', (col) => col.notNull())                                                                                                       
    .addColumn('minimum', 'real', (col) => col.notNull())                                                                                                       
    .addColumn('timestamp', 'bigint', (col) => col.notNull())                                                                                                   
    .addColumn('token', 'varchar(32)', (col) => col.notNull())                                                                                                  
    .execute();                                                                                                                                                 
}                                                                                                                                                               
                                                                                                                                                                
export async function down(db: Kysely<any>): Promise<void> {                                                                                                    
  await db.schema.dropTable('purchasequote').execute()                                                                                                          
}

Run this migration with

node_modules/.bin/kysely migrate up
◐ Starting migration up                                                         
✔ Migration complete                                                            
ℹ Ran 1 migration:                                                                                                                                              
[✓] 20250607212916_create_purchasequote

After that you can inspect the database and the generated tables:

$ psql -U kysely -h /var/www/var/run/postgresql kyselyp1
#  \dt                                                                                                       
                List of relations                                                                                                                               
 Schema |         Name          | Type  | Owner                                                                                                                 
--------+-----------------------+-------+--------                                                                                                               
 public | kysely_migration      | table | kysely                                                                                                                
 public | kysely_migration_lock | table | kysely                                                                                                                
 public | purchasequote         | table | kysely                                                                                                                
(3 rows)                                            
# \d purchasequote                                                                                          
                      Table "public.purchasequote"                                                                                                              
    Column     |         Type          | Collation | Nullable | Default                                                                                         
---------------+-----------------------+-----------+----------+---------                                                                                        
 id            | integer               |           | not null | nextval('purchasequote_id_seq'::regclass)
 senderPhone   | character varying(15) |           | not null |                                                                                                 
 senderName    | character varying(80) |           | not null |                                                                                                 
 senderWallet  | character varying(50) |           | not null |                                                                                                 
 usdPriceInSle | real                  |           | not null |                                                                                                 
 maximum       | real                  |           | not null |                                                                                                 
 minimum       | real                  |           | not null | 
 timestamp     | bigint                |           | not null | 
 token         | character varying(32) |           | not null |
# \d kysely_migration
                   Table "public.kysely_migration"
  Column   |          Type          | Collation | Nullable | Default 
-----------+------------------------+-----------+----------+---------
 name      | character varying(255) |           | not null | 
 timestamp | character varying(255) |           | not null | 
Indexes:
    "kysely_migration_pkey" PRIMARY KEY, btree (name)
# \d kysely_migration_lock
                Table "public.kysely_migration_lock"
  Column   |          Type          | Collation | Nullable | Default 
-----------+------------------------+-----------+----------+---------
 id        | character varying(255) |           | not null | 
 is_locked | integer                |           | not null | 0
Indexes:
    "kysely_migration_lock_pkey" PRIMARY KEY, btree (id)
# select * from kysely_migration;
                name                 |        timestamp         
-------------------------------------+--------------------------
 20250607212916_create_purchasequote | 2025-06-07T22:19:26.589Z
(1 row)

Time: 0,672 ms
# select * from kysely_migration_lock;
       id       | is_locked 
----------------+-----------
 migration_lock |         0
(1 row)

Time: 0,508 ms

Using kysely-codegen generate interfaces for each table in the database in the file ./db/db.d.ts:

% mkdir db
% ./node_modules/.bin/kysely-codegen --out-file ./db/db.d.ts

Generate a seed file with some initial information in the tables:

node_modules/.bin/kysely seed:make firstquote

And edit the generated file (for example seeds/1749379714750_firstquote.ts) with:

import { Insertable, Kysely, PostgresDialect, sql } from 'kysely';
import { DB, Purchasequote } from './db/db.d.ts';

export async function seed(db: Kysely<any>): Promise<void> {
  let nquote:Insertable<Purchasequote> = {
    id: 1,
    minimum: 200,
    maximum: 20,
    senderName: "I",
    senderPhone: "1",
    senderWallet: "0xb",
    timestamp: "12",
    token: "u",
    usdPriceInSle: 22.3,
  }

  let iquote = await db
    .insertInto('purchasequote')
   .values(nquote)
   .returningAll()
   .executeTakeFirstOrThrow()
  console.log("After insert iquote=", iquote)

  // Serial not autoincremented because we set id:1 Need to update
  let rs=await sql<any>`SELECT setval('public.purchasequote_id_seq', MAX(id)) FROM public.purchasequote`.execute(db)
  console.log("rs=", rs)

}

And now to do the basic operations in index.ts:

import { Insertable, Kysely, PostgresDialect, sql } from 'kysely';
import 'dotenv/config'

import defineConfig from './.config/kysely.config.ts'
import { DB, Purchasequote } from './db/db.d.ts';

const db = new Kysely<DB>({
  dialect: defineConfig.dialect
})

let nquote:Insertable<Purchasequote> = {
  minimum: 200,
  maximum: 20,
  senderName: "E",
  senderPhone: "0",
  senderWallet: "0xa",
  timestamp: "11",
  token: "t",
  usdPriceInSle: 23.3,
}

let iquote = await db
  .insertInto('purchasequote')
  .values(nquote)
  .returningAll()
  .executeTakeFirstOrThrow()
console.log("After insert iquote=", iquote)

let rows = await db.selectFrom('purchasequote').selectAll().execute();
console.log("Query of all rows=", rows)

let crows = await sql<number[]>`select count(*) from purchasequote`.execute(db)
console.log("Count of rows=", crows.rows[0].count)

let uquote:Updatable<Purchasequote> = {
  minimum: 300,
  maximum: 30,
  usdPriceInSle: 24,
}
let rupdate=await db.updateTable('purchasequote').set(uquote).where('id', '=', iquote.id).execute()
console.log("After update rupdate=", rupdate)

rows = await db.selectFrom('purchasequote').selectAll().execute();
console.log("Query of all rows=", rows)

let rdelete = await db.deleteFrom('purchasequote').where('id', '=', iquote.id)
  .returningAll()
  .executeTakeFirst()
console.log("After delete rdelete=", rdelete)


crows = await sql<number[]>`select count(*) from purchasequote`.execute(db)
console.log("Count of rows=", crows.rows[0].count)

await db.destroy();

And run with

% tsx index
⚠️ **GitHub.com Fallback** ⚠️