While I was building aihelperbot.com I did what I imagine many developers do: I used an ORM for everything database related. I used ORM to handle migrations, define schemas, insert data, update data, select data, etc. I didn’t think much about it.

It was only later while building aihelperbot.com I started to wonder if I really needed an ORM? Using AI more and more I started to realize that I could do most of the things I did with an ORM directly with SQL. And that I often could do it quicker because I didn’t have to lookup ORM specific syntax. I could just write SQL queries directly.

That doesn’t mean I dismiss ORMs entirely. An ORM like Prisma has useful features like having the entire database in code and the automatically generated types. This combined with basic and type-safe CRUD queries is where Prisma shines. However I often found myself spending more time trying to figure out how to do something with Prisma compared to bare-bone SQL approach. Things like reverting a failed migration can be a major headache or how to upsert data with multiple relations.

I doubt I would have started to question ORMs without AI. I originally learned SQL at university but I haven’t been using it actively for years. It was just something I put on my CV. Essentially all interactions with the database went through tools like Prisma, Sequelize or GraphQL. I didn’t feel confident and proficient enough using SQL to write queries by hand.

AI changed that for me and I have already started to incorporate SQL queries into my workflow. Concretely I build an admin dashboard with various useful statistics from the database. I used AI to generate the SQL queries (would have been much more cumbersome doing this through an ORM). The insights I have gained with the admin dashboard helped me understand site usage and improving pricing strategy. SQL is a powerful tool that can solve many problems and give many insights.

An alternative approach

To replace a comprehensive ORM like Prisma, you need two things (and possibly an AI Bot to help you with the SQL queries):

  • a database client that can run SQL queries
  • a migration tool (well, depends on your specific setup)

There are many database clients available. One that I like is Postgres.js. It offers a minimalist, transparent, and performant client that executes SQL directly instead of abstracting it away. The focus when interacting with the database shifts from a specific ORM implementation to actual SQL queries. It makes the code easier to understand.

For migrations Postgres.js suggestions a few tools that lets you write simple .sql migrations files. If you are working on a feature that requires a database migration, you can create a your_feature_name.sql in a dedicated migrations folder and add SQL migration code. The migration tool can then execute this during deployment. It doesn’t get simpler and more transparent than that. The advantages of this approach:

  • No ORM lock-in (including “mental” lock-in)
  • No ORM specific API to learn
  • Simplicity and transparency of SQL from queries to migrations
  • Better performance, less overhead and easier to optimize

Simplicity and transparency might sound like an empty phrases, but it enables you to read and understand your easily code. And that is a key pillar in building a maintainable codebase.

Building the database schema

To get started you need a database schema. Let’s say you want a users table, you can simply ask AI to create it:

create table users with id, name, email, email_verified, image, created_at, updated_at

And it will generate the following SQL query:

CREATE TABLE
  users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    email_verified BOOLEAN DEFAULT FALSE,
    image VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

You can use the same pattern for other tables and add them to a schema.sql file and execute it directly or pop it into the migration folder and have the migration tool run it.

Other types of migrations are done in the same simple way. Formulate what you desire and AI will generate the SQL query for you e.g. add is_admin to users, rename column name to username in users table or add create index for email on users table. They can be formulated using natural language and AI will interpret it and generate a SQL statement (you can also use pseudo SQL syntax e.g. get users from USA via users.country_location if you desire more specificity).

You can experiment with the wording and retry if you aren’t fully satisfied with the result.

Building the SQL queries

Most of the SQL queries I run are simple SELECT statements, e.g. getting user information, subscriptions, or products. This is easy for AI to generate. To make the generated SQL queries more precise, I have imported my database schema. In “beast mode” it will be automatically added as database context for the query. This ensures accurate SQL queries that you can copy and paste directly into your code.

A normal use case is a JOIN query. I use one for getting list of products joined with their prices for the subscription page:

all active products with their prices

This will generate the following SQL query (query snippet):

SELECT
  p.id,
  p.name,
  p.description,
  p.image,
  p.stripeId,
  pr.unitAmount,
  pr.recurring,
  pr.type,
  pr.metadata,
  pr.currency,
  pr.stripeId
FROM
  Product p
  JOIN Price pr ON p.id = pr.productId
WHERE
  p.active = TRUE
  AND pr.active = TRUE;

The similar query for writing this with Prisma would be (had to google to get the syntax right and still not really sure what happens behind the scenes):

const products = await prisma.product.findMany({
  where: { active: true },
  select: {
    name: true,
    description: true,
    prices: {
      select: {
        stripeId: true,
        unitAmount: true,
        currency: true,
        recurring: true,
        metadata: true,
        active: true,
      },
    },
  },
});

If you want type safety I would suggest creating a schema.ts file that contains all your database types. This can then be used when inserting/updating data, and selecting data.

For inserting you can also ask AI to wrap variables with ${} (or other patterns specific to the used database driver) so you can use it directly in your code (query snippet)]):

insert subscription and wrap variables with ${}
INSERT INTO Subscription (status,
  quantity,
  cancelAtPeriodEnd,
  createdAt,
  currentPeriodStart,
  currentPeriodEnd,
  endedAt,
  cancelAt,
  canceledAt,
  trialStart,
  trialEnd,
  updatedAt,
  priceId,
  userId,
  stripeId)
VALUES (${status}, ${quantity}, ${cancelAtPeriodEnd}, ${createdAt}, ${currentPeriodStart}, ${currentPeriodEnd}, ${endedAt}, ${cancelAt}, ${canceledAt}, ${trialStart}, ${trialEnd}, ${updatedAt}, ${priceId}, ${userId}, ${stripeId});

Conclusion

I still use Prisma on aihelperbot.com for now but will migrate to Postgres.js in the future when I have some free time. In the meantime I am using raw SQL queries via prisma.$queryRaw in places like the admin dashboard (which essentially is an array of SQL queries that I iterate over and print as HTML). AI has boosted both my productivity and capabilities and wouldn’t want start a new project without it.