Database

Launchway has pre-built configurations that support and Postgres and SQLite databases. MySQL will be supported in the future

ORM

Launchway uses Drizzle (opens in a new tab) as the ORM. Drizzle is an extremely flexible Typescript ORM that supports traditional relational ORM queries and an SQL-like query builder, all with complete type-safety.

// Relational
const result = await db.query.users.findMany({
  with: {
    issues: true,
  },
})
 
// SQL-like
const result = await db
  .select()
  .from(UsersTable)
  .innerJoin(IssuesTable, eq(IssuesTable.userId, UsersTable.id))

All queries that come with Launchway use the SQL-like syntax as a matter of preference, but they can all be easily swapped out for their relational equivalent.

Schema

The schema is defined Typescript in the app/drizzle/schema.ts file. This is where you define the tables, indexes, constraints, foreign keys and enums. The syntax and methods used to generate the schema for different SQL dialects (Postgres, SQLite, MySQL etc.) is slightly different and if you change database dialect this will need to change.

The npm run init setup script will ask if you're using Postgres or SQLite and will include a predefined for script for both. At the time of writing, there is no predefined schema for MySQL, but it is coming soon.

Migrations

When you make any changes to your schema, you can automatically generate and run migrations against your database using the following built-in commands

# Generate - This will generate a .sql file with your changes
npm run db:generate
 
# Migrate - This will run the migrations against the database you've defined in your .env file
npm run db:migrate

Conventions

Drizzle is never directly invoked within an action or loader, instead all the database methods for reading, inserting, updating and deleting data live in the app/services/db/ directory (the convention is one file per table). This small abstraction makes it easier to reuse database methods, separate concerns of data access logic and route handling, and refactor the codebase or queries.

The general naming convention for database methods is in the format [find/insert/delete] $object by $key e.g. findApiKeyById, findUserByEmail, insertIssue, deleteVerificationToken. This is not strictly enforced, but having a consistent naming scheme for this makes it easier to navigate and makes generating new database operations with LLMs way easier.

Partial selects

It's highly encouraged to only select what you need from the database when querying. Rather than selecting all columns from a table like this

// SELECT * FROM users WHERE id = 1
db.select().from(UsersTable).where(eq(UsersTable.id, 1))

prefer partial selects like this

// SELECT id, first_name, last_name FROM users WHERE id = 1
db.select({
  id: UsersTable.id,
  firstName: UsersTable.firstName,
  lastName: UsersTable.lastName,
})
  .from(UsersTable)
  .where(eq(UsersTable.id, 1))

This comes with a few benefits:

• Reduces the amount of data transferred between the database and the app
• Decreases the amount of time spent serializing and deserializing data
• Helps prevent unintended data exposure by not retrieving sensitive fields unnecessarily (e.g. SELECT * FROM users will return the hashed password field to your client-side code)
• Explicit field declarations improves readability and maintainability of queries

Browsing the database

Drizzle has a built-in database browser called Drizzle Studio (opens in a new tab). This reads your database from your configuration file and sets up a local web server, so you can view and manage the database set in your .env from your local machine. This is the same database browser used in production by both Turso (opens in a new tab) and Neon (opens in a new tab).

To launch it just run

npm run drizzle:browser