# Neon + Remix Integration Setup Guide This guide provides step-by-step instructions for integrating Neon serverless Postgres with a Remix application. ## Prerequisites - Node.js and npm installed - Remix project initialized - Neon account and project created ## Installation 1. Install required dependencies: ```bash npm install @neondatabase/serverless ``` ## Configuration 1. Create a `.env` file in your project root: ```env DATABASE_URL=postgresql://:@.neon.tech/?sslmode=require ``` 2. Create a database configuration file `app/lib/db.server.ts`: ```typescript import { neon } from '@neondatabase/serverless'; const sql = neon(process.env.DATABASE_URL!); export { sql }; ``` ## Usage Examples ### Basic Database Operations ```typescript // app/routes/users.tsx import { json, type LoaderFunctionArgs } from '@remix-run/node'; import { useLoaderData } from '@remix-run/react'; import { sql } from '~/lib/db.server'; interface User { id: number; name: string; email: string; } export async function loader({ request }: LoaderFunctionArgs) { try { const users = await sql` SELECT id, name, email FROM users ORDER BY name ASC `; return json({ users }); } catch (error: any) { throw new Error(`Failed to fetch users: ${error.message}`); } } export async function action({ request }: LoaderFunctionArgs) { const formData = await request.formData(); const action = formData.get('action'); try { switch (action) { case 'create': { const name = formData.get('name') as string; const email = formData.get('email') as string; const [user] = await sql` INSERT INTO users (name, email) VALUES (${name}, ${email}) RETURNING id, name, email `; return json({ user }); } case 'update': { const id = parseInt(formData.get('id') as string); const name = formData.get('name') as string; const email = formData.get('email') as string; const [user] = await sql` UPDATE users SET name = ${name}, email = ${email} WHERE id = ${id} RETURNING id, name, email `; return json({ user }); } case 'delete': { const id = parseInt(formData.get('id') as string); await sql`DELETE FROM users WHERE id = ${id}`; return json({ success: true }); } default: throw new Error('Invalid action'); } } catch (error: any) { throw new Error(`Failed to perform action: ${error.message}`); } } export default function Users() { const { users } = useLoaderData(); return ( Users Add User {users.map((user) => ( {user.name} ({user.email}) Delete ))} ); } ``` ### Database Utility Functions ```typescript // app/lib/db.utils.server.ts import { sql } from './db.server'; export async function withTransaction( callback: (transaction: typeof sql) => Promise ): Promise { try { await sql`BEGIN`; const result = await callback(sql); await sql`COMMIT`; return result; } catch (error) { await sql`ROLLBACK`; throw error; } } export async function createPaginatedQuery( baseQuery: string, page: number, pageSize: number, params: any[] = [] ): Promise<{ data: T[]; total: number; pages: number }> { const offset = (page - 1) * pageSize; const countQuery = `SELECT COUNT(*) as total FROM (${baseQuery}) as subquery`; const [{ total }] = await sql.raw(countQuery, params); const data = await sql.raw( `${baseQuery} LIMIT ${pageSize} OFFSET ${offset}`, params ); return { data, total: parseInt(total), pages: Math.ceil(total / pageSize) }; } export async function executeInBatch( items: T[], batchSize: number, callback: (batch: T[]) => Promise ): Promise { for (let i = 0; i < items.length; i += batchSize) { const batch = items.slice(i, i + batchSize); await callback(batch); } } ``` ## Best Practices 1. **Environment Configuration** - Use environment variables for database credentials - Never commit sensitive credentials to version control - Use different database branches for development/production 2. **Database Access** - Keep database logic in server-only files (*.server.ts) - Use typed queries with interfaces - Implement proper error handling 3. **Query Safety** - Use parameterized queries to prevent SQL injection - Validate input data before queries - Implement proper error handling 4. **Performance** - Use connection pooling when possible - Implement pagination for large datasets - Cache frequently accessed data 5. **Type Safety** - Define interfaces for database models - Use TypeScript's strict mode - Implement proper data validation ## Security Considerations 1. **Database Access** - Use least privilege database users - Enable SSL for database connections - Regularly rotate database credentials 2. **Query Safety** - Always use parameterized queries - Validate and sanitize all user inputs - Implement rate limiting 3. **Data Protection** - Encrypt sensitive data - Implement proper access controls - Regular security audits ## Troubleshooting Common issues and solutions: 1. **Connection Issues** - Verify database URL format - Check network connectivity - Ensure SSL is properly configured 2. **Query Errors** - Check SQL syntax - Verify table and column names - Validate data types 3. **Performance Problems** - Monitor query execution time - Check for missing indexes - Optimize large queries ## Additional Resources - [Neon Documentation](https://neon.tech/docs) - [Remix Documentation](https://remix.run/docs) - [TypeScript Documentation](https://www.typescriptlang.org/)